Executing WHERE IN using bindparameters in Sqlalchemy/Postgres

Question

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()

Have you profiled that this actually makes the query more efficient? — univerio
– univerio, Commented May 17, 2017 at 15:11
@univerio: I tested it. It does make a difference. I used 1000 queries with an array of 1000 elements. SimpleVersion = 40s; Bindparams=10s. You also need the bindparams to use a bakery. If you have a query with many joins, the bakery makes an enormous difference. In my case it was factor 40. — Jan
– Jan, Commented May 18, 2017 at 8:47

r-m-n · Accepted Answer · 2017-05-17 12:29:09Z

7

Use op('IN')

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

see this issue

answered May 17, 2017 at 12:29

r-m-n

15.3k6 gold badges74 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jan Over a year ago

That is exactly what I was searching for. Thanks

Collectives™ on Stack Overflow

Executing WHERE IN using bindparameters in Sqlalchemy/Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related