Using SQLAlchemy, can I join a Python object (i.e. a list) within a Query?

Question

Assuming I have a Postgres-DB table looking like this (with with many more rows):

|------|-------|
|col1  |col 2  |
|------|-------|
|a1    |10     |
|a2    |55     |
|a3    |24     |
|------|-------|

And a List in Python with Tuples looking like this:

|------|-------|
|a1    |1      |
|a3    |2      |
|------|-------|

During runtime I now want to (inner) join the table with the list without having to persist the list as a DB object.

What does work, is to filter using the list and SQLAlchemys .in_ operator.

However when I try db.query(Table).join(list) I get this error:

sqlalchemy.exc.NoInspectionAvailable: No inspection system is available for object of type

Of course a workaround would be to first fetch all elements from the db using the filter operator and then using python for the join...but it feels like there should be a

By the column "col1" and the first element of the Tuple for each row in the list — Sebastian Hätälä, Jun 17 '20 at 10:47
So you only want to get the rows from database, where col1 matches tuple[0]? — Mikhail Beliansky, Jun 17 '20 at 10:48
Not quite: I want to filter col1 by each first element in the list AND then interact with both, e.g. by adding each second element in the list to col2. — Sebastian Hätälä, Jun 17 '20 at 10:56

Mikhail Beliansky · Accepted Answer · 2020-06-17T11:57:22.523

2

If I understood correctly and you want to only get the rows where col1 == tuple[0], you can use the sqlalchemy filter and in_ operators:

tuples_list = [('a1', 1), ('a3', 2)]

search_dict = dict(tuples_list)  # Convert to dict 

res = db.query(Table).filter(Table.col1.in_(search_dict.keys())).all()

for instance in res:
    instance.col2 = instance.col2 + search_dict[instance.col1]

There's also a detailed example without using the ORM here: SQLAlchemy IN clause

edited Jun 17 '20 at 11:57

answered Jun 17 '20 at 10:52

Mikhail Beliansky

618
5
15

Thanks, great hint but not quite what I'm looking for. As I already mentioned in the question: "What does work, is to filter using the list and SQLAlchemys .in_ operator." – Sebastian Hätälä Jun 17 '20 at 10:56
Modified the answer, please try it out. – Mikhail Beliansky Jun 17 '20 at 11:57

Using SQLAlchemy, can I join a Python object (i.e. a list) within a Query?

1 Answers1

Linked