Menu
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()
216
This SQLAlchemy engine is a global object which can be created and configured once and use the same engine object multiple times for different operations. The first step in establishing a connection with the PostgreSQL database is creating an engine object using the create_engine() function of SQLAlchemy.
SQLAlchemy Core The already created students table is referred which contains 4 columns, namely, first_name, last_name, course, score. But we will be only selecting a specific column. In the example, we have referred to the first_name and last_name columns. Other columns can also be provided in the entities list.
Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as shown below, create a table called books with columns book_id and book_price. Insert record into the tables using insert() and values() function as shown.
The grouping is done with the group_by() query method, which takes the column to use for the grouping as an argument, same as the GROUP BY counterpart in SQL. The statement ends by calling subquery() , which tells SQLAlchemy that our intention for this query is to use it inside a bigger query instead of on its own.
Use op('IN')
s.query(Model)
.filter(Model.id.op('IN')(bindparam('my_tuple')))
.params(my_tuple=(1,2,3)).all()
see this issue
77
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
