使用 reportlab 绘制 sqlite3 数据 [英] graphing sqlite3 data with reportlab
问题描述
试图从 sqlite3 数据库中绘制简单的温度数据.python的新手，不确定我做错了什么.我的 sql 数据如下所示:
trying to lineplot simple temperature data from a sqlite3 database. New to python, not sure what I'm doing wrong. my data from sql looks like:
[(70.8,), (70.8,), (70.9,), (71.0,), (71.0,), (71.2,), (71.2,), (71.2,), (71.4,), (71.7,), (71.7,), (72.0,), (72.0,), (72.0,), (72.2,), (72.2,), (71.9,), (72.0,), (72.0,), (72.2,), (72.2,), (72.2,), (72.2,), (71.7,), (71.9,), (71.9,), (72.0,), (72.0,), (72.3,), (72.0,), (72.0,), (72.2,), (72.2,), (72.2,), (72.3,), (72.2,), (72.2,), (72.3,), (72.3,), (72.3,), (72.3,), (72.4,), (72.5,), (72.4,), (72.5,), (72.5,), (72.6,), (72.7,), (72.7,), (73.0,), (73.0,), (73.0,), (73.1,), (73.1,), (73.3,), (73.5,), (73.5,), (73.6,), (73.7,), (73.8,), (73.7,), (73.7,), (73.7,), (73.8,), (73.7,), (73.7,), (73.6,), (73.7,), (73.8,), (73.8,), (73.8,), (73.8,), (73.7,), (73.6,), (73.6,), (73.5,), (73.6,), (73.5,), (73.5,), (73.5,), (73.5,), (73.2,), (73.1,), (73.0,), (73.0,), (72.7,), (72.8,), (72.8,), (72.8,), (72.7,), (72.7,), (72.6,), (72.6,), (72.7,), (72.7,), (72.7,), (72.7,), (72.7,), (72.8,), (72.7,), (72.8,), (72.8,), (72.8,), (72.8,), (72.8,), (72.8,), (72.7,), (72.7,), (72.7,), (73.0,), (73.0,), (73.0,), (73.0,), (73.4,), (73.5,), (73.5,), (73.6,), (73.5,), (73.6,), (73.5,), (73.5,), (73.6,), (73.6,), (73.5,), (73.5,), (73.6,), (73.5,), (73.6,), (73.6,), (73.6,), (73.6,), (73.5,)]
[(70.8,), (70.8,), (70.9,), (71.0,), (71.0,), (71.2,), (71.2,), (71.2,), (71.4,), (71.7,), (71.7,), (72.0,), (72.0,), (72.0,), (72.2,), (72.2,), (71.9,), (72.0,), (72.0,), (72.2,), (72.2,), (72.2,), (72.2,), (71.7,), (71.9,), (71.9,), (72.0,), (72.0,), (72.3,), (72.0,), (72.0,), (72.2,), (72.2,), (72.2,), (72.3,), (72.2,), (72.2,), (72.3,), (72.3,), (72.3,), (72.3,), (72.4,), (72.5,), (72.4,), (72.5,), (72.5,), (72.6,), (72.7,), (72.7,), (73.0,), (73.0,), (73.0,), (73.1,), (73.1,), (73.3,), (73.5,), (73.5,), (73.6,), (73.7,), (73.8,), (73.7,), (73.7,), (73.7,), (73.8,), (73.7,), (73.7,), (73.6,), (73.7,), (73.8,), (73.8,), (73.8,), (73.8,), (73.7,), (73.6,), (73.6,), (73.5,), (73.6,), (73.5,), (73.5,), (73.5,), (73.5,), (73.2,), (73.1,), (73.0,), (73.0,), (72.7,), (72.8,), (72.8,), (72.8,), (72.7,), (72.7,), (72.6,), (72.6,), (72.7,), (72.7,), (72.7,), (72.7,), (72.7,), (72.8,), (72.7,), (72.8,), (72.8,), (72.8,), (72.8,), (72.8,), (72.8,), (72.7,), (72.7,), (72.7,), (73.0,), (73.0,), (73.0,), (73.0,), (73.4,), (73.5,), (73.5,), (73.6,), (73.5,), (73.6,), (73.5,), (73.5,), (73.6,), (73.6,), (73.5,), (73.5,), (73.6,), (73.5,), (73.6,), (73.6,), (73.6,), (73.6,), (73.5,)]
我得到的错误信息是:
Traceback (most recent call last):
File "db-test5.py", line 93, in <module>
renderPM.drawToFile(d, 'rework6.png')
File "/usr/lib/python2.7/dist-packages/reportlab/graphics/renderPM.py", line 655, in drawToFile
c = drawToPMCanvas(d, dpi=dpi, bg=bg, configPIL=configPIL,showBoundary=showBoundary)
File "/usr/lib/python2.7/dist-packages/reportlab/graphics/renderPM.py", line 641, in drawToPMCanvas
draw(d, c, 0, 0, showBoundary=showBoundary)
File "/usr/lib/python2.7/dist-packages/reportlab/graphics/renderPM.py", line 50, in draw
R.draw(renderScaledDrawing(drawing), canvas, x, y, showBoundary=showBoundary)
File "/usr/lib/python2.7/dist-packages/reportlab/graphics/renderbase.py", line 199, in draw
self.drawNode(drawing)
File "/usr/lib/python2.7/dist-packages/reportlab/graphics/renderPM.py", line 109, in drawNode
self.drawNodeDispatcher(node)
File "/usr/lib/python2.7/dist-packages/reportlab/graphics/renderbase.py", line 280, in drawNodeDispatcher
self.drawGroup(node)
File "/usr/lib/python2.7/dist-packages/reportlab/graphics/renderbase.py", line 297, in drawGroup
node = _expandUserNode(node,canvas)
File "/usr/lib/python2.7/dist-packages/reportlab/graphics/renderbase.py", line 161, in _expandUserNode
node = node.provideNode()
File "/usr/lib/python2.7/dist-packages/reportlab/graphics/widgetbase.py", line 150, in provideNode
return self.draw()
File "/usr/lib/python2.7/dist-packages/reportlab/graphics/charts/lineplots.py", line 351, in draw
self.calcPositions()
File "/usr/lib/python2.7/dist-packages/reportlab/graphics/charts/lineplots.py", line 194, in calcPositions
if isinstance(datum[0],str):
TypeError: 'float' object has no attribute '__getitem__'
我的代码是:
#!/usr/bin/python
from reportlab.graphics.charts.lineplots import LinePlot, AreaLinePlot
from reportlab.graphics.shapes import Drawing
from reportlab.lib import colors
from random import randint
from datetime import date, timedelta
from reportlab.graphics import renderPM
import sqlite3, os, datetime, time
##############################################
######## time functions ##################
##############################################
def RecYear():
return datetime.date.today().strftime("%Y")
def RecMonth():
return datetime.date.today().strftime("%m")
def RecFullDateTime():
return time.time()
def RecTime():
return datetime.datetime.now().strftime("%H:%M:%S")
def RecDate():
return datetime.datetime.now().strftime("%m-%d-%Y")
def RecDay():
return datetime.date.today().strftime("%d")
currentdate_ds18b20=int(datetime.date.today().strftime("%Y"))*1000+ int(time.localtime().tm_yday)
os.system('clear')
sqlite_file="2-database_archived/archived_2017-02-27-HMS-ds18b20-v2.2-DATABASE.db"
table_name = "DS18B20temps"
print "filename = ", sqlite_file
print "table = ", table_name
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()
print "db open"
### Calculate total length of table
c.execute('SELECT * FROM {tn}'.\
format(tn=table_name))
print "the length of the database is:", len(c.fetchall()), " rows\n"
###Calculate total number of kitchen temp readings
c.execute('SELECT ({coi}) FROM {tn} WHERE {cn}="Kitchen"'.\
format(coi="temp", tn=table_name, cn="sensor_name"))
kitchen_temp_list = c.fetchall()
print "the number of kitchen temp readings = ", len(kitchen_temp_list)
new_kitchen_list = list(sum(kitchen_temp_list, ()))
print "averge temp = ", (sum(new_kitchen_list))/len(new_kitchen_list)
print ""
# 4) Retrieve all IDs of entries between 6am and noon
c.execute("SELECT {idf} FROM {tn} WHERE {cnn}='Kitchen' AND {cn} BETWEEN '18:00:00' AND '23:59:59'".\
format(idf="temp", tn=table_name, cnn="sensor_name", cn="recorded_time"))
all_date_times = c.fetchall()
print "readings from 6 pm to midnight = ", len(all_date_times)
print all_date_times
width=800
height=500
d = Drawing(width, height)
lp = LinePlot()
lp.data=all_date_times
lp.width= 700
lp.height = 400
lp.xValueAxis.valueMin = 0
lp.xValueAxis.valueMax =400
#lp.xValueAxis.valueSteps = [6,8,10,12,14,16,18,20]
lp.yValueAxis.valueMin = 0
lp.yValueAxis.valueMax =200
lp.strokeColor=colors.black
lp.fillColor=colors.grey
#lp.reversePlotOrder = False
lp.joinedLines=1
d.add(lp)
renderPM.drawToFile(d, 'rework6.png')
每当我传递错误数据时，它都会在 renderPM 中出错，但我很难理解错误.我的预感是我传递的数据不适合线图?
It seams like whenever I pass bad data it craps out at renderPM, but I'm having a hard time understanding the error. My hunch is the data I'm passing is not appropriate for a line plot?
非常感谢任何帮助......
any help greatly appreciated......
推荐答案
在阅读了报告实验室文档并尝试了他们的示例后，我确定问题在于我的数据的呈现方式.使用报告实验室文档中的示例数据，我能够消除调用 renderPM 时遇到的错误.
after going through the report lab documentation and experimenting with their examples I determined the problem was with how my data was presented. Using the sample data from the report lab documentation I was able to eliminate the error I was getting when renderPM was called.
然后基于 Muhammad Haseeb Khan 和 Paul Rooney 在此发布
Then based on the help from Muhammad Haseeb Khan and Paul Rooney in this post
通过将 about 代码修改为:
I was able to data from [(70.1,), (71.1,),(71.1,)] to [(70.1, 71.1, 71.1)] by modifying the about code to:
c.execute("SELECT {idf} FROM {tn} WHERE {cnn}='Kitchen' AND {cn} BETWEEN '18:00:00' AND '23:59:59'".\
format(idf="temp", time="read_ID", tn=table_name, cnn="sensor_name",
cn="recorded_time"))
all_date_times = c.fetchall()
print "readings from 6 pm to midnight = ", len(all_date_times)
level1 = [list(row) for row in all_date_times]
level2=[i[0] for i in all_date_times]
level3=[tuple(level2)]
#### build report lab chart
drawing = Drawing(600, 400)
data = level3
lc = HorizontalLineChart()
lc.x = 25
lc.y = 25
lc.height = 450
lc.width = 550
lc.data = data
lc.joinedLines = 1
lc.valueAxis.valueMin = 70
lc.valueAxis.valueMax = 75
drawing.add(lc)
renderPM.drawToFile(drawing, 'chartgraphicfile.png')
这篇关于使用 reportlab 绘制 sqlite3 数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
