0

Similar Question: Using SQLAlchemy, can I join a Python object (i.e. a list) within a Query? - however, this question involves a simpler use of a Python list that could be reworked into a filter, which doesn't seem to apply here.

I have a 4 column table X (columns A,B,C,D) in my database, and two lists (a,b) of the same length in Python.

For each pair of values (x,y) in zip(a,b), I want to find the value of D corresponding to the row with the smallest value of C such that the value of column A is x and the value of column B is y. And if such a row doesn't exist, I want to find None/NULL.

If Y=list(zip(a,b)) was a two column (A,B) table in our Postgres database, we could find the answer very easily as follows:

SELECT DISTINCT ON (Y.A, Y.B) X.D
FROM Y 
    LEFT JOIN X 
        ON Y.A=X.A
        AND Y.B=X.B 
ORDER BY X.C

How would I do this in SqlAlchemy?

Rushabh Mehta
  • 1,529
  • 1
  • 13
  • 29
  • Could you explain two list (a, b)? Are they indepent list like "a=[elementn, elementp,...], b = [elementk, elementl,...]" and you are combining them or they are just elements and you are binding them with zip? Maybe removing "two lists" and putting tuple instead would be better? – Umut TEKİN Jul 12 '22 at 15:37
  • Yes, they are independent lists of the same length. I avoided using the word tuple since it doesn't imply binary tuple. – Rushabh Mehta Jul 12 '22 at 15:45

0 Answers0