async sqlalchemy 异步查询

 

 实体和属性

返回多条数据 fetchall()

q = select(models.User)
result = await session.execute(q)
for i in result:
    print(i)

fetchone() 返回一条数据

q = select(models.User)
result = await session.execute(q)
print(result.fetchone())

>>>(<model.models.User object at 0x0000021E63C284C0>,)

scalars() 返回实体

q = select(models.User)
result = await session.execute(q)
print(result.scalars().all())
>>>[<model.models.User object at 0x00000257683A64F0>, <model.models.User object at 0x00000257683A69A0>]

同时返回2个人实体 select(models.User, models.Order).join(models.User.orders）

q = select(models.User, models.Order).join(models.User.orders).order_by(models.User.id, models.Order.id)
result = await session.execute(q)
print(result.fetchall())
>>>[(<model.models.User object at 0x00000227D150E160>, <model.models.Order object at 0x00000227D150E1C0>), ]

同时返回2个实体的具体数据 select(models.User.name, models.Order.id).join(models.User.orders)

q = select(models.User.name, models.Order.id).join(models.User.orders).order_by(models.User.id, models.Order.id)
result = await session.execute(q)
for i in result:
    print(i)
>>>
('Updated User', 1)
('Updated User', 2)
('cuicui', 3)

将一组列表达式分组到结果行中 Bundle()

q = select(
     Bundle("user", models.User.name, models.User.mobile),
    Bundle("email", models.Order.user_id)
).join_from(models.User, models.Order).order_by(models.User.id, models.Order.id)
result = await session.execute(q)
for i in result:
    print(i)
>>>
(('Updated User', '777-666666-777'), (2,))
(('cuicui', '111111122'), (4,))

使用别名 aliased(models.User, name="a")

a = aliased(models.User, name="a")
q = select(a).order_by(a.id)
result = await session.execute(q)
for i in result:
    print(i)

执行sql语句 text()

q = text("SELECT id, name, mobile FROM third_api_user ORDER BY id")
result = await session.execute(q)
for i in result:
    print(i)
>>>
(2, 'Updated User', '777-666666-777')
(4, 'cuicui', '111111122')
(70, 'ytf11112', '111111122')

sql语句转成 实体对象 from_statement()

# sql语句
q = text("SELECT id, name, mobile FROM third_api_user ORDER BY id")
# sql生成字段
textual_sql = q.columns(models.User.id, models.User.name, models.User.mobile)
# 转成实体对象
orm_sql = select(models.User).from_statement(textual_sql)
result = await session.execute(orm_sql)
for i in result.scalars():
    print(i.__dict__)
>>>
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000176E1EC40A0>, 'id': 2, 'mobile': '777-666666-777', 'name': 'Updated User'}
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000176E1EC4100>, 'id': 3, 'mobile': '111111122', 'name': 'cuicui'}
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000176E1EC4280>, 'id': 72, 'mobile': '11', 'name': 's'}

 

连表

根据User.addresses

>>> stmt = select(User).join(User.addresses)
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN address ON user_account.id = address.user_id

 链接多个连接：

构造一个连接链，多个 Select.join() 可以使用呼叫。relationship-bound属性同时表示连接的左侧和右侧。考虑其他实体 Order 和 Item 那里 User.orders 关系是指 Order 实体，以及 Order.items 关系是指 Item 实体，通过关联表 order_items . 二 Select.join() 将加入来自第一个调用的结果 User 到 Order ，还有一秒钟 Order 到 Item . 但是，自从 Order.items 是一个 many to many 关系中，它将生成两个单独的JOIN元素，结果SQL中总共有三个JOIN元素：

>>> stmt = (
...     select(User).
...     join(User.orders).
...     join(Order.items)
... )
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id

每次调用 Select.join() 方法的重要性仅限于我们希望从中连接的内容的“左”端需要在我们指示新目标之前出现在from列表中。 Select.join() 例如，如果我们指定 select(User).join(Order.items).join(User.orders) ，并将引发错误。在正确的实践中 Select.join() 方法的调用方式与我们希望如何呈现SQL中的JOIN子句一致，并且每个调用都应该表示与它前面的内容之间的清晰链接。

我们在FROM子句中目标的所有元素都可以作为继续从连接的潜在点。我们可以继续添加其他元素从 User 上面的实体，例如在 User.addresses 与我们的连接链的关系：

>>> stmt = (
...     select(User).
...     join(User.orders).
...     join(Order.items).
...     join(User.addresses)
... )
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id
JOIN address ON user_account.id = address.user_id