[SQLAlchemy] sqlAlchemy学习笔记(3): 在orm中使用join

JOIN连接表

➡️官方文档⬅️
在sqlalchemy中实现JOIN/ON语句, 需要用到Select.join()或者Select.join_from()

假设我们有两张表 (在python中就变成了两个类) User和Address, User表中有一列addresses, 表示该用户使用的所有地址的集合 (反映到代码中, 就是Address对象的集合); 同时Address表中也有一列user_id, 对应User表中的id字段

使用relationship()来进行关联
有关relationship()的内容看➡️这里
本文中还是使用user_account和address这两个表, 以及User和Address这两个类, User.id和Address.user_id为主/外键关联, User.addresses和Address.user之间使用relationship()关联

JOIN的基本使用方法

先来看看在sql中我们如何写join语句

-- 假设: 返回用户和他们的所有地址
SELECT * FROM user_account u
JOIN address a ON u.id = a.user_id;

注意JOIN...ON...是必备的组合, 但是在sqlalchemy中, ON语句可以被自动推断出来, 前提是对应类中有配置相应的主/外键属性

# 在user/address案例中, id/user_id为主/外键
print(select(User).join(Address))

对应的sql语句为

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account 
JOIN address ON user_account.id = address.user_id;

这里没有指定ON语句后的条件, 但join方法会自动根据外键来生成

指定条件

⬇️这是在join方法里指定了ON条件, 注意我们使用的是表达式

print(select(User).join(Address, User.id == Address.user_id))

生成的sql语句是一样的, 但是这里指定了ON条件

增加条件

⬇️我们也可以通过PropComponent._and()来增加条件名字好奇怪
注意写法User.addresses.and_(...), and_()是属于主表(左表)里由relationship()绑定的那个

print(select(User.fullname).join(
    User.addresses.and_(Address.email_address == "squirrel@squirrelpower.org")
))

sql

SELECT user_account.fullname
FROM user_account
JOIN address ON user_account.id = address.user_id AND address.email_address = 'squirrel@squirrelpower.org';

and_()方法和relationship()在某种程度上挂钩

使用别名

在这里我们以可以用别名区分表(类)

# 取个别名
a1 = aliased(Address)
a2 = aliased(Address)
# 查询语句
stmt = (
    select(User)
    .join(a1, User.addresses)
    .where(a1.email_address == "patrick@aol.com")
    .join(a2, User.addresses)
    .where(a2.email_address == "patrick@gmail.com")
)

sql

1. 重复使用where的时候会自动由AND相连

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS a1 ON user_account.id = a1.user_id
JOIN address AS a2 ON user_account.id = a2.user_id
WHERE a1.email_address = :email_address_1 AND a2.email_address = :email_address_2;

使用子查询

Select.subquery()

subq = select(Address).where(Address.email_address == "pat999@aol.com").subquery()
address_subq = aliased(Address, subq, name="address")
stmt = select(User, address_subq).join(address_subq)
for row in session.execute(stmt):
    print(f"{row.User} {row.address}")

上面这段代码对应的sql是这样的

-- 返回邮箱为'pat999@aol.com'的用户信息和特定邮箱地址
SELECT user_account.id, user_account.name, user_account.fullname, a1.id AS id_1, a1.user_id, a1.email_address
FROM user_account
JOIN 
   (
   -- 返回邮箱地址为'pat999@aol.com'的行
    SELECT address.id AS id,
           address.user_id AS user_id,
           address.email_address AS email_address
    FROM address
    WHERE address.email_address = 'pat999@aol.com'
    ) AS a1 
ON user_account.id = a1.user_id;

User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')

或者用relationship()改进一下

subq = select(Address).where(Address.email_address == "pat999@aol.com").subquery()
address_subq = aliased(Address, subq, name="address")
stmt = select(User, address_subq).join(User.addresses.of_type(address_subq))
for row in session.execute(stmt):
    print(f"{row.User} {row.address}")

对应的sql是一样的

-- 返回所有用户及其地址, 每个地址都是一个单独行
SELECT user_account.id, user_account.name, user_account.fullname, a2.id AS id_1, a2.user_id, a2.email_address
FROM user_account
JOIN 
  (SELECT address.id AS id,
          address.user_id AS user_id, 
          address.email_address AS email_address
   FROM address
   WHERE address.email_address = 'pat999@aol.com'
   ) AS a2 
ON user_account.id = a2.user_id;

甚至可以在一个子查询里引用两个类

子查询

user_address_subq = (
    select(User.id, User.name, User.fullname, Address.id, Address.email_address)
    .join_from(User, Address)
    .where(Address.email_address.in_(["pat999@aol.com", "squirrel@squirrelpower.org"]))
    .subquery()
)

别名, 这样一个子查询就分别应用在两个类(实例)上了

user_alias = aliased(User, user_address_subq, name="user")
address_alias = aliased(Address, user_address_subq, name="address")

执行时, 两个实例的子查询会同时执行, 但是结果会被同时写入结果中

stmt = select(user_alias, address_alias).where(user_alias.name == "sandy")
for row in session.execute(stmt):
    print(f"{row.user} {row.address}")

sql

SELECT anon_1.id, anon_1.name, anon_1.fullname, anon_1.id_1, anon_1.email_address
FROM 
    (
       SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname, address.id AS id_1,
address.email_address AS email_address
       FROM user_account 
       JOIN address ON user_account.id = address.user_id 
       WHERE address.email_address IN 'pat999@aol.com', 'squirrel@squirrelpower.org')
    ) AS anon_1
WHERE anon_1.name = 'sandy';