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 = []
stmt_1 = select([user]).where(

stmt_2 = select([user]).where(
        user.c.user_id.in_(bindparam('foo', expanding=True))

engine.execute(stmt_2, {"foo": id_list})

returns the expected result (where the "in empty list" is replaced by false)

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 

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 ? 
For example "select 1 where false" returns and empty list and this works on 
at least postgres 
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


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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Reply via email to