Lab8 ORM拓展¶
本章主要介绍 Python 使用 ORM 对 PostgreSQL 数据库进行多表连接，子查询，反向查询等一些操作，并且进行综合练习
预先准备¶
连接数据库¶
In [1]:
Copied!
import sqlalchemy
from sqlalchemy import Column, String, create_engine, Integer, Text, Date
from sqlalchemy.orm import sessionmaker,scoped_session
from sqlalchemy.ext.declarative import declarative_base
import time
from sqlalchemy import create_engine
engine = create_engine("postgresql://ecnu10211900416:ECNU10211900416@172.16.253.154:5432/ecnu10211900416",
max_overflow=0,
# 链接池大小
pool_size=5,
# 链接池中没有可用链接则最多等待的秒数，超过该秒数后报错
pool_timeout=10,
# 多久之后对链接池中的链接进行一次回收
pool_recycle=1,
# 查看原生语句（未格式化）
echo=True
)
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
DbSession = sessionmaker(bind=engine)
session = DbSession()
import sqlalchemy
from sqlalchemy import Column, String, create_engine, Integer, Text, Date
from sqlalchemy.orm import sessionmaker,scoped_session
from sqlalchemy.ext.declarative import declarative_base
import time
from sqlalchemy import create_engine
engine = create_engine("postgresql://ecnu10211900416:ECNU10211900416@172.16.253.154:5432/ecnu10211900416",
max_overflow=0,
# 链接池大小
pool_size=5,
# 链接池中没有可用链接则最多等待的秒数，超过该秒数后报错
pool_timeout=10,
# 多久之后对链接池中的链接进行一次回收
pool_recycle=1,
# 查看原生语句（未格式化）
echo=True
)
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
DbSession = sessionmaker(bind=engine)
session = DbSession()
创建数据表¶
In [9]:
Copied!
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Enum,ForeignKey,UniqueConstraint
from sqlalchemy.orm import relationship
Base = declarative_base()
class StudentsNumberInfo(Base):
"""学号表"""
__tablename__ = "studentsNumberInfo"
id = Column(Integer, primary_key=True, autoincrement=True, comment="主键")
number = Column(Integer, nullable=False, unique=True, comment="学生编号")
admission = Column(Date, nullable=False, comment="入学时间")
graduation = Column(Date, nullable=False, comment="毕业时间")
class TeachersInfo(Base):
"""教师表"""
__tablename__ = "teachersInfo"
id = Column(Integer, primary_key=True, autoincrement=True, comment="主键")
number = Column(Integer, nullable=False, unique=True, comment="教师编号")
name = Column(String(64), nullable=False, comment="教师姓名")
gender = Column(String(1), nullable=False, comment="教师性别")
age = Column(Integer, nullable=False, comment="教师年龄")
class ClassesInfo(Base):
"""班级表"""
__tablename__ = "classesInfo"
id = Column(Integer, primary_key=True, autoincrement=True, comment="主键")
number = Column(Integer, nullable=False, unique=True, comment="班级编号")
name = Column(String(64), nullable=False, unique=True, comment="班级名称")
# 一对一关系必须为连接表的连接字段创建UNIQUE的约束，这样才能是一对一，否则是一对多
fk_teacher_id = Column(
Integer,
ForeignKey(
"teachersInfo.id",
ondelete="CASCADE",
onupdate="CASCADE",
),
nullable=False,
unique=True,
comment="班级负责人"
)
# 下面这2个均属于逻辑字段，适用于正反向查询。在使用ORM的时候，我们不必每次都进行JOIN查询，而恰好正反向的查询使用频率会更高
# 这种逻辑字段不会在物理层面上创建，它只适用于查询，本身不占据任何数据库的空间
# sqlalchemy的正反向概念与Django有所不同，Django是外键字段在那边，那边就作为正
# 而sqlalchemy是relationship字段在那边，那边就作为正
# 比如班级表拥有 relationship 字段，而老师表不曾拥有
# 那么用班级表的这个relationship字段查老师时，就称为正向查询
# 反之，如果用老师来查班级，就称为反向查询
# 另外对于这个逻辑字段而言，根据不同的表关系，创建的位置也不一样：
# - 1 TO 1：建立在任意一方均可，查询频率高的一方最好
# - 1 TO M：建立在M的一方
# - M TO M：中间表中建立2个逻辑字段，这样任意一方都可以先反向，再正向拿到另一方
# - 遵循一个原则，ForeignKey建立在那个表上，那个表上就建立relationship
# - 有几个ForeignKey，就建立几个relationship
# 总而言之，使用ORM与原生SQL最直观的区别就是正反向查询能带来更高的代码编写效率，也更加简单
# 甚至我们可以不用外键约束，只创建这种逻辑字段，让表与表之间的耦合度更低，但是这样要避免脏数据的产生
# 班级负责人，这里是一对一关系，一个班级只有一个负责人
leader_teacher = relationship(
# 正向查询时所链接的表，当使用 classesInfo.leader_teacher 时，它将自动指向fk的那一条记录
"TeachersInfo",
# 反向查询时所链接的表，当使用 teachersInfo.leader_class 时，它将自动指向该老师所管理的班级
backref="leader_class",
)
class ClassesAndTeachersRelationship(Base):
"""任教老师与班级的关系表"""
__tablename__ = "classesAndTeachersRelationship"
id = Column(Integer, primary_key=True, autoincrement=True, comment="主键")
# 中间表中注意不要设置单列的UNIQUE约束，否则就会变为一对一
fk_teacher_id = Column(
Integer,
ForeignKey(
"teachersInfo.id",
ondelete="CASCADE",
onupdate="CASCADE",
),
nullable=False,
comment="教师记录"
)
fk_class_id = Column(
Integer,
ForeignKey(
"classesInfo.id",
ondelete="CASCADE",
onupdate="CASCADE",
),
nullable=False,
comment="班级记录"
)
# 多对多关系的中间表必须使用联合唯一约束，防止出现重复数据
__table_args__ = (
UniqueConstraint("fk_teacher_id", "fk_class_id"),
)
# 逻辑字段
# 给班级用的，查看所有任教老师
mid_to_teacher = relationship(
"TeachersInfo",
backref="mid",
)
# 给老师用的，查看所有任教班级
mid_to_class = relationship(
"ClassesInfo",
backref="mid"
)
class StudentsInfo(Base):
"""学生信息表"""
__tablename__ = "studentsInfo"
id = Column(Integer, primary_key=True, autoincrement=True, comment="主键")
name = Column(String(64), nullable=False, comment="学生姓名")
gender = Column(String(1), nullable=False, comment="学生性别")
age = Column(Integer, nullable=False, comment="学生年龄")
# 外键约束
# 一对一关系必须为连接表的连接字段创建UNIQUE的约束，这样才能是一对一，否则是一对多
fk_student_id = Column(
Integer,
ForeignKey(
"studentsNumberInfo.id",
ondelete="CASCADE",
onupdate="CASCADE"
),
nullable=False,
comment="学生编号"
)
# 相比于一对一，连接表的连接字段不用UNIQUE约束即为多对一关系
fk_class_id = Column(
Integer,
ForeignKey(
"classesInfo.id",
ondelete="CASCADE",
onupdate="CASCADE"
),
comment="班级编号"
)
# 逻辑字段
# 所在班级, 这里是一对多关系，一个班级中可以有多名学生
from_class = relationship(
"ClassesInfo",
backref="have_student",
)
# 学生学号，这里是一对一关系，一个学生只能拥有一个学号
number_info = relationship(
"StudentsNumberInfo",
backref="student_info",
)
if __name__ == "__main__":
# 删除表
Base.metadata.drop_all(engine)
# 创建表
Base.metadata.create_all(engine)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Enum,ForeignKey,UniqueConstraint
from sqlalchemy.orm import relationship
Base = declarative_base()
class StudentsNumberInfo(Base):
"""学号表"""
__tablename__ = "studentsNumberInfo"
id = Column(Integer, primary_key=True, autoincrement=True, comment="主键")
number = Column(Integer, nullable=False, unique=True, comment="学生编号")
admission = Column(Date, nullable=False, comment="入学时间")
graduation = Column(Date, nullable=False, comment="毕业时间")
class TeachersInfo(Base):
"""教师表"""
__tablename__ = "teachersInfo"
id = Column(Integer, primary_key=True, autoincrement=True, comment="主键")
number = Column(Integer, nullable=False, unique=True, comment="教师编号")
name = Column(String(64), nullable=False, comment="教师姓名")
gender = Column(String(1), nullable=False, comment="教师性别")
age = Column(Integer, nullable=False, comment="教师年龄")
class ClassesInfo(Base):
"""班级表"""
__tablename__ = "classesInfo"
id = Column(Integer, primary_key=True, autoincrement=True, comment="主键")
number = Column(Integer, nullable=False, unique=True, comment="班级编号")
name = Column(String(64), nullable=False, unique=True, comment="班级名称")
# 一对一关系必须为连接表的连接字段创建UNIQUE的约束，这样才能是一对一，否则是一对多
fk_teacher_id = Column(
Integer,
ForeignKey(
"teachersInfo.id",
ondelete="CASCADE",
onupdate="CASCADE",
),
nullable=False,
unique=True,
comment="班级负责人"
)
# 下面这2个均属于逻辑字段，适用于正反向查询。在使用ORM的时候，我们不必每次都进行JOIN查询，而恰好正反向的查询使用频率会更高
# 这种逻辑字段不会在物理层面上创建，它只适用于查询，本身不占据任何数据库的空间
# sqlalchemy的正反向概念与Django有所不同，Django是外键字段在那边，那边就作为正
# 而sqlalchemy是relationship字段在那边，那边就作为正
# 比如班级表拥有 relationship 字段，而老师表不曾拥有
# 那么用班级表的这个relationship字段查老师时，就称为正向查询
# 反之，如果用老师来查班级，就称为反向查询
# 另外对于这个逻辑字段而言，根据不同的表关系，创建的位置也不一样：
# - 1 TO 1：建立在任意一方均可，查询频率高的一方最好
# - 1 TO M：建立在M的一方
# - M TO M：中间表中建立2个逻辑字段，这样任意一方都可以先反向，再正向拿到另一方
# - 遵循一个原则，ForeignKey建立在那个表上，那个表上就建立relationship
# - 有几个ForeignKey，就建立几个relationship
# 总而言之，使用ORM与原生SQL最直观的区别就是正反向查询能带来更高的代码编写效率，也更加简单
# 甚至我们可以不用外键约束，只创建这种逻辑字段，让表与表之间的耦合度更低，但是这样要避免脏数据的产生
# 班级负责人，这里是一对一关系，一个班级只有一个负责人
leader_teacher = relationship(
# 正向查询时所链接的表，当使用 classesInfo.leader_teacher 时，它将自动指向fk的那一条记录
"TeachersInfo",
# 反向查询时所链接的表，当使用 teachersInfo.leader_class 时，它将自动指向该老师所管理的班级
backref="leader_class",
)
class ClassesAndTeachersRelationship(Base):
"""任教老师与班级的关系表"""
__tablename__ = "classesAndTeachersRelationship"
id = Column(Integer, primary_key=True, autoincrement=True, comment="主键")
# 中间表中注意不要设置单列的UNIQUE约束，否则就会变为一对一
fk_teacher_id = Column(
Integer,
ForeignKey(
"teachersInfo.id",
ondelete="CASCADE",
onupdate="CASCADE",
),
nullable=False,
comment="教师记录"
)
fk_class_id = Column(
Integer,
ForeignKey(
"classesInfo.id",
ondelete="CASCADE",
onupdate="CASCADE",
),
nullable=False,
comment="班级记录"
)
# 多对多关系的中间表必须使用联合唯一约束，防止出现重复数据
__table_args__ = (
UniqueConstraint("fk_teacher_id", "fk_class_id"),
)
# 逻辑字段
# 给班级用的，查看所有任教老师
mid_to_teacher = relationship(
"TeachersInfo",
backref="mid",
)
# 给老师用的，查看所有任教班级
mid_to_class = relationship(
"ClassesInfo",
backref="mid"
)
class StudentsInfo(Base):
"""学生信息表"""
__tablename__ = "studentsInfo"
id = Column(Integer, primary_key=True, autoincrement=True, comment="主键")
name = Column(String(64), nullable=False, comment="学生姓名")
gender = Column(String(1), nullable=False, comment="学生性别")
age = Column(Integer, nullable=False, comment="学生年龄")
# 外键约束
# 一对一关系必须为连接表的连接字段创建UNIQUE的约束，这样才能是一对一，否则是一对多
fk_student_id = Column(
Integer,
ForeignKey(
"studentsNumberInfo.id",
ondelete="CASCADE",
onupdate="CASCADE"
),
nullable=False,
comment="学生编号"
)
# 相比于一对一，连接表的连接字段不用UNIQUE约束即为多对一关系
fk_class_id = Column(
Integer,
ForeignKey(
"classesInfo.id",
ondelete="CASCADE",
onupdate="CASCADE"
),
comment="班级编号"
)
# 逻辑字段
# 所在班级, 这里是一对多关系，一个班级中可以有多名学生
from_class = relationship(
"ClassesInfo",
backref="have_student",
)
# 学生学号，这里是一对一关系，一个学生只能拥有一个学号
number_info = relationship(
"StudentsNumberInfo",
backref="student_info",
)
if __name__ == "__main__":
# 删除表
Base.metadata.drop_all(engine)
# 创建表
Base.metadata.create_all(engine)
2023-11-23 06:28:15,011 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:28:15,012 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-23 06:28:15,012 INFO sqlalchemy.engine.Engine [cached since 107.5s ago] {'table_name': 'studentsNumberInfo', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-23 06:28:15,040 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-23 06:28:15,041 INFO sqlalchemy.engine.Engine [cached since 107.6s ago] {'table_name': 'teachersInfo', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-23 06:28:15,043 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-23 06:28:15,043 INFO sqlalchemy.engine.Engine [cached since 107.6s ago] {'table_name': 'classesInfo', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-23 06:28:15,045 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-23 06:28:15,046 INFO sqlalchemy.engine.Engine [cached since 107.6s ago] {'table_name': 'classesAndTeachersRelationship', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-23 06:28:15,047 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-23 06:28:15,048 INFO sqlalchemy.engine.Engine [cached since 107.6s ago] {'table_name': 'studentsInfo', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-23 06:28:15,049 INFO sqlalchemy.engine.Engine
DROP TABLE "studentsInfo"
2023-11-23 06:28:15,050 INFO sqlalchemy.engine.Engine [no key 0.00051s] {}
2023-11-23 06:28:15,069 INFO sqlalchemy.engine.Engine
DROP TABLE "classesAndTeachersRelationship"
2023-11-23 06:28:15,070 INFO sqlalchemy.engine.Engine [no key 0.00065s] {}
2023-11-23 06:28:15,076 INFO sqlalchemy.engine.Engine
DROP TABLE "classesInfo"
2023-11-23 06:28:15,077 INFO sqlalchemy.engine.Engine [no key 0.00052s] {}
2023-11-23 06:28:15,088 INFO sqlalchemy.engine.Engine
DROP TABLE "teachersInfo"
2023-11-23 06:28:15,089 INFO sqlalchemy.engine.Engine [no key 0.00053s] {}
2023-11-23 06:28:15,093 INFO sqlalchemy.engine.Engine
DROP TABLE "studentsNumberInfo"
2023-11-23 06:28:15,093 INFO sqlalchemy.engine.Engine [no key 0.00048s] {}
2023-11-23 06:28:15,096 INFO sqlalchemy.engine.Engine COMMIT
/tmp/ipykernel_60/3227781301.py:4: MovedIn20Warning: The ``declarative_base()`` function is now available as sqlalchemy.orm.declarative_base(). (deprecated since: 2.0) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9) Base = declarative_base()
2023-11-23 06:28:15,542 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:28:15,543 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-23 06:28:15,544 INFO sqlalchemy.engine.Engine [cached since 108.1s ago] {'table_name': 'studentsNumberInfo', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-23 06:28:15,546 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-23 06:28:15,546 INFO sqlalchemy.engine.Engine [cached since 108.1s ago] {'table_name': 'teachersInfo', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-23 06:28:15,548 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-23 06:28:15,549 INFO sqlalchemy.engine.Engine [cached since 108.1s ago] {'table_name': 'classesInfo', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-23 06:28:15,550 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-23 06:28:15,551 INFO sqlalchemy.engine.Engine [cached since 108.1s ago] {'table_name': 'classesAndTeachersRelationship', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-23 06:28:15,552 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
2023-11-23 06:28:15,553 INFO sqlalchemy.engine.Engine [cached since 108.1s ago] {'table_name': 'studentsInfo', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2023-11-23 06:28:15,555 INFO sqlalchemy.engine.Engine
CREATE TABLE "studentsNumberInfo" (
id SERIAL NOT NULL,
number INTEGER NOT NULL,
admission DATE NOT NULL,
graduation DATE NOT NULL,
PRIMARY KEY (id),
UNIQUE (number)
)
2023-11-23 06:28:15,555 INFO sqlalchemy.engine.Engine [no key 0.00059s] {}
2023-11-23 06:28:15,702 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "studentsNumberInfo".id IS '主键'
2023-11-23 06:28:15,703 INFO sqlalchemy.engine.Engine [no key 0.00080s] {}
2023-11-23 06:28:15,707 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "studentsNumberInfo".number IS '学生编号'
2023-11-23 06:28:15,707 INFO sqlalchemy.engine.Engine [no key 0.00053s] {}
2023-11-23 06:28:15,708 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "studentsNumberInfo".admission IS '入学时间'
2023-11-23 06:28:15,709 INFO sqlalchemy.engine.Engine [no key 0.00053s] {}
2023-11-23 06:28:15,710 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "studentsNumberInfo".graduation IS '毕业时间'
2023-11-23 06:28:15,711 INFO sqlalchemy.engine.Engine [no key 0.00053s] {}
2023-11-23 06:28:15,712 INFO sqlalchemy.engine.Engine
CREATE TABLE "teachersInfo" (
id SERIAL NOT NULL,
number INTEGER NOT NULL,
name VARCHAR(64) NOT NULL,
gender VARCHAR(1) NOT NULL,
age INTEGER NOT NULL,
PRIMARY KEY (id),
UNIQUE (number)
)
2023-11-23 06:28:15,713 INFO sqlalchemy.engine.Engine [no key 0.00076s] {}
2023-11-23 06:28:15,790 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "teachersInfo".id IS '主键'
2023-11-23 06:28:15,791 INFO sqlalchemy.engine.Engine [no key 0.00072s] {}
2023-11-23 06:28:15,792 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "teachersInfo".number IS '教师编号'
2023-11-23 06:28:15,792 INFO sqlalchemy.engine.Engine [no key 0.00053s] {}
2023-11-23 06:28:15,793 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "teachersInfo".name IS '教师姓名'
2023-11-23 06:28:15,794 INFO sqlalchemy.engine.Engine [no key 0.00052s] {}
2023-11-23 06:28:15,795 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "teachersInfo".gender IS '教师性别'
2023-11-23 06:28:15,795 INFO sqlalchemy.engine.Engine [no key 0.00052s] {}
2023-11-23 06:28:15,796 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "teachersInfo".age IS '教师年龄'
2023-11-23 06:28:15,797 INFO sqlalchemy.engine.Engine [no key 0.00047s] {}
2023-11-23 06:28:15,798 INFO sqlalchemy.engine.Engine
CREATE TABLE "classesInfo" (
id SERIAL NOT NULL,
number INTEGER NOT NULL,
name VARCHAR(64) NOT NULL,
fk_teacher_id INTEGER NOT NULL,
PRIMARY KEY (id),
UNIQUE (number),
UNIQUE (name),
UNIQUE (fk_teacher_id),
FOREIGN KEY(fk_teacher_id) REFERENCES "teachersInfo" (id) ON DELETE CASCADE ON UPDATE CASCADE
)
2023-11-23 06:28:15,799 INFO sqlalchemy.engine.Engine [no key 0.00057s] {}
2023-11-23 06:28:15,935 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "classesInfo".id IS '主键'
2023-11-23 06:28:15,936 INFO sqlalchemy.engine.Engine [no key 0.00106s] {}
2023-11-23 06:28:15,938 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "classesInfo".number IS '班级编号'
2023-11-23 06:28:15,939 INFO sqlalchemy.engine.Engine [no key 0.00099s] {}
2023-11-23 06:28:15,940 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "classesInfo".name IS '班级名称'
2023-11-23 06:28:15,941 INFO sqlalchemy.engine.Engine [no key 0.00093s] {}
2023-11-23 06:28:15,942 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "classesInfo".fk_teacher_id IS '班级负责人'
2023-11-23 06:28:15,942 INFO sqlalchemy.engine.Engine [no key 0.00056s] {}
2023-11-23 06:28:15,944 INFO sqlalchemy.engine.Engine
CREATE TABLE "classesAndTeachersRelationship" (
id SERIAL NOT NULL,
fk_teacher_id INTEGER NOT NULL,
fk_class_id INTEGER NOT NULL,
PRIMARY KEY (id),
UNIQUE (fk_teacher_id, fk_class_id),
FOREIGN KEY(fk_teacher_id) REFERENCES "teachersInfo" (id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(fk_class_id) REFERENCES "classesInfo" (id) ON DELETE CASCADE ON UPDATE CASCADE
)
2023-11-23 06:28:15,945 INFO sqlalchemy.engine.Engine [no key 0.00103s] {}
2023-11-23 06:28:16,025 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "classesAndTeachersRelationship".id IS '主键'
2023-11-23 06:28:16,026 INFO sqlalchemy.engine.Engine [no key 0.00074s] {}
2023-11-23 06:28:16,027 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "classesAndTeachersRelationship".fk_teacher_id IS '教师记录'
2023-11-23 06:28:16,028 INFO sqlalchemy.engine.Engine [no key 0.00067s] {}
2023-11-23 06:28:16,029 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "classesAndTeachersRelationship".fk_class_id IS '班级记录'
2023-11-23 06:28:16,030 INFO sqlalchemy.engine.Engine [no key 0.00096s] {}
2023-11-23 06:28:16,032 INFO sqlalchemy.engine.Engine
CREATE TABLE "studentsInfo" (
id SERIAL NOT NULL,
name VARCHAR(64) NOT NULL,
gender VARCHAR(1) NOT NULL,
age INTEGER NOT NULL,
fk_student_id INTEGER NOT NULL,
fk_class_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(fk_student_id) REFERENCES "studentsNumberInfo" (id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(fk_class_id) REFERENCES "classesInfo" (id) ON DELETE CASCADE ON UPDATE CASCADE
)
2023-11-23 06:28:16,033 INFO sqlalchemy.engine.Engine [no key 0.00092s] {}
2023-11-23 06:28:16,101 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "studentsInfo".id IS '主键'
2023-11-23 06:28:16,102 INFO sqlalchemy.engine.Engine [no key 0.00076s] {}
2023-11-23 06:28:16,103 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "studentsInfo".name IS '学生姓名'
2023-11-23 06:28:16,104 INFO sqlalchemy.engine.Engine [no key 0.00050s] {}
2023-11-23 06:28:16,105 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "studentsInfo".gender IS '学生性别'
2023-11-23 06:28:16,105 INFO sqlalchemy.engine.Engine [no key 0.00054s] {}
2023-11-23 06:28:16,106 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "studentsInfo".age IS '学生年龄'
2023-11-23 06:28:16,107 INFO sqlalchemy.engine.Engine [no key 0.00047s] {}
2023-11-23 06:28:16,108 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "studentsInfo".fk_student_id IS '学生编号'
2023-11-23 06:28:16,108 INFO sqlalchemy.engine.Engine [no key 0.00061s] {}
2023-11-23 06:28:16,110 INFO sqlalchemy.engine.Engine COMMENT ON COLUMN "studentsInfo".fk_class_id IS '班级编号'
2023-11-23 06:28:16,110 INFO sqlalchemy.engine.Engine [no key 0.00055s] {}
2023-11-23 06:28:16,111 INFO sqlalchemy.engine.Engine COMMIT
插入数据¶
In [10]:
Copied!
import datetime
session.add_all(
(
# 插入学号表数据
StudentsNumberInfo(
number=160201,
admission=datetime.datetime.date(datetime.datetime(2016, 9, 1)),
graduation=datetime.datetime.date(datetime.datetime(2021, 6, 15))
),
StudentsNumberInfo(
number=160101,
admission=datetime.datetime.date(datetime.datetime(2016, 9, 1)),
graduation=datetime.datetime.date(datetime.datetime(2021, 6, 15))
),
StudentsNumberInfo(
number=160301,
admission=datetime.datetime.date(datetime.datetime(2016, 9, 1)),
graduation=datetime.datetime.date(datetime.datetime(2021, 6, 15))
),
StudentsNumberInfo(
number=160102,
admission=datetime.datetime.date(datetime.datetime(2016, 9, 1)),
graduation=datetime.datetime.date(datetime.datetime(2021, 6, 15))
),
StudentsNumberInfo(
number=160302,
admission=datetime.datetime.date(datetime.datetime(2016, 9, 1)),
graduation=datetime.datetime.date(datetime.datetime(2021, 6, 15))
),
StudentsNumberInfo(
number=160202,
admission=datetime.datetime.date(datetime.datetime(2016, 9, 1)),
graduation=datetime.datetime.date(datetime.datetime(2021, 6, 15))
),
# 插入教师表数据
TeachersInfo(
number=3341, name="David", gender="m", age=32,
),
TeachersInfo(
number=3342, name="Jason", gender="m", age=30,
),
TeachersInfo(
number=3343, name="Lisa", gender="f", age=28,
),
# 插入班级表数据
ClassesInfo(
number=1601, name="one year one class", fk_teacher_id=1
),
ClassesInfo(
number=1602, name="one year two class", fk_teacher_id=2
),
ClassesInfo(
number=1603, name="one year three class", fk_teacher_id=3
),
# 插入中间表数据
ClassesAndTeachersRelationship(
fk_class_id=1, fk_teacher_id=1
),
ClassesAndTeachersRelationship(
fk_class_id=2, fk_teacher_id=1
),
ClassesAndTeachersRelationship(
fk_class_id=3, fk_teacher_id=1
),
ClassesAndTeachersRelationship(
fk_class_id=1, fk_teacher_id=2
),
ClassesAndTeachersRelationship(
fk_class_id=3, fk_teacher_id=3
),
# 插入学生表数据
StudentsInfo(
name="Jack", gender="m", age=17, fk_student_id=1, fk_class_id=2
),
StudentsInfo(
name="Tom", gender="m", age=18, fk_student_id=2, fk_class_id=1
),
StudentsInfo(
name="Mary", gender="f", age=16, fk_student_id=3,
fk_class_id=3
),
StudentsInfo(
name="Anna", gender="f", age=17, fk_student_id=4,
fk_class_id=1
),
StudentsInfo(
name="Bobby", gender="m", age=18, fk_student_id=6, fk_class_id=2
),
)
)
session.commit()
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
import datetime
session.add_all(
(
# 插入学号表数据
StudentsNumberInfo(
number=160201,
admission=datetime.datetime.date(datetime.datetime(2016, 9, 1)),
graduation=datetime.datetime.date(datetime.datetime(2021, 6, 15))
),
StudentsNumberInfo(
number=160101,
admission=datetime.datetime.date(datetime.datetime(2016, 9, 1)),
graduation=datetime.datetime.date(datetime.datetime(2021, 6, 15))
),
StudentsNumberInfo(
number=160301,
admission=datetime.datetime.date(datetime.datetime(2016, 9, 1)),
graduation=datetime.datetime.date(datetime.datetime(2021, 6, 15))
),
StudentsNumberInfo(
number=160102,
admission=datetime.datetime.date(datetime.datetime(2016, 9, 1)),
graduation=datetime.datetime.date(datetime.datetime(2021, 6, 15))
),
StudentsNumberInfo(
number=160302,
admission=datetime.datetime.date(datetime.datetime(2016, 9, 1)),
graduation=datetime.datetime.date(datetime.datetime(2021, 6, 15))
),
StudentsNumberInfo(
number=160202,
admission=datetime.datetime.date(datetime.datetime(2016, 9, 1)),
graduation=datetime.datetime.date(datetime.datetime(2021, 6, 15))
),
# 插入教师表数据
TeachersInfo(
number=3341, name="David", gender="m", age=32,
),
TeachersInfo(
number=3342, name="Jason", gender="m", age=30,
),
TeachersInfo(
number=3343, name="Lisa", gender="f", age=28,
),
# 插入班级表数据
ClassesInfo(
number=1601, name="one year one class", fk_teacher_id=1
),
ClassesInfo(
number=1602, name="one year two class", fk_teacher_id=2
),
ClassesInfo(
number=1603, name="one year three class", fk_teacher_id=3
),
# 插入中间表数据
ClassesAndTeachersRelationship(
fk_class_id=1, fk_teacher_id=1
),
ClassesAndTeachersRelationship(
fk_class_id=2, fk_teacher_id=1
),
ClassesAndTeachersRelationship(
fk_class_id=3, fk_teacher_id=1
),
ClassesAndTeachersRelationship(
fk_class_id=1, fk_teacher_id=2
),
ClassesAndTeachersRelationship(
fk_class_id=3, fk_teacher_id=3
),
# 插入学生表数据
StudentsInfo(
name="Jack", gender="m", age=17, fk_student_id=1, fk_class_id=2
),
StudentsInfo(
name="Tom", gender="m", age=18, fk_student_id=2, fk_class_id=1
),
StudentsInfo(
name="Mary", gender="f", age=16, fk_student_id=3,
fk_class_id=3
),
StudentsInfo(
name="Anna", gender="f", age=17, fk_student_id=4,
fk_class_id=1
),
StudentsInfo(
name="Bobby", gender="m", age=18, fk_student_id=6, fk_class_id=2
),
)
)
session.commit()
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
2023-11-23 06:28:26,662 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:28:26,664 INFO sqlalchemy.engine.Engine INSERT INTO "studentsNumberInfo" (number, admission, graduation) VALUES (%(number__0)s, %(admission__0)s, %(graduation__0)s), (%(number__1)s, %(admission__1)s, %(graduation__1)s), (%(number__2)s, %(admission__2)s, %(graduation__2)s), (%(number__3)s, %(admission__3)s, %(graduation__3)s), (%(number__4)s, %(admission__4)s, %(graduation__4)s), (%(number__5)s, %(admission__5)s, %(graduation__5)s) RETURNING "studentsNumberInfo".id
2023-11-23 06:28:26,665 INFO sqlalchemy.engine.Engine [generated in 0.00010s (insertmanyvalues)] {'graduation__0': datetime.date(2021, 6, 15), 'admission__0': datetime.date(2016, 9, 1), 'number__0': 160201, 'graduation__1': datetime.date(2021, 6, 15), 'admission__1': datetime.date(2016, 9, 1), 'number__1': 160101, 'graduation__2': datetime.date(2021, 6, 15), 'admission__2': datetime.date(2016, 9, 1), 'number__2': 160301, 'graduation__3': datetime.date(2021, 6, 15), 'admission__3': datetime.date(2016, 9, 1), 'number__3': 160102, 'graduation__4': datetime.date(2021, 6, 15), 'admission__4': datetime.date(2016, 9, 1), 'number__4': 160302, 'graduation__5': datetime.date(2021, 6, 15), 'admission__5': datetime.date(2016, 9, 1), 'number__5': 160202}
2023-11-23 06:28:26,689 INFO sqlalchemy.engine.Engine INSERT INTO "teachersInfo" (number, name, gender, age) VALUES (%(number__0)s, %(name__0)s, %(gender__0)s, %(age__0)s), (%(number__1)s, %(name__1)s, %(gender__1)s, %(age__1)s), (%(number__2)s, %(name__2)s, %(gender__2)s, %(age__2)s) RETURNING "teachersInfo".id
2023-11-23 06:28:26,690 INFO sqlalchemy.engine.Engine [generated in 0.00014s (insertmanyvalues)] {'age__0': 32, 'number__0': 3341, 'name__0': 'David', 'gender__0': 'm', 'age__1': 30, 'number__1': 3342, 'name__1': 'Jason', 'gender__1': 'm', 'age__2': 28, 'number__2': 3343, 'name__2': 'Lisa', 'gender__2': 'f'}
2023-11-23 06:28:26,714 INFO sqlalchemy.engine.Engine INSERT INTO "classesInfo" (number, name, fk_teacher_id) VALUES (%(number__0)s, %(name__0)s, %(fk_teacher_id__0)s), (%(number__1)s, %(name__1)s, %(fk_teacher_id__1)s), (%(number__2)s, %(name__2)s, %(fk_teacher_id__2)s) RETURNING "classesInfo".id
2023-11-23 06:28:26,714 INFO sqlalchemy.engine.Engine [generated in 0.00007s (insertmanyvalues)] {'fk_teacher_id__0': 1, 'number__0': 1601, 'name__0': 'one year one class', 'fk_teacher_id__1': 2, 'number__1': 1602, 'name__1': 'one year two class', 'fk_teacher_id__2': 3, 'number__2': 1603, 'name__2': 'one year three class'}
2023-11-23 06:28:26,754 INFO sqlalchemy.engine.Engine INSERT INTO "classesAndTeachersRelationship" (fk_teacher_id, fk_class_id) VALUES (%(fk_teacher_id__0)s, %(fk_class_id__0)s), (%(fk_teacher_id__1)s, %(fk_class_id__1)s), (%(fk_teacher_id__2)s, %(fk_class_id__2)s), (%(fk_teacher_id__3)s, %(fk_class_id__3)s), (%(fk_teacher_id__4)s, %(fk_class_id__4)s) RETURNING "classesAndTeachersRelationship".id
2023-11-23 06:28:26,755 INFO sqlalchemy.engine.Engine [generated in 0.00012s (insertmanyvalues)] {'fk_teacher_id__0': 1, 'fk_class_id__0': 1, 'fk_teacher_id__1': 1, 'fk_class_id__1': 2, 'fk_teacher_id__2': 1, 'fk_class_id__2': 3, 'fk_teacher_id__3': 2, 'fk_class_id__3': 1, 'fk_teacher_id__4': 3, 'fk_class_id__4': 3}
2023-11-23 06:28:26,783 INFO sqlalchemy.engine.Engine INSERT INTO "studentsInfo" (name, gender, age, fk_student_id, fk_class_id) VALUES (%(name__0)s, %(gender__0)s, %(age__0)s, %(fk_student_id__0)s, %(fk_class_id__0)s), (%(name__1)s, %(gender__1)s, %(age__1)s, %(fk_student_id__1)s, %(fk_class_id__1)s), ... 178 characters truncated ... )s, %(gender__4)s, %(age__4)s, %(fk_student_id__4)s, %(fk_class_id__4)s) RETURNING "studentsInfo".id
2023-11-23 06:28:26,784 INFO sqlalchemy.engine.Engine [generated in 0.00010s (insertmanyvalues)] {'name__0': 'Jack', 'fk_student_id__0': 1, 'gender__0': 'm', 'age__0': 17, 'fk_class_id__0': 2, 'name__1': 'Tom', 'fk_student_id__1': 2, 'gender__1': 'm', 'age__1': 18, 'fk_class_id__1': 1, 'name__2': 'Mary', 'fk_student_id__2': 3, 'gender__2': 'f', 'age__2': 16, 'fk_class_id__2': 3, 'name__3': 'Anna', 'fk_student_id__3': 4, 'gender__3': 'f', 'age__3': 17, 'fk_class_id__3': 1, 'name__4': 'Bobby', 'fk_student_id__4': 6, 'gender__4': 'm', 'age__4': 18, 'fk_class_id__4': 2}
2023-11-23 06:28:26,805 INFO sqlalchemy.engine.Engine COMMIT
JOIN¶
In [11]:
Copied!
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
result = session.query(
StudentsInfo.name,
StudentsNumberInfo.number,
ClassesInfo.number
).join(
StudentsNumberInfo,
StudentsInfo.fk_student_id == StudentsNumberInfo.id
).join(
ClassesInfo,
StudentsInfo.fk_class_id == ClassesInfo.id
).all()
print(result)
# [('Jack', 160201, 1602), ('Tom', 160101, 1601), ('Mary', 160301, 1603), ('Anna', 160102, 1601), ('Bobby', 160202, 1602)]
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
result = session.query(
StudentsInfo.name,
StudentsNumberInfo.number,
ClassesInfo.number
).join(
StudentsNumberInfo,
StudentsInfo.fk_student_id == StudentsNumberInfo.id
).join(
ClassesInfo,
StudentsInfo.fk_class_id == ClassesInfo.id
).all()
print(result)
# [('Jack', 160201, 1602), ('Tom', 160101, 1601), ('Mary', 160301, 1603), ('Anna', 160102, 1601), ('Bobby', 160202, 1602)]
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
2023-11-23 06:28:41,103 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:28:41,105 INFO sqlalchemy.engine.Engine SELECT "studentsInfo".name AS "studentsInfo_name", "studentsNumberInfo".number AS "studentsNumberInfo_number", "classesInfo".number AS "classesInfo_number"
FROM "studentsInfo" JOIN "studentsNumberInfo" ON "studentsInfo".fk_student_id = "studentsNumberInfo".id JOIN "classesInfo" ON "studentsInfo".fk_class_id = "classesInfo".id
2023-11-23 06:28:41,106 INFO sqlalchemy.engine.Engine [generated in 0.00076s] {}
[('Jack', 160201, 1602), ('Tom', 160101, 1601), ('Mary', 160301, 1603), ('Anna', 160102, 1601), ('Bobby', 160202, 1602)]
2023-11-23 06:28:41,152 INFO sqlalchemy.engine.Engine ROLLBACK
LEFT JOIN¶
left join只需要在每个JOIN中指定isouter关键字参数为True即可：
session.query( 左表.字段, 右表.字段 ) .join( 右表, 链接条件, isouter=True ).all()
RIGHT JOIN¶
需要换表的位置，SQLALchemy本身并未提供RIGHT JOIN，所以使用时一定要注意驱动顺序，小表驱动大表：
session.query( 左表.字段, 右表.字段 ) .join( 左表, 链接条件, isouter=True ).all()
子查询¶
子查询使用subquery()实现，如下所示，查询每个班级中年龄最小的人：
In [12]:
Copied!
# 获取链接池、ORM表对象
from sqlalchemy import func
# 子查询中所有字段的访问都需要加上c的前缀
# 如 sub_query.c.id、 sub_query.c.name等
sub_query = session.query(
# 使用label()来为字段AS一个别名
# 后续访问需要通过sub_query.c.alias进行访问
func.min(StudentsInfo.age).label("min_age"),
ClassesInfo.id,
ClassesInfo.name
).join(
ClassesInfo,
StudentsInfo.fk_class_id == ClassesInfo.id
).group_by(
ClassesInfo.id
).subquery()
result = session.query(
StudentsInfo.name,
sub_query.c.min_age,
sub_query.c.name
).join(
sub_query,
sub_query.c.id == StudentsInfo.fk_class_id
).filter(
sub_query.c.min_age == StudentsInfo.age
)
print(result.all())
# [('Jack', 17, 'one year two class'), ('Mary', 16, 'one year three class'), ('Anna', 17, 'one year one class')]
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
# 获取链接池、ORM表对象
from sqlalchemy import func
# 子查询中所有字段的访问都需要加上c的前缀
# 如 sub_query.c.id、 sub_query.c.name等
sub_query = session.query(
# 使用label()来为字段AS一个别名
# 后续访问需要通过sub_query.c.alias进行访问
func.min(StudentsInfo.age).label("min_age"),
ClassesInfo.id,
ClassesInfo.name
).join(
ClassesInfo,
StudentsInfo.fk_class_id == ClassesInfo.id
).group_by(
ClassesInfo.id
).subquery()
result = session.query(
StudentsInfo.name,
sub_query.c.min_age,
sub_query.c.name
).join(
sub_query,
sub_query.c.id == StudentsInfo.fk_class_id
).filter(
sub_query.c.min_age == StudentsInfo.age
)
print(result.all())
# [('Jack', 17, 'one year two class'), ('Mary', 16, 'one year three class'), ('Anna', 17, 'one year one class')]
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
2023-11-23 06:28:45,278 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:28:45,280 INFO sqlalchemy.engine.Engine SELECT "studentsInfo".name AS "studentsInfo_name", anon_1.min_age AS anon_1_min_age, anon_1.name AS anon_1_name
FROM "studentsInfo" JOIN (SELECT min("studentsInfo".age) AS min_age, "classesInfo".id AS id, "classesInfo".name AS name
FROM "studentsInfo" JOIN "classesInfo" ON "studentsInfo".fk_class_id = "classesInfo".id GROUP BY "classesInfo".id) AS anon_1 ON anon_1.id = "studentsInfo".fk_class_id
WHERE anon_1.min_age = "studentsInfo".age
2023-11-23 06:28:45,281 INFO sqlalchemy.engine.Engine [generated in 0.00071s] {}
[('Jack', 17, 'one year two class'), ('Mary', 16, 'one year three class'), ('Anna', 17, 'one year one class')]
2023-11-23 06:28:45,303 INFO sqlalchemy.engine.Engine ROLLBACK
In [13]:
Copied!
# 查询所有学生的所在班级，我们可以通过学生的from_class字段拿到其所在班级
# 另外，对于学生来说，班级只能有一个，所以have_student应当是一个对象
# 获取链接池、ORM表对象
students_lst = session.query(
StudentsInfo
).all()
for row in students_lst:
print(f"""
student name : {row.name}
from : {row.from_class.name}
""")
# student name : Mary
# from : one year three class
# student name : Anna
# from : one year one class
# student name : Bobby
# from : one year two class
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
# 查询所有学生的所在班级，我们可以通过学生的from_class字段拿到其所在班级
# 另外，对于学生来说，班级只能有一个，所以have_student应当是一个对象
# 获取链接池、ORM表对象
students_lst = session.query(
StudentsInfo
).all()
for row in students_lst:
print(f"""
student name : {row.name}
from : {row.from_class.name}
""")
# student name : Mary
# from : one year three class
# student name : Anna
# from : one year one class
# student name : Bobby
# from : one year two class
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
2023-11-23 06:28:50,704 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:28:50,705 INFO sqlalchemy.engine.Engine SELECT "studentsInfo".id AS "studentsInfo_id", "studentsInfo".name AS "studentsInfo_name", "studentsInfo".gender AS "studentsInfo_gender", "studentsInfo".age AS "studentsInfo_age", "studentsInfo".fk_student_id AS "studentsInfo_fk_student_id", "studentsInfo".fk_class_id AS "studentsInfo_fk_class_id"
FROM "studentsInfo"
2023-11-23 06:28:50,706 INFO sqlalchemy.engine.Engine [generated in 0.00085s] {}
2023-11-23 06:28:50,715 INFO sqlalchemy.engine.Engine SELECT "classesInfo".id AS "classesInfo_id", "classesInfo".number AS "classesInfo_number", "classesInfo".name AS "classesInfo_name", "classesInfo".fk_teacher_id AS "classesInfo_fk_teacher_id"
FROM "classesInfo"
WHERE "classesInfo".id = %(pk_1)s
2023-11-23 06:28:50,716 INFO sqlalchemy.engine.Engine [generated in 0.00061s] {'pk_1': 2}
student name : Jack
from : one year two class
2023-11-23 06:28:50,731 INFO sqlalchemy.engine.Engine SELECT "classesInfo".id AS "classesInfo_id", "classesInfo".number AS "classesInfo_number", "classesInfo".name AS "classesInfo_name", "classesInfo".fk_teacher_id AS "classesInfo_fk_teacher_id"
FROM "classesInfo"
WHERE "classesInfo".id = %(pk_1)s
2023-11-23 06:28:50,731 INFO sqlalchemy.engine.Engine [cached since 0.01611s ago] {'pk_1': 1}
student name : Tom
from : one year one class
2023-11-23 06:28:50,733 INFO sqlalchemy.engine.Engine SELECT "classesInfo".id AS "classesInfo_id", "classesInfo".number AS "classesInfo_number", "classesInfo".name AS "classesInfo_name", "classesInfo".fk_teacher_id AS "classesInfo_fk_teacher_id"
FROM "classesInfo"
WHERE "classesInfo".id = %(pk_1)s
2023-11-23 06:28:50,734 INFO sqlalchemy.engine.Engine [cached since 0.01849s ago] {'pk_1': 3}
student name : Mary
from : one year three class
student name : Anna
from : one year one class
student name : Bobby
from : one year two class
2023-11-23 06:28:50,735 INFO sqlalchemy.engine.Engine ROLLBACK
反向查询¶
下面是反向查询的示例，反向查询是指从没有relationship逻辑字段的表开始查询：
In [14]:
Copied!
# 查询所有班级中的所有学生，学生表中有relationship，并且它的backref为have_student，所以我们可以通过班级.have_student来获取所有学生记录
# 另外，对于班级来说，学生可以有多个，所以have_student应当是一个序列
classes_lst = session.query(
ClassesInfo
).all()
for row in classes_lst:
print("class name :", row.name)
for student in row.have_student:
print("student name :", student.name)
# class name : one year one class
# student name : Jack
# student name : Anna
# class name : one year two class
# student name : Tom
# class name : one year three class
# student name : Mary
# student name : Bobby
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
# 查询所有班级中的所有学生，学生表中有relationship，并且它的backref为have_student，所以我们可以通过班级.have_student来获取所有学生记录
# 另外，对于班级来说，学生可以有多个，所以have_student应当是一个序列
classes_lst = session.query(
ClassesInfo
).all()
for row in classes_lst:
print("class name :", row.name)
for student in row.have_student:
print("student name :", student.name)
# class name : one year one class
# student name : Jack
# student name : Anna
# class name : one year two class
# student name : Tom
# class name : one year three class
# student name : Mary
# student name : Bobby
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
2023-11-23 06:28:54,118 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:28:54,119 INFO sqlalchemy.engine.Engine SELECT "classesInfo".id AS "classesInfo_id", "classesInfo".number AS "classesInfo_number", "classesInfo".name AS "classesInfo_name", "classesInfo".fk_teacher_id AS "classesInfo_fk_teacher_id"
FROM "classesInfo"
2023-11-23 06:28:54,120 INFO sqlalchemy.engine.Engine [generated in 0.00112s] {}
class name : one year one class
2023-11-23 06:28:54,137 INFO sqlalchemy.engine.Engine SELECT "studentsInfo".id AS "studentsInfo_id", "studentsInfo".name AS "studentsInfo_name", "studentsInfo".gender AS "studentsInfo_gender", "studentsInfo".age AS "studentsInfo_age", "studentsInfo".fk_student_id AS "studentsInfo_fk_student_id", "studentsInfo".fk_class_id AS "studentsInfo_fk_class_id"
FROM "studentsInfo"
WHERE %(param_1)s = "studentsInfo".fk_class_id
2023-11-23 06:28:54,138 INFO sqlalchemy.engine.Engine [generated in 0.00062s] {'param_1': 1}
student name : Tom
student name : Anna
class name : one year two class
2023-11-23 06:28:54,145 INFO sqlalchemy.engine.Engine SELECT "studentsInfo".id AS "studentsInfo_id", "studentsInfo".name AS "studentsInfo_name", "studentsInfo".gender AS "studentsInfo_gender", "studentsInfo".age AS "studentsInfo_age", "studentsInfo".fk_student_id AS "studentsInfo_fk_student_id", "studentsInfo".fk_class_id AS "studentsInfo_fk_class_id"
FROM "studentsInfo"
WHERE %(param_1)s = "studentsInfo".fk_class_id
2023-11-23 06:28:54,146 INFO sqlalchemy.engine.Engine [cached since 0.008536s ago] {'param_1': 2}
student name : Jack
student name : Bobby
class name : one year three class
2023-11-23 06:28:54,148 INFO sqlalchemy.engine.Engine SELECT "studentsInfo".id AS "studentsInfo_id", "studentsInfo".name AS "studentsInfo_name", "studentsInfo".gender AS "studentsInfo_gender", "studentsInfo".age AS "studentsInfo_age", "studentsInfo".fk_student_id AS "studentsInfo_fk_student_id", "studentsInfo".fk_class_id AS "studentsInfo_fk_class_id"
FROM "studentsInfo"
WHERE %(param_1)s = "studentsInfo".fk_class_id
2023-11-23 06:28:54,148 INFO sqlalchemy.engine.Engine [cached since 0.01103s ago] {'param_1': 3}
student name : Mary
2023-11-23 06:28:54,150 INFO sqlalchemy.engine.Engine ROLLBACK
总结，正向查询的逻辑字段总是得到一个对象，反向查询的逻辑字段总是得到一个列表。
反向方法
使用逻辑字段relationship可以直接对一些跨表记录进行增删改查。
由于逻辑字段是一个类似于列表的存在（仅限于反向查询，正向查询总是得到一个对象），所以列表的绝大多数方法都能用。
<class 'sqlalchemy.orm.collections.InstrumentedList'> - append() - clear() - copy() - count() - extend() - index() - insert() - pop() - remove() - reverse() - sort() 下面不再进行实机演示，因为我们上面的几张表中做了很多约束。
In [15]:
Copied!
# 一下代码只是举例，运行时不会成功
# 比如
# 给老师增加班级
result = session.query(Teachers).first()
# extend方法：
result.re_class.extend([
Classes(name="三年级一班",),
Classes(name="三年级二班",),
])
# 比如
# 减少老师所在的班级
result = session.query(Teachers).first()
# 待删除的班级对象,集合查找比较快
delete_class_set = {
session.query(Classes).filter_by(id=7).first(),
session.query(Classes).filter_by(id=8).first(),
}
# 循换老师所在的班级
# remove方法：
for class_obj in result.re_class:
if class_obj in delete_class_set:
result.re_class.remove(class_obj)
# 比如
# 清空老师所任教的所有班级
# 拿出一个老师
result = session.query(Teachers).first()
result.re_class.clear()
# 一下代码只是举例，运行时不会成功
# 比如
# 给老师增加班级
result = session.query(Teachers).first()
# extend方法：
result.re_class.extend([
Classes(name="三年级一班",),
Classes(name="三年级二班",),
])
# 比如
# 减少老师所在的班级
result = session.query(Teachers).first()
# 待删除的班级对象,集合查找比较快
delete_class_set = {
session.query(Classes).filter_by(id=7).first(),
session.query(Classes).filter_by(id=8).first(),
}
# 循换老师所在的班级
# remove方法：
for class_obj in result.re_class:
if class_obj in delete_class_set:
result.re_class.remove(class_obj)
# 比如
# 清空老师所任教的所有班级
# 拿出一个老师
result = session.query(Teachers).first()
result.re_class.clear()
--------------------------------------------------------------------------- NameError Traceback (most recent call last) Cell In[15], line 6 1 # 一下代码只是举例，运行时不会成功 2 3 4 # 比如 5 # 给老师增加班级 ----> 6 result = session.query(Teachers).first() 7 # extend方法： 8 result.re_class.extend([ 9 Classes(name="三年级一班",), 10 Classes(name="三年级二班",), 11 ]) NameError: name 'Teachers' is not defined
查询案例¶
（1）查看每个班级共有多少学生：
In [16]:
Copied!
#JOIN查询：
# 获取链接池、ORM表对象
from sqlalchemy import func
result = session.query(
ClassesInfo.name,
func.count(StudentsInfo.id)
).join(
StudentsInfo,
ClassesInfo.id == StudentsInfo.fk_class_id
).group_by(
ClassesInfo.id
).all()
print(result)
# [('one year one class', 2), ('one year two class', 2), ('one year three class', 1)]
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
#JOIN查询：
# 获取链接池、ORM表对象
from sqlalchemy import func
result = session.query(
ClassesInfo.name,
func.count(StudentsInfo.id)
).join(
StudentsInfo,
ClassesInfo.id == StudentsInfo.fk_class_id
).group_by(
ClassesInfo.id
).all()
print(result)
# [('one year one class', 2), ('one year two class', 2), ('one year three class', 1)]
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
2023-11-23 06:29:41,662 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:29:41,664 INFO sqlalchemy.engine.Engine SELECT "classesInfo".name AS "classesInfo_name", count("studentsInfo".id) AS count_1
FROM "classesInfo" JOIN "studentsInfo" ON "classesInfo".id = "studentsInfo".fk_class_id GROUP BY "classesInfo".id
2023-11-23 06:29:41,664 INFO sqlalchemy.engine.Engine [generated in 0.00085s] {}
[('one year two class', 2), ('one year one class', 2), ('one year three class', 1)]
2023-11-23 06:29:41,696 INFO sqlalchemy.engine.Engine ROLLBACK
In [17]:
Copied!
#正反查询：
result = {}
class_lst = session.query(
ClassesInfo
).all()
for row in class_lst:
for student in row.have_student:
count = result.setdefault(row.name, 0)
result[row.name] = count + 1
print(result.items())
# dict_items([('one year one class', 2), ('one year two class', 2), ('one year three class', 1)])
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
#正反查询：
result = {}
class_lst = session.query(
ClassesInfo
).all()
for row in class_lst:
for student in row.have_student:
count = result.setdefault(row.name, 0)
result[row.name] = count + 1
print(result.items())
# dict_items([('one year one class', 2), ('one year two class', 2), ('one year three class', 1)])
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
2023-11-23 06:29:45,513 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:29:45,514 INFO sqlalchemy.engine.Engine SELECT "classesInfo".id AS "classesInfo_id", "classesInfo".number AS "classesInfo_number", "classesInfo".name AS "classesInfo_name", "classesInfo".fk_teacher_id AS "classesInfo_fk_teacher_id"
FROM "classesInfo"
2023-11-23 06:29:45,515 INFO sqlalchemy.engine.Engine [cached since 51.4s ago] {}
2023-11-23 06:29:45,530 INFO sqlalchemy.engine.Engine SELECT "studentsInfo".id AS "studentsInfo_id", "studentsInfo".name AS "studentsInfo_name", "studentsInfo".gender AS "studentsInfo_gender", "studentsInfo".age AS "studentsInfo_age", "studentsInfo".fk_student_id AS "studentsInfo_fk_student_id", "studentsInfo".fk_class_id AS "studentsInfo_fk_class_id"
FROM "studentsInfo"
WHERE %(param_1)s = "studentsInfo".fk_class_id
2023-11-23 06:29:45,531 INFO sqlalchemy.engine.Engine [cached since 51.39s ago] {'param_1': 1}
2023-11-23 06:29:45,538 INFO sqlalchemy.engine.Engine SELECT "studentsInfo".id AS "studentsInfo_id", "studentsInfo".name AS "studentsInfo_name", "studentsInfo".gender AS "studentsInfo_gender", "studentsInfo".age AS "studentsInfo_age", "studentsInfo".fk_student_id AS "studentsInfo_fk_student_id", "studentsInfo".fk_class_id AS "studentsInfo_fk_class_id"
FROM "studentsInfo"
WHERE %(param_1)s = "studentsInfo".fk_class_id
2023-11-23 06:29:45,538 INFO sqlalchemy.engine.Engine [cached since 51.4s ago] {'param_1': 2}
2023-11-23 06:29:45,540 INFO sqlalchemy.engine.Engine SELECT "studentsInfo".id AS "studentsInfo_id", "studentsInfo".name AS "studentsInfo_name", "studentsInfo".gender AS "studentsInfo_gender", "studentsInfo".age AS "studentsInfo_age", "studentsInfo".fk_student_id AS "studentsInfo_fk_student_id", "studentsInfo".fk_class_id AS "studentsInfo_fk_class_id"
FROM "studentsInfo"
WHERE %(param_1)s = "studentsInfo".fk_class_id
2023-11-23 06:29:45,540 INFO sqlalchemy.engine.Engine [cached since 51.4s ago] {'param_1': 3}
dict_items([('one year one class', 2), ('one year two class', 2), ('one year three class', 1)])
2023-11-23 06:29:45,542 INFO sqlalchemy.engine.Engine ROLLBACK
（2）查看每个学生的入学、毕业年份以及所在的班级名称：
In [18]:
Copied!
#JOIN查询：
result = session.query(
StudentsNumberInfo.number,
StudentsInfo.name,
ClassesInfo.name,
StudentsNumberInfo.admission,
StudentsNumberInfo.graduation
).join(
StudentsInfo,
StudentsInfo.fk_class_id == ClassesInfo.id
).join(
StudentsNumberInfo,
StudentsNumberInfo.id == StudentsInfo.fk_student_id
).order_by(
StudentsNumberInfo.number.asc()
).all()
print(result)
# [
# (160101, 'Tom', 'one year one class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160102, 'Anna', 'one year one class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160201, 'Jack', 'one year two class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160202, 'Bobby', 'one year two class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160301, 'Mary', 'one year three class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15))
# ]
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
#JOIN查询：
result = session.query(
StudentsNumberInfo.number,
StudentsInfo.name,
ClassesInfo.name,
StudentsNumberInfo.admission,
StudentsNumberInfo.graduation
).join(
StudentsInfo,
StudentsInfo.fk_class_id == ClassesInfo.id
).join(
StudentsNumberInfo,
StudentsNumberInfo.id == StudentsInfo.fk_student_id
).order_by(
StudentsNumberInfo.number.asc()
).all()
print(result)
# [
# (160101, 'Tom', 'one year one class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160102, 'Anna', 'one year one class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160201, 'Jack', 'one year two class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160202, 'Bobby', 'one year two class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160301, 'Mary', 'one year three class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15))
# ]
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
2023-11-23 06:29:50,937 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:29:50,939 INFO sqlalchemy.engine.Engine SELECT "studentsNumberInfo".number AS "studentsNumberInfo_number", "studentsInfo".name AS "studentsInfo_name", "classesInfo".name AS "classesInfo_name", "studentsNumberInfo".admission AS "studentsNumberInfo_admission", "studentsNumberInfo".graduation AS "studentsNumberInfo_graduation"
FROM "classesInfo" JOIN "studentsInfo" ON "studentsInfo".fk_class_id = "classesInfo".id JOIN "studentsNumberInfo" ON "studentsNumberInfo".id = "studentsInfo".fk_student_id ORDER BY "studentsNumberInfo".number ASC
2023-11-23 06:29:50,940 INFO sqlalchemy.engine.Engine [generated in 0.00081s] {}
[(160101, 'Tom', 'one year one class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)), (160102, 'Anna', 'one year one class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)), (160201, 'Jack', 'one year two class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)), (160202, 'Bobby', 'one year two class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)), (160301, 'Mary', 'one year three class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15))]
2023-11-23 06:29:50,970 INFO sqlalchemy.engine.Engine ROLLBACK
In [19]:
Copied!
#正反查询：
result = []
student_lst = session.query(
StudentsInfo
).all()
for row in student_lst:
result.append((
row.number_info.number,
row.name,
row.from_class.name,
row.number_info.admission,
row.number_info.graduation
))
print(result)
# [
# (160101, 'Tom', 'one year one class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160102, 'Anna', 'one year one class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160201, 'Jack', 'one year two class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160202, 'Bobby', 'one year two class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160301, 'Mary', 'one year three class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15))
# ]
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
#正反查询：
result = []
student_lst = session.query(
StudentsInfo
).all()
for row in student_lst:
result.append((
row.number_info.number,
row.name,
row.from_class.name,
row.number_info.admission,
row.number_info.graduation
))
print(result)
# [
# (160101, 'Tom', 'one year one class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160102, 'Anna', 'one year one class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160201, 'Jack', 'one year two class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160202, 'Bobby', 'one year two class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)),
# (160301, 'Mary', 'one year three class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15))
# ]
# 关闭链接，亦可使用session.remove()，它将回收该链接
session.close()
2023-11-23 06:29:53,168 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:29:53,169 INFO sqlalchemy.engine.Engine SELECT "studentsInfo".id AS "studentsInfo_id", "studentsInfo".name AS "studentsInfo_name", "studentsInfo".gender AS "studentsInfo_gender", "studentsInfo".age AS "studentsInfo_age", "studentsInfo".fk_student_id AS "studentsInfo_fk_student_id", "studentsInfo".fk_class_id AS "studentsInfo_fk_class_id"
FROM "studentsInfo"
2023-11-23 06:29:53,170 INFO sqlalchemy.engine.Engine [cached since 62.46s ago] {}
2023-11-23 06:29:53,180 INFO sqlalchemy.engine.Engine SELECT "studentsNumberInfo".id AS "studentsNumberInfo_id", "studentsNumberInfo".number AS "studentsNumberInfo_number", "studentsNumberInfo".admission AS "studentsNumberInfo_admission", "studentsNumberInfo".graduation AS "studentsNumberInfo_graduation"
FROM "studentsNumberInfo"
WHERE "studentsNumberInfo".id = %(pk_1)s
2023-11-23 06:29:53,180 INFO sqlalchemy.engine.Engine [generated in 0.00059s] {'pk_1': 1}
2023-11-23 06:29:53,191 INFO sqlalchemy.engine.Engine SELECT "classesInfo".id AS "classesInfo_id", "classesInfo".number AS "classesInfo_number", "classesInfo".name AS "classesInfo_name", "classesInfo".fk_teacher_id AS "classesInfo_fk_teacher_id"
FROM "classesInfo"
WHERE "classesInfo".id = %(pk_1)s
2023-11-23 06:29:53,191 INFO sqlalchemy.engine.Engine [cached since 62.48s ago] {'pk_1': 2}
2023-11-23 06:29:53,207 INFO sqlalchemy.engine.Engine SELECT "studentsNumberInfo".id AS "studentsNumberInfo_id", "studentsNumberInfo".number AS "studentsNumberInfo_number", "studentsNumberInfo".admission AS "studentsNumberInfo_admission", "studentsNumberInfo".graduation AS "studentsNumberInfo_graduation"
FROM "studentsNumberInfo"
WHERE "studentsNumberInfo".id = %(pk_1)s
2023-11-23 06:29:53,207 INFO sqlalchemy.engine.Engine [cached since 0.02765s ago] {'pk_1': 2}
2023-11-23 06:29:53,209 INFO sqlalchemy.engine.Engine SELECT "classesInfo".id AS "classesInfo_id", "classesInfo".number AS "classesInfo_number", "classesInfo".name AS "classesInfo_name", "classesInfo".fk_teacher_id AS "classesInfo_fk_teacher_id"
FROM "classesInfo"
WHERE "classesInfo".id = %(pk_1)s
2023-11-23 06:29:53,210 INFO sqlalchemy.engine.Engine [cached since 62.49s ago] {'pk_1': 1}
2023-11-23 06:29:53,211 INFO sqlalchemy.engine.Engine SELECT "studentsNumberInfo".id AS "studentsNumberInfo_id", "studentsNumberInfo".number AS "studentsNumberInfo_number", "studentsNumberInfo".admission AS "studentsNumberInfo_admission", "studentsNumberInfo".graduation AS "studentsNumberInfo_graduation"
FROM "studentsNumberInfo"
WHERE "studentsNumberInfo".id = %(pk_1)s
2023-11-23 06:29:53,212 INFO sqlalchemy.engine.Engine [cached since 0.03216s ago] {'pk_1': 3}
2023-11-23 06:29:53,214 INFO sqlalchemy.engine.Engine SELECT "classesInfo".id AS "classesInfo_id", "classesInfo".number AS "classesInfo_number", "classesInfo".name AS "classesInfo_name", "classesInfo".fk_teacher_id AS "classesInfo_fk_teacher_id"
FROM "classesInfo"
WHERE "classesInfo".id = %(pk_1)s
2023-11-23 06:29:53,214 INFO sqlalchemy.engine.Engine [cached since 62.5s ago] {'pk_1': 3}
2023-11-23 06:29:53,216 INFO sqlalchemy.engine.Engine SELECT "studentsNumberInfo".id AS "studentsNumberInfo_id", "studentsNumberInfo".number AS "studentsNumberInfo_number", "studentsNumberInfo".admission AS "studentsNumberInfo_admission", "studentsNumberInfo".graduation AS "studentsNumberInfo_graduation"
FROM "studentsNumberInfo"
WHERE "studentsNumberInfo".id = %(pk_1)s
2023-11-23 06:29:53,217 INFO sqlalchemy.engine.Engine [cached since 0.03674s ago] {'pk_1': 4}
2023-11-23 06:29:53,218 INFO sqlalchemy.engine.Engine SELECT "studentsNumberInfo".id AS "studentsNumberInfo_id", "studentsNumberInfo".number AS "studentsNumberInfo_number", "studentsNumberInfo".admission AS "studentsNumberInfo_admission", "studentsNumberInfo".graduation AS "studentsNumberInfo_graduation"
FROM "studentsNumberInfo"
WHERE "studentsNumberInfo".id = %(pk_1)s
2023-11-23 06:29:53,219 INFO sqlalchemy.engine.Engine [cached since 0.0392s ago] {'pk_1': 6}
[(160201, 'Jack', 'one year two class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)), (160101, 'Tom', 'one year one class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)), (160301, 'Mary', 'one year three class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)), (160102, 'Anna', 'one year one class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15)), (160202, 'Bobby', 'one year two class', datetime.date(2016, 9, 1), datetime.date(2021, 6, 15))]
2023-11-23 06:29:53,220 INFO sqlalchemy.engine.Engine ROLLBACK
3）查看David所教授的学生中年龄最小的学生：
练习1：¶
In [24]:
Copied!
# todo
#JOIN查询
# [('David', 'Mary', 16, 'one year three class')]
result = session.query(
TeachersInfo.name,
StudentsInfo.name,
StudentsInfo.age,
ClassesInfo.name
).join(
ClassesInfo,
StudentsInfo.fk_class_id == ClassesInfo.id
).join(
ClassesAndTeachersRelationship,
ClassesInfo.id == ClassesAndTeachersRelationship.fk_class_id
).join(
TeachersInfo,
ClassesAndTeachersRelationship.fk_teacher_id == TeachersInfo.id
).filter(
TeachersInfo.name == 'David'
).order_by(
StudentsInfo.age.asc()
).first()
print("")
print(result)
session.close()
# todo
#JOIN查询
# [('David', 'Mary', 16, 'one year three class')]
result = session.query(
TeachersInfo.name,
StudentsInfo.name,
StudentsInfo.age,
ClassesInfo.name
).join(
ClassesInfo,
StudentsInfo.fk_class_id == ClassesInfo.id
).join(
ClassesAndTeachersRelationship,
ClassesInfo.id == ClassesAndTeachersRelationship.fk_class_id
).join(
TeachersInfo,
ClassesAndTeachersRelationship.fk_teacher_id == TeachersInfo.id
).filter(
TeachersInfo.name == 'David'
).order_by(
StudentsInfo.age.asc()
).first()
print("")
print(result)
session.close()
2023-11-23 06:33:43,671 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:33:43,672 INFO sqlalchemy.engine.Engine SELECT "teachersInfo".name AS "teachersInfo_name", "studentsInfo".name AS "studentsInfo_name", "studentsInfo".age AS "studentsInfo_age", "classesInfo".name AS "classesInfo_name"
FROM "studentsInfo" JOIN "classesInfo" ON "studentsInfo".fk_class_id = "classesInfo".id JOIN "classesAndTeachersRelationship" ON "classesInfo".id = "classesAndTeachersRelationship".fk_class_id JOIN "teachersInfo" ON "classesAndTeachersRelationship".fk_teacher_id = "teachersInfo".id
WHERE "teachersInfo".name = %(name_1)s ORDER BY "studentsInfo".age ASC
LIMIT %(param_1)s
2023-11-23 06:33:43,673 INFO sqlalchemy.engine.Engine [cached since 190.3s ago] {'name_1': 'David', 'param_1': 1}
('David', 'Mary', 16, 'one year three class')
2023-11-23 06:33:43,716 INFO sqlalchemy.engine.Engine ROLLBACK
练习2:¶
In [25]:
Copied!
#正反查询：
# ('David', 'Mary', 16, 'one year three class')
result = []
student_lst = session.query(
StudentsInfo
).all()
teacher_lst = session.query(
TeachersInfo
).all()
teacher_class_lst = session.query(
ClassesAndTeachersRelationship
).all()
class_id = []
for row in teacher_lst:
if row.name == 'David':
class_id = [x.fk_class_id for x in row.mid]
for row in student_lst:
if row.from_class.id in class_id:
result.append((
'David',
row.name,
row.age,
row.from_class.name
))
result.sort(key = lambda x: x[2])
print("")
print(result[0])
session.close()
#正反查询：
# ('David', 'Mary', 16, 'one year three class')
result = []
student_lst = session.query(
StudentsInfo
).all()
teacher_lst = session.query(
TeachersInfo
).all()
teacher_class_lst = session.query(
ClassesAndTeachersRelationship
).all()
class_id = []
for row in teacher_lst:
if row.name == 'David':
class_id = [x.fk_class_id for x in row.mid]
for row in student_lst:
if row.from_class.id in class_id:
result.append((
'David',
row.name,
row.age,
row.from_class.name
))
result.sort(key = lambda x: x[2])
print("")
print(result[0])
session.close()
2023-11-23 06:34:15,419 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:34:15,420 INFO sqlalchemy.engine.Engine SELECT "studentsInfo".id AS "studentsInfo_id", "studentsInfo".name AS "studentsInfo_name", "studentsInfo".gender AS "studentsInfo_gender", "studentsInfo".age AS "studentsInfo_age", "studentsInfo".fk_student_id AS "studentsInfo_fk_student_id", "studentsInfo".fk_class_id AS "studentsInfo_fk_class_id"
FROM "studentsInfo"
2023-11-23 06:34:15,421 INFO sqlalchemy.engine.Engine [cached since 324.7s ago] {}
2023-11-23 06:34:15,429 INFO sqlalchemy.engine.Engine SELECT "teachersInfo".id AS "teachersInfo_id", "teachersInfo".number AS "teachersInfo_number", "teachersInfo".name AS "teachersInfo_name", "teachersInfo".gender AS "teachersInfo_gender", "teachersInfo".age AS "teachersInfo_age"
FROM "teachersInfo"
2023-11-23 06:34:15,429 INFO sqlalchemy.engine.Engine [cached since 194.1s ago] {}
2023-11-23 06:34:15,438 INFO sqlalchemy.engine.Engine SELECT "classesAndTeachersRelationship".id AS "classesAndTeachersRelationship_id", "classesAndTeachersRelationship".fk_teacher_id AS "classesAndTeachersRelationship_fk_teacher_id", "classesAndTeachersRelationship".fk_class_id AS "classesAndTeachersRelationship_fk_class_id"
FROM "classesAndTeachersRelationship"
2023-11-23 06:34:15,439 INFO sqlalchemy.engine.Engine [cached since 194.1s ago] {}
2023-11-23 06:34:15,448 INFO sqlalchemy.engine.Engine SELECT "classesAndTeachersRelationship".id AS "classesAndTeachersRelationship_id", "classesAndTeachersRelationship".fk_teacher_id AS "classesAndTeachersRelationship_fk_teacher_id", "classesAndTeachersRelationship".fk_class_id AS "classesAndTeachersRelationship_fk_class_id"
FROM "classesAndTeachersRelationship"
WHERE %(param_1)s = "classesAndTeachersRelationship".fk_teacher_id
2023-11-23 06:34:15,449 INFO sqlalchemy.engine.Engine [cached since 194.1s ago] {'param_1': 1}
2023-11-23 06:34:15,451 INFO sqlalchemy.engine.Engine SELECT "classesInfo".id AS "classesInfo_id", "classesInfo".number AS "classesInfo_number", "classesInfo".name AS "classesInfo_name", "classesInfo".fk_teacher_id AS "classesInfo_fk_teacher_id"
FROM "classesInfo"
WHERE "classesInfo".id = %(pk_1)s
2023-11-23 06:34:15,451 INFO sqlalchemy.engine.Engine [cached since 324.7s ago] {'pk_1': 2}
2023-11-23 06:34:15,465 INFO sqlalchemy.engine.Engine SELECT "classesInfo".id AS "classesInfo_id", "classesInfo".number AS "classesInfo_number", "classesInfo".name AS "classesInfo_name", "classesInfo".fk_teacher_id AS "classesInfo_fk_teacher_id"
FROM "classesInfo"
WHERE "classesInfo".id = %(pk_1)s
2023-11-23 06:34:15,466 INFO sqlalchemy.engine.Engine [cached since 324.8s ago] {'pk_1': 1}
2023-11-23 06:34:15,468 INFO sqlalchemy.engine.Engine SELECT "classesInfo".id AS "classesInfo_id", "classesInfo".number AS "classesInfo_number", "classesInfo".name AS "classesInfo_name", "classesInfo".fk_teacher_id AS "classesInfo_fk_teacher_id"
FROM "classesInfo"
WHERE "classesInfo".id = %(pk_1)s
2023-11-23 06:34:15,468 INFO sqlalchemy.engine.Engine [cached since 324.8s ago] {'pk_1': 3}
('David', 'Mary', 16, 'one year three class')
2023-11-23 06:34:15,470 INFO sqlalchemy.engine.Engine ROLLBACK
4）查看每个班级的负责人是谁，以及任课老师都有谁：
练习3:¶
In [26]:
Copied!
# JOIN
# todo
# [('one year one class', 'David', 'Jason,David'), ('one year two class', 'Jason', 'David'), ('one year three class', 'Lisa', 'David,Lisa')]
from sqlalchemy import func
sub_query = session.query(
ClassesInfo.id,
ClassesInfo.name,
func.string_agg(TeachersInfo.name, ',').label("teaching_teachers")
).join(
ClassesAndTeachersRelationship,
ClassesInfo.id == ClassesAndTeachersRelationship.fk_class_id
).join(
TeachersInfo,
ClassesAndTeachersRelationship.fk_teacher_id == TeachersInfo.id
).group_by(
ClassesInfo.id
).subquery()
result = session.query(
ClassesInfo.name,
TeachersInfo.name,
sub_query.c.teaching_teachers
).join(
sub_query,
sub_query.c.id == ClassesInfo.id
).join(
TeachersInfo,
ClassesInfo.fk_teacher_id == TeachersInfo.id
).all()
print("")
print(result)
session.close()
# JOIN
# todo
# [('one year one class', 'David', 'Jason,David'), ('one year two class', 'Jason', 'David'), ('one year three class', 'Lisa', 'David,Lisa')]
from sqlalchemy import func
sub_query = session.query(
ClassesInfo.id,
ClassesInfo.name,
func.string_agg(TeachersInfo.name, ',').label("teaching_teachers")
).join(
ClassesAndTeachersRelationship,
ClassesInfo.id == ClassesAndTeachersRelationship.fk_class_id
).join(
TeachersInfo,
ClassesAndTeachersRelationship.fk_teacher_id == TeachersInfo.id
).group_by(
ClassesInfo.id
).subquery()
result = session.query(
ClassesInfo.name,
TeachersInfo.name,
sub_query.c.teaching_teachers
).join(
sub_query,
sub_query.c.id == ClassesInfo.id
).join(
TeachersInfo,
ClassesInfo.fk_teacher_id == TeachersInfo.id
).all()
print("")
print(result)
session.close()
2023-11-23 06:34:51,576 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:34:51,579 INFO sqlalchemy.engine.Engine SELECT "classesInfo".name AS "classesInfo_name", "teachersInfo".name AS "teachersInfo_name", anon_1.teaching_teachers AS anon_1_teaching_teachers
FROM "classesInfo" JOIN (SELECT "classesInfo".id AS id, "classesInfo".name AS name, string_agg("teachersInfo".name, %(string_agg_1)s) AS teaching_teachers
FROM "classesInfo" JOIN "classesAndTeachersRelationship" ON "classesInfo".id = "classesAndTeachersRelationship".fk_class_id JOIN "teachersInfo" ON "classesAndTeachersRelationship".fk_teacher_id = "teachersInfo".id GROUP BY "classesInfo".id) AS anon_1 ON anon_1.id = "classesInfo".id JOIN "teachersInfo" ON "classesInfo".fk_teacher_id = "teachersInfo".id
2023-11-23 06:34:51,579 INFO sqlalchemy.engine.Engine [generated in 0.00081s] {'string_agg_1': ','}
[('one year one class', 'David', 'David,Jason'), ('one year two class', 'Jason', 'David'), ('one year three class', 'Lisa', 'David,Lisa')]
2023-11-23 06:34:51,623 INFO sqlalchemy.engine.Engine ROLLBACK
练习4¶
In [27]:
Copied!
# 正反查询
# todo
# [('one year one class', 'David', 'Jason,David'), ('one year two class', 'Jason', 'David'), ('one year three class', 'Lisa', 'David,Lisa')]
result = []
class_lst = session.query(
ClassesInfo
).all()
teacher_lst = session.query(
TeachersInfo
).all()
for c in class_lst:
c_teacher_name = None
t_teachers_id = [x.fk_teacher_id for x in c.mid]
t_teachers = []
for t in teacher_lst:
if c.fk_teacher_id == t.id:
c_teacher_name = t.name
if t.id in t_teachers_id:
t_teachers.append(t.name)
result.append((c.name, c_teacher_name, ','.join(t_teachers)))
print("")
print(result)
session.close()
# 正反查询
# todo
# [('one year one class', 'David', 'Jason,David'), ('one year two class', 'Jason', 'David'), ('one year three class', 'Lisa', 'David,Lisa')]
result = []
class_lst = session.query(
ClassesInfo
).all()
teacher_lst = session.query(
TeachersInfo
).all()
for c in class_lst:
c_teacher_name = None
t_teachers_id = [x.fk_teacher_id for x in c.mid]
t_teachers = []
for t in teacher_lst:
if c.fk_teacher_id == t.id:
c_teacher_name = t.name
if t.id in t_teachers_id:
t_teachers.append(t.name)
result.append((c.name, c_teacher_name, ','.join(t_teachers)))
print("")
print(result)
session.close()
2023-11-23 06:35:43,994 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-23 06:35:43,995 INFO sqlalchemy.engine.Engine SELECT "classesInfo".id AS "classesInfo_id", "classesInfo".number AS "classesInfo_number", "classesInfo".name AS "classesInfo_name", "classesInfo".fk_teacher_id AS "classesInfo_fk_teacher_id"
FROM "classesInfo"
2023-11-23 06:35:43,995 INFO sqlalchemy.engine.Engine [cached since 409.9s ago] {}
2023-11-23 06:35:44,011 INFO sqlalchemy.engine.Engine SELECT "teachersInfo".id AS "teachersInfo_id", "teachersInfo".number AS "teachersInfo_number", "teachersInfo".name AS "teachersInfo_name", "teachersInfo".gender AS "teachersInfo_gender", "teachersInfo".age AS "teachersInfo_age"
FROM "teachersInfo"
2023-11-23 06:35:44,011 INFO sqlalchemy.engine.Engine [cached since 282.7s ago] {}
2023-11-23 06:35:44,021 INFO sqlalchemy.engine.Engine SELECT "classesAndTeachersRelationship".id AS "classesAndTeachersRelationship_id", "classesAndTeachersRelationship".fk_teacher_id AS "classesAndTeachersRelationship_fk_teacher_id", "classesAndTeachersRelationship".fk_class_id AS "classesAndTeachersRelationship_fk_class_id"
FROM "classesAndTeachersRelationship"
WHERE %(param_1)s = "classesAndTeachersRelationship".fk_class_id
2023-11-23 06:35:44,022 INFO sqlalchemy.engine.Engine [generated in 0.00059s] {'param_1': 1}
2023-11-23 06:35:44,032 INFO sqlalchemy.engine.Engine SELECT "classesAndTeachersRelationship".id AS "classesAndTeachersRelationship_id", "classesAndTeachersRelationship".fk_teacher_id AS "classesAndTeachersRelationship_fk_teacher_id", "classesAndTeachersRelationship".fk_class_id AS "classesAndTeachersRelationship_fk_class_id"
FROM "classesAndTeachersRelationship"
WHERE %(param_1)s = "classesAndTeachersRelationship".fk_class_id
2023-11-23 06:35:44,032 INFO sqlalchemy.engine.Engine [cached since 0.01071s ago] {'param_1': 2}
2023-11-23 06:35:44,034 INFO sqlalchemy.engine.Engine SELECT "classesAndTeachersRelationship".id AS "classesAndTeachersRelationship_id", "classesAndTeachersRelationship".fk_teacher_id AS "classesAndTeachersRelationship_fk_teacher_id", "classesAndTeachersRelationship".fk_class_id AS "classesAndTeachersRelationship_fk_class_id"
FROM "classesAndTeachersRelationship"
WHERE %(param_1)s = "classesAndTeachersRelationship".fk_class_id
2023-11-23 06:35:44,034 INFO sqlalchemy.engine.Engine [cached since 0.01282s ago] {'param_1': 3}
[('one year one class', 'David', 'David,Jason'), ('one year two class', 'Jason', 'David'), ('one year three class', 'Lisa', 'David,Lisa')]
2023-11-23 06:35:44,036 INFO sqlalchemy.engine.Engine ROLLBACK
原生SQL
查看执行命令
如果一条查询语句是filter()结尾，则该对象的__str__方法会返回格式化后的查询语句：
In [28]:
Copied!
print(
session.query(StudentsInfo).filter()
)
print(
session.query(StudentsInfo).filter()
)
SELECT "studentsInfo".id AS "studentsInfo_id", "studentsInfo".name AS "studentsInfo_name", "studentsInfo".gender AS "studentsInfo_gender", "studentsInfo".age AS "studentsInfo_age", "studentsInfo".fk_student_id AS "studentsInfo_fk_student_id", "studentsInfo".fk_class_id AS "studentsInfo_fk_class_id" FROM "studentsInfo"
In [ ]:
Copied!