Python ORM之SQLAlchemy全面指南
一、SQLAlchemy简介
官方文档地址：
Python程序员必收藏！The Python Database Suite
指向https://www.sqlalchemy.org/的目标标签为‘www.sqlalchemy.org/’
SQLAlchemy 是 Python 中的一个 ORM 框架。简而言之，则是帮助我们摆脱冗杂的 SQL 语句束缚，在无需编写原生 SQL 语句的情况下就能实现目标功能：只需运用 Python 的语法操作对象即可自动生成对应的 SQL 语句。
它包含几个不同的组件，既可以单独使用也可以以组合形式一起使用。这些主要组件之间的依赖关系结构如图所示：
Schema/Types 类别与表之间的对应关系是数据建模的重要基础。
SQL 表达式语法中使用的各种关键字和函数构成了完整的查询语言体系。
处理机制引擎负责协调数据库访问请求与资源分配。
优化连接管理的配置参数包括最大连接数、重连策略等关键指标。
dialect 支持不同数据库系统的特点及其对应的 SQL 语法实现，
例如 Oracle 的 Dialect 支持 Oracle 数据库的 API 并生成相应的 SQL 语句，
同样适用于 PostgreSQL 和 MySQL 等主流数据库系统，
每个 dialect 都有其特定的数据库支持范围及对应的 SQL 语法规范。
二、安装
通过PIP安装
pip install SQLAlchemy
AI写代码使用setup.py安装
python setup.py install
AI写代码三、连接引擎
Each SQLAlchemy application begins with a unique engine object, which acts as the central hub for connecting to specific databases and manages all session connections.
Engine对象通常是一个专门针对单一数据库服务器设计的一次性全局对象，并由一个URL字符串构成用于连接至数据库主机或后端。
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)
AI写代码create_engine的参数有很多，我列一些比较常用的：
- echo=False -- 如果设置为true，则启用对所有语句及其
repr()方法参数列表的日志记录。 - enable_from_linting -- 默认设置为true。若检测到给定的
SELECT语句与可能导致笛卡尔积操作的对象脱节，则会发出警告。 - encoding -- 默认采用
utf-8编码方案。 - future -- 采用2.0版本的风格进行处理。
- hide_parameters -- 该选项为布尔值，在设置为true时会阻止SQL语句中的参数在信息日志中显示，并避免以
StatementError对象格式化。 - listeners -- 可配置的一个或多个
PoolListener实例，在处理连接池事件时会被激活。 - logging_name -- 一个标识符字符串，默认取自对象id的十六进制表示形式。
- max_identifier_length -- 指定数据库方言确定的最大字段名长度整数类型变量。
- max_overflow=10 -- 允许超出预先定义的最大池容量范围外最多打开10个额外连接（仅当池大小被设定时才有效）。
- pool_size=5 -- 设定预定义最大连接池容量大小（默认值设为5）。
- plugins -- 定义一组插件名称列表，在启动数据库引擎前会依次加载并注册这些插件模块。
四、声明映射
在Python编程语言中定义的一个类，在该数据库系统内对应管理的一张表；该类的所有属性即为该表中的字段名称
该类型的逻辑结构与数据库中的表结构相当。我们就可以将此类命名为MappingClass。为了实现这一目标，请开发一个新的MappingClass基例，默认情况下它遵循现有的逻辑框架。每个新开发的MappingClass都需要继承并依赖于这个基础架构 declarative_base()。
>>> from sqlalchemy.orm import declarative_base
>>> Base = declarative_base()
AI写代码既然我们有了一个“基”类，就可以根据它定义任意数量的映射类。
我们准备新建一个名为users的MySQL表，并称其为用户表。该User类将被用来映射此数据库表的信息。在该类中包含了主要涉及表名以及各列名称和数据类型的详细内容：包括主键设置、索引配置等关键字段设置信息。
>>> from sqlalchemy import Column, Integer, String
>>> class User(Base):
... __tablename__ = 'users'
...
... id = Column(Integer, primary_key=True)
... name = Column(String)
... fullname = Column(String)
... nickname = Column(String)
...
... def __repr__(self):
... return "<User(name='%s', fullname='%s', nickname='%s')>" % (
... self.name, self.fullname, self.nickname)
AI写代码tablename 代表表名
Column : 代表数据表中的一列，内部定义了数据类型
primary_key： 主键
五、创建表到数据库
在定义User类的过程中, 已明确了与表相关的各项信息, 又被称为tablerightMetadata, 即表的元数据信息. 进而可通过查看__table__属性:
>>> User.__table__
Table('users', MetaData(),
Column('id', Integer(), table=<users>, primary_key=True, nullable=False),
Column('name', String(), table=<users>),
Column('fullname', String(), table=<users>),
Column('nickname', String(), table=<users>), schema=None)
AI写代码开始创建表：
>>> Base.metadata.create_all(engine)
BEGIN...
CREATE TABLE users (
id INTEGER NOT NULL,
name VARCHAR,
fullname VARCHAR,
nickname VARCHAR,
PRIMARY KEY (id)
)
[...] ()
COMMIT
AI写代码六、创建映射类的实例
映射完成后，现在让我们创建一个User对象的实例：
>>> ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
>>> ed_user.name
'ed'
>>> ed_user.nickname
'edsnickname'
>>> str(ed_user.id)
'None'
AI写代码此时，实例对象只是在环境的内存中有效，并没有在表中真正生成数据。
七、创建会话
>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)
# 实例化
>>> session = Session()
AI写代码我们对表的所有操作，都是通过会话实现的。
八、添加和更新对象
>>> ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
>>> session.add(ed_user)
AI写代码我们成功注册了新用户账户。然而，在此之前，当前该数据尚未完成同步上传至数据库系统中，并需等待进一步处理流程的完成。
只有执行了 commit() 方法后，才会真正在数据表中创建数据。
如果我们查询数据库，则首先刷新所有待处理信息，然后立即发出查询。
>>> our_user = session.query(User).filter_by(name='ed').first()
>>> our_user
<User(name='ed', fullname='Ed Jones', nickname='edsnickname')>
AI写代码当前获取到的数据结果并不等于数据库表中的完整数据记录,而属于映射类的一个独立对象实例
九、回滚
在**commit()**之前实施对实例对象属性的修改行为时，在发生变更时应确保能够恢复至修改前的状态。
>>> session.rollback()
AI写代码该系统仅将某一条数据（即映射类实例）从内存中删除，并未对数据库进行任何操作。
十、查询
通过 query 关键字查询。
>>> for instance in session.query(User).order_by(User.id):
... print(instance.name, instance.fullname)
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flintstone
AI写代码通过query.filter()执行过滤操作
使用query.filter_by()进行关键字过滤
调用query.all()获取完整的数据列表
通过query.first()获取第一条记录
当数据集中仅有一个元素时，使用query.one()会成功返回该元素
采用query.one_or_none()可避免未找到结果时的错误情况，并返回None表示无结果
通过调用one方法并将结果存储于变量中实现单个记录查询功能的技术是：
count = query.count()
按照指定字段进行排序操作的函数是：
query.order_by()
query.join() 连接查询
>>> session.query(User).join(Address).\
... filter(Address.email_address=='jack@google.com').\
... all()
[<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>]
AI写代码query(column.label()) 可以为字段名（列）设置别名：
>>> for row in session.query(User.name.label('name_label')).all():
... print(row.name_label)
ed
wendy
mary
fred
AI写代码aliased() 为查询对象设置别名：
>>> from sqlalchemy.orm import aliased
>>> user_alias = aliased(User, name='user_alias')
4. SQL>>> for row in session.query(user_alias, user_alias.name).all():
... print(row.user_alias)
<User(name='ed', fullname='Ed Jones', nickname='eddie')>
<User(name='wendy', fullname='Wendy Williams', nickname='windy')>
<User(name='mary', fullname='Mary Contrary', nickname='mary')>
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>
AI写代码十一、查询常用筛选器运算符
# 等于
query.filter(User.name == 'ed')
# 不等于
query.filter(User.name != 'ed')
# like和ilike
query.filter(User.name.like('%ed%'))
query.filter(User.name.ilike('%ed%')) # 不区分大小写
# in
query.filter(User.name.in_(['ed', 'wendy', 'jack']))
query.filter(User.name.in_(
session.query(User.name).filter(User.name.like('%ed%'))
))
# not in
query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
# is
query.filter(User.name == None)
query.filter(User.name.is_(None))
# is not
query.filter(User.name != None)
query.filter(User.name.is_not(None))
# and
from sqlalchemy import and_
query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
# or
from sqlalchemy import or_
query.filter(or_(User.name == 'ed', User.name == 'wendy'))
# match
query.filter(User.name.match('wendy'))
AI写代码十二、使用文本SQL
文字字符串可以灵活地用于Query 查询。
>>> from sqlalchemy import text
SQL>>> for user in session.query(User).\
... filter(text("id<224")).\
... order_by(text("id")).all():
... print(user.name)
ed
wendy
mary
fred
AI写代码可以通过冒号来指定绑定参数。建议采用以下方式来实现：若需指定具体的值，请参考[Query(params)](https://link.zhihu.com/?target=https%3A//www.osgeo.cn/sqlalchemy/orm/query.html#sqlalchemy.orm.Query.params "Query(params)\(\)")这一方法的具体操作中。
>>> session.query(User).filter(text("id<:value and name=:name")).\
... params(value=224, name='fred').order_by(User.id).one()
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>
AI写代码十三、一对多
允许多个邮件地址给一个用户是允许的，这意味着我们需要为每个用户创建一个关联表，并将其与用户表建立映射关系以实现数据查询操作。
>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship
4. >>> class Address(Base):
... __tablename__ = 'addresses'
... id = Column(Integer, primary_key=True)
... email_address = Column(String, nullable=False)
... user_id = Column(Integer, ForeignKey('users.id'))
...
... user = relationship("User", back_populates="addresses")
...
... def __repr__(self):
... return "<Address(email_address='%s')>" % self.email_address
>>> User.addresses = relationship(
... "Address", order_by=Address.id, back_populates="user")
AI写代码ForeignKey定义两列之间依赖关系，表示关联了用户表的用户ID
关联性告诉ORMAddress类本身应连接到User类，在这种情况下,back_populates 属性表示的是反向引用字段对应的表名称, 即反向引用字段对应的表名称就是当前表自身的名称.
十四、多对多
除了单对单关系外，在常见的博客网站平台中还存在着一对多的关系模式此外还存在一对多的关系这种情况下一个 BlogPost 对象通常会包含多个 Keyword 标签每个 Keyword 标签又能与多个不同的 BlogPost 实例相关联
在普通多对多场景中, 我们需建立一个非映射表构造用于作为关联表使用. 具体而言, 如下所示:
>>> from sqlalchemy import Table, Text
>>> # association table
>>> post_keywords = Table('post_keywords', Base.metadata,
... Column('post_id', ForeignKey('posts.id'), primary_key=True),
... Column('keyword_id', ForeignKey('keywords.id'), primary_key=True)
... )
AI写代码在下一步步骤中我们定义了BlogPost和Keyword类，并采用互补relationship进行构造；每个引用post_keywords表作为关联表。
>>> class BlogPost(Base):
... __tablename__ = 'posts'
...
... id = Column(Integer, primary_key=True)
... user_id = Column(Integer, ForeignKey('users.id'))
... headline = Column(String(255), nullable=False)
... body = Column(Text)
...
... # many to many BlogPost<->Keyword
... keywords = relationship('Keyword',
... secondary=post_keywords,
... back_populates='posts')
...
... def __init__(self, headline, body, author):
... self.author = author
... self.headline = headline
... self.body = body
...
... def __repr__(self):
... return "BlogPost(%r, %r, %r)" % (self.headline, self.body, self.author)
>>> class Keyword(Base):
... __tablename__ = 'keywords'
...
... id = Column(Integer, primary_key=True)
... keyword = Column(String(50), nullable=False, unique=True)
... posts = relationship('BlogPost',
... secondary=post_keywords,
... back_populates='keywords')
...
... def __init__(self, keyword):
... self.keyword = keyword
AI写代码多对多关系的其核心特性是通过secondary关键字参数引用指定的关联表实体实现关联功能。