python - sqlalchemy 使用中间表加入自我(工作 sql,而不是 sqlalchemy)
问题描述
我正在尝试使用中间表执行多对多连接。(首先在 Items 中选择行,在 FK 上加入 Attribution,然后在 Attribution 中加入其他 FK 以从 Items 中获取更多信息)Schema 如下所示:
class Items(Base):
__tablename__ = "items"
id = Column(Integer, primary_key=True)
name = Column(Text)
project = Column(Integer)
linkid = Column(Integer, ForeignKey("items.linkid"))
linked = relationship("Items", foreign_keys="[Items.linkid]")
class Attribution(Base):
__tablename__ = "attribution"
id = Column(Integer, primary_key=True)
link_id_d = Column(Integer, ForeignKey('items.linkid'))
link_id_m = Column(Integer, ForeignKey('items.linkid'))
查询如下所示:
final_items = aliased(Items)
proj_1 = session.query(Items)\
.join(Attribution, ( (Items.name=="upper_third") & (Attribution.link_id_m==Items.linkid) ))\
.join(final_items, final_items.linkid==Attribution.link_id_d)\
.all()
这仅提供一排;输出:
upper_third 1
我通过 sqlite 使用的 sql 提供了预期的三行,如下所示:
SELECT * FROM items
join attribution on items.name = 'upper_third' and items.linkid == attribution.link_id_m
join items as tbl1 on tbl1.linkid == attribution.link_id_d
输出:
5|upper_third|1|1|1|2|1|2|lower_first|2|2
5|upper_third|1|1|1|2|1|4|lower_second|2|2
5|upper_third|1|1|1|2|1|6|lower_third|2|2
这两个查询之间的逻辑区别是什么,如何将 sql 解决方案迁移到 sqalchemy?
(生成sqlite数据库和测试查询的完整可运行代码如下)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, UniqueConstraint, ForeignKey, func, and_
from sqlalchemy.orm import sessionmaker, relationship, aliased, backref
Base = declarative_base()
from sqlalchemy import Column, Integer, String, Float, Boolean, Text, Table
class Items(Base):
__tablename__ = "items"
id = Column(Integer, primary_key=True)
name = Column(Text)
project = Column(Integer)
linkid = Column(Integer, ForeignKey("items.linkid"))
linked = relationship("Items", foreign_keys="[Items.linkid]")
attributions_m = relationship("Attribution", foreign_keys="[Attribution.link_id_m]")
attributions_d = relationship("Attribution", foreign_keys="[Attribution.link_id_d]")
class Attribution(Base):
__tablename__ = "attribution"
id = Column(Integer, primary_key=True)
link_id_d = Column(Integer, ForeignKey('items.linkid')) # one of the tests to link to
link_id_m = Column(Integer, ForeignKey('items.linkid')) # one of the tests to link to
import os
if os.path.exists('app.db'):
os.remove('app.db')
engine = create_engine('sqlite+pysqlite:///app.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
u1 = Items(name="upper_first", linkid=1, project=1)
l1 = Items(name="lower_first", linkid=2, project=2)
u2 = Items(name="upper_second", linkid=1, project=1)
l2 = Items(name="lower_second", linkid=2, project=2)
u3 = Items(name="upper_third", linkid=1, project=1)
l3 = Items(name="lower_third", linkid=2, project=2)
session.add(u1)
session.add(l1)
session.add(u2)
session.add(l2)
session.add(u3)
session.add(l3)
session.commit()
a1 = Attribution(link_id_m=u3.linkid, link_id_d=l3.linkid)
session.add(a1)
session.commit()
final_items = aliased(Items)
proj = session.query(Items)\
.join(Attribution, ( (Items.name=="upper_third") & (Attribution.link_id_m==Items.linkid) ))\
.join(final_items, final_items.linkid==Attribution.link_id_d)\
.all()
for l in proj:
print(l.name, l.linkid)
解决方案
简短的回答:SQLAlchemy 按预期工作。
关键的罪魁祸首是当您查询映射模型时,您将获得模型的实例作为结果。如果多次返回相同的模型,SA 将确保每个只返回一次,这就是为什么您只返回 1 行而不是预期的 3 行。
您可以通过您构造的 SQL ( select * ...
) 与 SA 为您创建的 SQL ( select items.*
; SA 不创建SQL 的区别来看到这*
一点,但关键是它仅从items
表中选择。
解决方案是将其他实体添加query(...)
到要返回的对象中:
final_items = aliased(Items, name="FinalItems")
proj = (
session
.query(Items, Attribution, final_items) # IMPORTANT !!!
.join(Attribution, ( (Items.name=="upper_third") & (Attribution.link_id_m==Items.linkid) ))
.join(final_items, final_items.linkid==Attribution.link_id_d)
)
运行如下:
for l in proj.all():
print(l)
# print(l.Items, l.Attribution, l.FinalItems) # also can access models using names.
...将产生一个列表tuple(Items, Attribution, Items)
:
(<Items(id=5, linkid=1, name='upper_third', project=1)>, <Attribution(id=1, link_id_d=2, link_id_m=1)>, <Items(id=2, linkid=2, name='lower_first', project=2)>)
(<Items(id=5, linkid=1, name='upper_third', project=1)>, <Attribution(id=1, link_id_d=2, link_id_m=1)>, <Items(id=4, linkid=2, name='lower_second', project=2)>)
(<Items(id=5, linkid=1, name='upper_third', project=1)>, <Attribution(id=1, link_id_d=2, link_id_m=1)>, <Items(id=6, linkid=2, name='lower_third', project=2)>)
如果您真的想像示例中那样返回列,则可以执行statement
查询。下面的代码
for row in session.execute(proj.statement):
print(row)
将返回:
(5, 'upper_third', 1, 1, 1, 2, 1, 2, 'lower_first', 2, 2)
(5, 'upper_third', 1, 1, 1, 2, 1, 4, 'lower_second', 2, 2)
(5, 'upper_third', 1, 1, 1, 2, 1, 6, 'lower_third', 2, 2)
推荐阅读
- gsm - 如何终止拨出 CSD 通话?
- node.js - 如何连接到 mongodb atlas 集群以使用节点 js 创建新数据库
- node.js - jsonwebtoken 在 nodejs 版本 10x 中返回 null
- python-3.x - 如何让 jupyter 脚本在 ubuntu 液滴上 24/7 全天候运行
- c# - 以静默模式运行可执行文件
- python - 如何从包含多个人名的电子邮件中提取发件人姓名?
- python - 在 python 中执行 OneHotEncoder 后保留列名的最佳方法是什么?
- python - 从列表 Python 中的字符串创建字符串列表
- mysql - MySQL 更新查询(存储过程)的“字段列表”错误中的未知列
- java - 如何在 Java 泛型方法中使用 int 代替 T?