How to make bulk `SELECT` queries with SQLAlchemy?

Question

I have a simple query like:

query = """SELECT (A.name, A.food) FROM animals AS A
    WHERE
        A.type = :animal_type AND M.id = :member_id
    INNER JOIN members as M ON M.animal_id = A.animal_id;"""

I have a list of inputs, like:

animals_memberships = [
    {
        "animal_type": "BEAR",
        "member_id": 100,
    },
    {
        "animal_type": "RAT",
        "member_id": 200,
    },
    ...
]

Currently, I'm looping over each animal membership, and querying for the data:

queries = []
for item in animals_memberships:
    session.execute(
        query, {
            "member_id": item["member_id"],
            "animal_type": item["animal_type"],
        }
    )

animal_names_foods = [
    result.fetchall()[0] for result in queries
]

Is it possible to do "bulk" queries, similar to how bulk inserts work with session.bulk_save_objects()?

https://stackoverflow.com/questions/948212/sqlalchemy-complex-in-clause-with-tuple-in-list-of-tuples, https://stackoverflow.com/questions/9140015/how-to-get-rows-which-match-a-list-of-3-tuples-conditions-with-sqlalchemy — Ilja Everilä, Jan 31 '22 at 08:36

score 0 · Accepted Answer · answered Jan 31 '22 at 08:19

You can fetch all your data in one query like this

SELECT (A.name, A.food) FROM animals AS A
    WHERE
        (A.type, M.id) in (VALUES ('BEAR', 100), ('RAT', 200))
    INNER JOIN members as M ON M.animal_id = A.animal_id;

Here the way, how can you build this query in safe way in python

animals_memberships = [
    {
        "member_id": 100,
        "animal_type": "BEAR",
    },
    {
        "member_id": 200,
        "animal_type": "RAT",
    },
]

parts = []
params = {}

for idx, item in enumerate(animals_memberships):
    params[f"member_id_{idx}"] = item["member_id"]
    params[f"animal_type_{idx}"] = item["animal_type"]
    parts.append(f"(:member_id_{idx}, :animal_type_{idx})")

parts_str = ",".join(parts)

query = f"""
    SELECT (A.name, A.food) FROM animals AS A
    WHERE
        (A.type, M.id) IN (VALUES {parts_str})
    INNER JOIN members as M ON M.animal_id = A.animal_id;
"""

session.execute(query, params)

How to make bulk `SELECT` queries with SQLAlchemy?

1 Answers1