為什麼不直接用 SQLAlchemy?對於輕量專案,ORM 過於龐大。本文示範如何從零構建 transwarp.db 模組:連線引擎、執行緒區域儲存、上下文管理器、裝飾器簡化調用、事務支援,打造簡潔可靠的資料庫存取層。
# 初始化
db.create_engine(user='root', password='pwd', database='test', host='127.0.0.1', port=3306)
# 查詢 → 列表[字典]
users = db.select('select * from user')
# [{'id':1, 'name':'Michael'}, ...]
# 增刪改 → 影響行數
n = db.update('insert into user(id, name) values(?, ?)', 4, 'Jack')
# 連線上下文:同一連線執行多條 SQL
with db.connection():
db.select('...')
db.update('...')
# 事務上下文:自動 commit/rollback
with db.transaction():
db.select('...')
db.update('...')
# db.py
# 1. 連線引擎單例
class _Engine(object):
def __init__(self, connect):
self._connect = connect
def connect(self):
return self._connect()
engine = None
# 2. 執行緒區域儲存:每個執行緒獨立連線
class _DbCtx(threading.local):
def __init__(self):
self.connection = None
self.transactions = 0
def is_init(self):
return self.connection is not None
def init(self):
self.connection = _LazyConnection()
self.transactions = 0
def cleanup(self):
self.connection.cleanup()
self.connection = None
def cursor(self):
return self.connection.cursor()
_db_ctx = _DbCtx() # threading.local 確保執行緒隔離
class _ConnectionCtx(object):
def __enter__(self):
global _db_ctx
self.should_cleanup = False
if not _db_ctx.is_init():
_db_ctx.init()
self.should_cleanup = True
return self
def __exit__(self, exctype, excvalue, traceback):
global _db_ctx
if self.should_cleanup:
_db_ctx.cleanup()
def connection():
return _ConnectionCtx()
def with_connection(func):
@functools.wraps(func)
def _wrapper(*args, **kw):
with _ConnectionCtx():
return func(*args, **kw)
return _wrapper
@with_connection
def select(sql, *args):
cursor = _db_ctx.cursor()
cursor.execute(sql.replace('?', '%s'), args)
names = [d[0] for d in cursor.description]
return [dict(zip(names, row)) for row in cursor.fetchall()]
@with_connection
def update(sql, *args):
cursor = _db_ctx.cursor()
cursor.execute(sql.replace('?', '%s'), args)
return cursor.rowcount
class _TransactionCtx(object):
def __enter__(self):
global _db_ctx
self.should_close_conn = False
if not _db_ctx.is_init():
_db_ctx.init()
self.should_close_conn = True
_db_ctx.transactions += 1
return self
def __exit__(self, exctype, excvalue, traceback):
global _db_ctx
_db_ctx.transactions -= 1
if _db_ctx.transactions == 0:
if exctype is None:
_db_ctx.connection.commit()
else:
_db_ctx.connection.rollback()
if self.should_close_conn:
_db_ctx.cleanup()
def transaction():
return _TransactionCtx()
| 特性 | 實現方式 |
|---|---|
| 執行緒隔離 | threading.local 儲存連線 |
| 參數化查詢 | 統一 ? 佔位符 → %s,防 SQL 注入 |
| 延遲連線 | _LazyConnection 首次使用時才建立實體連線 |
| 巢狀事務支援 | 計數器 transactions 追蹤層級 |
| 裝飾器/上下文雙介面 | 滿足簡單調用與複雜流程控制 |
# 簡單查詢
users = db.select('select * from user where name=?', 'Bob')
# 事務中多步操作
with db.transaction():
db.update('update account set balance=balance-? where id=?', 100, 1)
db.update('update account set balance=balance+? where id=?', 100, 2)