`in_` operator does not supports bound parameter

[Taha Jahangir]

Using column.in_(bindparam('name')) (to place a variable list of values) throws following exception: (with psycopg2 engine)

sqlalchemy.exc.InvalidRequestError: in_() accepts either a list of expressions or a selectable: BindParameter('name', None, type_=NullType())

[Taha Jahangir]

Using IN operator in postgresql (>8.2) is same as using ANY function, so this can be written as:

from sqlalchemy import func
query.filter(column == func.any(bindparam('name')))

[Valanto Kousetti]

Hi @taha_jahangir , The ANY operator doesn't seem to exist in SQLite. Is there an other way to solve this?

Thanks

[Taha Jahangir]

@zzzeek The ANY workaround only works for postgresql

[Mike Bayer]

There's no bug here. IN in SQL requires distinct values to be passed.

[Yegor Roganov]

@zzzeek, could you please explain what you mean by "IN in SQL requires distinct values"? I cannot figure out how to apply baked extension to queries with IN operator since passing bindparam(...) to in_ fails with the mentioned exception.

[Mike Bayer]

@roganov in SQL, IN looks like this: "SELECT * FROM table WHERE column IN (1, 2, 3)", with bound params that's "SELECT * FROM table WHERE column IN (?, ?, ?)". Thats the case SQLAlchemy's in_() supports directly. Recent versions of Postgresql can probably accept a single ARRAY for IN, and I may recall that some of the MySQL drivers like pymysql may also be accepting a single bind that they rewrite into multiple positionals before sending the query to the server.

seems to work for psycopg2 and pymysql for a tuple. we can look to in_() to support this in some way but it has to be something non-ambiguous, as well as something that isn't going to make people complain when it doesn't work on sqlite / oracle / sql server / etc:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)

# works
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

# works
# e = create_engine("mysql+pymysql://scott:tiger@localhost/test", echo=True)


Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)

s.add_all([
    A(id=i) for i in range(1, 10)
])
s.commit()

q = s.query(A).filter(A.id.op("IN")(bindparam("x")))

print [a.id for a in q.params(x=(4, 6, 7))]

[Yegor Roganov]

Thank you for the explanation.