Raspberry Pi Learning Note--python sqlite insertion temperature record

0 PrefaceThis article uses Python to insert the Raspberry Pi temperature data into the SQLite database, and the SQLite database contains a record table with only three fields-parameter name, time and temperature values. This article mainly explains Python operation SQLite's specific method, because the online material is numerous, repeats the part no longer repeats only achieves the concrete situation concrete analysis."Related blog posts""Raspberry Pi Study notes-index blog post"-More posts please follow. "Raspberry Pi Study notes--Get Raspberry Pi CPU temperature" "Raspberry Pi Learning notes--timed to Yeelink upload Raspberry Pi cpu temperature" "Raspberry Pi Learning note--sqlite operation brief"
1 Creating a database and empty tables"Create-table-only.sql"

PRAGMA Foreign_keys=off; BEGIN TRANSACTION; CREATE TABLE temps (    name default ' Rpi.cpu ',     tdatetime datetime default (' Now ', ' localtime '),     Temperature NUMERIC not NULL); COMMIT;

"Brief description"The "1" database contains three fields, two of which have default values.    The "2" Temperature value cannot be empty. The "3" default is combined with a NOT NULL constraint to simplify the insert operation.
"create-table-only.sh"#!/bin/shdbname= "Cpu.db" rm-f $DBNAMEecho start inserting data sqlite3 $DBNAME < Create-table-only.sqlecho insert Complete"Brief description""1" chmod a+x create-table-only.sh Add executable permission "2" after running, create a database named Cpu.db in the same directory
2 python insert operationCreate a new file named db-insert-temp.py with the following details:

#!/usr/bin/env python#-*-coding:utf-8-*-import timeimport sqlite3def get_cpu_temp ():    # Opens file    = open ("/sys /class/thermal/thermal_zone0/temp ")    # Read result and convert to floating point    temp = float (file.read ())/$    # Close File    File.close ()    return tempdef insert_cpu_temp (temp):    # Connect to Database    conn=sqlite3.connect (' cpu.db ')    curs= Conn.cursor ()          # Insert Database    strtemp = "%.1f"% (temp);    Curs.execute ("INSERT into temps (temperature) VALUES ((?))", (strtemp,))    conn.commit ()        # Close the database    Conn.close () def main (): While    True:        temp = get_cpu_temp ()        insert_cpu_temp (temp)        time.sleep (5* if __name__ = = ' __main__ ':    Main ()

"Simple description"The "1" conn=sqlite3.connect (' cpu.db ') connects to the database. "2" curs=conn.cursor () gets the cursor.    The cursor can be understood as a file manipulation handle, and with it you can fiddle with "cpu.db". "3" focuses on Curs.execute ("INSERT into temps (temperature) VALUES ((?)) ", (strtemp,) "3.1" takes advantage of the default constraints of name and Tdatetime, where only the insertion temperature value of the ' 3.2 ' execute function must use a placeholder (?), and the string formatting method cannot be used. If there are two placeholders, it is recommended that you write the values ((?),(?)) "3.3" Parameterized Query (strtemp,) is a python tuple data type, if there is only one elementcomma must not be omitted。 If there are two parameters, it is recommended to write this (Paraa,parab) "4" Do not forget to submit the operationConn. Commit () "5" Do not forget to close the operationConn. Close ()"Query Insert Results"You can enter Sqlite3 cpu-temp "select * from Temps;" In the console and return the results as follows. rpi.cpu|2014-08-03 10:40:40|48.7rpi.cpu|2014-08-03 10:41:41|48.7rpi.cpu|2014-08-03 10:42:41|49.2RPi.CPU| 2014-08-03 10:43:41|48.7rpi.cpu|2014-08-03 10:44:41|48.7rpi.cpu|2014-08-03 10:45:41|49.2
3 boot background run"Startup script"--auto-start.shCreate a new auto-start.sh script in this directory, as follows #!/bin/bashcd/home/pi/python-works/cpu-temp python db-insert-temp.py &"Simple description""1" Python db-insert-temp.py & Background Run db-insert-temp.py "2" Do not forget to modify the execution permissions, chmod a+x auto-start.sh
"Modify startup Item"--/etc/rc.localAdd the # Raspberry Pi temperature to the database before the last line exit 0/home/pi/python-works/cpu-temp/auto-start.sh start
"Restart Raspberry Pi"sudo reboot again after restarting the contents of the table, you will find an increase in the record one article.
4 SummaryThe "1" Python sqlite parameterized operation must use placeholders?    The "2" parameter is a Python tuple type, and you need to be aware that the number of elements is 1. The "3" insert operation was successful, with flask attempting to query the operation.
5 References"1" SQLite Tutorial | W3cschool Rookie Tutorial "2" Python SQLite3 Help documentation