SQLite嵌入式数据库Python实践
笔者以前使用shell+sqlite搭建cpu温度数据库，SQLite 轻便嵌入式的特征，今天笔者测试使用Python来操作SQLite。实验参考文章：树莓派使用 Python + SQLite 建立温度数据库。
实验平台：WSL Ubuntu 20.04 LTS
python版本：Python3
sqlite版本：SQLite3
1、安装SQLite：sudo apt install sqlite3
2、进入命令行：sqlite3
3、查看点命令使用：.help，.exit退出SQLite命令行
4、sql语句创建temps表
PRAGMA foreign_keys=OFF;
BEGINTRANSACTION;
CREATETABLEtemps(
nameDEFAULT'RPi.CPU',
tdatetime DATETIME DEFAULT(datetime('now', 'localtime')),
temperature NUMERICNOTNULL
);
COMMIT;
5、创建数据库test.db
在terminal命令行：sqlite3 test.db < createtb.sql
连接数据库conn.py，如果数据库不存在，那么它就会被创建。
#!/usr/bin/python3
import sqlite3
conn = sqlite3.connect('test.db')
print("Opened database successfully")
6、插入数据库inset.py
#!/usr/bin/python3
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Opened database successfully")
c.execute("INSERT INTO temps (temperature) VALUES (34)")
conn.commit()
print("Records created successfully")
conn.close()
4、查询数据库
命令行：select * from temps;
python脚本select.py：
#!/usr/bin/python3
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Opened database successfully")
cursor = c.execute("SELECT * from temps")
for row in cursor:
print("defaultname = ", row[0])
print("date = ", row[1])
print("temperate = ", row[2])
print("Operation done successfully")
conn.close()
5、更新数据库update.py
#!/usr/bin/python3
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Opened database successfully")
c.execute("UPDATE temps set temperature=32 where temperature=40")
conn.commit()
print("Total number of rows updated :", conn.total_changes)
cursor = conn.execute("SELECT * from temps")
for row in cursor:
print("defaultname = ", row[0])
print("date = ", row[1])
print("temperate = ", row[2])
print("Operation done successfully")
conn.close()
6、删除数据库delete.py
#!/usr/bin/python3
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Opened database successfully")
c.execute("DELETE from temps where temperature=34")
conn.commit()
print("Total number of rows updated :", conn.total_changes)
cursor = conn.execute("SELECT * from temps")
for row in cursor:
print("defaultname = ", row[0])
print("date = ", row[1])
print("temperate = ", row[2])
print("Operation done successfully")
conn.close()
