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?
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()
Session.query(class). Rest should be the same. And it is only about on what Object you want to bind the data. But I could be wrong because as I said I didn't use it in a long time.
Below will recreated the SQL you asked for:
_id = 18865
T = aliased(ProductStock, name="T")
T1 = aliased(ProductStock, name="T1")
subquery = (
session.query(func.max(T1.detected_date).label("detected_date"))
.filter(T1.id == _id)
# .filter(T1.id == T.id) # @note: i prefer this one to the line above
.as_scalar()
)
qry = (
session.query(T)
.filter(T.detected_date == subquery)
.filter(T.id == _id)
)
Is this the most efficient way to accomplish what you want? - I am not so sure, but not enough information
With Core SQLAlchemy 1.4/2.0:
from sqlalchemy import text, select, column
sql = 'SELECT foo FROM bar'
sql = text(sql)
sql = sql.columns(column('foo')) # This let's it be used as a subquery
sel = select(sql.selected_columns.foo).select_from(sql.subquery())
joined = sel.outerjoin(baz_t, baz_t.foo==sel.c.foo)
final = select(sel.c.foo).select_from(joined)
With Core SQLAlchemy < 1.4:
sql = 'SELECT foo FROM bar'
sql = text(sql)
sql = sql.columns() # This let's it be used as a subquery
sel = select(['foo']).select_from(sql)
# I needed this for a complex query or else columns would be ambiguous
sel = sel.alias('sel')
joined = sel.outerjoin(baz_t, baz_t.foo==sel.c.foo)
final = select([sel.c.foo]).select_from(joined)
Note that the columns() is necessary, and the alias() is helpful if the query is complex.
The following text documentation is helpful.
