连接表 A 和表 B 中的两列

发布于 2025-01-07 20:22:05 字数 1253 浏览 0 评论 0原文

我找到了回答我的问题，但格式错误。

使用 SQL Alchemy，我想将表 A 中的列连接到表 B 上的一列。

表 A 包含两列位置代码。我可以通过加入表 B 来检索位置名称，但如何执行此操作？

到目前为止，我有这样的：

locationreq = sa.Table("INMPTL_LOCATION_REQUEST", meta.metadata,
    sa.Column("request_id", sa.types.String(), primary_key=True),
    sa.Column("status", sa.types.String(100)),
    sa.Column("new_loc", sa.types.String(), sa.ForeignKey("INMPTL_LOCATIONS_TBL.inmptl_location_code")),
    sa.Column("previous_loc", sa.types.String(), sa.ForeignKey("INMPTL_LOCATIONS_TBL.inmptl_location_code")),
    autoload=True,
    autoload_with=engine)

locationtable = sa.Table("INMPTL_LOCATIONS_TBL", meta.metadata,
    sa.Column("INMPTL_LOCATION_CODE", sa.types.Integer(), primary_key=True),
    autoload=True,
    autoload_with=engine)

orm.mapper(Location, locationtable )
orm.mapper(LocationRequest, locationreq, extension= wf.WorkflowExtension(), properties = {'location':relation(Location)}

如果这些列中只有一列映射到第二个表，我可以调用诸如以下内容：

model.LocationRequest.location.location_name

但是因为我将两列映射到同一个表，所以它变得混乱。

有谁知道实现这一目标的正确方法？

原文

I have found the answer to my question, but in the wrong format.

Using SQL Alchemy I want to join columns from Table A to one column on Table B.

Table A contains two columns for Location Code. I can retrieve the Location Name by joining on to Table B, but how to do this?

So far I have this:

locationreq = sa.Table("INMPTL_LOCATION_REQUEST", meta.metadata,
    sa.Column("request_id", sa.types.String(), primary_key=True),
    sa.Column("status", sa.types.String(100)),
    sa.Column("new_loc", sa.types.String(), sa.ForeignKey("INMPTL_LOCATIONS_TBL.inmptl_location_code")),
    sa.Column("previous_loc", sa.types.String(), sa.ForeignKey("INMPTL_LOCATIONS_TBL.inmptl_location_code")),
    autoload=True,
    autoload_with=engine)

locationtable = sa.Table("INMPTL_LOCATIONS_TBL", meta.metadata,
    sa.Column("INMPTL_LOCATION_CODE", sa.types.Integer(), primary_key=True),
    autoload=True,
    autoload_with=engine)

orm.mapper(Location, locationtable )
orm.mapper(LocationRequest, locationreq, extension= wf.WorkflowExtension(), properties = {'location':relation(Location)}

If only one of these columns were mapped to the second table, I could call something such as:

model.LocationRequest.location.location_name

But because I am mapping two columns to the same table, it is getting confused.

Does anyone know the proper way to achieve this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苏璃陌 2025-01-14 20:22:05

我本来打算删除这个问题，但这不是重复的。答案是这里（设置主要和次要连接）

orm.mapper(LocationRequest, locationreq, extension= wf.WorkflowExtension(),
    properties={
        "new_location":relation(Location,
        primaryjoin=locationtable.c.inmptl_location_code==locationreq.c.new_loc, lazy = False),
        "previous_location":relation(Location,
        primaryjoin=locationtable.c.inmptl_location_code==locationreq.c.previous_loc, lazy = False)
        })

I was going to delete this question, but this is not a duplicate. The answer is here (setting the primary and secondary joins)

orm.mapper(LocationRequest, locationreq, extension= wf.WorkflowExtension(),
    properties={
        "new_location":relation(Location,
        primaryjoin=locationtable.c.inmptl_location_code==locationreq.c.new_loc, lazy = False),
        "previous_location":relation(Location,
        primaryjoin=locationtable.c.inmptl_location_code==locationreq.c.previous_loc, lazy = False)
        })

回复收藏 0 原文

~没有更多了~