Python variable in long SQL string

Question

What is a safe way to replace the number in the second-to-last line of this SQL query with a variable?

Say my variable is customer_id. Can I use {} in place of 2 and put .format(customer_id) at the end of this string?

unlicensed_query = """ SELECT SUM(x.quantity), SUM(x.quantity * p.list_price) FROM ( SELECT cu.customer_id, cu.product_id, cu.quantity FROM csi_usage cu LEFT JOIN csi c ON cu.customer_id = c.customer_id AND cu.product_id = c.product_id WHERE c.product_id IS NULL AND cu.customer_id = 2) x, product p WHERE x.product_id = p.id; """

mysql but the query is execute with sqlalchemy like so: self.session.execute(unlicensed_query).fetchall()[0] — Casey
– Casey, Commented Apr 5, 2017 at 20:12
I used bound parameters using thebjorn's link and it's working well. Thanks! — Casey
– Casey, Commented Apr 5, 2017 at 20:47

Casey · Accepted Answer · 2017-04-05 21:02:42Z

As stated by thebjorn, the correct way to do this is to use bound parameters (http://docs.sqlalchemy.org/en/latest/core/tutorial.html#specifying-bound-parameter-behaviors). An example is here:

from sqlalchemy.sql import text fully_utilized_query = text(""" SELECT SUM(x.quantity) FROM ( SELECT cu.customer_id, cu.product_id, cu.quantity FROM csi_usage cu JOIN csi c ON cu.customer_id = c.customer_id AND cu.product_id = c.product_id AND cu.quantity = c.licence_qty WHERE cu.customer_id = :customer_id) x; """) fully_utilized = self.session.execute(fully_utilized_query, {'customer_id': current_user.customer_id}).scalar()

Collectives™ on Stack Overflow

Python variable in long SQL string

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related