CODEWITHSUNDEEP
pythonsqlpandaspostgresql
Gross
Gross

Reputation: 165

PostgeSQL query in Python : how to pass dynamic parameter?

I'm trying to create a dynamic PostgreSQL query in Python:

import psycopg2
import pandas as pd

V_ID=111

conn = psycopg2.connect(host="xxxx", port = 5432, database="xxxx", user="xxxxx", password="xxx")

df= pd.read_sql_query 
("""SELECT u.user_name, sum(s.sales_amount)
FROM public.users u left join public.sales s on u.id=s.user_id
WHERE USER_ID = v_ID
Group by u.user_name""",con=conn)
df.head()

When I set a fixed ID then a query ran fine, but if I set a variable "V_ID", then get an error.

Help me please, how to properly place a variable within a query...

Upvotes: 0

Views: 1952

Answers (1)

Kaushal Sharma
Kaushal Sharma

Reputation: 2320

You can use string formatting to pass the value in the query string. You can read more about string formatting here: https://www.w3schools.com/python/ref_string_format.asp

v_ID = "v_id that you want to pass"

query = """SELECT u.user_name, sum(s.sales_amount)
FROM public.users u left join public.sales s on u.id=s.user_id
WHERE USER_ID = {v_ID}
Group by u.user_name""".format(v_ID=v_ID)

df= pd.read_sql_query(query ,con=conn)

As mentioned by @Adrian in the comments, string formatting is not the right way to do this.

More details here: https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries

As per the docs, params can be list, tuple or dict.

The syntax used to pass parameters is database driver dependent. Check your database driver documentation for which of the five syntax styles, described in PEP 249’s paramstyle, is supported. Eg. for psycopg2, uses %(name)s so use params={‘name’ : ‘value’}.

Here is how you can do this in the case of psycopg2.

v_ID = "v_id that you want to pass"

query = """SELECT u.user_name, sum(s.sales_amount)
FROM public.users u left join public.sales s on u.id=s.user_id
WHERE USER_ID = %(v_ID)s
Group by u.user_name"""

df= pd.read_sql_query(query ,con=conn, params={"v_ID":v_ID})

Upvotes: 4

Related Questions