SQLAlchemy及其案例

SQLAlchemy

官网原文链接如下：https://www.sqlalchemy.org/
SQLAlchemy 是 Python SQL 工具包和对象关系映射器，为应用程序开发人员提供 SQL 的全部功能和灵活性。
它提供了一整套众所周知的企业级持久化模式，专为高效、高性能的数据库访问而设计，并适应于简单且Pythonic的领域语言。

SQLAlchemy的基本原理

随着大小和性能变得越来越重要，SQL 数据库的行为不再像对象集合；对象集合的行为不像表和行，抽象越重要。SQLAlchemy 旨在适应这两个原则。

SQLAlchemy 将数据库视为关系代数引擎，而不仅仅是表的集合。不仅可以从表中选择行，还可以从连接和其他选择语句中选择行；这些单元中的任何一个都可以组成一个更大的结构。SQLAlchemy 的表达式语言从其核心建立在这个概念之上。

SQLAlchemy 以其对象关系映射器（ORM）而闻名，这是一个提供数据映射器模式的可选组件，其中类可以以开放式、多种方式映射到数据库 - 允许对象模型和数据库模式以一种从一开始就彻底解耦。

SQLAlchemy 解决这些问题的总体方法与大多数其他 SQL/ORM 工具完全不同，其根源在于所谓的面向互补性 的方法；所有流程都完全暴露在一系列可组合的透明工具中，而不是将 SQL 和对象关系细节隐藏在自动化的墙后面。该库承担了自动化冗余任务的工作，而开发人员仍然可以控制数据库的组织方式以及 SQL 的构建方式。

SQLAlchemy的主要特点

其官网链接如下：https://www.sqlalchemy.org/features.html

特点	说明
无需 ORM	SQLAlchemy 由两个不同的组件组成，称为 核心和ORM。Core 本身是一个功能齐全的 SQL 抽象工具包，为各种 DBAPI 实现和行为提供平滑的抽象层，以及允许通过生成式 Python 表达式来表达 SQL 语言的 SQL 表达式语言。既可以发出 DDL 语句又可以内省现有模式的模式表示系统，以及允许将 Python 类型映射到数据库类型的类型系统，使系统更加完善。对象关系映射器是一个构建在核心之上的可选包。许多应用程序严格构建在Core之上，使用SQL表达式系统来提供对数据库交互的简洁和精确的控制。
成熟的高性能架构	经过七年多的不断开发、分析和重构，我们形成了一个高性能、准确、全面覆盖测试并部署在数千个环境中的工具包。由于几乎每个主要组件都在第二次或第三次完整迭代中，SQLAlchemy 0.6 的速度大约是几年前旧版 0.4 版本的两倍，并且版本 0.7 和 0.8 继续改进。其原始执行速度与同类工具相比具有竞争力，并且先进的 ORM 功能（例如工作单元、内存中集合、通过联接或辅助子选择急切加载集合以及其他优化）使 SQLAlchemy 的 ORM 能够发出比 SQLAlchemy 更少且更高效的查询。任何以前的版本。
DBA 批准	旨在满足 DBA 的需求，包括用手动优化的语句替换生成的 SQL、对所有文字值充分使用绑定参数、使用工作单元模式进行完全事务化和批量数据库写入的能力。所有对象关系模式都是围绕正确的引用完整性的使用而设计的，而外键是其使用的一个组成部分。
不固执己见	SQLAlchemy 高度重视不妨碍数据库和应用程序架构。与许多工具不同，它 从不“生成”模式（不要与它擅长的发出用户定义的DDL混淆）或依赖于任何类型的命名约定。SQLAlchemy 尽可能支持最广泛的数据库和架构设计。
工作单元	工作单元系统是 SQLAlchemy 的对象关系映射器 (ORM) 的核心部分，它将挂起的插入/更新/删除操作组织到队列中，并在一批中将它们全部刷新。为了实现这一点，它对队列中的所有修改项执行拓扑“依赖性排序”，以便遵守行间依赖性，并将冗余语句分组在一起，有时可以进一步批处理它们。这会产生最大的效率和交易安全性，并最大限度地减少死锁的可能性。以Fowler 的“工作单元”模式以及 Java 领先的对象关系映射器Hibernate为模型。
基于函数的查询构造	基于函数的查询构造允许通过 Python 函数和表达式构建 SQL 子句。全部可能的内容包括布尔表达式、运算符、函数、表别名、可选择子查询、插入/更新/删除语句、相关更新、选择和EXISTS子句、UNION 子句、内连接和外连接、绑定参数以及文字的自由混合表达式中的文本。构造表达式可针对任意数量的供应商数据库实现（例如 PostgreSQL 或 Oracle）进行编译，具体取决于实现提供的“方言”和“编译器”的组合。
模块化和可扩展	SQLAlchemy 的不同部分可以独立于其余部分使用。连接池、SQL 语句编译和事务服务等元素可以彼此独立使用，也可以通过各种插件点进行扩展。集成事件系统允许在超过 50 个交互点注入自定义代码，包括核心语句执行、模式生成和内省、连接池操作、对象关系配置、持久性操作、属性突变事件和事务阶段。新的 SQL 表达式元素和自定义数据库类型可以无缝构建和集成
单独的映射和类设计	ORM 基于“声明式”配置系统进行标准化，该系统允许构建与其映射到的表元数据内联的用户定义类，就像大多数其他对象关系工具提供的方式一样。然而，这个系统是完全可选的 - 在其核心，ORM 认为用户定义的类、关联的表元数据以及两者的映射是完全独立的。通过使用该mapper()函数，任何任意Python类都可以映射到数据库表或视图。映射类还保留可序列化（pickling），以便在各种缓存系统中使用。
相关对象和集合的预加载和缓存	一旦加载，ORM 就会缓存对象之间的集合和引用，因此每次访问时都不需要发出 SQL。急切加载功能允许使用很少或仅一个查询来加载由集合和引用链接的对象的整个图表，可在每个映射或每个查询的基础上配置到精确的语句计数，而无需更改现有查询。“N+1”问题（ORM 需要为集合中的所有对象发出单独的语句）对于 SQLAlchemy 来说已经成为过去
复合（多列）主键	在 SQLAlchemy 中，主键和外键表示为列集；真正的复合行为是从头开始实现的。ORM 对有意义的（非代理）主键具有工业强度的支持，包括可变性和与 ON UPDATE CASCADE 的兼容性，以及对其他常见复合 PK 模式的显式支持，例如“关联”对象（与每个关联都附加了额外的含义）
自引用对象映射	ORM 支持自引用映射。可以通过适当的级联来创建、保存和删除邻接列表结构，除了非自引用结构之外，没有任何代码开销。任何深度的自引用结构的加载都可以调整为通过具有一系列连接的单个语句（即连接加载）递归地加载集合，或者通过多个语句，其中每个语句以不同的深度级别加载完整的记录集（即子查询负载）。使用“更新后”功能还可以本地支持具有相互依赖的外键对（即“许多x”/“一个特定x”）的表的持久性。
继承映射	显式支持单表、具体表和连接表继承。所有三种样式都支持多态加载（即返回多个后代类型的对象的查询）。每个的加载可以被优化，使得仅使用一次往返来完全加载多态结果集。
原始 SQL 语句映射	SQLA 的对象关系查询工具可以容纳原始 SQL 语句以及普通结果集，并且可以按照与任何其他 ORM 操作相同的方式从这些结果生成对象实例。您或您的 DBA 可以编写的任何超优化查询都可以在 SQLAlchemy 中运行，只要它返回行集中的预期列，您就可以从中获取对象。也可以使用表示多种对象的语句，将结果作为命名元组接收，或者将依赖对象路由到父对象的集合中。
数据的预处理和后处理	类型系统允许在绑定参数和结果集级别上对数据进行预处理和后处理。用户定义类型可以与内置类型自由混合。可以使用通用类型和 SQL 特定类型。
支持的平台	SQLAlchemy 支持 Python 2.5 到最新的 3.x 版本。其他支持的平台包括 Jython 和 Pypy。
支持的数据库	SQLAlchemy 包括 SQLite、Postgresql、MySQL、Oracle、MS-SQL、Firebird、Sybase 等方言，其中大多数支持多个 DBAPI。其他方言作为外部项目发布。 使用每个特定数据库需要相应的DB-API 2.0实现（或有时可用的几个实现之一）。查看当前的 DBAPI 支持

案例说明

示例 1: 使用 SQLAlchemy Core

SQLAlchemy Core 提供了一个 SQL 表达式语言，可以用于直接与数据库进行交互，而不需要 ORM

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String

# 创建连接
engine = create_engine('postgresql+psycopg2://user:password@localhost/mydatabase')

# 定义元数据
metadata = MetaData()

# 定义表
users = Table('users', metadata,
              Column('id', Integer, primary_key=True),
              Column('name', String),
              Column('age', Integer))

# 创建表
metadata.create_all(engine)

# 插入数据
with engine.connect() as conn:
    insert_statement = users.insert().values(name='张三', age=30)
    conn.execute(insert_statement)

# 查询数据
with engine.connect() as conn:
    select_statement = users.select()
    result = conn.execute(select_statement)
    for row in result:
        print(row)

示例 2: 使用 SQLAlchemy ORM

ORM 允许将 Python 类映射到数据库表上

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

# 创建连接
engine = create_engine('postgresql+psycopg2://user:password@localhost/mydatabase')
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 添加新用户
new_user = User(name='李四', age=25)
session.add(new_user)
session.commit()

# 查询所有用户
users = session.query(User).all()
for user in users:
    print(user.name, user.age)

示例 3: 事务处理

在 SQLAlchemy 中，事务可以通过会话（Session）来管理

# 继续使用上面定义的 Session 和 User 类

# 开始一个新的事务
session = Session()

try:
    # 执行一些数据库操作
    session.add(User(name='王五', age=28))
    session.add(User(name='赵六', age=33))

    # 提交事务
    session.commit()
except:
    # 如果发生异常，回滚事务
    session.rollback()
    raise
finally:
    # 关闭会话
    session.close()

示例 4: 原生 SQL 语句

SQLAlchemy 也支持执行原生 SQL 语句

# 继续使用上面定义的 engine

# 执行原生 SQL
with engine.connect() as conn:
    result = conn.execute("SELECT * FROM users WHERE age > 30")
    for row in result:
        print(row)

示例 5: 复合主键和自引用映射

SQLAlchemy 也支持复合（多列）主键和自引用表的映射

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

# 创建表
Base.metadata.create_all(engine)

# 插入和查询数据
with Session() as session:
    parent = Parent(id=1)
    child1 = Child(id=1, parent_id=1)
    child2 = Child(id=2, parent_id=1)
    
    session.add_all([parent, child1, child2])
    session.commit()

    # 查询
    p = session.query(Parent).filter_by(id=1).first()
    print("Parent ID:", p.id)
    for c in p.children:
        print("Child ID:", c.id)

示例 6: 继承映射

SQLAlchemy 支持几种继承映射策略，例如单表继承

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50))
    
    __mapper_args__ = {
        'polymorphic_identity':'employee',
        'polymorphic_on':type
    }

class Engineer(Employee):
    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }

    # Engineer 特有的属性
    engineer_info = Column(String(100))

# 创建表
Base.metadata.create_all(engine)

# 插入和查询数据
with Session() as session:
    engineer = Engineer(name='Tom', engineer_info='Python Developer')
    session.add(engineer)
    session.commit()

    # 查询
    e = session.query(Engineer).first()
    print(e.name, e.engineer_info)

示例 7: 预处理和后处理数据

from sqlalchemy.types import TypeDecorator, String

class MyUpperCase(TypeDecorator):
    impl = String

    def process_bind_param(self, value, dialect):
        if value is not None:
            return value.upper()
        return value

    def process_result_value(self, value, dialect):
        return value.lower()

class User(Base):
    __tablename__ = 'user_with_custom_type'
    id = Column(Integer, primary_key=True)
    name = Column(MyUpperCase(50))

# 创建表
Base.metadata.create_all(engine)

# 插入和查询数据
with Session() as session:
    user = User(name='alice')
    session.add(user)
    session.commit()

    # 查询
    u = session.query(User).first()
    print(u.name)  # 输出为小写，因为 process_result_value 将结果转为小写

示例 8: 懒加载和急加载

SQLAlchemy 的 ORM 提供了丰富的加载策略，包括懒加载（默认）和急加载

class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    books = relationship("Book", back_populates="author", lazy='select')

class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True)
    title = Column(String)
    author_id = Column(Integer, ForeignKey('author.id'))
    author = relationship("Author", back_populates="books")

# 创建表
Base.metadata.create_all(engine)

# 插入数据
with Session() as session:
    author = Author(name='余华')
    book1 = Book(title='活着', author=author)
    book2 = Book(title='许三观卖血记', author=author)
    session.add_all([author, book1, book2])
    session.commit()

# 查询数据
with Session() as session:
    author = session.query(Author).filter_by(name='余华').first()
    print("作者:", author.name)
    for book in author.books:
        print("书名:", book.title)

示例 9: 使用原生 SQL 和混合查询

SQLAlchemy 允许您执行原生 SQL 语句，并将结果映射到对象

# 执行原生 SQL 查询
with engine.connect() as connection:
    result = connection.execute("SELECT * FROM book")
    for row in result:
        print("书名:", row.title)

# 使用 text 对象进行查询
from sqlalchemy import text

with Session() as session:
    books = session.query(Book).from_statement(
        text("SELECT * FROM book WHERE title=:title")
    ).params(title='活着').all()
    for book in books:
        print("书名:", book.title)

示例 10: 事件系统和插件

SQLAlchemy 提供了一个事件系统，允许在各种操作中插入自定义代码

from sqlalchemy import event

# 定义在插入数据之前执行的函数
@event.listens_for(User, 'before_insert')
def before_insert(mapper, connection, target):
    print("即将插入数据:", target.name)

# 插入数据，触发事件
with Session() as session:
    user = User(name='小王')
    session.add(user)
    session.commit()

示例 11: 多数据库连接

SQLAlchemy 允许同时连接多个数据库，并在这些数据库之间进行操作

# 创建第二个数据库引擎
engine2 = create_engine('postgresql+psycopg2://user:password@localhost/otherdatabase')

# 使用第二个引擎操作另一个数据库
with engine2.connect() as connection:
    result = connection.execute("SELECT * FROM some_other_table")
    for row in result:
        print(row)

示例 12: 使用 SQL 函数和别名

SQLAlchemy 可以使用各种 SQL 函数，并对表和列使用别名。

from sqlalchemy import func

# 使用函数和别名
with Session() as session:
    q = session.query(
        Author.name.label('author_name'),
        func.count(Book.id).label('book_count')
    ).join(Author.books).group_by(Author.name)

    for author_name, book_count in q:
        print(f"作者：{author_name}, 书籍数量：{book_count}")

示例 13: 使用混合属性

混合属性允许您将字段逻辑定义在模型层面，可用于计算值或其他复杂操作。

from sqlalchemy.ext.hybrid import hybrid_property

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    first_name = Column(String)
    last_name = Column(String)

    @hybrid_property
    def full_name(self):
        return f"{self.first_name} {self.last_name}"

# 创建表
Base.metadata.create_all(engine)

# 插入和查询数据
with Session() as session:
    customer = Customer(first_name='李', last_name='雷')
    session.add(customer)
    session.commit()

    c = session.query(Customer).first()
    print("客户全名:", c.full_name)

示例 14: 多表联合查询

SQLAlchemy 允许执行多表的联合查询

# 多表联合查询
with Session() as session:
    q = session.query(Author, Book).filter(Author.id == Book.author_id).all()
    for author, book in q:
        print(f"作者：{author.name}, 书名：{book.title}")

示例 15: 自定义编译 SQL 表达式

您可以自定义编译 SQL 表达式，以适应特定数据库的语言

from sqlalchemy.sql import expression

class my_custom_expression(expression.FunctionElement):
    name = "my_custom_function"

@compiles(my_custom_expression, 'postgresql')
def pg_my_custom_expression(element, compiler, **kw):
    return "MY_POSTGRESQL_FUNCTION()"

# 使用自定义表达式
with engine.connect() as conn:
    result = conn.execute(select([my_custom_expression()]))
    for row in result:
        print(row)

示例 16: 动态加载关联

SQLAlchemy 允许您动态加载关联，这对于处理大型集合特别有用，因为它允许延迟加载项的集合

class Publisher(Base):
    __tablename__ = 'publisher'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    books = relationship("Book", back_populates="publisher", lazy='dynamic')

class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True)
    title = Column(String)
    publisher_id = Column(Integer, ForeignKey('publisher.id'))
    publisher = relationship("Publisher", back_populates="books")

# 创建表
Base.metadata.create_all(engine)

# 插入和查询数据
with Session() as session:
    publisher = Publisher(name='出版社A')
    book1 = Book(title='书A', publisher=publisher)
    book2 = Book(title='书B', publisher=publisher)
    session.add_all([publisher, book1, book2])
    session.commit()

    p = session.query(Publisher).filter_by(name='出版社A').first()
    books = p.books.order_by(Book.title).all()
    for book in books:
        print("书名:", book.title)

示例 17: 多对多关系

SQLAlchemy 可以方便地处理多对多关系，通过使用关联表来建立两个模型之间的关系

from sqlalchemy import Table

# 多对多关联表
author_book = Table('author_book', Base.metadata,
    Column('author_id', Integer, ForeignKey('author.id')),
    Column('book_id', Integer, ForeignKey('book.id'))
)

class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    books = relationship("Book", secondary=author_book, back_populates="authors")

class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True)
    title = Column(String)
    authors = relationship("Author", secondary=author_book, back_populates="books")

# 创建表
Base.metadata.create_all(engine)

# 插入数据
with Session() as session:
    author1 = Author(name='作者1')
    author2 = Author(name='作者2')
    book = Book(title='共同的书', authors=[author1, author2])
    session.add(book)
    session.commit()

示例 18: 自定义事件监听

利用 SQLAlchemy 的事件系统，您可以对各种数据库事件进行自定义监听和处理

@event.listens_for(Book, 'after_insert')
def receive_after_insert(mapper, connection, target):
    print(f"刚刚插入了书籍：{target.title}")

with Session() as session:
    book = Book(title='新书')
    session.add(book)
    session.commit()

示例 19: 分页和排序

SQLAlchemy 查询提供了方便的分页和排序功能

with Session() as session:
    # 获取第一页，每页两条数据
    page1 = session.query(Book).order_by(Book.title).limit(2).all()
    print("第一页书籍:")
    for book in page1:
        print(book.title)

    # 获取第二页
    page2 = session.query(Book).order_by(Book.title).offset(2).limit(2).all()
    print("\n第二页书籍:")
    for book in page2:
        print(book.title)

示例 20: 级联删除和更新

在 ORM 中，您可以设置级联操作，使得在删除或更新一个对象时，相关联的对象也会相应地被删除或更新

class Order(Base):
    __tablename__ = 'order'
    id = Column(Integer, primary_key=True)
    customer_id = Column(Integer, ForeignKey('customer.id'))
    customer = relationship("Customer", back_populates="orders", cascade="all, delete-orphan")

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    orders = relationship("Order", back_populates="customer")

# 创建表
Base.metadata.create_all(engine)

# 插入数据
with Session() as session:
    customer = Customer(name='顾客1')
    order = Order(customer=customer)
    session.add(customer)
    session.commit()

    # 级联删除
    session.delete(customer)
    session.commit()

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String  # 根据需要导入其他类型

# 定义 ORM 模型
Base = declarative_base()

class YourTable(Base):
    __tablename__ = 'your_table'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    # 定义其他字段...

# 数据库连接
mysql_engine = create_engine('mysql+pymysql://user:password@localhost/mysql_db')
oracle_engine = create_engine('oracle+cx_oracle://user:password@host:port/oracle_db')
postgresql_engine = create_engine('postgresql+psycopg2://user:password@localhost/postgresql_db')

# 创建会话
SessionMySQL = sessionmaker(bind=mysql_engine)
SessionPostgreSQL = sessionmaker(bind=postgresql_engine)
# 对 Oracle 进行同样的操作，如果需要

def sync_data(source_session, target_session):
    source_data = source_session.query(YourTable).all()
    for data in source_data:
        # 检查目标数据库是否已有数据
        exists = target_session.query(YourTable).filter_by(id=data.id).first()
        if not exists:
            # 插入到目标数据库
            target_session.add(YourTable(id=data.id, name=data.name))  # 根据实际字段调整
    target_session.commit()

def main():
    session_mysql = SessionMySQL()
    session_postgresql = SessionPostgreSQL()

    # 从 MySQL 同步到 PostgreSQL
    sync_data(session_mysql, session_postgresql)

    # 关闭会话
    session_mysql.close()
    session_postgresql.close()

if __name__ == "__main__":
    main()