0 PrefaceThis article describes how to use the SQLite database to store CPU temperature data in a Raspberry Pi. SQLite is a lightweight 0 configuration database that is ideal for use in Raspberry Pi and other embedded systems. SQLite documentation is rich in detail, this article will not explain in detail all aspects of the SQLite database operations, can only be combined with specific scenarios on-demand description. The SQLite techniques described in this article can also be used on other platforms, not limited to Raspberry Pi. This article continues to toss the Raspberry Pi temperature, which needs to be played out in a twist.
"Related blog post" "Raspberry Pi Study notes-index blog post"-More posts please follow. Raspberry Pi Learning notes-get Raspberry Pi CPU Temperature "Raspberry Pi Study notes-upload Raspberry Pi CPU temperature to Yeelink regularly"
1 Creating tables and inserting dataCreate a new file named Insert.sql. file with the following details:
PRAGMA Foreign_keys=off; BEGIN TRANSACTION; CREATE TABLE temps ( name default ' Rpi.cpu ', tdatetime datetime default (' Now ', ' localtime '), Temperature NUMERIC not NULL); Insert into Temps VALUES (' Rpi.cpu ', DateTime ("Now", ' localtime ', '-3 hours '), 40.1); insert into temps (name, Tdatetime, tem perature) VALUES (' Rpi.cpu ', datetime (' Now ', ' localtime ', '-2 hours '), 40.2); INSERT into temps (tdatetime, temperature) VALUES (DateTime (' Now ', ' localtime ', '-1 hours '), 40.3), INSERT into temps (temperature) values (40.4); COMMIT;
"brief description"--Some details please note"1" creates a table with 3 fields, name,tdatetime and temperature, respectively. "2" name default ' Rpi.cpu ', the Name field defaults to ' Rpi.cpu ', and the string in SQLite is wrapped in quotation marks. "3" tdatetime datetime Default (DateTime (' Now ', ' localtime ')), the Tdatetime field defaults to the current time. LocalTime in "4" datetime (' Now ', ' localtime ') representstime in this time zone, or Greenwich time if the parameter is not there. "5" DEFAULT(DateTime (' Now ', ' localtime ')),The parentheses must never be less。 The expression in default must be wrapped in parentheses. "6" with a number of different insertion methods, the first one does not contain field names, the second contains the field names, and the third, because there are default values when creating the table, you can use a more concise insert method, such as insert into temps (temperature) values (40.4);
2 Creating tables and inserting dataCreate a script named Create-table.sh that reads as follows#!/bin/shrm-f cpu.dbecho begins inserting datasqlite3 cpu.db < Insert.sqlecho Insert Complete"Brief description"The "1" database name is Cpu-temp.db "2" Sqlite3 cpu.db < insert.sql insert Insert.sql script into the database, Insert.sql includes two steps to create a table and insert data into the table. "3" modifies the Execute permission chmod a+x create-tabel.sh, and then executes the./create-table.sh "4" SQLite also has the command line mode, but the command does not have the history query function, writes the mistake also to write again, All shell script directives are more conducive to modification and attempt.
3 Query ContentSimple query creates a new script named Show.sh, which reads as follows#!/bin/shdbname= "Cpu.db"sqlite3 $DBNAME "select * from Temps;""Simple description"The "1" database name is cpu.db. "2" Sqlite3 $DBNAME "select * from Temps;" Queries all records in the table. "3" modifies the Execute permission chmod a+x show.sh, and then executes the./show.sh "4" execution results are as follows, from the results of the insertion time interval of one hours, to meet the expected effect. rpi.cpu|2014-08-02 17:27:47|40.1rpi.cpu|2014-08-02 18:27:47|40.2rpi.cpu|2014-08-02 19:27:47|40.3RPi.CPU| 2014-08-02 20:27:47|40.4
"Modify Time Order"--Reverse time output#!/bin/shdbname= "Cpu.db"sqlite3 $DBNAME "select * from Temps ORDER by Tdatetime DESC;""Simple description""1" ORDER by Tdatetime DESC in descending order of tdatetime. "2" Output rpi.cpu|2014-08-02 20:27:47|40.4rpi.cpu|2014-08-02 19:27:47|40.3rpi.cpu|2014-08-02 18:27:47|40.2RPi.CPU| 2014-08-02 17:27:47|40.1
"Limit Time"--returns data for the last 3 hours#!/bin/shdbname= "Cpu.db"sqlite3 $DBNAME "select * from Tempswhere Tdatetime > DateTime (' Now ', ' localtime ', '-3 hours ')ORDER by Tdatetime ASC; ""Brief description""1" datetime (' Now ', ' localtime ', '-3 hours ') represents a point in time 3 hours before the current time, be sure to add the localtime parameter "2" where Tdatetime > DateTime (' Now ', ' LocalTime ', '-3 hours ') limit the condition 3 hours ago to the present. The "3" output is as follows, specifically indicating that the operation takes approximately 22:05. rpi.cpu|2014-08-02 19:27:47|40.3rpi.cpu|2014-08-02 20:27:47|40.4
"Limit Time"--query data over a period of time#!/bin/shdbname= "Cpu.db"sqlite3 $DBNAME "select * from Tempswhere Tdatetime > DateTime (' 2014-08-02 19:00:00 ') andtdatetime <= datetime (' 2014-08-02 20:00:00 '); " "Brief description""1" 2014-08-02 19:00:00 must account for 4 figures, others must account for 2 numbers, insufficient to use 0. "2" Here does not need to increase the localtime parameter, the specific reason is unknown.
5 Summary"1" When you create a table, you can use the default constraint to increase the defaults to simplify the insert operation and avoid null values. The "2" insert operation contains a variety of methods, depending on the actual situation. The "3" SQLite datetime function needs to specify the localtime parameter, specifying the local time zone.
6 References"1" SQLite Date and Time Functions "2" Set up a SQLite database on a Raspberry Pi "3" SQLite column-constraint "4" SQLite Tutorial | W3cschool Rookie Tutorial
