树莓派学习笔记——Python SQLite查询历史温度

时间：2022-03-13 22:35

# -*- coding: utf-8 -*- import sqlite3 # 连接数据库 con = sqlite3.connect("cpu.db") cur = con.cursor() name = 'RPi.CPU' # 查询记录总数 cur.execute("select count(*) from temps where name=(?);", (name, )) total = cur.fetchone() # 返回元组类型 print type(total) print type(total[0]) print total[0]

# -*- coding: utf-8 -*- import sqlite3 # 连接数据库 con = sqlite3.connect("cpu.db") cur = con.cursor() name = 'RPi.CPU' # 查询数据库，获得最近一小时的记录 cur.execute('''SELECT * FROM temps WHERE name=(?) AND tdatetime > datetime('now', 'localtime', '-1 hours') ORDER BY tdatetime ASC;''', (name, )) # 获得所有结果 rows = cur.fetchall() for row in rows: print row

# -*- coding: utf-8 -*- import sqlite3 def dict_factory(cursor, row): d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d # 连接数据库 con = sqlite3.connect("cpu.db") # 指定工厂方法 con.row_factory = dict_factory cur = con.cursor() name = 'RPi.CPU' # 查询数据库，获得最近一小时的记录 cur.execute('''SELECT * FROM temps WHERE name=(?) AND tdatetime > datetime('now', 'localtime', '-1 hours') ORDER BY tdatetime ASC;''', (name, )) rows = cur.fetchall() for row in rows: print row

【简单说明】

【1】def dict_factory(cursor, row): 元组类型转换为字典类型，该函数来自python sqlite说明文档。

【2】con.row_factory = dict_factory 指定工厂方法

【3】返回结果，请注意()变为了{}，表明返回结果为字典类型。

{‘tdatetime‘: u‘2014-08-04 20:22:54‘, ‘name‘: u‘RPi.CPU‘, ‘temperature‘: 47.1}

{‘tdatetime‘: u‘2014-08-04 20:27:54‘, ‘name‘: u‘RPi.CPU‘, ‘temperature‘: 47.1}

{‘tdatetime‘: u‘2014-08-04 20:32:54‘, ‘name‘: u‘RPi.CPU‘, ‘temperature‘: 47.6}

{‘tdatetime‘: u‘2014-08-04 20:37:54‘, ‘name‘: u‘RPi.CPU‘, ‘temperature‘: 46.5}

{‘tdatetime‘: u‘2014-08-04 20:42:54‘, ‘name‘: u‘RPi.CPU‘, ‘temperature‘: 47.6}

{‘tdatetime‘: u‘2014-08-04 20:47:54‘, ‘name‘: u‘RPi.CPU‘, ‘temperature‘: 47.1}

{‘tdatetime‘: u‘2014-08-04 20:52:54‘, ‘name‘: u‘RPi.CPU‘, ‘temperature‘: 47.1}

{‘tdatetime‘: u‘2014-08-04 20:57:54‘, ‘name‘: u‘RPi.CPU‘, ‘temperature‘: 47.6}