Raspberry Pi Learning Note--python sqlite query history temperature

0 PrefaceThis article through the Python SQLite query Raspberry Pi CPU temperature history data, in the previous blog post has introduced the Raspberry Pi CPU temperature acquisition, SQLite operation and the use of Python to insert historical data, the following describes how to query data, this article is mainly divided into three parts,    The first part is the query of all temperature records, the second section obtains the most recent hour data, and the third is the format of the obtained result as a dictionary type.    "Related blog post" "Raspberry Pi Study notes-index blog post"-More posts please follow. "Raspberry Pi Study notes--Get Raspberry Pi CPU temperature" "Raspberry Pi Study notes-timed to Yeelink upload Raspberry Pi cpu temperature" "Raspberry Pi Learning note--sqlite operation brief" "Raspberry Pi Learning Note--python sqlite insertion temperature Record"
1 Query record number of barsCreate a new file named query-cpu-temp.py with the following file contents.

#-*-coding: utf-8-*-
import sqlite3

# Connect to the database
con = sqlite3.connect ("cpu.db")
cur = con.cursor ()

name = 'RPi.CPU'
# Query the total number of records
cur.execute ("select count (*) from temps where name = (?);", (name,))
total = cur.fetchone ()

# Return tuple type
print type (total)
print type (total [0])
print total [0]

"Brief description""1" Cur.execute ("SELECT count (*) from Temps where name= (?);", (name,)) The total number of records in the query table for the field name is Rpi.cpu "2" Cur.fetchone ()get a record, the returned result is a tuple type.    "3" total[0], the returned result is a tuple type with only one element, Total[0] is the total number of records, and the type is int. "4" Returns the result <type ' tuple ' ><type ' int ' >166
2 Query The last hour of temperatureRe-Modify the query-cpu-temp.py, the following details

#-*-coding: utf-8-*-
import sqlite3

# Connect to the database
con = sqlite3.connect ("cpu.db")
cur = con.cursor ()

name = 'RPi.CPU'
# Query the database to get the record of the last hour
cur.execute ('' 'SELECT * FROM temps
                 WHERE name = (?) AND tdatetime> datetime ('now', 'localtime', '-1 hours')
                 ORDER BY tdatetime ASC; '' ', (name,))
# Get all results
rows = cur.fetchall ()

for row in rows:
     print row

"Brief description" "1" WHERE name= (?)    and Tdatetime > DateTime (' Now ', ' localtime ', '-1 hours '), query the temperature parameters of an hour ago, ' localtime ' represents the time of the current time zone. "2" Cur.fetchall () to obtain eligibleAll Records。 The result returned by "3" is a list type, and each element in the class table is a tuple type (U ' rpi.cpu ', U ' 2014-08-04 20:07:53 ', 46.5) (U ' rpi.cpu ', U ' 2014-08-04 20:12:53 ', 46.5) (U ') Rpi.cpu ', u ' 2014-08-04 20:17:53 ', 46.5) (U ' rpi.cpu ', U ' 2014-08-04 20:22:54 ', 47.1) (U ' rpi.cpu ', U ' 2014-08-04 20:27:54 ', 47.1) (U ' rpi.cpu ', U ' 2014-08-04 20:32:54 ', 47.6) (U ' rpi.cpu ', U ' 2014-08-04 20:37:54 ', 46.5) (U ' rpi.cpu ', U ' 2014-08-04 20:42:54 ', 47.6) (U ' rpi.cpu ', U ' 2014-08-04 20:47:54 ', 47.1) (U ' rpi.cpu ', U ' 2014-08-04 20:52:54 ', 47.1) (U ' rpi.cpu ', U ' 2014-08-04 20:57:54 ', 47.6) (U ' rpi.cpu ', U ' 2014-08-04 21:02:55 ', 47.6)
3 factory methods converted to dictionary formatIn the process of network transmission, most of them are exchanged in JSON data format, and the dictionary format in Python can be converted to JSON format better.

#-*-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

# Connect to the database
con = sqlite3.connect ("cpu.db")
# Specify a factory method
con.row_factory = dict_factory
cur = con.cursor ()

name = 'RPi.CPU'
# Query the database to get the record of the last hour
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 

"Simple description"    "1" def dict_factory (cursor, row): The tuple type is converted to a dictionary type, which comes from the Python sqlite documentation.     "2" con.row_factory = Dict_factory Specifies the factory method     "3" Returns the result, note () becomes {}, indicating that the returned result is of the dictionary type. {' 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} {' Tdatetime ': U ' 2014-08-04 21:02:55 ', ' name ': U ' rpi.cpu ', ' Temperature ': 47.6} {' Tdatetime ': U ' 2014-08-04 21:07:55 ', ' name ': U ' rpi.cpu ', ' Temperature ': 47.1} {' Tdatetime ': U ' 2014-08-04 21:12:55 ',' Name ': U ' rpi.cpu ', ' temperature ': 47.1}{' tdatetime ': U ' 2014-08-04 21:17:55 ', ' name ': U ' rpi.cpu ', ' Temperature ': 47.6}
4 SummaryThe "1" method for obtaining database records is Fetchone and Fetchall. "2" returns by defaultmeta-Group results。 "3" needs to convert the tuple type to a Row_factory property by modifying theDictionary type。
5 References"1" Python SQLite description document--SQLITE3. Connection.row_factory "2" Accessing a SQLite database with Python