CODEWITHSUNDEEP
pythonsqlalchemy
Jossy
Jossy

Reputation: 999

Why is my generated WHERE clause not correct?

I've written the following query in SQLAlchemy:

session.query(matches_atp).filter(matches_atp.ID_M == 668)

And I'm getting a weird WHERE clause when I check it:

WHERE belgarath.matches_atp.`ID_M` = %(ID_M_1)s

When I switch to:

session.query(matches_atp).filter(matches_atp.ID_M == None)

Then everything looks fine:

WHERE belgarath.matches_atp.`ID_M` IS NULL

Why am I getting the %(ID_M_1)s instead of 668?

If this isn't an easy answer then I'll post the code for an MRE.

Upvotes: 2

Views: 46

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

That's a placeholder. Your DB driver will then replace it with the proper value when executing. In other words it is as expected. You should use placeholders in your manual "raw" queries as well, instead of inlining values to strings using formatting etc., which is injection and error prone. For example:

from sqlalchemy import text

# Wrap with text() in order to be able to use named placeholder style no
# matter which driver is in use underneath SQLA.
stmt = text("SELECT * FROM foo WHERE bar = :bar")
engine.execute(stmt, {"bar": 1})

SQLAlchemy has an exception for == None in the query compiler that produces IS NULL instead of = NULL, which would rarely, if ever, make sense.

Upvotes: 3

Related Questions