PostgeSQL query in Python : how to pass dynamic parameter?

Question

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...

Kaushal Sharma · Accepted Answer · 2021-03-11 19:15:04Z

4

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

edited Mar 11, 2021 at 19:15

answered Mar 10, 2021 at 23:16

Kaushal Sharma

2,4601 gold badge15 silver badges17 bronze badges

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

4 Comments

Gross Over a year ago

Thanks for the answer, I'm a little confused where do I put a number? Initially v_ID ="111" or in format : .format(v_ID=111) ??

Kaushal Sharma Over a year ago

you have to write it in the first line only, like v_ID="111", please consider upvoting my answer if it helps.

Adrian Klaver Over a year ago

The reasons why you should not use this method: Parameters. Instead use the params argument to read_sql_query to pass in the value.

Kaushal Sharma Over a year ago

@AdrianKlaver Thank you for mentioning this, I have edited my answer. You can upvote it again if you think that could help others now.

Collectives™ on Stack Overflow

PostgeSQL query in Python : how to pass dynamic parameter?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related