- changed status to resolved
`in_` operator does not supports bound parameter
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())
Comments (8)
-
reporter -
reporter - changed status to closed
-
Hi @taha_jahangir , The ANY operator doesn't seem to exist in SQLite. Is there an other way to solve this?
Thanks
-
reporter - changed status to open
@zzzeek The
ANY
workaround only works for postgresql -
repo owner - changed status to closed
There's no bug here. IN in SQL requires distinct values to be passed.
-
@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 passingbindparam(...)
toin_
fails with the mentioned exception. -
repo owner @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))]
-
Thank you for the explanation.
- Log in to comment
Using
IN
operator in postgresql (>8.2) is same as usingANY
function, so this can be written as: