Python 之SQLAlchemy使用详细说明

定义：ORM（Object-Relational Mapping）模型，即对象关系映射，是一种程序设计技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。在面向对象的编程语言中，如Java、Python等，数据通常被组织成复杂的对象关系。简单来说就是ORM模型把面向对象编程与操作数据库之间建立了映射。设置开发者操作数据库无需维护和编写SQL语句，而是基于面对对象的方式操作数据库。

映射关系：数据库中的表>编程语言中的类，表中的字段>类中的属性，表之间的关系>类之间的关系。

使用ORM模型的优势在于：

提高开发效率：开发者可以使用面向对象的方式来操作数据库，无需编写大量的SQL语句。
易于维护：由于ORM模型提供了清晰的映射规则，使得代码更加清晰、易于理解和维护。
跨数据库平台：ORM框架通常支持多种数据库平台，使得应用程序可以轻松地在不同的数据库之间迁移。

当然ORM也存在一下劣势：

性能开销：ORM框架在将对象的操作转换为SQL语句并执行这些语句时，可能会产生一定的性能开销。
复杂查询的复杂性：在处理复杂的查询时，ORM的语法可能会变得复杂且难以维护。

1.2、SQLAlchemy概述

在Python语言中实现ORM系统的就是SQLAlchemy，它具备以下特点：

对象关系映射（ORM）：SQLAlchemy 允许开发者将数据库表映射为 Python 类，将表的行映射为 Python 对象，从而简化了数据库操作。开发者可以像操作 Python 对象一样来操作数据库记录，无需编写大量的 SQL 语句。
动态 SQL 生成：SQLAlchemy 提供了表达式语言，允许开发者在运行时动态地构建 SQL 语句。这使得开发者能够灵活地根据应用程序的需求来生成和执行 SQL 语句。
支持多种数据库：SQLAlchemy 支持多种关系型数据库，如 MySQL、PostgreSQL、SQLite、Oracle 等。开发者可以轻松地切换数据库后端，而无需修改太多代码。
连接池和事务管理：SQLAlchemy 提供了连接池和事务管理的功能，以确保数据库连接的稳定性和事务的原子性。这有助于开发者编写高效、可靠的数据库应用程序。
关系映射：SQLAlchemy 支持各种关系数据库中的关系类型，如一对一、一对多、多对多等，并提供了相应的 API 来处理这些关系。

然而，ORM模型也存在一些缺点：

性能开销：ORM框架在将对象的操作转换为SQL语句并执行这些语句时，可能会产生一定的性能开销。
复杂查询的复杂性：在处理复杂的查询时，ORM的语法可能会变得复杂且难以维护。

注意：在处理复杂的SQL查询时，由于ORM框架效率低下，所以这个时候可以编写SQL语句执行原生SQL语句。

1.3、SQLAlchemy的组成部分

1、核心架构（Core）：

引擎（Engine）：负责与数据库的通信，管理连接池和事务。它是SQLAlchemy与数据库交互的入口点。
连接（Connection）：代表与数据库的单个连接会话。它是执行SQL语句的直接通道。
会话（Session）：在ORM中使用，代表与数据库的持久化会话。它用于管理对象的持久化，包括添加、修改、删除和查询对象。
元数据（Metadata）：用于定义和存储关于数据库结构的信息，如表和列的定义。
表（Table）：表示数据库中的一个表。
列（Column）：表示表中的一个列。
类型（Types）：用于定义列的数据类型。
表达式构造器（Expression Language）：用于构建SQL表达式，如select(), insert(), update(), delete()等。

2、ORM架构：

声明基类（Declarative Base）：用于定义ORM模型。
模型（Model）：表示数据库中的一个表，由Python类定义。
属性（Attributes）：表示模型的属性，与数据库表的列相对应。
关系（Relationships）：表示模型之间的关系，如一对多、多对多等。

4、数据库连接池（Connection Pooling）：管理数据库连接的池化，确保高效的数据库连接复用。

5、Dialect：选择连接数据库的DB API种类，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作。

6、架构和类型（Schema/Types）：定义数据库的架构和数据类型。

7、SQL表达式语言（SQL Expression Language）：

选择（SELECT）：使用select()构造器来构建查询语句。
该语言允许用户以非常灵活和直观的方式构造SQL语句，而无需直接编写SQL字符串。

1.4、SQLAlchemy的使用

SQLAlchemy官方文档：Dialects — SQLAlchemy 2.0 Documentation

1.4.1、安装

pip install sqlalchemy

1.4.2、创建数据库连接

注意：sqlalchemy没有提供直接连接数据库的操作，所以需要借助第三方库来连接数据库，操作数据库。以 MySQL 为例，sqlalchemy就是借助pymsql库来实现对数据的连接和操作。

连接不同/相同的数据库借助不同的第三方库如下：

MySQL-Python
mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
pymysql
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
MySQL-Connector
mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
cx_Oracle
oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

创建连接：

from sqlalchemy import create_engine
from urllib import parse

user = "root" # 用户名
password = "" # 密码
pwd = parse.quote_plus(password)   # 解决密码中含@符导致报错
host = "172.22.70.174" # 数据库主机地址
# 第一步: 创建engine
engine = create_engine(
    url=f"mysql+pymysql://{user}:{pwd}@{host}:3306/test?charset=utf8",
    max_overflow=10,  # 超过连接池大小外最多创建的连接
    pool_size=10,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 对线程池中的线程进行一次连接的回收的时间，如果是3600，表示1个小时后对连接进行回收
)

1.4.3、执行原生SQL语句

需求：查询表t_student的全部数据，执行的SQL语句是：select * from t_student

from sqlalchemy import create_engine
from urllib import parse
import threading

user = "root" # 用户名
password = "" # 密码
pwd = parse.quote_plus(password)   # 解决密码中含@符导致报错
host = "172.22.70.174" # 数据库主机地址
# 第一步: 创建engine
engine = create_engine(
    url=f"mysql+pymysql://{user}:{pwd}@{host}:3306/test?charset=utf8",
    max_overflow=2,  # 超过连接池大小外最多创建的连接
    pool_size=3,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 对线程池中的线程进行一次连接的回收的时间，如果是3600，表示1个小时后对连接进行回收
)

# 第二步：使用
def test_execute():
    # conn = engine.connect() # 创建一个新的连接
    conn = engine.raw_connection()  # 从连接池中取一个连接
    cursor = conn.cursor() # 创建游标
    sql = "select * from t_student" # 定义执行的SQL语句
    cursor.execute(sql) # 执行SQL语句
    print(cursor.fetchall()) # 获取执行的结果并打印置控制台

# 测试配置是否生效
if __name__ == '__main__':
    for i in range(20):
        t = threading.Thread(target=test_execute)
        t.start()

1.4.4、映射已存在的表

说明：使用ORM映射已存在的表时，只能映射其对应的字段，对于每个字段的约束最好和原表保持一致，映射已存在的表时不能新增字段，新增外键约束，新增索引操作。如果需要进行这些操作可以数据库中执行相关sql语句或者使用SQLAlchemy的迁移工具（如Alembic）

注意：

在某些情况下，即使SQLAlchemy模型中的约束与原始数据库表不完全一致，应用程序可能仍然能够正常运行。但是，这可能会导致数据完整性问题或难以预测的行为。
因此，最佳实践是尽可能保持SQLAlchemy模型中的约束与原始数据库表一致。

示例：

需求：创建Student表模型映射数据库中的t_student表

import datetime
from sqlalchemy.orm import declarative_base , sessionmaker
from sqlalchemyBaseUse import engine # 这里的engine就是上面创建连接中创建的engine
from sqlalchemy import Column, Integer, String, Text,  DateTime, Index

# 声明ORM基类
Base = declarative_base()

class Student(Base):
    __tablename__ = 't_student'
    id = Column(Integer, primary_key=True)
    stuno = Column(String(10), comment="学号")
    name = Column(String(10), comment="姓名")
    gender = Column(String(1), comment="性别")
    age = Column(Integer, comment="年龄")
    idcard = Column(String(18), comment="身份证")
    entrydate = Column(DateTime, default=datetime.datetime.now, comment="入学时间")
    addr = Column(String(50), comment="家庭地址")

def init_db():
    # 创建继承base类的表的映射关系
    Base.metadata.create_all(engine)

def drop_db():
    # 删除继承base类的表，注意：除了删除表的映射关系，数据库中的表和数据都会被删减，生产中谨慎操作
    Base.metadata.drop_all(engine)

if __name__ == '__main__':
    init_db()

1.4.5、创建表

1.4.5.1、创建表的两种方式

1、使用 Table 类直接创建表

编程风格：这是一种更接近于 SQL 风格的方法，因为它直接定义了表的列和约束，没有额外的类定义。
用法：你需要显式地创建 Table 对象，并指定列和约束。这种方法在不需要 ORM 功能，只需要直接操作数据库表的场景中特别有用。
灵活性：由于这种方法没有与 ORM 类绑定，因此它更加灵活，可以更容易地用于更复杂的数据库操作或与其他数据库工具集成。

示例：

from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table, CheckConstraint  
  
# 假设已经有了一个引擎和元数据  
engine = create_engine('sqlite:///:memory:')  
metadata = MetaData()  

# 创建一个表，并添加一个检查约束  
my_table = Table('my_table', metadata,  
    Column('id', Integer, primary_key=True),  
    Column('name', String(50)),  
    Column('age', Integer),  
    CheckConstraint("age >= 0 AND age <= 150", name='age_check')  
)  
  
# 创建表（包括检查约束）  
metadata.create_all(engine)

2、使用声明式方式创建模型类

编程风格：这是一种面向对象的方法，通过定义类来定义表结构。这种方式更加符合 Python 的编程习惯，并且与 ORM 紧密集成。
用法：你需要继承一个由 declarative_base() 创建的基类，并在子类中定义字段和关系。字段通常使用 Column 类定义，而关系则使用 relationship() 函数定义。SQLAlchemy 的 ORM 层会将这些类定义转换为实际的数据库表。
灵活性：虽然这种方法不如直接使用 Table 类那么灵活，但它提供了更高级别的抽象和更强大的 ORM 功能。通过 ORM，你可以使用 Python 对象来操作数据库记录，而无需编写繁琐的 SQL 语句。

示例：

在 SQLAlchemy 的声明式（Declarative）映射中，__table_args__ 是一个类变量，用于指定表级别的参数和选项，这些参数和选项在创建表时会被应用到数据库表上。__table_args__ 通常是一个元组，其中包含多个 SQLAlchemy 提供的表级构造器，如 UniqueConstraint、ForeignKeyConstraint、Index、CheckConstraint 等。

from sqlalchemy import Column, Integer, String, CheckConstraint  
from sqlalchemy.ext.declarative import declarative_base  
from sqlalchemy.orm import sessionmaker  
  
Base = declarative_base()  
  
class MyModel(Base):  
    __tablename__ = 'my_table'  
  
    id = Column(Integer, primary_key=True)  
    name = Column(String(50))  
    age = Column(Integer)  
  
    __table_args__ = (  
        CheckConstraint("age >= 0 AND age <= 150", name='age_check'),  
    )

1.4.5.2、约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。

分类：

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本之后)	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

1、创建约束示列

需求如下：

要求创建一张名为t_user的数据表，各个字段的规则如下：

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID唯一标识	int	主键，并且自动增长	PRIMARY KEY,AUTO_INCREMENT
name	姓名	varchar(10)	不为空，并且唯一	NOT NULL , UNIQUE
age	年龄	int	大于0，并且小于等于150	CHECK
status	状态	char(1)	如果没有指定该值，默认为1	DEFAULT
gender	性别	char(1)	无

from sqlalchemy.orm import declarative_base , sessionmaker
from sqlalchemyBaseUse import engine
from sqlalchemy import Column, Integer, String, CheckConstraint

# 声明ORM基类
Base = declarative_base()

class UserModel(Base):
    __tablename__ = "t_user"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(10), nullable=True, unique=True, comment="姓名")
    age = Column(Integer, comment="年龄")
    status = Column(String(1), default=1, comment="状态")
    gender = Column(String(1), comment="性别")

    # 添加age的检查约束
    # 注意__table_args__是元祖数据类型，如果只有一个数据的时候，注意后面的逗号不能少
    __table_args__ = (
       CheckConstraint("age >0 AND age <= 150", name="age_check"),
    )

def init_db():
    # 创建继承base类的表的映射关系
    Base.metadata.create_all(engine)

if __name__ == '__main__':
    init_db()

2、外键约束

作用：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

mysql 语法：

1、添加外键
CREATE TABLE 表名(
字段名数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);
或
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)REFERENCES 主表 (主表列名) ;

2、删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

在 SQLAlchemy 中，创建外键约束通常不需要直接使用 __table_args__，因为 SQLAlchemy 提供了 ForeignKey 和 relationship 这两个工具来定义关系和外键约束。

示例：

需求：创建一张部门表和员工表，建立外键约束关系一个员工对应一个部门。

部门表 t_departments

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID唯一标识	int	主键，并且自动增长	PRIMARY KEY,AUTO_INCREMENT
name	部门名称	varchar(50)	非空约束	NOT NULL

员工表 t_employees

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID唯一标识	int	主键，并且自动增长	PRIMARY KEY,AUTO_INCREMENT
name	姓名	varchar(50)	非空约束	NOT NULL
age	年龄	int
dept_id	部门id	int	外键约束	FOREIGN KEY

1、创建ORM表模型如下：

from sqlalchemy.orm import declarative_base, relationship
from sqlalchemyBaseUse import engine
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index, CheckConstraint

# 声明ORM基类
Base = declarative_base()
# 创建部门表
class Department(Base):
    __tablename__ = 't_departments'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False, comment="部门名称")

    # 定义与 Employee 的一对多关系
    employees = relationship("Employee", back_populates="department")

# 创建员工表
class Employee(Base):
    __tablename__ = 't_employees'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False, comment="姓名")
    age = Column(Integer, comment="年龄")
    department_id = Column(Integer, ForeignKey('t_departments.id', ondelete="CASCADE"), nullable=False)
    # 定义与 Department 的多对一关系
    department = relationship("Department", back_populates="employees")

def init_db():
    # 创建继承base类的表的映射关系
    Base.metadata.create_all(engine)

if __name__ == '__main__':
    init_db()

2、往数据库中插入测试数据如下：

# 部门表测试数据
INSERT INTO t_departments (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部');

# 员工表测试数据
INSERT INTO t_employees (id, name, age, department_id)
VALUES (1, '张三', 22, 1), (2, '李四', 33, 1), (3, 'TOM', 30, 2), (4, '小艺', 25, 2), (5, '小李', 36, 3);

3、展示通过关联关系查询，如：查询员工姓名为张三对应的部门信息

def query():
    Session = sessionmaker(bind=engine)
    session = Session()
    # 查询姓名为张三的员工
    employee = session.query(Employee).filter_by(name="张三").first()
    # 如果找到了员工，则获取其部门信息
    if employee:
        department = employee.department
        print(f"员工姓名: {employee.name}, 部门名称: {department.name}")
    else:
        print("没有找到姓名为'张三'的员工")


if __name__ == '__main__':
    # init_db()
    query()

4、展示通过关联关系查询，如：查询研发部所有员工信息

def query2():
    Session = sessionmaker(bind=engine)
    session = Session()
    # 直接通过部门对象获取其员工
    department = session.query(Department).filter_by(name='研发部').first()
    if department:
        for employee in department.employees:  # 使用了Department 模型中有一个名为 'employees' 的反向关联
            print(f"员工姓名: {employee.name}, 员工ID: {employee.id}")

if __name__ == '__main__':
    # init_db()
    query2()

还是上面4的示例，如果没有使用反向关联，在正常情况下我们应该如何查询呢？

思考：如果正常在数据库中查询时，利用的外键关系进行查询，可以使用内连接查询，对应的SQL语句如下：

select e.*, d.name from t_departments d inner join t_employees e on e.department_id=d.id where d.name='研发部';

那么把SQL语句对应到ORM中就是（使用子查询）：

1、先在t_departments表中把研发部对应的部门id查询出来
2、然后在t_employees表中把上面查询出来的部门id对应的员工信息查询出来实现代码如下：

def query3():
    Session = sessionmaker(bind=engine)
    session = Session()
    # 1、先查询研发部信息
    department = session.query(Department).filter_by(name='研发部').first()

    # 如果没有找到研发部，则退出查询
    if not department:
        print("没有找到研发部")
        session.close()
        exit()

    # 2、根据研发部的部门id查询所有员工
    employees = session.query(Employee).filter_by(department_id=department.id).all()
    # 打印研发部所有员工的信息
    for employee in employees:
        print(f"员工姓名: {employee.name}, 员工ID: {employee.id}")

if __name__ == '__main__':
    # init_db()
    query3()

综上：使用反向关联可以直接查询所需要的数据，效率更高，代码更加简洁。

5、relationship的使用说明

可以看到上面两张表中分别创建了一个反向关联如下：

t_departments： employees = relationship("Employee", back_populates="department")

t_employees ：department = relationship("Department", back_populates="employees")

参数说明：

relationship的第一个参数：对应关联的模型类的类名（字符串形式），如在t_departments表中建立一个与t_employees的关联，而t_employees表对应的数据模型类为：Employee，所以第一个参数就是Employee
relationship的第二个参数：是一个可选参数，反向引用另一个数据模型类里的属性，引用的属性必须在关联的数据模型类中显示存在。如： employees = relationship("Employee", back_populates="department") 这意味着在 Employee 类中，必须要有一个名为 department 的属性，该属性可以自动访问到关联的 Department 对象。

3、删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，就称为删除/更新行为。具体的删除/更新行为有以下几种：

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 RESTRICT 一致) 默认行为
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 NO ACTION 一致) 默认行为
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则同时删除/更新外键在子表中的记录。
SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。
SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持)

对应SQL语法：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

示例：

在上面的t_employees表中我们创建了一个外键，并指定了外键的删除行为为CASCADE，也就是如果父表t_departments发生了删除，那么对应子表t_employees中外键关联的数据也会被删除。

department_id = Column(Integer, ForeignKey('t_departments.id', ondelete="CASCADE"), nullable=False)

注意事项：

在本例中，如果ondelete="SET NULL"或onupdate="SET NULL" 会出现建立外键失败的错误，因为在t_employees表中的外键字段department_id设置了非空约束（nullable=False），这就和删除更新行为“SET NULL”互相冲突，所以会出现创建外键失败。

1.4.5.3、多表关系

1、relationship 的作用与使用

relationship 是 SQLAlchemy 库中的一个重要功能，它用于在模型（或称为“表”）之间建立关联关系。以下是对 relationship 的详细说明，包括其作用和使用方式：

作用：

定义模型之间的关系：relationship 允许你定义模型之间的一对一、一对多、多对一和多对多关系。这种关系映射了数据库中表之间的关系，使得你可以以面向对象的方式处理数据。
自动关联查询：通过 relationship 定义的关联关系，SQLAlchemy 可以在查询时自动进行关联查询，从而简化了开发过程。
反向引用：使用 backref 参数，你可以在一个关系上定义反向引用，这使得从一个对象可以轻松地访问与之相关的另一个对象。

使用方式：

1、引入必要的模块：


from sqlalchemy.orm import relationship

2、定义模型：
- 在模型中，使用 Column 定义字段，使用 ForeignKey 定义外键，外键也可以不用定义
- 使用 relationship 定义与其他模型的关系。
3、建立关联关系：
- 一对一关系：通过在从表模型中增加字段和 relationship 对象，并使用 uselist=False 参数指定。
- 一对多关系：在“多”的表中设置外键，并使用 relationship 函数指向“一”的表。
- 多对多关系：创建一个包含两个外键的“中间表”，并在两个需要做多对多关系的模型中分别使用 relationship 函数，并通过 secondary 参数指定中间表。
4、参数设置：
- backref：定义反向引用，使得从另一个模型可以方便地访问当前模型。
- uselist：对于一对一关系，设置为 False 表示关联的对象不是列表，而是单个对象。
- cascade：定义级联操作，如 "all, delete-orphan" 表示当父对象被删除时，所有相关的子对象也将被删除。
- lazy：是用于控制关系加载方式的。这个参数决定了当从数据库中查询一个对象时，相关的对象是如何被加载的。lazy 参数可以接受几个不同的值，每个值都对应着不同的加载策略：
- select (默认值): 使用单独的 SELECT 语句来加载相关对象。这是默认的加载策略，因为它简单且直观。但是，如果你知道你会经常访问相关的对象，并且想要减少数据库的查询次数，那么使用其他的加载策略可能会更有效。
- order_by：指定关联对象的排序方式。
- joined: 使用 JOIN 来加载主对象和相关对象。这通常会导致更大的查询，但可以减少查询的数量，特别是当你需要访问相关的对象时。
- dynamic: 这会返回一个可以发出额外查询的查询对象。这意味着你可以根据需要来动态地加载相关的对象，而不是立即加载它们。这允许你执行更复杂的查询或操作相关的对象集合。
- noload: 不加载相关的对象。这可以用于在你知道不需要相关的对象时节省数据库查询。
- immediate: 这会立即加载关系，而不是在首次访问相关对象时。然而，这种策略在 SQLAlchemy 的当前版本中并不常用，因为它可能会导致意外的行为。
- subquery: 使用子查询来加载相关对象。这种策略在某些情况下可能比默认的 select 策略更有效，因为它可以在一个查询中加载多个相关的对象。
5、查询：
- 通过 relationship 定义的关联关系，你可以使用 SQLAlchemy 的查询 API 进行复杂的关联查询。

2、一对一关系

实现：在任意一方添加外键，关联另一方的主键，并设置外键为唯一的（UNIQUE），一般用作单表的拆分【注：这里的实现是指在MySQL中的实现方式，但是在ORM中实现思路是一样的】

示列：用户与用户详情的关系

关系：一对一关系，用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，用来提升操作效率。

1、方式一：使用back_populates参数实现反向关联

# 声明ORM基类
Base = declarative_base()

class User(Base):
    __tablename__ = 't_user'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False, comment="姓名")

    # 定义一个关系来访问UserDetail对象
    detail = relationship("UserDetail", uselist=False, back_populates="user")

class UserDetail(Base):
    __tablename__ = 't_user_details'

    id = Column(Integer, primary_key=True, autoincrement=True)
    address = Column(String(100), comment="地址")
    phone = Column(String(20), comment="电话")
    # 外键列，引用t_user表的id
    user_id = Column(Integer, ForeignKey('t_user.id'), nullable=False, unique=True)
    # 定义一个关系来回引用User对象
    user = relationship("User", back_populates="detail")

2、方式二：使用backref参数实现反向关联

说明：backref 参数是 relationship() 函数的一个非常有用的功能，它允许我们自动创建反向关系，而无需在另一个模型类中显式定义它。使用 backref 可以简化代码并减少冗余。

在这个例子中，User 类中的 detail 关系使用了 backref="user" 参数。这会在 UserDetail 类的实例上自动创建一个名为 user 的属性，该属性是一个指向与之关联的 User 对象的引用。因此，我们不再需要在 UserDetail 类中显式定义 user 关系。

现在，如果你有一个 UserDetail 对象 ud，你可以通过 ud.user 访问与之关联的 User 对象。同样地，如果你有一个 User 对象 u，你可以通过 u.detail 访问与之关联的 UserDetail 对象（假设它存在）。

class User(Base):
    __tablename__ = 't_user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False, comment="姓名")

    # 使用 backref 自动创建反向关系  
    detail = relationship("UserDetail", uselist=False, backref="user")

class UserDetail(Base):
    __tablename__ = 't_user_details'

    id = Column(Integer, primary_key=True, autoincrement=True)
    address = Column(String(100), comment="地址")
    phone = Column(String(20), comment="电话")
    # 外键列，引用t_user表的id
    user_id = Column(Integer, ForeignKey('t_user.id'), nullable=False, unique=True)
    # 注意：这里我们不再需要显式定义 user 关系，因为 backref 已经为我们做了
    # 定义一个关系来回引用User对象
    # user = relationship("User", back_populates="detail")

3、backref与back_populates的区别

backref

backref 是一个方便的工具，用于在 relationship() 中自动创建反向的 relationship()。它通常用于一对多或多对一关系，其中反向关系是一个简单的属性。
当你使用 backref 时，SQLAlchemy 会为你自动创建一个反向的 relationship()，并附加到目标模型类上。你不需要在目标模型类中显式定义这个关系。

back_populates

back_populates 是一个更底层的参数，用于在定义双向关系时手动指定反向的 relationship() 属性的名称。当你需要在两个模型类中都明确控制关系的各个方面时，或者当你正在处理更复杂的关系（如多对多）时，back_populates 会很有用。
使用 back_populates 时，你需要在两个模型类中都显式定义 relationship()，并使用 back_populates 参数来指示哪个属性是反向关系。

3、一对多/多对一关系

实现：在多的一方建立外键，指向一的一方的主键

示例：部门表和员工表关系

关系：一个员工对应一个部门，一个部门对应多个员工

class Department(Base):
    __tablename__ = 't_departments'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False, comment="部门名称")

    # 定义与 Employee 的一对多关系
    employees = relationship("Employee", back_populates="department")

class Employee(Base):
    __tablename__ = 't_employees'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False, comment="姓名")
    age = Column(Integer, comment="年龄")
    department_id = Column(Integer, ForeignKey('t_departments.id', ondelete="CASCADE"), nullable=False)

    # 定义与 Department 的多对一关系
    department = relationship("Department", back_populates="employees")

4、多对多关系

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方的主键

示列：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可让多个学生选择

学生表 t_student

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID唯一标识	int	主键，并且自动增长	PRIMARY KEY,AUTO_INCREMENT
name	姓名	varchar(10)	非空约束	NOT NULL
stuno	学号	varchar(10)	非空约束	NOT NULL

课程表 t_course

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID唯一标识	int	主键，并且自动增长	PRIMARY KEY,AUTO_INCREMENT
name	课程名称	varchar(10)	非空约束	NOT NULL

学生课程关系表 t_student_course

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID唯一标识	int	主键，并且自动增长	PRIMARY KEY,AUTO_INCREMENT
studentid	学生ID	int	非空约束，外键约束	NOT NULL, FOREIGN KEY
courseid	课程ID	int	非空约束，外键约束	NOT NULL, FOREIGN KEY

方式一：关联表不直接映射到ORM类，使用back_populates参数实现反向关联

在多对多关联中，通常不需要直接对关联表进行ORM映射（注意：在ORM映射中实体表不一定需要创建外键约束），因为relationship()函数已经足够处理这种关系。

from sqlalchemy.orm import declarative_base, sessionmaker, relationship
from sqlalchemyBaseUse import engine
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, Table

# 声明ORM基类
Base = declarative_base()

# 关联表（不直接映射到ORM类）
student_course = Table('t_student_course', Base.metadata,
                       Column("id", Integer, primary_key=True, autoincrement=True),
                       Column('studentid', Integer, ForeignKey('t_student.id'), nullable=False),
                       Column('courseid', Integer, ForeignKey('t_course.id'), nullable=False),
                       )

class Student(Base):
    __tablename__ = 't_student'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(10), nullable=False, comment="姓名")
    stuno = Column(String(10), nullable=False, comment="学号")

    # 使用relationship()定义多对多关系
    courses = relationship("Course",
                           # 指定中间关联表 
                           secondary="t_student_course",
                           back_populates="students",
                           lazy='dynamic')  # 使用lazy='dynamic'可以返回查询对象

class Course(Base):
    __tablename__ = 't_course'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(10), nullable=False, comment="课程名称")

    # 使用relationship()定义反向多对多关系
    students = relationship("Student",
                            # 指定中间关联表 
                            secondary="t_student_course",
                            back_populates="courses")

def init_db():
    # 创建继承base类的表的映射关系
    Base.metadata.create_all(engine)

if __name__ == '__main__':
    init_db()

方式二：关联表映射到ORM类使用backref参数实现反向关联

因为backref会自动创建反向关联，所以只需要在除了关联表外的其中一个表里面建立反向关联即可，需要借助参数secondary。

class Student(Base):
    __tablename__ = 't_student'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(10), nullable=False, comment="姓名")
    stuno = Column(String(10), nullable=False, comment="学号")

    # 与StudentCourse定义多对多关系
    courses = relationship("Course",
                           secondary="t_student_course",  # 关联表的名称
                           backref="students")  # 为Course模型创建反向引用

class Course(Base):
    __tablename__ = 't_course'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(10), nullable=False, comment="课程名称")


class StudentCourse(Base):
    __tablename__ = 't_student_course'

    id = Column(Integer, primary_key=True, autoincrement=True)
    studentid = Column(Integer, ForeignKey('t_student.id'), nullable=False)
    courseid = Column(Integer, ForeignKey('t_course.id'), nullable=False)

    __table_args__ = (
        # 确保student_id和course_id的组合是唯一的
        UniqueConstraint('studentid', 'courseid', name='_student_course_uc'),
    )

示例：查询姓名为张三选择的所有课程名称

1、插入测试数据

insert into t_student values (null, '张三', '2000100101'),(null, '李四',
'2000100102'),(null, '小五', '2000100103'),(null, '小七', '2000100104');

insert into t_course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop');

insert into t_student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2), (null,2,3),(null,3,4);

2、代码实现

def query():
    Session = sessionmaker(bind=engine)
    session = Session()
    stu = session.query(Student).filter_by(name="张三").first()
    if stu:
        for course in stu.courses:
            print(course.name)

if __name__ == '__main__':
    query()

1.4.5.4、scoped_session实现线程安全

在SQLAlchemy中，scoped_session是一个工厂，它产生线程局部（thread-local）的Session对象。也就是在一个线程中，多次调用scoped_session工厂将返回同一个Session实例，而在另一个线程中，你会得到一个不同的实例。这有助于实现线程安全的数据库会话管理，因为每个线程都有自己的会话，从而避免了并发问题。

使用scoped_session来实现线程安全的步骤：

首先，你需要一个Session类，这通常是通过sessionmaker创建的。
然后，你使用scoped_session来包装这个Session类。
其次你可以通过scoped_session工厂来获取会话，并在需要时使用它。
最后在不需要时，关闭它。【注意：当线程结束时，scoped_session会自动关闭并清理其内部的Session实例。所以，通常不需要在代码中显式地关闭或清理会话。】

from sqlalchemy import create_engine  
from sqlalchemy.orm import sessionmaker, scoped_session，declarative_base
  
# 假设你有一个Base类和你的模型定义...  
Base = declarative_base()  
  
# 创建一个引擎  
engine = create_engine('sqlite:///example.db')  
  
# 创建一个Session类  
Session = sessionmaker(bind=engine)  
  
# 使用scoped_session来包装Session类  
# 这将确保每次在同一个线程中调用scoped_session()时，都会返回相同的Session实例  
scoped_session = scoped_session(Session)  
  
# 在你的代码中...  
def some_function():  
    # 获取一个会话  
    session = scoped_session()  
      
    # 使用会话进行查询、添加、更新或删除操作...  
    # 例如: result = session.query(MyModel).filter_by(some_column='value').first()  
      
    # 提交事务（如果需要）  
    session.commit()  
      
    # 关闭会话（通常不需要，因为scoped_session会在线程结束时自动关闭会话）  
    # 但如果你想在函数结束时立即关闭它，可以调用remove()方法  
    # scoped_session.remove()

1.4.5.5、新增数据

需求：在表t_student中新增数据

数据模型：

class Student(Base):
    __tablename__ = 't_student'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(10), nullable=False, comment="姓名")
    stuno = Column(String(10), nullable=False, comment="学号")

    # 使用relationship()定义多对多关系
    courses = relationship("Course",
                           secondary="t_student_course",
                           back_populates="students",
                           lazy='dynamic')  # 使用lazy='dynamic'可以返回查询对象

新增数据示例：

from sqlalchemy.orm import declarative_base, sessionmaker, scoped_session
from sqlalchemyBaseUse import engine
from many_to_many_relationship import Student

# 创建ORM基类
Base = declarative_base()

# 创建会话类
Session = sessionmaker(bind=engine)
# 使用scoped_session创建线程安全会话
session = scoped_session(Session)

# # 新增一条数据
# # 创建一个新的Student实例
# stu1 = Student(name="Tom", stuno="2000100105")
# # 将新实例添加到会话中
# session.add(stu1)
# # 提交会话，将数据保存到数据库
# session.commit()

# 批量添加数据
session.add_all([
    Student(name="Jack", stuno="2000100106"),
    Student(name="小爱", stuno="2000100107"),
    Student(name="大胖", stuno="2000100108"),
])
session.commit()

1.4.5.6、修改数据

修改数据的流程：先查询出需要修改的数据，然后修改数据，最后提交修改。

需求：修改表t_student的数据

1、修改id=5的数据，修改为name="Nicky", stuno="2000100109"
2、修改name="大胖"的数据，修改为name="小胖"
3、修改id>6的stuno,每个stuno前面都加123

注意：如果stuno的长度是10，需要增加一下长度，不然新增超过长度，会出现添加失败的错误。修改字段数据类型的SQL语句如下（需要执行SQL语句）：

ALTER TABLE t_student MODIFY stuno varchar(30);

from sqlalchemy.orm import declarative_base, sessionmaker, scoped_session
from sqlalchemyBaseUse import engine
from many_to_many_relationship import Student

# 创建ORM基类
Base = declarative_base()

# 创建会话类
Session = sessionmaker(bind=engine)
# 使用scoped_session创建线程安全会话
session = scoped_session(Session)

# 1、修改id=5的数据，修改为name="Nicky", stuno="2000100109"
# # 查询出id=5的数据
# stu = session.get(Student, 5)
# # 修改数据
# if stu:
#     stu.name = "Nicky"
#     stu.stuno = "2000100109"
# # 提交修改的数据
# session.commit()

# # 2、修改name="大胖"的数据，修改为name="小胖"
# session.query(Student).filter_by(name="大胖").update({"name":"小胖"})
# # 提交修改的数据
# session.commit()

# 3、修改id>6的stuno,每个stuno前面都加123
session.query(Student).filter(Student.id > 6).update({"stuno": "123" + Student.stuno})
session.commit()

1.4.5.7、删除数据

删除数据流程：先查询出数据，然后再删除数据

需求：删除表t_student的数据

1、删除id=5的数据
2、删除name="小胖"的数据

from sqlalchemy.orm import declarative_base, sessionmaker, scoped_session
from sqlalchemyBaseUse import engine
from many_to_many_relationship import Student

# 创建ORM基类
Base = declarative_base()

# 创建会话类
Session = sessionmaker(bind=engine)
# 使用scoped_session创建线程安全会话
session = scoped_session(Session)

# 1、删除id=5的数据
# stu_info = session.get(Student, 5)
# if stu_info:
#     session.delete(stu_info)
#     session.commit()

# 2、删除name="小胖"的数据
session.query(Student).filter_by(name="小胖").delete()
session.commit()

1.4.5.8、查询数据

1、测试数据准备

创建一张t_student2表，表的数据模型如下：
注意：如果数据库的编码类型不是utf8mb4，表生成后，需要执行以下SQL语句修改表的编码为utf8mb4，不然插入中文会出现报错。

ALTER TABLE t_student2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

from sqlalchemy.orm import declarative_base , sessionmaker, scoped_session
from sqlalchemyBaseUse import engine
from sqlalchemy import Column, Integer, String, DateTime

# 声明ORM基类
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = scoped_session(Session)

class Student(Base):
    __tablename__ = 't_student2'
    id = Column(Integer, primary_key=True)
    stuno = Column(String(10), comment="学号")
    name = Column(String(10), comment="姓名")
    gender = Column(String(1), comment="性别")
    age = Column(Integer, comment="年龄")
    idcard = Column(String(18), comment="身份证")
    entrydate = Column(DateTime, comment="入学时间")
    addr = Column(String(50), comment="家庭地址")

def init_db():
    # 创建继承base类的表的映射关系
    Base.metadata.create_all(engine)

def basic_query():
    pass


if __name__ == '__main__':
    init_db()

往表中插入测试数据，如下：

# 向表中插入数据
insert into t_student2 values('1','1','小洋洋','女',18,'12345678901234957','2023-02-03',"北京"),
('2','2','小芳','女',18,'123456789012345789','2023-02-03',"北京"),
('3','3','小枫','男',22,'123456789012345123','2023-01-03',"上海"),
('4','4','小敏','女',20,'123456789012345345','2022-01-03',"北京"),
('5','5','小李','男',20,'12345678901234534X','2022-01-03',"上海"),
('6','6','王小敏','女',16,'123456789012345345','2022-01-03',"成都"),
('7','7','大刘','男',25,'123456789012345102','2022-01-03',"深圳"),
('8','8','林逸','男',17,'12345678901234534X','2022-01-03',"北京"),
('9','9','莫小迪','女',21,'123456789012345302','2022-01-03',"成都"),
('10','10','林仙仙','女',16,'123456789012345330','2022-01-03',"深圳"),
('11','11','叶小辰','男',18,'123456789012345352','2022-01-03',"成都"),
('12','12','韩跑跑','男',24,'12345678901234554X','2022-01-03',"北京");

2、基础查询

2.1、查询多个字段（查询指定字段）

# 指定字段查询
stus = session.query(Student.name, Student.age).all()
for name, age in stus:
    print(name, age)

# 全表查询
stus = session.query(Student).all()
for stu in stus:
    print(stu.name)

2.2、去除重复记录

# 查询单个字段的不重复值
# 查询User表中所有不重复的name字段值  
unique_names = session.query(distinct(User.name)).all()  
  
# 遍历结果  
for name in unique_names:  
    print(name)
    
# 查询多个字段的不重复组合
# 查询User表中所有不重复的name和age组合  
unique_name_age_combinations = session.query(distinct(User.name, User.age)).all()  
  
# 遍历结果  
for name, age in unique_name_age_combinations:  
    print(name, age)

2.3、调试小技巧

在编写完查询代码后，可以先打印出代码对应转化的SQL语句，检查SQL语句是否正确，然后再执行。

示例：

# 指定字段查询
# 此时语句结尾不加.all(),输出的就是SQL语句
stus = session.query(Student.name, Student.age)
print(stus)

3、条件查询

3.1、常用的比较运算符

比较运算符	作用
>	大于
>=	大于等于
	小于
	小于等于
=	等于
<> 或 !=	不等于
IN(...)	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)
IS NULL	是NULL

3.2、常见的逻辑运算符

逻辑运算符	作用
AND	并且 (多个条件同时成立)
OR	或者 (多个条件任意一个成立)
NOT	非 , 不是

3.3、综合示例

def conditional_query():
    # 1、查询年龄大于20的学生
    stus = session.query(Student).filter(Student.age > 20).all()

    # 2、查询年龄不等于20的学生
    stus2 = session.query(Student).filter(Student.age != 20).all()
    # 或者使用<>, 注意python3.X版本不支持使用<>，官方推荐使用!=作为不等于运算符。
    # stus2_1 = session.query(Student).filter(Student.age <> 20).all()

    # 3、查询年龄为18或20或25的学生信息
    stus3 = session.query(Student).filter(Student.age.in_([18, 20, 25])).all()

    # 4、查询年龄不为18或20或25的学生信息
    stus4 = session.query(Student).filter(~Student.age.in_([18, 20, 25])).all()

    # 5、查询家庭住址为空的学生信息
    stus5 = session.query(Student).filter(Student.addr == None).all()
    # 或者使用is_()方法
    stus5_1 = session.query(Student).filter(Student.addr.is_(None)).all()

    # 6、查询家庭住址不为空的学生信息
    stus6 = session.query(Student).filter(Student.addr != None).all()
    # 或者使用isnot_()方法
    stus6_1 = session.query(Student).filter(Student.addr.isnot(None)).all()

    # 7、查询姓林，名字是两个字的学生信息
    stus7 = session.query(Student).filter(Student.name.like("林_")).all()

    # 8、查询身份证号最后一位是X的学生信息
    stus8 = session.query(Student).filter(Student.idcard.like("%X")).all()

    # 9、查询年龄在18岁（包含）到25岁（包含）之间的学生信息
    stus9 = session.query(Student).filter(Student.age >=18, Student.age <= 25).all()
    # 或者使用and_()方法
    stus9_1 = session.query(Student).filter(and_(Student.age >= 18, Student.age <= 25)).all()

    # 10、查询年龄为18或20或25的学生信息
    stus10 = session.query(Student).filter(or_(Student.age==18, Student.age==20, Student.age==25)).all()

4、聚合函数

说明：将一列数据作为一个整体，进行纵向计算。

语法：

SELECT 聚合函数(字段列表) FROM 表名 ;

注意 : NULL值是不参与所有聚合函数运算的。

4.1、常见的聚合函数

函数	作用
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

说明：SQLAlchemy 提供了一组内置的函数，这些函数可以在 func 命名空间中直接使用，类似于 SQL 中的聚合函数，如 COUNT(), SUM(), AVG(), MAX(), MIN() 等。

4.2、综合示列

def aggregate_query():
    # 1、统计学生总人数
    count = session.query(func.count(Student.id)).scalar()

    # 2、统计学生的平均年龄
    avg = session.query(func.avg(Student.age)).scalar()

    # 3、统计学生的最大年龄
    max = session.query(func.max(Student.age)).scalar()

    # 4、统计学生的最小年龄
    min = session.query(func.min(Student.age)).scalar()

    # 统计男生的总年龄
    count_man = session.query(func.sum(Student.age)).filter_by(gender="男").scalar()

5、分组查询

1、where与having区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项：

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
执行顺序: where > 聚合函数 > having 。
支持多字段分组, 具体语法为 : group by columnA,columnB

在 SQLAlchemy 中，使用 group_by() 方法来执行分组查询。分组查询通常与聚合函数（如 COUNT(), SUM(), AVG(), MAX(), MIN() 等）一起使用，以便对每个分组进行计算。

5.1、综合示例

def group_by_query():
    # 1、根据性别分组，统计男学生和女学生的数量
    # select sex, count(*) from t_student2 group by sex;
    results = session.query(Student.gender, func.count(Student.id).label("stu_nums")).group_by(Student.gender).all()
    # for gender, stu_nums in results:
    #     print(gender, stu_nums)

    # 2、根据性别分组 , 统计男学生和女学生的平均年龄
    # select sex,avg(age) from t_student2 group by sex;
    results2 = session.query(Student.gender, func.avg(Student.age).label("avg_age")).group_by(Student.gender).all()
    # for gender, avg_age in results2:
    #     print(gender, avg_age)

    # 3、查询年龄小于25的学生 , 并根据家庭地址分组 , 获取学生数量大于等于3的家庭地址
    # select addr,count(*) addr_num from t_student2 where age<25 group by addr having addr_num>=3;
    # 方式一: 通过having实现
    results3 = session.query(Student.addr, func.count(Student.id)).filter(Student.age < 25).group_by(Student.addr)\
        .having(func.count(Student.id) >= 3).all()
    
    # 方式二: 通过子查询实现，在子句中使用 filter()和比较运算符来实现它。
    # 3.1、 先查询年龄小于 25 的学生，并根据家庭地址分组，获取学生数量大于等于 3 的家庭地址
    subquery = session.query(Student.addr, func.count(Student.id).label("stu_nums")).filter(Student.age < 25).\
        group_by(Student.addr).subquery()
    # 3.2、使用子查询和 filter 子句来过滤出学生数量大于等于 3 的家庭地址
    results3_1 = session.query(subquery.c.addr, subquery.c.stu_nums).filter(subquery.c.stu_nums >= 3).all()

    # for addr, stu_nums in results3:
    #     print(addr, stu_nums)

    # 统计不同家庭地址男女生的数量
    # select addr,gender,count(*) from t_student2 group by addr,gender;
    results4 = session.query(Student.addr, Student.gender, func.count(Student.id).label("stu_nums")).group_by(Student.addr, Student.gender).all()
    print(results4)

6、排序查询

6.1、排序方式

ASC : 升序(默认值)
DESC: 降序

注意：

如果是升序, 可以不指定排序方式ASC ;
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;

def order_by_query():
    # 1、根据年龄对学生进行升序排序
    # select *from t_student order by age asc;
    res = session.query(Student).order_by(Student.age.asc()).all()
    # 或者
    # select *from t_student order by age;
    res_1 = session.query(Student).order_by(Student.age).all()
    # for res in res:
    #     print(res.age)

    # 2、根据年龄对学生进行升序排序 , 年龄相同 , 再按照入学时间进行降序排序
    # select *from t_student order by age,entrydate desc;
    res2 = session.query(Student).order_by(Student.age, Student.entrydate.desc()).all()
    # for res in res2:
    #     print(res.age,res.entrydate)

7、分页查询

注意：

起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

说明：在 SQLAlchemy 中，分页查询通常通过使用 offset() 和 limit() 方法来实现。这两个方法分别用于设置索引偏移量和限制查询结果的条数。

7.1、综合示例

def limit_query():
    # 1、查询第1页学生数据, 每页展示10条记录
    # select *from t_student limit 0,10;
    res = session.query(Student).offset(0).limit(10).all()
    # 或者
    # select *from t_student limit 10;
    res_1 = session.query(Student).limit(10).all()
    # for stu in res:
    #     print(stu.id)

    # 2、查询第2页学生数据, 每页展示10条记录
    # 说明:起始索引=(页码-1)*页展示记录数
    # select *from t_student limit 10,10;
    res2 = session.query(Student).offset(10).limit(10).all()
    # for stu in res2:
    #     print(stu.id)

1.4.5.9、多表查询

1、多表查询概述

1.1、测试数据准备

说明：先建立t_emp员工表和t_dept部门表两张表，并插入对应数据

1、创建对应的ORM模型如下：

from sqlalchemy.orm import declarative_base , sessionmaker, scoped_session
from sqlalchemyBaseUse import engine
from sqlalchemy import Column, Integer, String, DateTime, distinct, or_, and_, func, ForeignKey

# 声明ORM基类
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = scoped_session(Session)

# 部门表
class Departments(Base):
    __tablename__ = 't_dept'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False, comment="部门名称")

# 员工表
class Employes(Base):
    __tablename__ = "t_emp"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False, comment="姓名")
    age = Column(Integer, comment="年龄")
    job = Column(String(20), comment="职位")
    salary = Column(Integer, comment="薪资")
    entrydate = Column(DateTime, comment="入职时间")
    managerid = Column(Integer, comment="直属领导ID")
    dept_id = Column(Integer, ForeignKey("t_dept.id"))


def init_db():
    # 创建继承base类的表的映射关系
    Base.metadata.create_all(engine)

if __name__ == '__main__':
    init_db()

注意：如果创建的表的编码不是utf8mb4，需要修改为utf8mb4，不然插入中文的数据会出现编码错误，执行以下SQL语句：

ALTER TABLE t_dept CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE t_emp CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2、插入测试数据

INSERT INTO t_dept (name) VALUES ('研发部'), ('市场部'),('财务部'), ('销售部'), ('总经办'), ('人事部');

INSERT INTO t_emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
(1, '大刘', 28, '总裁',40000, '2000-01-01', null,5),
(2, '夏析', 20, '项目经理',20000, '2005-12-05', 1,1),
(3, '李兴', 33, '开发', 8000,'2000-11-03', 2,1),
(4, '张敏', 30, '开发',11000, '2002-02-05', 2,1),
(5, '林夕', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小美', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '林逸', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '李媛', 19, '会计',48000, '2006-06-02', 7,3),
(9, '林妙妙', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵芳', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '张三', 56, '职员',3750, '2006-10-03', 10,2),
(12, '李四', 19, '职员',3750, '2007-05-09', 10,2),
(13, '王二', 19, '职员',5500, '2009-02-12', 10,2),
(14, '周鑫', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '刘达', 38, '销售',4600, '2004-10-12', 14,4),
(16, '老钱', 40, '销售',4600, '2004-10-12', 14,4),
(17, '小六', 42, null,2000, '2011-10-12', 1,null);

1.2、概述

说明：多表查询就是指从多张表中查询数据。

操作：要执行多表查询，就只需要使用逗号分隔多张表，如： select * from t_emp , t_dept;

具体的执行结果如下:

解释：可见查询结果中包含了大量的结果集，总共102条记录，而这其实就是员工表emp所有的记录 (17) 与部门表dept所有记录(6) 的所有组合情况，这种现象称之为笛卡尔积。

笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合和 B集合的所有组合情况。

而在多表查询中，需要消除无效的笛卡尔积，只保留两张表关联部分的数据；使用SQL语句，消除多表查询的笛卡尔积：

select *from t_emp,t_dept where t_emp.dept_id = t_dept.id;

1.3、多表查询的分类

连接查询
- 内连接：相当于查询A、B交集部分数据
- 外连接：
  - 右外连接：查询右表所有数据，以及两张表交集部分数据
  - 左外连接：查询左表所有数据，以及两张表交集部分数据
- 自连接：当前表与自身的连接查询，自连接必须使用表别名
子查询

2、内连接

说明：内连接查询的是两张表交集部分的数据

内连接的语法分为两种:

隐式内连接
显式内连接

语法：

1、隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

2、显示内连接（inner join）

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

注意：SQL语句中的内连接的inner关键可以省略

说明：在 SQLAlchemy 中，内连接（INNER JOIN）是默认的连接类型，当你使用 join() 方法而不指定 isouter=True 时，你就是在执行内连接。内连接只返回满足连接条件的行，即两个表中都存在匹配项的行。

示例：

1、查询每一个员工的姓名 , 及关联的部门的名称 (使用隐式内连接实现)

使用filter()函数实现，在filter()函数中声明关联关系

res = session.query(Employes, Departments).filter(Employes.dept_id == Departments.id).all()

2、查询每一个员工的姓名 , 及关联的部门的名称 (使用显式内连接实现)

使用join()函数实现，当你想要连接两个模型时，应该首先指定主模型（在 session.query() 中列出的第一个模型），然后使用 join() 方法连接第二个模型，并指定连接条件。

res2 = session.query(Employes, Departments).join(Departments, Employes.dept_id == Departments.id).all()

3、查询每一个员工的姓名 , 及关联的部门的名称（使用relationship实现内连接查询）

首先在Departments类中新建relationship反向关联关系

employes = relationship("Employes", back_populates="departments")

在Employes类中新建relationship反向关联关系

departments = relationship("Departments", back_populates="employes")

重新执行ORM映射

def init_db():
    # 创建继承base类的表的映射关系
    Base.metadata.create_all(engine)

if __name__ == '__main__':
    init_db()

代码实现如下：

res3 = session.query(Employes).join(Employes.departments)

3、外连接

外连接分为两种，分别是：

左外连接
右外连接

1、左外连接

说明：左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

语法：

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

2、右外连接

说明：右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

语法：

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

说明：SQLAlchemy 支持左外连接（LEFT OUTER JOIN）和右外连接（RIGHT OUTER JOIN）。不过，在 SQLAlchemy 的 ORM 层，通常更常见的是使用左外连接，因为 SQLAlchemy 更多地是按照关系型数据库的标准来设计的，而标准 SQL 中右外连接并不如左外连接那样常见。

但是，你可以使用 SQLAlchemy 的 Core 表达式语言来执行右外连接。以下是如何在 SQLAlchemy 中使用左外连接和右外连接的示例：

注意事项：左外连接和右外连接是可以相互替换，只需要调整在连接查询时SQL中，表结构的先后顺序就可以。在实际开发使用时，左外连接常用。

示例：

1、查询t_emp表的所有数据, 和对应的部门信息 (左外连接)

# 1、查询t_emp表的所有数据, 和对应的部门信息(左外连接)
# select e.*,d.name from t_emp e left outer join t_dept d on e.dept_id = d.id;
# 注意：outerjoin()函数中的连接条件可以省略，Employes.dept_id == Departments.id
res = session.query(Employes, Departments.name).outerjoin(Departments).all()
# print(res)

2、查询t_dept表的所有数据, 和对应的员工信息（右外连接）

# 查询t_dept表的所有数据, 和对应的员工信息（右外连接）
# select d.*,e.* from t_emp e right outer join  t_dept d on d.id = e.dept_id;
# 使用join()函数实现，isouter=True使用左外连接，左外连接和右外连接可以相互转换，也就是表的位置不同
# join()函数中的第一个参数Employes表示左外连接关联的表是t_emp，主表是t_dept
res2 = session.query(Employes, Departments).join(Employes, isouter=True).all()
# print(res2)

4、自连接查询

说明：自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。

连接方式：对于自连接查询，可以是内连接查询，也可以是外连接查询。

注意事项：在自连接查询中，必须要为表起别名，不然不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

说明：同样的在SQlAlchemy中实现自连接查询，也需要为表起别名的方式，起别名使用aliased()函数实现。

示列：

1、查询员工及其所属领导的名字

分析：可将t_emp表看作两个表，要求查询每个员工及其对应的领导姓名，可见这是一个交集关系，需要使用内连接。

# select a.name '员工姓名', b.name '领导姓名' from t_emp a join t_emp b on a.managerid = b.id;
# 或
# select a.name '员工姓名', b.name '领导姓名' from t_emp a, t_emp b where a.managerid = b.id;
# 为表Employes创建别名
user = aliased(Employes)
manager = aliased(Employes)

# 使用Inner join 的连接方式
res = session.query(user.name.label("employee_name"), manager.name.label("manager_name")).join(manager, user.managerid == manager.id).all()
print(res)

2、查询所有员工 t_emp a 及其领导的名字 t_emp b, 如果员工没有领导, 也需要查询出来。

分析：如果员工没有领导, 也需要查询出来，由此可见需要使用外连接，一般使用左外连接

# 为表Employes创建别名
user = aliased(Employes)
manager = aliased(Employes)

# select a.name '员工姓名', b.name '领导姓名' from t_emp a left join t_emp b on a.managerid = b.id;
res2 = session.query(user.name.label("employee_name"), manager.name.label("manager_name")).join(manager, user.managerid == manager.id, isouter=True).all()
print(res2)

5、联合查询

关键字：union

说明：

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起。
union 会对合并之后的数据去重。

语法：

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

说明：在 SQLAlchemy 中，可以使用 union() 或者 union_all()方法来执行联合查询。

示例：

1、将薪资低于 5000 的员工 , 和年龄大于 45 岁的员工全部查询出来.

分析：当前对于上述需求，可以直接使用多条件查询，使用逻辑运算符 and 连接即可。也可以通过union/union all来联合查询.

SQL实现如下：

select * from t_emp where salary < 5000
union all
select * from t_emp where age > 45;

ORM实现如下：

query1 = session.query(Employes).filter(Employes.salary < 5000)
query2 = session.query(Employes).filter(Employes.age > 45)
union_query = query1.union(query2).all()
for un in union_query:
    print(un.name)

6、子查询

6.1、概述

1、概念

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

如：

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

2、分类

分类依据：根据子查询结果

分为：

标量子查询（子查询结果为单个值）
列子查询(子查询结果为一列)
行子查询(子查询结果为一行)
表子查询(子查询结果为多行多列)

分类依据2：根据子查询位置

分为：

WHERE之后
FROM之后
SELECT之后

6.2、标量子查询

说明：子查询返回的结果是单个值（数字、字符串、日期等）

常用操作符：= <> > >= <

示列：

1、查询“销售部”所有员工的信息

SQL实现如下：

分析：可先将查询分为两步
1、先查询出销售部的部门id
select id from t_dept where name = '销售部';

2、然后根据查出的部门id再查询销售部的所有员工信息
select e.* from t_emp e where dept_id = (select id from t_dept where name = '销售部');

或者使用关联查询
select e.* from t_emp e join t_dept d on e.dept_id = d.id where d.name='销售部';

ORM实现如下：

使用 .scalar_subquery() 方法明确地产生一个标量子查询
注意：代码可能会出现警告但是可以正常执行，因为SQLAlchemy 建议在 in_() 方法中明确传递一个 select() 构造，而不是直接将子查询对象传递给 in_() 方法。

# 1.1、先查询出销售部的部门id
subquery = session.query(Departments.id).filter(Departments.name=="销售部").scalar_subquery()
# 可能会出现警告，可以将子查询转换成 select 语句
# subquery = select([Departments.id]).where(Departments.name == "销售部")
# 1.2、然后根据查出的部门id再查询销售部的所有员工信息
res = session.query(Employes).filter(Employes.dept_id.in_(subquery)).all()
print(res)

2、查询在员工“林逸”之后入职的所有员工信息

SQL实现如下：

分析：可先将查询分为两步
1、先查询出林逸的入职时间
select entrydate from t_emp where name = '林逸';

2、然后根据入职时间再查找出在林逸入职时间之后的员工信息
select e.* from t_emp e where entrydate > (select entrydate from t_emp where name = '林逸');

ORM实现如下：

subquery2 = session.query(Employes.entrydate).filter_by(name="林逸").scalar_subquery()
res2 = session.query(Employes).filter(Employes.entrydate > subquery2)
print(res2)

6.3、列子查询

说明：子查询返回的结果是一列（可以是多行）

常用操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

示列：

1、查询 "销售部" 和 "市场部" 的所有员工信息

SQL实现如下：

分析：可先将查询分为两步
1、先在部门表中查询出销售部和市场部的部门id
select id from t_dept where name = '销售部' or name = '市场部';

2、然后根据1查出的结果，使用关键字in查询出对应销售部和市场部的员工信息1
select * from t_emp where dept_id in (select id from t_dept where name = '销售部' or name = '市场部');

ORM实现如下：

subquery = session.query(Departments.id).filter(or_(Departments.name=="销售部", Departments.name=="市场部")).subquery()
res = session.query(Employes).filter(Employes.id.in_(subquery))

2、查询比“财务部”所有人工资都高的员工信息

SQL实现如下：

分析：可先将查询分为两步
1、先查询出财务部所有人员的工资信息
select salary from t_emp e join t_dept d on e.dept_id = d.id where d.name = '财务部';

2、然后根据1查出的结果，使用关键字all查询出比财务部所有人工资都高的员工信息
select * from t_emp where salary > all (select salary from t_emp e join t_dept d on e.dept_id = d.id where d.name = '财务部');
或
select * from t_emp where salary > all ( select salary from t_emp where dept_id =(select id from t_dept where name = '财务部') );

ORM实现如下：

使用 filter() 方法结合 all_() 函数来实现

subquery2 = session.query(Employes.salary).join(Departments).filter(Departments.name=="财务部").scalar_subquery()
res2 = session.query(Employes).filter(Employes.salary > all_(subquery2)).all()

3、查询比“研发部”其中任意一人工资都高的员工信息

SQL实现如下：

分析：可先将查询分为两步
1、先查询出研发部所有人员的工资信息
select salary from t_emp where dept_id = (select id from t_dept where name = '研发部');

2、然后根据1查出的结果，使用关键字any查询出比研发部任意一人工资都高的员工信息
select * from t_emp where salary > any (select salary from t_emp where dept_id = (select id from t_dept where name = '研发部'));

ORM实现如下：

使用 filter() 方法结合 any_() 函数来实现

subquery3 = session.query(Employes.salary).join(Departments).filter(Departments.name == "研发部").scalar_subquery()
res3 = session.query(Employes).filter(Employes.salary > any_(subquery3)).all()

6.4、行子查询

说明：子查询返回的结果是一行（可以是多列）

常用的操作符：= 、<> 、IN 、NOT IN

示列：

1、查询与 "张敏" 的薪资及直属领导相同的员工信息

SQL实现如下：

分析：可先将查询分为两步
1、先查询出张敏的薪资和直属领导的id
select salary,managerid from t_emp where name = '张敏';

2、然后根据1查出的结果，使用 = 查询与"张敏"的薪资及直属领导相同的员工信息
select * from t_emp where (salary,managerid) = (select salary,managerid from t_emp where name = '张敏');

ORM实现如下：

使用 filter() 方法结合 tuple_() 函数来实现

subquery = session.query(Employes.salary, Employes.managerid).filter_by(name="张敏").subquery()
res = session.query(Employes).filter(tuple_(Employes.salary,Employes.managerid).in_(subquery)).all()

6.5、表子查询

说明：子查询返回的结果是多行多列

常用的操作符：IN

示列：

1、查询与 "林夕" , "林妙妙" 的职位和薪资相同的员工信息

SQL实现如下：

分析：可先将查询分为两步
1、先查询林夕和林妙妙的职位与薪资信息
select job, salary from t_emp where name in ('林夕','林妙妙');

2、然后根据1查出的结果，使用 in 查询与"林夕","林妙妙"的职位和薪资相同的员工信息
select * from t_emp where (job,salary) in (select job, salary from t_emp where name in ('林夕','林妙妙'));

ORM实现如下：

使用 filter() 方法结合 tuple_() 函数来实现

subquery = session.query(Employes.job, Employes.salary).filter(Employes.name.in_(["林夕", "林妙妙"])).subquery()
res = session.query(Employes).filter(tuple_(Employes.job, Employes.salary).in_(subquery)).all()

2、查询入职日期是 "2002-09-12" 之后的员工信息 , 及其部门信息

SQL实现如下：

分析：可先将查询分为两步
1、先查询出入职日期是 "2002-09-12" 之后的员工信息
select * from t_emp where entrydate > "2002-09-12";

2、根据1查询出的表信息，在查询对应的部门信息
select e.*, d.* from (select * from t_emp where entrydate > "2002-09-12") e join t_dept d on e.dept_id = d.id;

ORM实现如下：

使用join()实现

subquery2 = session.query(Employes).filter(Employes.entrydate > "2002-09-12").subquery()
res2 = session.query(subquery2, Departments).join(Departments, subquery2.c.dept_id == Departments.id).all()

测试有道

关注

36
点赞
踩
22

收藏

觉得还不错? 一键收藏
0
评论
Python 之SQLAlchemy使用详细说明

ORM（Object-Relational Mapping）模型，即对象关系映射，是一种程序设计技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。在面向对象的编程语言中，如Java、Python等，数据通常被组织成复杂的对象关系。简单来说就是ORM模型把面向对象编程与操作数据库之间建立了映射。设置开发者操作数据库无需维护和编写SQL语句，而是基于面对对象的方式操作数据库。数据库中的表>编程语言中的类，表中的字段>类中的属性，表之间的关系>类之间的关系。
复制链接

扫一扫