SQLALCHEMY: Perform bulk SELECT statement

Question

I was trying to send a bunch of select statements to a single database using sqlalchemy unfortunately, my approach did not succeed. All my queries are also conditioned to look into indexed columns. I filter on a list of different combinations for the conditions. Here is my current approach

condition1 = [1,2,3,4,5,6,7,8,9]
condition2 = [1,2,3,4,5,6,7,8,9]

queries_collection = []

for condition_value1 in condition1:

    for condition_value2  in condition2:

        condition = and_(
                        DataModel.column1 == int(condition_value1),
                        DataModel.column2 ==  int(condition_value2))

        queries_collection.append(

           self.db.session.query(DataModel).filter(condition)

        )


self.db.session.add_all(queries_collection)
result = self.db.session.execute()

Following an example of how to use add_all() found here https://github.com/sqlalchemy/sqlalchemy/wiki/FilteredQuery

Generally, can I first create all queries and then execute them in bulk?

What am I missing?

Answer 1

Just

result = self.db.session.query(DataModel).\
    filter(DataModel.column1.in_(condition1),
           DataModel.column2.in_(condition2)).\
    all()

if you're looking for combinations (product) of conditions 1 & 2.

Session.add_all() is for adding a list of (transient) model objects to the session.

Answer 2

Make one query rather than a list of queries

query = db.session.query(DataModel)

for condition_value1 in condition1:

    for condition_value2  in condition2:

        condition = and_(
                    DataModel.column1 == int(condition_value1[0]),
                    DataModel.column2 ==  int(condition_value2[0])

        )

    query = query.filter(condition)

result = query.all()