[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
- 重复使用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;
使用子查询
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';