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

SELECT的作用

select在sql中的作用是选中特定列并以表的形式返回, 是必要的关键字; 在sqlalchemy中, select()方法会返回一个Select对象, 并根据这个对象引入其他方法, 如where(), join(), order_by()等等

from sqlalchemy import select
stmt = select(User).where(User.name == "spongebob")

即

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = "spongebob";

注意select()里的参数是orm中映射数据表的类 -> i.e.User类对应user_account表

使用scalars()格式化返回结果

首先来看一看这条

result = session.execute(select(User).order_by(User.id))

如果我们执行上面的语句, execute方法实际上会返回一个含有Row对象的数组(Result对象), 每个Row对象里只有一个元素, 那就是一个User对象
result.all()
返回结果为

[(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),),
(User(id=2, name='sandy', fullname='Sandy Cheeks'),),
(User(id=3, name='patrick', fullname='Patrick Star'),),
(User(id=4, name='squidward', fullname='Squidward Tentacles'),),
(User(id=5, name='ehkrabs', fullname='Eugene H. Krabs'),)]

这时候我们需要scalars方法去掉Row, 这有两种方式, 但她们都使用了同一种方法(名)

• Session.scalars()

session.scalars(select(User).order_by(User.id)).all()

• Result.scalars()
  记住Session.execute返回了一个Result对象, 而它的主体类里则定义了一个scalars方法

result = session.execute(select(User).order_by(User.id))
result.scalars().all()

选中复数orm对象

stmt = select(User, Address).join(User.addresses).order_by(User.id, Address.id)
for row in session.execute(stmt):
    print(f"{row.User.name} {row.Address.email_address}")

SELECT user_account.id, user_account.name, user_account.fullname, address.id AS id_1, address.user_id, address.email_address
FROM user_account
JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id;

选中特定列

stmt = select(User.name, Address.email_address).join(User.addresses).order_by(User.id, Address.id)
for row in session.execute(stmt):
	print(f"{row.name}  {row.email_address}")

SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id;

使用Bundle进行分组

当我们试图选中列时, 需要使用类名来进行区分, 这是必须的->但是如果被选中的列很多, 像上一种写法必然是繁琐的, 我们可以使用Bundle类来区分它们, 注意使用Bundle类的结果和单纯使用select是一样的, 只是增加了代码的可读性

from sqlalchemy.orm import Bundle
# 使用Bundle将来自不同类的属性区分开
stmt = select(
    Bundle("user", User.name, User.fullname),
    Bundle("email", Address.email_address),
).join_from(User, Address)
# 注意这里引用的路径也不一样了
for row in session.execute(stmt):
    print(f"{row.user.name} {row.user.fullname} {row.email.email_address}")

使用自定义别名

在写sql语句的时候, 经常需要使用别名来简写表名或是区别相同表, 在sqlalchemy中也有同样的用法

from sqlalchemy.orm import aliased
u1 = aliased(User, name="u1")
stmt = select(u1).order_by(u1.id)
row = session.execute(stmt).first()
print(f"{row.u1.name}")

子查询

有时候sql语句可以很复杂, 经常在查询里加查询,在sqlalchemy中我们需要用到subquery方法

# 内查询语句
inner_stmt = select(User).where(User.id < 7).order_by(User.id)
# 拿subquery封装一下
subq = inner_stmt.subquery()
# 给结果加个别名, 绑定一下对应类(表)
aliased_user = aliased(User, subq)
# 外部的sql查询
stmt = select(aliased_user)
# 执行, 打印结果
for user_obj in session.execute(stmt).scalars():
    print(user_obj)

sql查询语句长这样

SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM 
	(SELECT user_account.id AS id, 
		user_account.name AS name, 
		user_account.fullname AS fullname
	 FROM user_account
	 WHERE user_account.id < ? 
	 ORDER BY user_account.id) 
AS anon_1;

注意因为没有指定别名, 因此别名anon_1是系统自动给的

实现UNION

UNION也是sql中一个常见的关键字(虽然我不喜欢用)

from sqlalchemy import union_all
# sql查询命令的文本, 其实是一个内查询的语句 --> 集合两张结果表, 并将结果以id排序
u = union_all(
    select(User).where(User.id < 2), select(User).where(User.id == 3)
).order_by(User.id)
# 将sql文本(内查询语句)加进最终指令stmt中
stmt = select(User).from_statement(u)
# 执行并打印
for user_obj in session.execute(stmt).scalars():
    print(user_obj)

注意这个方法没有用到任何子查询⬇️直来直去的

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id < ? 
UNION ALL 
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id = ? 
ORDER BY id;

另一种方法, 使用subquery

subq = union_all(
    select(User).where(User.id < 2), select(User).where(User.id == 3)
).subquery()
user_alias = aliased(User, subq)
stmt = select(user_alias).order_by(user_alias.id)
for user_obj in session.execute(stmt).scalars():
    print(user_obj)

对应的sql是这样⬇️很难懂但很有观赏性

SELECT anon_1.id, anon_1.name, anon_1.fullname FROM 
	(SELECT 
		user_account.id AS id,
		user_account.name AS name,
		user_account.fullname AS fullname
	 FROM user_account
	 WHERE user_account.id < ? 
	 UNION ALL 
	 SELECT 
	 	user_account.id AS id, 
		user_account.name AS name, 
		user_account.fullname AS fullname
	 FROM user_account
	 WHERE user_account.id = ?) AS anon_1 
ORDER BY anon_1.id;