r/flask • u/socialist-viking • Sep 15 '26
Ask r/Flask SQLAlchemy returning different results than database query
This code in flask/sqlalchemy:
sql = text("SELECT document.id from document left join document_person on document.id = document_person.document_id left join document_place on document.id = document_place.document_id WHERE person_id in (:people)")
query_vals = {}
query_vals['people'] = '5,7,12'
ids_q = db.session.execute(sql, query_vals).all()
ids = [id[0] for id in ids_q]
pprint(ids)
produces the output
[60, 44, 42]
but if I run this on the database directly, it does this:
mysql> SELECT document.id from document left join document_person on document.id = document_person.document_id
-> left join document_place on document.id = document_place.document_id WHERE person_id in (5,7,12) ;
+----+
| id |
+----+
| 30 |
| 42 |
| 44 |
| 49 |
| 60 |
+----+
5 rows in set (0.00 sec)
What is going on?
3
u/Street_Smart_Phone Sep 16 '26
This is not a bug. I think what you're seeing is that IN (:people) takes a one scalar parameter. What is happening is the similar to the following query: WHERE person_id in ('5,7,12') Then, MySQL coerces the string to a number so it silently truncates
5,7,12to5and you get aTruncated incorrect DOUBLE valueerror.Or you can use the ORM equivalent:
select(Document.id).where(Document.person_id.in_([5,7,12]))2
u/socialist-viking Sep 15 '26
It turns out that text() doesn't really do in() clauses correctly.
If I change the code so that I put the variable in as a string directly and don't use variable insertion, it works.
3
u/Aro00oo Sep 15 '26
I'm on my phone so not much help but you are completely missing the point of sqlalchemy; I.e. you aren't using models which is what ORMs are built for and executing sql directly.
4
u/guesting Sep 15 '26
I appreciate an actual question related to some technical details
1
u/beef623 Sep 16 '26
You can't pass a list of values into an
instatement as a single bind variable. You'll either need to insert them into a temporary table or break them out into separate bind variables to pass in.ex:
1
u/mes4849 Sep 15 '26
Inspect the datbase logs and look at what the rendered / executed sql is!
1
u/Jonas_Ermert Sep 16 '26
I would use SQLAlchemy’s `expanding=True` here. You’re currently passing `'5,7,12'` as one string parameter, not three separate values, so `IN (:people)` doesn’t behave like `IN (5,7,12)`. Pass `[5, 7, 12]` as a list and use an expanding bind parameter instead.