6
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?

HaveNoDisplayName
  • 7,993
  • 106
  • 33
  • 46
yesyouken
  • 192
  • 2
  • 12

3 Answers3

7

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()
muthan
  • 2,144
  • 2
  • 17
  • 31
  • Would you know how to do this from classic SQLAlchemy? – Matthew Moisen Mar 13 '17 at 20:14
  • didn't used SQLAlchemy in a long time. But as far as I remember this was the way to do it. Not sure what you mean with classic SQL alchemy. But maybe you mean the short handle for a specific Model which only replaces the `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. – muthan Mar 13 '17 at 20:41
  • In case someone runs into problems with this, I found the following answer to be useful https://stackoverflow.com/questions/23344728/sqlalchemy-from-statement-cannot-locate-column – Johann Jan 28 '20 at 12:09
  • exactly what I needed. – Deepam Gupta Mar 14 '22 at 13:38
2

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

van
  • 68,849
  • 11
  • 156
  • 163
2

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.

Matthew Moisen
  • 14,590
  • 25
  • 104
  • 205