完整腳本
import sqlite3 as db
conn = db.connect('mytest.db')
cursor = conn.cursor()
# 建表
cursor.execute('DROP TABLE IF EXISTS datecounts')
cursor.execute('CREATE TABLE datecounts(date TEXT, count INT)')
# 批量插入
data = [
('12/1/2011', 35), ('12/2/2011', 42), ('12/3/2011', 38),
('12/4/2011', 41), ('12/5/2011', 40), ('12/6/2011', 28),
('12/7/2011', 45)
]
cursor.executemany('INSERT INTO datecounts VALUES(?, ?)', data)
conn.commit()
# 行工廠：按列名訪問
conn.row_factory = db.Row
cursor.execute('SELECT * FROM datecounts')
for row in cursor.fetchall():
print(f"{row['date']} {row['count']}")
# 聚合查詢
cursor.execute('SELECT AVG(count) FROM datecounts')
avg = cursor.fetchone()[0]
print(f"平均值: {avg}")
# 刪除
cursor.execute('DELETE FROM datecounts WHERE count = 40')
conn.commit()
# 驗證
cursor.execute('SELECT * FROM datecounts')
for row in cursor.fetchall():
print(f"{row['date']} {row['count']}")
conn.close()
關鍵點解析
| 特性 | 說明 |
|---|
executemany | 批量執行，防止 SQL 注入，性能優於循環 execute |
row_factory = db.Row | 返回類字典對象，支持 row['col'] 和 row[col] |
? 佔位符 | 參數化查詢，自動轉義，防注入 |
conn.commit() | 顯式提交事務，DML 操作必須調用 |
上下文管理器模式（Python 3.4+）
with db.connect('mytest.db') as conn:
conn.row_factory = db.Row
cursor = conn.cursor()
cursor.execute('SELECT * FROM datecounts')
for row in cursor:
print(row['date'], row['count'])
# 自動 commit/rollback 並關閉
遷移提示
- 生產環境建議使用
sqlite3 內置模塊，無需額外依賴；
- 高併發寫入場景考慮 WAL 模式：
PRAGMA journal_mode=WAL;；
- 大數據量遷移至 PostgreSQL/MySQL 時，SQL 語法基本兼容。
