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/20060609130037.32155.qm...@web37915.mail.mud.yahoo.com) 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 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.