python - SQLAlchemy - 将 where 子句添加到选择生成子查询

Question

docs for Select.where 表示此方法应该将 WHERE 子句附加到现有的 SELECT 查询，但有时它似乎将 select 包装为子查询，从而导致ProgrammingError: (psycopg2.ProgrammingError) FROM 中的子查询必须有别名 错误。这似乎只发生在我试图过滤的选择在查询链的早期有一个连接时

# this works
my_select = select([tbl.c.id, tbl.c.my_col])
filtered = my_select.where(tbl.c.my_col == 'foo')

# this doesn't work, and seems to wrap the my_select in an additional subquery
j = tbl1.join(tbl2, tbl1.c.id == tbl2.c.id)
my_select = select([tbl1.c.id, tbl2.c.my_col]).select_from(j)
filtered = my_select.where(my_select.c.my_col == 'foo')

# Text representations will usually work as expected
j = tbl1.join(tbl2, tbl1.c.id == tbl2.c.id)
my_select = select([tbl1.c.id, tbl2.c.my_col]).select_from(j)
filtered = my_select.where("my_col = 'foo'")

Answer 1

我想我刚刚弄明白了。问题似乎是自引用列，所以

filtered = my_select.where(my_select.c.my_col == 'foo')

应该是

filtered = my_select.where(tbl2.c.my_col == 'foo')

以便它引用原始列对象而不是选择中的列。

这只是一个假设，如果其他人也可以验证，我们将不胜感激。