python 如何在sqlalchemy中加入同一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2416454/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to join the same table in sqlalchemy
提问by Gary van der Merwe
I'm trying to join the same table in sqlalchemy. This is a minimial version of what I tried:
我正在尝试在 sqlalchemy 中加入同一个表。这是我尝试过的最小版本：
#!/usr/bin/env python
import sqlalchemy as sa
from sqlalchemy import create_engine
from sqlalchemy.orm import mapper, sessionmaker, aliased
engine = create_engine('sqlite:///:memory:', echo=True)
metadata = sa.MetaData()
device_table = sa.Table("device", metadata,
sa.Column("device_id", sa.Integer, primary_key=True),
sa.Column("name", sa.String(255), nullable=False),
sa.Column("parent_device_id", sa.Integer, sa.ForeignKey('device.device_id')),
)
class Device(object):
device_id = None
def __init__(self, name, parent_device_id=None):
self.name = name
self.parent_device_id = parent_device_id
def __repr__(self):
return "<Device(%s, '%s', %s)>" % (self.device_id,
self.name,
self.parent_device_id )
mapper(Device, device_table)
metadata.create_all(engine)
db_session = sessionmaker(bind=engine)()
parent = Device('parent')
db_session.add(parent)
db_session.commit()
child = Device('child', parent.device_id)
db_session.add(child)
db_session.commit()
ParentDevice = aliased(Device, name='parent_device')
q = db_session.query(Device, ParentDevice)\
.outerjoin(ParentDevice,
Device.parent_device_id==ParentDevice.device_id)
print list(q)
This gives me this error:
这给了我这个错误：
ArgumentError:Can't determine join between 'device' and 'parent_device'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.
ArgumentError：无法确定“设备”和“父设备”之间的连接；表之间有多个外键约束关系。请明确指定此连接的“onclause”。
But I am specifying a onclausefor the join. How should I be doing this?
但我onclause为连接指定了一个。我该怎么做？
采纳答案by Gary van der Merwe
For query.[outer]join, you specify as list of joins (which is different to expression.[outer]join.) So I needed to put the 2 elements of the join, the table and the onclausein a tuple, like this:
对于query.[outer]join，您指定为连接列表（与 不同expression.[outer]join。）所以我需要将连接的 2 个元素、表和onclause放在一个元组中，如下所示：
q = db_session.query(Device, ParentDevice)\
.outerjoin(
(ParentDevice, Device.parent_device_id==ParentDevice.device_id)
)
回答by Wolph
Your mapper should specificy the connection between the two items, here's an example: adjacency list relationships.
您的映射器应该具体说明这两个项目之间的连接，这是一个示例：邻接列表关系。
