python - SQLAlchemy: ObjectNotExecuteableError on joins

Question

I'm currently struggeling with a statement in SQLAlchemy. Basically the SQL script itself is working and looks like'

SELECT DISTINCT t1.col1, t1.col2, t2.col1, t2.col2, t2.col3, t3.col1
FROM t1
JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 on t1.id = t3.id and t3.col2 = 12345

Now, splitting this statement into SQLAlchemy I've ended up with something like this:

stmt = select([t1.c.col1, t1.c.col2, t2.c.col1, t2.c.col2, t2.c.col3, t3.c.col1]).distinct().\
     select_from(table(t1)).\
     join(table(t2), t1.c.id == t2.c.id).\
     outerjoin(table(t3), and_((t1.c.id == t3.c.id), (t3.c.col2 == 12345))

I guess it's because of the distinct. I've also placed it at the end of the full line after outerjoin, but there I get an attribute error, saying that join has no attribute distinct.

Any hints on this for my?

Many thanks in advance, regards, Thomas

标签： pythonsqlpython-3.xselectsqlalchemy

Answer 1

我自己都不敢相信，但我找到了问题的答案。这或多或少是如何将连接连接在一起的方式。

我上面的查询的解决方案是：

stmt = select([t1.c.col1, t1.c.col2, t2.c.col1, t2.c.col2, t2.c.col3, t3.c.col1]).\
     select_from(
         table(t1).\
         join(table(t2), t1.c.id == t2.c.id).\
         outerjoin(table(t3), and_((t1.c.id == t3.c.id), (t3.c.col2 == 12345)))
     ).distinct()