Hello everyone,
While toying around with the cool new features of 1.2, I noticed that the
expanding of a bindparam in a "in" doesn't accept empty list.
This is documented, and the error message is pretty clear, but the behavior
itself is pretty inconsistent with the other "in" syntax :
from sqlalchemy import *
metadata = MetaData()
engine = create_engine('sqlite://')
user = Table('user', metadata,
Column('user_id', Integer, primary_key=True),
)
id_list = []
metadata.create_all(engine)
stmt_1 = select([user]).where(
user.c.user_id.in_(id_list)
)
stmt_2 = select([user]).where(
user.c.user_id.in_(bindparam('foo', expanding=True))
)
engine.execute(stmt_2, {"foo": id_list})
Here
engine.execute(stmt_1)
returns the expected result (where the "in empty list" is replaced by false)
And
engine.execute(stmt_2, {"foo": id_list})
returns an error
If I'm not mistaken the first statement already use a tricks to simulate an
empty list as print(stmt_1) returns
SELECT "user".user_id FROM "user" WHERE 1 != 1
If my understantding of sqlalchemy is correct in the second statement the
query
SELECT user.user_id \nFROM user \nWHERE user.user_id IN ([EXPANDING_foo])'
is aready "compiled" and there is a replacement of the string
"[EXPANDING_foo]" by the actual list put in the bindparam.
Would it be possible in the case where the list is empty replace it by a
query returning an empty list instead of throwing an error ?
(https://bitbucket.org/zzzeek/sqlalchemy/src/beab362b2e892fe11d0526064eabd136d3b1f8ee/lib/sqlalchemy/engine/default.py?at=master&fileviewer=file-view-default#default.py-735)
For example "select 1 where false" returns and empty list and this works on
at least postgres
(https://www.postgresql.org/message-id/[email protected])
and mysql.
TLDR; query producing empty lists such as "select 1 where false" may be
used as tricks to resovle the non handling of empty IN expression
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
