SQLAlchemy ORM: SELECT specific columns with multiple JOIN and WHERE clause-CodePudding

I'm trying to find a more pythonic way of executing SQL queries using the SQLalchemy ORM. Below is a sample SQL statement:

SELECT A.payment_plan_id, A.plan_id, A.amount, C.promo_code, B.sso_guid, B.user_id
FROM payment_plans as A
JOIN users as B ON A.user_id = B.user_id
JOIN invoices as C ON A.invoice_id = C.invoice_id
WHERE A.due_date::date < '01/01/2022'::date
AND (A.status = 'pending' OR A.status = 'failed')

Tried this approach:

orm_query = session.query(
    select(
        payment_plans.payment_plan_id,
        payment_plans.plan_id,
        payment_plans.amount,
        invoices.promo_code,
        users.sso_guid,
        users.user_id,
   )
   .join(users, payment_plans.user_id == users.user_id)
   .join(invoices, payment_plans.invoice_id == invoices.invoice_id)
   .where(payment_plans.due_date < '01/01/2022')
   .where(payment_plans.status in ["pending", "failed"])
)

and I'm always ending up with this error:

sqlalchemy.exc.ArgumentError: Column expression or FROM clause expected, got <sqlalchemy.sql.selectable.Select object at 0x7fe342d7ca60>. To create a FROM clause from a <class 'sqlalchemy.sql.selectable.Select'> object, use the .subquery() method.

I'm not sure what it means and I scoured google looking for answers. Any help is appreciated. Thanks!

CodePudding user response：

session.query and select both basically do the same thing, but session.query returns instances of your mapped classes while select returns rows like a traditional DB API. This means that you have to use session.query like you use select in your example and call .all() to receive a QueryResult your objects. Or you use select without the outer session.query and use your engine to execute the query. I assume that you want to use the latter:

query = select(

    payment_plans.payment_plan_id,
    payment_plans.plan_id,
    payment_plans.amount,
    invoices.promo_code,
    users.sso_guid,
    users.user_id,
)
   .join(users, payment_plans.user_id == users.user_id)
   .join(invoices, payment_plans.invoice_id == invoices.invoice_id)
   .where(payment_plans.due_date < '01/01/2022')
   .where(payment_plans.status in ["pending", "failed"])

result = engine.execute(query)

TIP: Install sqlalchemy-stubs (the official type stubs for sqlalchemy) via pip to get API hints for sqlalchemy in your IDE and a better feeling for what works and what not (works with VSCODE at least; you may have to restart your IDE).

CodePudding user response：

I managed to make this work by not using select, and notice the use of in_ for value evaluation with a list.

 orm_query = (
    session.query(
        payment_plans.payment_plan_id,
        payment_plans.plan_id,
        payment_plans.amount,
        invoices.promo_code,
        users.sso_guid,
        users.user_id,
   )
   .join(users, payment_plans.user_id == users.user_id)
   .join(invoices, payment_plans.invoice_id == invoices.invoice_id)
   .where(payment_plans.due_date < '01/01/2022')
   .where(payment_plans.status.in_(["pending", "failed"]))
)