SQLAlchemy多表关联查询报错ObjectNotExecuteableError求助

Let's break down what's causing the error and adjust your code to match the working raw SQL you have.

Key Issues in Your Current Code #

• Unnecessary table() wrapping: You’re passing table(t1) to select_from(), but t1 should already be a defined Table instance. Using table() on an existing Table object creates an invalid, non-executable object—this is the main culprit behind ObjectNotExecuteableError.
• Incorrect comparison operator: SQLAlchemy uses == (not =) to represent SQL equality checks. Using = here is Python assignment, not a valid query condition.
• Missing outerjoin for LEFT JOIN: The default join() in SQLAlchemy is an INNER JOIN. To replicate your LEFT JOIN, you need to use outerjoin(), and include the additional t3.col2 = 12345 condition directly in the join clause.

Corrected SQLAlchemy Code #

First, confirm your tables are defined properly (here's a quick example if you haven't already):

from sqlalchemy import Table, MetaData, Column, Integer, String
metadata = MetaData()

t1 = Table(
    "t1", metadata,
    Column("id", Integer, primary_key=True),
    Column("col1", String),
    Column("col2", String)
)

t2 = Table(
    "t2", metadata,
    Column("id", Integer, primary_key=True),
    Column("col1", String),
    Column("col2", String),
    Column("col3", String)
)

t3 = Table(
    "t3", metadata,
    Column("id", Integer),
    Column("col1", String),
    Column("col2", Integer)
)

Now the fixed query:

from sqlalchemy import select, and_

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

What Changed? #

1. Removed table() wrappers: We pass the existing t1 Table instance directly to select_from(), which keeps the query object valid and executable.
2. Swapped = for ==: All join conditions use SQLAlchemy’s equality operator to build valid SQL logic.
3. Used outerjoin() with combined conditions: The and_() function combines your two LEFT JOIN conditions (t1.id = t3.id and t3.col2 = 12345) exactly as they appear in your raw SQL.
4. Kept distinct() in place: The .distinct() chain call correctly replicates SELECT DISTINCT from your original query.

You can verify the generated SQL matches your raw query by printing the statement:

print(stmt)

This should output SQL identical to your working native script, and the ObjectNotExecuteableError will be resolved.

内容的提问来源于stack exchange，提问作者brillenheini