Convert raw SQL to SQLAlchemy query

Question

SELECT * 
FROM product_stocks 
WHERE detected_date = (
                         SELECT MAX(detected_date) 
                         FROM product_stocks 
                         WHERE id = 18865
                      ) 
      AND id = 18865;

Having lots of trouble converting this to SQLAlchemy query string. What's the most efficient way?

Answer 1

You can use from_statement to execute the raw SQL-Query and fetch it in a SQL-Alchemy Object. This helps when it's easier to write plain SQL then SQLAlchemy Syntax.

Session.query(YourClass).from_statement(text('''SELECT * FROM product_stocks 
WHERE detected_date = (SELECT MAX(detected_date) FROM product_stocks WHERE id = 18865)
AND id = 18865;''')).all()