1. sqlalchemy 介绍
1.1 什么是 sqlalchemy？
sqlalchemy 是一个流行的 Python SQL 工具包和对象关系映射（ORM）工具，它提供了数据库交互的高级抽象，使得开发者可以使用 Python 代码来操作数据库，而无需直接编写 SQL 语句。它支持多种数据库后端，包括 SQLite、PostgreSQL、MySQL、Oracle 等。
1.2 sqlalchemy 的主要特点
（1）SQL 表达式语言 sqlalchemy 提供了一个强大的 SQL 表达式语言，允许开发者以编程的方式构建 SQL 查询。这种语言既灵活又强大，可以生成复杂的 SQL 查询，同时避免了直接拼接 SQL 字符串带来的安全风险（如 SQL 注入）。
from sqlalchemy import create_engine, select, Table, MetaData
engine = create_engine('sqlite:///example.db')
metadata = MetaData()
# 定义表
users = Table('users', metadata, autoload_with=engine)
# 构建查询
query = select(users).where(users.c.age > 25)
（2）ORM 功能 sqlalchemy 的 ORM 功能允许开发者将数据库表映射为 Python 类。通过这种方式，开发者可以像操作普通 Python 对象一样操作数据库记录。
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
age = Column(Integer)
# 使用 ORM 添加记录
new_user = User(name='Alice', age=30)
session.add(new_user)
session.commit()
（3）数据库连接管理 sqlalchemy 提供了强大的连接池管理功能，可以自动管理数据库连接的生命周期，包括连接的创建、销毁和复用。这使得开发者无需手动管理连接，提高了代码的可维护性和性能。
（4）支持多种数据库 sqlalchemy 支持多种主流数据库，通过使用不同的数据库驱动程序，可以轻松切换数据库后端。例如：
• SQLite：sqlite:///example.db
• PostgreSQL：postgresql://user:password@localhost/dbname
• MySQL：mysql+pymysql://user:password@localhost/dbname
1.3 sqlalchemy 的基本使用
（1）安装 通过 pip 安装 sqlalchemy：
pip install sqlalchemy
（2）创建引擎 引擎是 sqlalchemy 与数据库交互的核心组件，负责管理数据库连接。
from sqlalchemy import create_engine
engine = create_engine('sqlite:///example.db', echo=True) # echo 参数用于调试，会打印 SQL 语句
（3）定义表结构 可以通过 SQL 表达式语言或 ORM 的方式定义表结构。
- SQL 表达式语言方式：
from sqlalchemy import Table, Column, Integer, String, MetaData
metadata = MetaData()
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('age', Integer))
- ORM 方式：
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
age = Column(Integer)
（4）创建表
metadata.create_all(engine) # 使用 SQL 表达式语言
Base.metadata.create_all(engine) # 使用 ORM
（5）插入数据
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
# 插入数据
new_user = User(name='Bob', age=25)
session.add(new_user)
session.commit()
（6）查询数据
# 使用 SQL 表达式语言
query = select(users).where(users.c.age > 25)
result = engine.execute(query)
for row in result:
print(row)
# 使用 ORM
users = session.query(User).filter(User.age > 25).all()
for user in users:
print(user.name, user.age)
1.4 sqlalchemy 的优势
• 灵活性：既可以使用 SQL 表达式语言，也可以使用 ORM，开发者可以根据需求选择最适合的方式。
• 安全性：避免了 SQL 注入的风险，因为 sqlalchemy 会自动处理参数化查询。
• 易用性：提供了丰富的文档和社区支持，学习曲线相对平缓。
• 性能：通过连接池管理和高效的查询优化，可以实现高性能的数据库操作。
1.5 适用场景
sqlalchemy 适用于各种需要与数据库交互的 Python 应用程序，包括：
• Web 应用（如 Flask、Django 等框架）
• 数据分析和数据处理工具
• 企业级应用程序
2. ctms-db中使用sqlalchemy
2.1 公共 ORM 模型
from datetime import datetime
from sqlalchemy.orm import Mapped, mapped_column
from core.database import Base
from sqlalchemy import DateTime, Integer, func, Boolean, inspect
class BaseModel(Base):
"""
公共 ORM 模型，基表
"""
__abstract__ = True
id: Mapped[int] = mapped_column(Integer, primary_key=True, comment='主键ID')
create_datetime: Mapped[datetime] = mapped_column(DateTime, server_default=func.now(), comment='创建时间')
update_datetime: Mapped[datetime] = mapped_column(
DateTime,
server_default=func.now(),
onupdate=func.now(),
comment='更新时间'
)
delete_datetime: Mapped[datetime | None] = mapped_column(DateTime, nullable=True, comment='删除时间')
is_delete: Mapped[bool] = mapped_column(Boolean, default=False, comment="是否软删除")
@classmethod
def get_column_attrs(cls) -> list:
"""
获取模型中除 relationships 外的所有字段名称
:return:
"""
mapper = inspect(cls)
return mapper.column_attrs.keys()
@classmethod
def get_attrs(cls) -> list:
"""
获取模型所有字段名称
:return:
"""
mapper = inspect(cls)
return mapper.attrs.keys()
@classmethod
def get_relationships_attrs(cls) -> list:
"""
获取模型中 relationships 所有字段名称
:return:
"""
mapper = inspect(cls)
return mapper.relationships.keys()
2.2 具体数据库表类
from datetime import date
from sqlalchemy.orm import Mapped, mapped_column
from db.db_base import BaseModel
from sqlalchemy import String, Float, Date, JSON
class ETF(BaseModel):
__tablename__ = "crypto_info_etf"
__table_args__ = ({'comment': 'etf'})
name: Mapped[str] = mapped_column(String(20), default='', comment="ETF名称")
total: Mapped[str] = mapped_column(Float, default='0', comment="总量:币数量")
total_usd: Mapped[str | None] = mapped_column(String(255), comment="总额:usd价值")
detailed: Mapped[list | None] = mapped_column(JSON, comment="ETF详情")
update_date: Mapped[date | None] = mapped_column(Date, comment="更新日期")
具体的数据库表类继承自基础ORM模型类BaseModel，表名是通过__tablename__定义。
3. 数据库连接
ctms-db里面对数据库的操作都采用异步方式，也是通过sqlalchemy的异步引擎。
3.1 创建异步连接
async_engine = create_async_engine(
SQLALCHEMY_DATABASE_URL,
echo=False,
echo_pool=False,
pool_pre_ping=True,
pool_recycle=3600,
pool_size=5,
max_overflow=5,
connect_args={}
)
3.2 创建数据库会话
session_factory = async_sessionmaker(
autocommit=False,
autoflush=False,
bind=async_engine,
expire_on_commit=True,
class_=AsyncSession
)
3.3 获取主数据库会话
数据库依赖项，它将在单个请求中使用，然后在请求完成后将其关闭。
async def db_getter() -> AsyncGenerator[AsyncSession, None]:
async with session_factory() as session:
# 创建一个新的事务，半自动 commit
async with session.begin():
yield session
4. 联系方式
- 公众号：ScienceStudio
- Github: github.com/KandyYe
- 知乎：www.zhihu.com/people/kand…
