萬盛學電腦網

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

Python SQLite 動態建立資料表:參數化 DDL 與 Schema 檢查

在 Python 中動態建立 SQLite 資料表,使用參數化 DDL 防注入、檢查表是否存在、定義欄位型別與約束,適用於插件系統、多租戶、運行時 Schema 變更場景。

基本動態建表

import sqlite3 as db

def create_table(conn, table_name, columns):
    '''
    columns: list of (col_name, col_type, constraints)
    e.g. [('id', 'INTEGER', 'PRIMARY KEY'), ('name', 'TEXT', 'NOT NULL')]
    '''
    col_defs = ', '.join(f'"{name}" {ctype} {constraints}' for name, ctype, constraints in columns)
    sql = f'CREATE TABLE IF NOT EXISTS "{table_name}" ({col_defs})'
    conn.execute(sql)
    conn.commit()

# 使用
conn = db.connect('mytest.db')
create_table(conn, 'person', [
    ('name', 'TEXT', 'NOT NULL'),
    ('age', 'INTEGER', ''),
    ('address', 'TEXT', '')
])
print("Table created")

檢查表是否存在

def table_exists(conn, table_name):
    cursor = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
        (table_name,)
    )
    return cursor.fetchone() is not None

動態新增欄位 (ALTER TABLE)

def add_column(conn, table_name, col_name, col_type, default=None):
    if table_exists(conn, table_name):
        # SQLite 不支援 DROP COLUMN (舊版),新增欄位較安全
        sql = f'ALTER TABLE "{table_name}" ADD COLUMN "{col_name}" {col_type}'
        if default is not None:
            sql += f' DEFAULT {default}'
        conn.execute(sql)
        conn.commit()

add_column(conn, 'person', 'email', 'TEXT', "'unknown@example.com'")

獲取表結構資訊

def get_table_info(conn, table_name):
    cursor = conn.execute(f'PRAGMA table_info("{table_name}")')
    return cursor.fetchall()  # [(cid, name, type, notnull, dflt_value, pk), ...]

for col in get_table_info(conn, 'person'):
    print(col)

完整範例:根據字典自動建表並插入

def ensure_table_and_insert(conn, table_name, data_dict):
    '''data_dict: {col: value} 自動建表並插入'''
    if not table_exists(conn, table_name):
        # 依值型別推斷欄位型別 (簡易版)
        type_map = {int: 'INTEGER', float: 'REAL', str: 'TEXT', bool: 'INTEGER'}
        columns = [(k, type_map.get(type(v), 'TEXT'), '') for k, v in data_dict.items()]
        create_table(conn, table_name, columns)
    
    cols = ', '.join(f'"{k}"' for k in data_dict.keys())
    placeholders = ', '.join('?' * len(data_dict))
    sql = f'INSERT INTO "{table_name}" ({cols}) VALUES ({placeholders})'
    conn.execute(sql, tuple(data_dict.values()))
    conn.commit()

# 使用
ensure_table_and_insert(conn, 'logs', {'level': 'INFO', 'message': 'Started', 'count': 1})

注意事項

  • SQLite 型別系統動態寬鬆,宣告型別僅為提示,實際儲存不強制。
  • 識別碼 (表名、欄位名) 含關鍵字或特殊字元時,務必用雙引號 "name" 包裹。
  • 大量動態 DDL 操作建議在交易中執行。
copyright © 萬盛學電腦網 all rights reserved