標籤：樹莓派 sqlite
0 前言 本文介紹如何在樹莓派中利用SQLite資料庫儲存CPU溫度資料。SQLite是一款輕量級零設定資料庫，非常適合在樹莓派和其他嵌入式系統中使用。SQLite文檔詳細資料豐富，本文不會詳細解釋SQLite資料庫操作的方方面面，只能結合具體情境按需說明。本文介紹的SQLite技巧也可以在其他平台使用，並不局限於樹莓派。 本文繼續折騰樹莓派溫度，需要從中可以玩出新花樣。
【相關博文】 【樹莓派學習筆記——索引博文】——更多博文請關注。 【樹莓派學習筆記——擷取樹莓派CPU溫度】 【樹莓派學習筆記——定時向yeelink上傳樹莓派CPU溫度】
1 建立表和插入資料 建立一個名為insert.sql檔案，檔案具體內容如下：
PRAGMA foreign_keys=OFF;BEGIN TRANSACTION;CREATE TABLE temps( name DEFAULT 'RPi.CPU', tdatetime DATETIME DEFAULT (datetime('now', 'localtime')), temperature NUMERIC NOT NULL); INSERT INTO temps VALUES('RPi.CPU', datetime('now', 'localtime', '-3 hours'), 40.1);INSERT INTO temps(name, tdatetime, temperature) 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; 【簡要說明】——若干細節請注意 【1】建立表，表中包括3個欄位，分別為name，tdatetime和 temperature。 【2】name DEFAULT ‘RPi.CPU‘，name欄位的預設值為‘RPi.CPU‘，SQLite中字串被單引號包裹。 【3】tdatetime DATETIME DEFAULT (datetime(‘now‘, ‘localtime‘)), tdatetime欄位預設值為目前時間。 【4】datetime(‘now‘, ‘localtime‘)中的localtime表示本時區時間，如果沒有該參數則為格林尼治時間。 【5】DEFAULT (datetime(‘now‘, ‘localtime‘)), 括弧絕對不能少。DEFault中的運算式一定要被括弧包裹。 【6】採用多種不同的插入方法，第一種不含欄位名稱，第二種包含欄位名稱，第三種，由於建立表格時有預設值，可以使用更簡潔的插入方法，例如 INSERT INTO temps(temperature) VALUES(40.4);
2 建立表和插入資料 建立一個名為create-table.sh指令碼，具體內容如下#!/bin/shrm -f cpu.dbecho 開始插入資料sqlite3 cpu.db < insert.sqlecho 插入完成 【簡要說明】 【1】資料庫名稱為cpu-temp.db 【2】sqlite3 cpu.db < insert.sql 把insert.sql指令碼插入到資料庫中，insert.sql包括兩個步驟，建立表並向表中插入資料。 【3】修改執行許可權 chmod a+x create-tabel.sh，然後執行./create-table.sh 【4】SQLite也有命令列模式，但是命令沒有曆史查詢功能，寫錯了還要重新寫一遍，所有shell指令碼指令更利於修改和嘗試。
3 查詢內容 【簡單查詢】 建立一個名為show.sh的指令碼，具體內容如下#!/bin/shDBNAME="cpu.db"sqlite3 $DBNAME "SELECT * FROM temps;" 【簡單說明】 【1】資料庫名稱為cpu.db。 【2】sqlite3 $DBNAME "select * from temps;" 查詢表中所有記錄。 【3】修改執行許可權 chmod a+x show.sh，然後執行./show.sh 【4】執行結果如下，從結果來看插入的時間間隔一個小時，符合預期效果。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
【修改時間順序】——時間倒序輸出#!/bin/shDBNAME="cpu.db"sqlite3 $DBNAME "select * from temps ORDER BY tdatetime DESC;" 【簡單說明】 【1】ORDER BY tdatetime DESC 以tdatetime降序排列。 【2】輸出結果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
【限制時間】——返回最近3個小時的資料#!/bin/shDBNAME="cpu.db"sqlite3 $DBNAME "SELECT * FROM temps where tdatetime > datetime(‘now‘, ‘localtime‘, ‘-3 hours‘) ORDER BY tdatetime ASC;" 【簡要說明】 【1】datetime(‘now‘, ‘localtime‘, ‘-3 hours‘) 表示目前時間3個小時之前的時間點，一定要加上localtime參數 【2】where tdatetime > datetime(‘now‘, ‘localtime‘, ‘-3 hours‘) 限制條件3個小時之前到現在。 【3】輸出結果如下，特別說明操作的時間約為22:05。RPi.CPU|2014-08-02 19:27:47|40.3RPi.CPU|2014-08-02 20:27:47|40.4
【限制時間】——查詢某個時間段內資料#!/bin/shDBNAME="cpu.db"sqlite3 $DBNAME "SELECT * FROM temps where tdatetime > datetime(‘2014-08-02 19:00:00‘) and tdatetime <= datetime(‘2014-08-02 20:00:00‘);" 【簡要說明】 【1】2014-08-02 19:00:00 年必須佔4個數字，其他必須佔2個數字，不足時使用0不足。 【2】此處不需要增加localtime參數，具體原因未知。
5 總結 【1】建立表格時可使用DEFAULT約束，增加預設值簡化插入操作，避免空值。 【2】INSERT操作含有多種方法，根據實際情況選用。 【3】SQLite datetime函數需要指定localtime參數，指定本地時區。
6 參考資料【1】SQLite Date And Time Functions【2】Set up an SQLite database on a Raspberry Pi【3】SQLite column-constraint【4】SQLite 教程 | w3cschool菜鳥教程
