Python: How insert variable inside SQL string with “{” character?

Question

I have seen a similar question with answers here How can I print literal curly-brace characters in a string and also use .format on it?

But he is not suitable for this case:

I run an SQL query as a string and place variables in it. The problem is that inside a query there is a symbol like "}" and then I get an error like:

Single '}' encountered in the format string.

as example:

import psycopg2
import pandas as pd
conn = psycopg2.connect(host="xxx", port = 5432, database="xxxx", user="xxx", password="xxxx")
statment= """
Select User_name,
       replace(replace(product_name,'{',''),'}','') as product_name,
      date
from Sales
where user_id={v_User_ID} and shop_id={v_shop_id}
UNION
Select User_name,
       replace(replace(product_name,'{',''),'}','') as product_name,
      date
from Sales
where shop_id={v_shop_id} and country="France"
"""


test=statment.format(v_User_ID=181 ,v_shop_id=107)
df= pd.read_sql_query(test ,con=conn)
df.head()

When I run a script without the "}" icon, everything is fine, but I must use this icon within the SQL query.

How can this problem be solved?

not sure but you can probably escape it (\{) or use double curly brackets ({{) — Berkay Berabi, Apr 21 '21 at 18:32
[`pd.read_sql_query`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql_query.html) accepts parameters via the aptly named `params` keyword. Use parameters instead of injecting your values (v_User_ID and v_shop_id) into your SQL. See [Exploits of a Mom](https://xkcd.com/327/) why what you're doing is dangerous. — , Apr 21 '21 at 18:40
Quick query, In your code Did you try with `{{` and `}}` instead of `{` and `}` — Dhinesh Sunder Ganapathi, Apr 21 '21 at 18:36
So, how exactly was this different from the solution you claim it’s different from? — deceze, Apr 21 '21 at 18:49
This string used the same variables in different places, and not the same variables in one place. — Gross, Apr 21 '21 at 18:53
So? The issue is that literal curly braces need special escaping (doubling), which is perfectly answered in that other question. — deceze, Apr 21 '21 at 18:55

score 2 · Accepted Answer · answered Apr 21 '21 at 18:32

Like in an f-string, you need to change the curly braces that you don't want to be used as part of formatting into double-curlies:

statement= """
Select User_name,
       replace(replace(product_name,'{{',''),'}}','') as product_name,
      date
from Sales
where user_id={v_User_ID} and shop_id={v_shop_id}
UNION
Select User_name,
       replace(replace(product_name,'{{',''),'}}','') as product_name,
      date
from Sales
where shop_id={v_shop_id} and country="France"
"""

>>> statement.format(v_User_ID=181 ,v_shop_id=107)
Select User_name,
       replace(replace(product_name,'{',''),'}','') as product_name,
      date
from Sales
where user_id=181 and shop_id=107
UNION
Select User_name,
       replace(replace(product_name,'{',''),'}','') as product_name,
      date
from Sales
where shop_id=107 and country="France"

Python: How insert variable inside SQL string with “{” character?

1 Answers1