CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Jan
Jan

Reputation: 685

Executing WHERE IN using bindparameters in Sqlalchemy/Postgres

I try to do a SELECT ... WHERE id IN (1,2,3) efficiently using Sqlalchemy with Postges.

If I do a simple select:

s.query(Model).filter(Model.id.in_([1,2,3])).all()

Sqlalchemy runs this query:

SELECT model.id AS model_id FROM model 
WHERE model.id IN (%(id_1)s, %(id_2)s, %(id_3)s)
{'id_1': 1, 'id_2': 2, 'id_3': 3}

When the array gets longer this is not efficient. Also this does not work with baked queries.

Knowing that Postgres supports tuples as parameters, I tried to put in my Array/Tuple directly into the parameter section by using a bind parameter:

s.query(Model)
 .filter(Model.id.in_(bindparam('my_tuple')))
 .params(my_tuple=(1,2,3)).all()

Unfortunately Sqlalchemy does not accept bindparam in an in_:

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

So I tried to trick Sqlalchemy somehow to accept a bindparam.
Extending the BindParam class I was able to do so:

class TupleBindParameter(BindParameter, Selectable):
    pass

s.query(Model)
 .filter(Model.id.in_(TupleBindParameter('my_tuple')))
 .params(my_tuple=(1,2,3)).all()

Now I get what I wanted:

SELECT model.id AS model_id FROM model 
WHERE model.id IN %(my_tuple)s
{'my_tuple': (1, 2, 3)}

This solution seems somehow hacky to me. Is there an official way to get Sqlalchemy do do the same?

--

The setup to reproduce my examples is very simple:

Base = declarative_base()    
class Model(Base):
    __tablename__ = 'model'
    id = Column(Integer, primary_key=True)
    def __init__(self, id): self.id = id

engine = create_engine('postgres://x:x@localhost/x')    
Base.metadata.create_all(bind=engine)

Session = sessionmaker(bind=engine)
s = Session()
s.add_all([Model(1), Model(2), Model(4)])
s.commit()

Upvotes: 6

Views: 1488

Answers (1)

r-m-n
r-m-n

Reputation: 15120

Use op('IN')

s.query(Model)
 .filter(Model.id.op('IN')(bindparam('my_tuple')))
 .params(my_tuple=(1,2,3)).all()

see this issue

Upvotes: 7

Related Questions