SQLAlchemy 是 Python 生态中最强大的 ORM 框架之一,以“用简单的 Python 语言实现高效高性能的数据库访问”著称。其设计理念独特:SQL 数据库的规模与性能重于对象集合,对象集合的抽象又重于表与行。本文通过完整示例,带你从零上手 Core 层核心操作。
# pip install sqlalchemy
# pip install pymysql
| 对象 | 作用 |
|---|---|
Engine | 连接池与方言管理,所有 SQL 执行的入口 |
Connection | 数据库会话,事务控制单元 |
MetaData | 表结构元数据容器 |
Table | 表定义,列、约束、索引均在此声明 |
from sqlalchemy import create_engine
engine = create_engine(
'mysql+pymysql://USERNAME:PASSWORD@DB_HOST:DB_PORT/DB_NAME',
pool_recycle=3600 # 连接回收时间,防止 MySQL 8 小时断连
)
connection = engine.connect()
from sqlalchemy import MetaData, Table, Column, Integer, String, DateTime
from datetime import datetime
metadata = MetaData()
users = Table('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('username', String(15), nullable=False, unique=True),
Column('email_address', String(255), nullable=False),
Column('phone', String(20), nullable=False),
Column('password', String(25), nullable=False),
Column('created_on', DateTime, default=datetime.now),
Column('updated_on', DateTime, default=datetime.now, onupdate=datetime.now)
)
metadata.create_all(engine)
ins = users.insert().values(
user_id=8888,
username='111cn.net',
email_address='support@111cn.net',
phone='12345678901',
password='www.111cn.net'
)
print(ins) # 查看生成的 SQL
# INSERT INTO users (...) VALUES (:user_id, :username, ...)
result = connection.execute(ins)
print(result.inserted_primary_key) # [8888]
ins = users.insert()
multi_data = [
{'user_id': 1, 'username': 'u1', 'email_address': 'u1@111cn.net', 'phone': '12345678901', 'password': 'www.111cn.net'},
{'user_id': 2, 'username': 'u2', 'email_address': 'u2@111cn.net', 'phone': '12345678901', 'password': 'www.111cn.net'}
]
result = connection.execute(ins, multi_data)
print(result.rowcount) # 2
from sqlalchemy import select
# 全表查询
s = select([users])
rp = connection.execute(s)
results = rp.fetchall()
for row in results:
print(row.username, row.email_address)
# 指定列
s = select([users.c.user_id, users.c.username])
rp = connection.execute(s)
print(rp.keys()) # ['user_id', 'username']
print(rp.fetchone()) # (8888, '111cn.net')
# 迭代游标
rp = connection.execute(s)
for record in rp:
print(record.username)
from sqlalchemy import desc
# 升序
s = select([users.c.user_id, users.c.username]).order_by(users.c.user_id)
# 降序
s = select([users.c.user_id, users.c.username]).order_by(desc(users.c.user_id))
# IN 条件
s = select([users.c.user_id, users.c.username]).where(users.c.user_id.in_([1, 2]))
from sqlalchemy import update
u = update(users).where(users.c.username == 'u1').values(phone='00000000000')
result = connection.execute(u)
print(result.rowcount) # 1
# 验证
s = select([users]).where(users.c.username == 'u1')
row = connection.execute(s).first()
print(dict(row)) # 含 updated_on 自动更新
from sqlalchemy import delete
d = delete(users).where(users.c.username == 'u1')
result = connection.execute(d)
print(result.rowcount) # 1
# 验证
s = select([users]).where(users.c.username == 'u1')
print(connection.execute(s).fetchall()) # []
connection.begin() 上下文管理器自动提交/回滚。