SQLAlchemy 使用总结

本文介绍SQLAlchemy三种数据库访问方式:rawsql、sqlexpression及ORM,并对比它们的特点。重点讲解了sqlexpression和rawsql的使用场景及示例,同时介绍了如何通过ORM实现数据库对象映射。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

使用 sqlalchemy 有3种方式:
方式1, 使用raw sql; 
方式2, 使用SqlAlchemy的sql expression; 
方式3, 使用ORM.  
前两种方式可以统称为 core 方式. 本文讲解 core 方式访问数据库, 不涉及 ORM. 

对于绝大多数应用, 推荐使用 SqlAlchemy. 即使是使用raw sql, SqlAlchemy 也可以带来如下好处: 
1. 内建数据库连接池. [注意]如果是sqlalchemy+cx_oracle的话, 需要禁掉 connection pool, 否则会有异常. 方法是设置sqlalchemy.poolclass为sqlalchemy.pool.NullPool
2. 强大的log功能
3. 数据库中立的写法, 包括: sql参数写法, limit语法
4. 特别提一下, where()条件的==your_value, 如果your_value等于None, 真正的Sql会转为Is None

SqlAlchemy的sql expression和raw sql的比较:
1. sql expression 写法是纯python代码, 阅读性更好, 尤其是在使用insert()方法时, 字段名和取值成对出现.  

2. raw sql 比 sql expression 更灵活, 如果SQL/DDL很复杂, raw sql就更有优势了. 

具体案例如下

# coding: utf-8

from sqlalchemy import * 
import tushare as ts
import pandas as pd
from sqlalchemy.orm import sessionmaker,mapper
from datetime import *

engine = create_engine('mysql+pymysql://root:123456@127.0.0.1/mystock?charset=utf8')
   
#%%  1  hand-written SQL 方法
result = engine.execute('select * from stock_basics where pe < %s', 2)

# sqlalchemy推荐使用text()函数封装一下sql字符串,不同数据库, 可以使用统一的sql参数传递写法. 参数须以:号引出.
result = engine.execute(text('select * from stock_basics where pe < :pe'), {'pe': 2})

# 遍历result时, 得到的每一个行都是RowProxy对象, 获取字段的方法非常灵活, 下标和字段名甚至属性都行.
# rowproxy[0] == rowproxy['id'] == rowproxy.id
ans = result.fetchall() # 获取所有数据
ans1 = pd.DataFrame(ans) # 将数据转成 DataFrame格式

#  事务处理
conn = engine.connect()
conn.begin()
try:
    dosomething(connection)
    conn.commit()
except:   
    conn.rollback()  
conn.close()  

  
    #%%  SQL-expressions in Python 方法
meta = MetaData(bind=engine, reflect=True)
table = meta.tables['stock_basics']
result2 = list(engine.execute(table.select(table.c.pe < 2)))   # pe为stock_basics的一个列名

#%% ORM 方法   表中要有主键
engine.echo = True  # We want to see the SQL we're creating
metadata = MetaData(engine)

# The stock_basics table already exists, so no need to redefine it. Just
# load it from the database using the "autoload" feature.
users = Table('stock_basics', metadata, autoload=True)

def run(stmt):
    rs = stmt.execute()
    for row in rs:
        print(row)

# Most WHERE clauses can be constructed via normal comparisons
s = users.select(users.c.code == '000001')
run(s)
s = users.select(users.c.pe < 1)  # pe为stock_basics的一个列名
rs = s.execute().fetchall()
ans2 = pd.DataFrame(rs)    #将结果转换成 DataFrame格式

# Python keywords like "and", "or", and "not" can't be overloaded, so
# SQLAlchemy uses functions instead
s = users.select(and_(users.c.age < 40, users.c.name != 'Mary'))
s = users.select(or_(users.c.age < 40, users.c.name != 'Mary'))
s = users.select(not_(users.c.name == 'Susan'))

# Or you could use &, | and ~ -- but watch out for priority!
s = users.select((users.c.age < 40) & (users.c.name != 'Mary'))  #最好添加(),注意优先级
s = users.select((users.c.age < 40) | (users.c.name != 'Mary'))
s = users.select(~(users.c.name == 'Susan'))

# There's other functions too, such as "like", "startswith", "endswith"
s = users.select(users.c.name.startswith('M'))
s = users.select(users.c.name.like('%a%'))
s = users.select(users.c.name.endswith('n'))

# The "in" and "between" operations are also available
s = users.select(users.c.age.between(30,39))
# Extra underscore after "in" to avoid conflict with Python keyword
s = users.select(users.c.name.in_('Mary', 'Susan'))

# If you want to call an SQL function, use "func"
s = users.select(func.substr(users.c.name, 2, 1) == 'a')

# You don't have to call select() on a table; it's got a bare form
s = select([users], users.c.name != 'Carl')
s = select([users.c.name, users.c.age], users.c.name != 'Carl')
 
# This can be handy for things like count()
s = select([func.count(users.c.user_id)])
# Here's how to do count(*)
s = select([func.count("*")], from_obj=[users])
#%%多表联查
#    现在存在两个表
users = Table('users', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('name', String(40)),
    Column('age', Integer),)
users.create()
#    emails = Table('emails', metadata,
#        Column('email_id', Integer, primary_key=True),
#        Column('address', String),
#        Column('user_id', Integer, ForeignKey('users.user_id')),)
s = select([users, emails], emails.c.user_id == users.c.user_id)
# 查询部分列
s = select([users.c.name, emails.c.address], emails.c.user_id == users.c.user_id)
#基于外键的李娜和查询
s = join(users, emails).select()
#使用 outerjoin 查询所有用户,不论是否有邮箱
s = outerjoin(users, emails).select()

#%% 将数据库中的对象映射到对象中
users = Table('users', metadata, autoload=True)
# These are the empty classes that will become our data classes
class User(object):
    pass

usermapper = mapper(User, users)
session = DBSession()
#  查询 -----------------
query = session.query(User)
print(query) # 显示SQL 语句
print(query.statement) # 同上
for user in query: # 遍历时查询
    print(user.name)
print(query.all()) # 返回的是一个类似列表的对象
print(query.first().name) # 记录不存在时,first() 会返回 None
# print(query.one().name) # 不存在,或有多行记录时会抛出异常
print(query.filter(User.id == 2).first().name)
print(query.get(2).name) # 以主键获取,等效于上句
print(query.filter('id = 2').first().name) # 支持字符串
query2 = session.query(User.name)
print(query2.all()) # 每行是个元组
print(query2.limit(1).all()) # 最多返回 1 条记录
print(query2.offset(1).all()) # 从第 2 条记录开始返回
print(query2.order_by(User.name).all())
print(query2.order_by('name').all())
print(query2.order_by(User.name.desc()).all())
print(query2.order_by('name desc').all())
print(session.query(User.id).order_by(User.name.desc(), User.id).all())
print(query2.filter(User.id == 1).scalar()) # 如果有记录,返回第一条记录的第一个元素
print(session.query('id').select_from(User).filter('id = 1').scalar())
print(query2.filter(User.id > 1, User.name != 'a').scalar()) # and
query3 = query2.filter(User.id > 1) # 多次拼接的 filter 也是 and
query3 = query3.filter(User.name != 'a')
print(query3.scalar())
print(query2.filter(or_(User.id == 1, User.id == 2)).all()) # or
print(query2.filter(User.id.in_((1, 2))).all()) # in
query4 = session.query(User.id)
print(query4.filter(User.name == None).scalar())
print(query4.filter('name is null').scalar())
print(query4.filter(not_(User.name == None)).all()) # not
print(query4.filter(User.name != None).all())
print(query4.count())
print(session.query(func.count('*')).select_from(User).scalar())
print(session.query(func.count('1')).select_from(User).scalar())
print(session.query(func.count(User.id)).scalar())
print(session.query(func.count('*')).filter(User.id > 0).scalar()) # filter() 中包含 User,因此不需要指定表
print(session.query(func.count('*')).filter(User.name == 'a').limit(1).scalar() == 1) # 可以用 limit() 限制 count() 的返回数
print(session.query(func.sum(User.id)).scalar())
print(session.query(func.now()).scalar()) # func 后可以跟任意函数名,只要该数据库支持
print(session.query(func.current_timestamp()).scalar())
print(session.query(func.md5(User.name)).filter(User.id == 1).scalar())
# 修删------
query.filter(User.id == 1).update({User.name: 'c'})
user = query.get(1)
print(user.name)
user.name = 'd'
session.flush() # 写数据库,但并不提交
print(query.get(1).name)
session.delete(user)
session.flush()
session.rollback()  # 回滚
query.filter(User.id == 1).delete()
session.commit()  #提交,保存到数据库
print query.get(1)
session.close()  # 关闭session


The SQLAlchemy Object Relational Mapper presents a method of associating user-defined Python classes with database tables, and instances of those classes (objects) with rows in their corresponding tables. It includes a sys- tem that transparently synchronizes all changes in state between objects and their related rows, called a unit of work, as well as a system for expressing database queries in terms of the user defined classes and their defined relationships between each other. The ORM is in contrast to the SQLAlchemy Expression Language, upon which the ORM is constructed. Whereas the SQL Expression Language, introduced in SQL Expression Language Tutorial, presents a system of representing the primitive constructs of the relational database directly without opinion, the ORM presents a high level and abstracted pattern of usage, which itself is an example of applied usage of the Expression Language. While there is overlap among the usage patterns of the ORM and the Expression Language, the similarities are more superficial than they may at first appear. One approaches the structure and content of data from the perspective of a user-defined domain model which is transparently persisted and refreshed from its underlying storage model. The other approaches it from the perspective of literal schema and SQL expression representations which are explicitly composed into messages consumed individually by the database. A successful application may be constructed using the Object Relational Mapper exclusively. In advanced situations, an application constructed with the ORM may make occasional usage of the Expression Language directly in certain areas where specific database interactions are required. The following tutorial is in doctest format, meaning each >>> line represents something you can type at a Python command prompt, and the following text represents the expected return value.
<think>嗯,用户想了解SQLAlchemy最新版本的使用方法总结。首先我得确认当前最新版本是什么。记得之前是2.0系列,现在可能已经是2.1或者更高了。需要先检查一下官方文档或者PyPI页面确认版本号。假设最新是2.1.0,那得总结它的主要变化和用法。 用户可能已经有一定的基础,但可能需要从安装开始。我应该先提到安装命令,比如pip install sqlalchemy。然后分步骤介绍核心功能,比如引擎配置、ORM的使用、会话管理、查询方法,以及新版本的特性。 需要注意用户提到的系统级指令,特别是中英文格式和LaTeX的正确使用。比如,在提到版本号的时候可能不需要数学公式,但如果有表达式的话要记得用$包裹。不过SQLAlchemy的内容可能较少涉及数学,但要注意其他格式要求,比如独立公式用$$,不过这里可能用不上。 接下来,结构方面需要清晰。可能分为安装、核心组件、新特性、最佳实践等部分。每个部分下再细分小点,比如引擎和连接、声明基类、模型定义、CRUD操作、事务管理等。新特性部分要突出2.0和2.1的变化,比如异步支持、类型注解增强、Declarative的改进等。 用户可能关心如何迁移旧代码到新版本,或者新版本带来的性能改进。需要提到兼容性问题和迁移建议,比如2.0不兼容1.x,建议使用新的ORM方式。同时,异步IO的支持可能是一个重点,因为现在异步编程越来越流行,特别是搭配FastAPI等框架。 要确保示例代码正确,符合最新语法。比如使用`create_async_engine`代替传统的引擎创建,以及`sessionmaker`的用法是否有变化。另外,类型提示在Python中很重要,新版本可能更强调这点,需要示例中体现出来。 可能还需要提到事务管理的最佳实践,比如使用上下文管理器来自动提交或回滚。此外,查询时推荐使用select()而不是传统的query()方法,这符合SQLAlchemy 2.0的统一API。 最后,检查是否有遗漏的重要功能,比如混合属性、关联代理,或者性能优化技巧。同时,确保所有步骤符合中文指引,语言简洁明了,结构清晰,帮助用户一步步掌握最新版本的使用方法。</think>以下为SQLAlchemy最新版本(2.1.x)的核心使用总结,采用分层结构说明: --- ### 一、安装与版本确认 ```bash # 安装最新版 pip install sqlalchemy -U # 验证版本(截至2024年8月,最新稳定版为2.1.x) import sqlalchemy print(sqlalchemy.__version__) # 输出示例:2.1.0 ``` --- ### 二、核心组件使用流程 #### 1. 引擎配置(异步/同步) ```python # 同步引擎(传统方式) from sqlalchemy import create_engine sync_engine = create_engine("postgresql+psycopg2://user:pass@host/db") # 异步引擎(2.0+推荐) from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession async_engine = create_async_engine("postgresql+asyncpg://user:pass@host/db") ``` #### 2. ORM模型定义(声明式) ```python from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column class Base(DeclarativeBase): pass class User(Base): __tablename__ = "users" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(30)) age: Mapped[int | None] # 类型提示支持Python 3.10+语法 ``` #### 3. 会话管理(推荐上下文管理器) ```python # 同步会话 from sqlalchemy.orm import sessionmaker Session = sessionmaker(sync_engine) with Session() as session: user = User(name="Alice", age=30) session.add(user) session.commit() # 异步会话(需搭配async with) async_session = sessionmaker(async_engine, class_=AsyncSession, expire_on_commit=False) async with async_session() as session: result = await session.execute(select(User).where(User.name == "Alice")) ``` --- ### 三、新版本核心特性 #### 1. 异步IO全面支持 - 统一异步API:`AsyncEngine`, `AsyncSession`, `AsyncConnection` - 必须使用`asyncpg`/`aiomysql`等异步驱动 - 查询需配合`await session.execute()` #### 2. 类型注解增强 - 支持Python 3.10+的`| None`语法 - 自动推导列类型(如`Mapped[str]`对应`String`) - 自定义类型映射通过`mapped_column(type=...)`指定 #### 3. 声明式改进 - `DeclarativeBase`基类替代旧版`declarative_base()` - 混合属性支持:`hybrid_property`与`hybrid_method` --- ### 四、查询操作最佳实践 #### 1. 新版查询语法(2.0+统一API) ```python from sqlalchemy import select, update, delete # SELECT stmt = select(User).where(User.age > 25).order_by(User.name) result = session.execute(stmt) # UPDATE stmt = update(User).where(User.id == 5).values(age=31) session.execute(stmt) # DELETE stmt = delete(User).where(User.age < 18) session.execute(stmt) ``` #### 2. 关联查询优化 ```python class Address(Base): __tablename__ = "addresses" user_id = mapped_column(ForeignKey("users.id")) email = mapped_column(String(120)) # 预加载关联数据 stmt = select(User).options(selectinload(User.addresses)) ``` --- ### 五、事务管理策略 ```python # 自动事务(推荐) with session.begin(): session.add(some_object) # 显式事务控制 transaction = session.begin() try: session.add(...) transaction.commit() except: transaction.rollback() ``` --- ### 六、性能优化建议 1. 使用`execution_options(stream_results=True)`处理大数据集 2. 启用`create_engine(pool_pre_ping=True)`连接池检测 3. 批量操作使用`session.bulk_save_objects()` 4. 异步场景优先使用`asyncpg`驱动(PostgreSQL) --- ### 七、迁移注意事项 1. 1.x到2.x需要修改: - 移除`Query`对象,改用`select()` - 会话`autocommit`模式已废弃 - 必须显式声明`ON DELETE CASCADE` 2. 官方提供`sqlacodegen`工具辅助迁移 建议参考官方迁移指南:[SQLAlchemy 2.0 Migration](https://docs.sqlalchemy.org/en/20/changelog/migration_20.html)
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值