Build full query from SQLAlchemy orm

Question

Ultimately I'm trying to use pandas read_sql which takes in a raw SQL query.

I'm trying to convert something like;

sql = str(session.query(Post).filter(Post.user_id=1))

That generates something like

select * from Post where user_id = %(user_id_1)

Is there any way to generate that query with the parameter already interpolated?

You could try passing that SQL string directly to read_sql_query along with params={"user_id_1": 1}. That would help you avoid the messiness of injecting literal values into the SQL command text. — Gord Thompson
– Gord Thompson, Commented Dec 6, 2020 at 23:03
Yep, that's what I'm doing now. It just seems like there should be a way to automatically do this with SQLAlchemy — Shaun
– Shaun, Commented Dec 7, 2020 at 3:02

Gord Thompson · Accepted Answer · 2020-12-07 13:04:02Z

1

As you have found, if we str() an ORM query we get the SQL command text with parameter placeholders using the paramstyle for our dialect:

qry = session.query(Parent).filter(Parent.id == 1)
sql = str(qry)
print(sql)
"""console output:
SELECT parent.id AS parent_id, parent.lastname AS parent_lastname, parent.firstname AS parent_firstname 
FROM parent 
WHERE parent.id = %(id_1)s
"""

If we want to have the parameter values embedded in the SQL statement then we need to .compile() it:

sql_literal = qry.statement.compile(
    compile_kwargs={"literal_binds": True},
)
print(sql_literal)
"""console output:
SELECT parent.id, parent.lastname, parent.firstname 
FROM parent 
WHERE parent.id = 1
"""

(Standard disclaimers regarding SQL Injection apply.)

answered Dec 7, 2020 at 13:04

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

Collectives™ on Stack Overflow

Build full query from SQLAlchemy 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