SQLite 是輕量級嵌入式資料庫,Python 內建 sqlite3 模組無需額外安裝即可操作。本文示範完整 CRUD 流程、參數化查詢防注入、Row 工廠轉字典、交易控制。
import sqlite3 as db
DB_FILE = 'mytest.db'
with db.connect(DB_FILE) as conn:
cursor = conn.cursor()
# 1. 建表 (IF NOT EXISTS 避免重複)
cursor.execute('''
CREATE TABLE IF NOT EXISTS datecounts (
date TEXT PRIMARY KEY,
count INTEGER
)
''')
# 2. 插入資料 (參數化查詢,防 SQL 注入)
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 OR REPLACE INTO datecounts VALUES (?, ?)', data)
conn.commit()
# 3. 查詢並使用 Row Factory (可用欄位名存取)
conn.row_factory = db.Row
cursor.execute('SELECT * FROM datecounts')
rows = cursor.fetchall()
for row in rows:
print(f"{row['date']} {row['count']}") # 字典式存取
# 4. 聚合查詢
cursor.execute('SELECT AVG(count) FROM datecounts')
avg = cursor.fetchone()[0]
print(f"平均值: {avg:.2f}")
# 5. 刪除
cursor.execute('DELETE FROM datecounts WHERE count = ?', (40,))
conn.commit()
print(f"刪除筆數: {cursor.rowcount}")
# 6. 更新
cursor.execute('UPDATE datecounts SET count = ? WHERE date = ?', (99, '12/1/2011'))
conn.commit()
print(f"更新筆數: {cursor.rowcount}")
# 錯誤:字串格式化 (有注入風險)
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")
# 正確:佔位符 ? (qmark 風格) 或 :name (named 風格)
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))
cursor.execute("SELECT * FROM users WHERE name = :name", {'name': name})
conn.row_factory = db.Row
row = cursor.fetchone()
print(row['column_name']) # 字典式
print(row[0]) # 索引式也可
# 手動交易
conn = db.connect(DB_FILE)
try:
cursor = conn.cursor()
cursor.execute('INSERT ...')
cursor.execute('UPDATE ...')
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
# 或使用 with 區塊 (Python 3.4+ 自動 commit/rollback)
with db.connect(DB_FILE) as conn:
cursor = conn.cursor()
cursor.execute('...')
# 正常結束自動 commit,異常自動 rollback
executemany。PRAGMA synchronous = OFF、PRAGMA journal_mode = MEMORY (風險:崩潰可能損壞資料)。CREATE INDEX idx_date ON datecounts(date)。WAL 模式提升並發:PRAGMA journal_mode = WAL。| 錯誤 | 原因 | 解決 |
|---|---|---|
| sqlite3.OperationalError: table already exists | 重複建表 | 用 CREATE TABLE IF NOT EXISTS |
| sqlite3.ProgrammingError: Incorrect number of bindings | 參數數量不匹配 | 檢查 ? 數量與元組長度 |
| Database is locked | 並發寫入衝突 | 啟用 WAL、縮短交易、重試機制 |