如何使用SQLalchemy Core动态连接多个表？

Question

父表中有几个主键，其中一个子表是我拥有的。子表的数目在运行时可以是任意的。使用SQLalchemy core，如何将多个子表联接到此父表？在

假设我有班级的桌子sqlalchemy.schema.Table具有有效的FK约束；如何构造此查询？在

我试过了，也就是说

childJoins= [sa.join(parentTable,childTables[0]),sa.join(parentTable,childTables[1])]
# childTables is a list() of Table objects who are guaranteed linked by pk 

qry = sa.select(["*"],from_obj=childJoins)

它给予

^{pr2}$

所以parentTable被列了两次。。。在

使用join（）等尝试了更多的变体。查看了文档，但我仍然无法得到我想要的

SELECT *
FROM parentTable
JOIN child1 ON parentTable.C1_Id=child1.P_Id
JOIN child2 ON parentTable.C2_Id=child2.P_Id 
...
JOIN childN ON parentTable.CN_Id=childN.P_Id

Answer 1

只需将连接链连接起来：

childJoins = parentTable
for child in childTables:
    childJoins = childJoins.join(child)

query = sa.select(['*'], from_obj=childJoins)

Answer 2

我的多表连接解决方案，灵感来自Audrius Kažukauskas上面的解决方案，使用SQLAlchemy核心：

from sqlalchemy.sql.expression import Select, ColumnClause

select = Select(for_update=for_update)
if self.columns:              # a list of ColumnClause objects
    for c in self.columns:
       select.append_column(c)

# table:  sqlalchemy Table type, the primary table to join to
for (join_type,left,right,left_col,right_col) in self.joins:
    isouter = join_type in ('left', 'left_outer', 'outer')
    onclause = (left.left_column == right.right_column)
    # chain the join tables
    table = table.join(right, onclause=onclause, isouter=isouter)

# if no joins, 'select .. from table where ..'
# if has joins, 'select .. from table join .. on .. join .. on .. where ..
select.append_from(table)

if self.where_clauses:
    select.append_whereclause(and_(*self.where_clauses))
...