在 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
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})
"name" 包裹。