Converting raw sql query to SQL Alchemy ORM

Question

I'm currently executing this query in one process:

SELECT DISTINCT ON (c.api_key, worker_id) worker_id, c.api_key, a.updated_at, b.user_id, a.country
           FROM TABLE_A a
               INNER JOIN TABLE_B b ON (b.id = a.user)
               INNER JOIN TABLE_C c ON (b.owner = c.id)
           WHERE 1=1 
               AND a.platform = 'x'
               AND a.country = 'y'
               AND a.access_token is not NULL
               
                      
           ORDER BY c.api_key, worker_id, a.updated_at desc

I'm currently wrapping it using from SQLAlchemy import text and then simply executing

query_results = db.execute(query).fetchall()
list_dicts = [r._asdict() for r in query_results]
df = pd.DataFrame(list_dicts)

and it works, but I would really like to see if it's possible to have it in the other notation, like :

db.query(TABLE_A).filter().join()... etc

Have you created SQLAlchemy models to map your tables?

Elias Dorneles
– Elias Dorneles

2022-01-21 12:02:28 +00:00
Commented Jan 21, 2022 at 12:02 — Elias Dorneles
– Elias Dorneles, Commented Jan 21, 2022 at 12:02
Yes. You can actually use db.query(TABLE_A).filter()...etc

Enrique Ortiz Casillas
– Enrique Ortiz Casillas

2022-01-21 23:30:12 +00:00
Commented Jan 21, 2022 at 23:30 — Enrique Ortiz Casillas
– Enrique Ortiz Casillas, Commented Jan 21, 2022 at 23:30

Elias Dorneles · Accepted Answer · 2022-01-21 12:13:42Z

1

Yes, it's possible.

But the exact way to do it will depend on your SQLAlchmey version and how you've setup your SQLAlchemy project and models.

You may want to check out the SQLAlchemy ORM querying guide and the Expression Language Tutorial to see which one fits better your case.

answered Jan 21, 2022 at 12:13

Elias Dorneles

24.2k12 gold badges91 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting raw sql query to SQL Alchemy ORM

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related