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

2

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()

answered Apr 5, 2017 at 21:02

Casey

2,7178 gold badges41 silver badges67 bronze badges

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

Collectives™ on Stack Overflow

Python variable in long SQL string

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