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ä
– Sebastian Hätälä, Commented Jun 17, 2020 at 10:47
So you only want to get the rows from database, where col1 matches tuple[0]? — Myke Bilyanskyy
– Myke Bilyanskyy, Commented Jun 17, 2020 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ä
– Sebastian Hätälä, Commented Jun 17, 2020 at 10:56

Myke Bilyanskyy · Accepted Answer · 2020-06-17 11:57:22Z

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, 2020 at 11:57

answered Jun 17, 2020 at 10:52

Myke Bilyanskyy

6987 silver badges17 bronze badges

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

2 Comments

Sebastian Hätälä Over a year ago

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."

Myke Bilyanskyy Over a year ago

Modified the answer, please try it out.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related