萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> 程式語言綜合

Python SQLite CRUD 完整實戰:建表、增刪改查與 Row Factory

SQLite 是輕量級嵌入式資料庫,Python 內建 sqlite3 模組無需額外安裝即可操作。本文示範完整 CRUD 流程、參數化查詢防注入、Row 工廠轉字典、交易控制。

完整 CRUD 範例

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})

Row Factory 讓結果可用欄位名

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。

常見錯誤

copyright © 萬盛學電腦網 all rights reserved
錯誤原因解決
sqlite3.OperationalError: table already exists重複建表用 CREATE TABLE IF NOT EXISTS
sqlite3.ProgrammingError: Incorrect number of bindings參數數量不匹配檢查 ? 數量與元組長度
Database is locked並發寫入衝突啟用 WAL、縮短交易、重試機制