psycopg2 Programming Error when using .format()

Question

Im not sure exactly whats happening here but it might have something to do with format in python. Running this causes an error.

x = '00000201000012EB'
sql = """ SELECT * FROM table WHERE id = {} """.format(x)
conn.execute(sql)

I get an error saying: syntax error near "EB"

however when i run the command like this:

sql = """ SELECT * FROM table WHERE id = '00000201000012EB' """
conn.execute(sql)

It works fine.

Is there something wrong with the way im formatting this sql statement?

score 2 · Accepted Answer · answered Jul 20 '18 at 16:54

2

Use the variable as an argument to execute():

cur.execute(""" SELECT * FROM my_table WHERE id = %s """, (x,))

If you are determined to use format(), you should add single quotes around the placeholder:

sql = """ SELECT * FROM my_table WHERE id = '{}' """.format(x)

answered Jul 20 '18 at 16:54

klin

i like it! The reason i'm using .format is because the input changes, i was just doing this as a simpler example. excellent answer sir – Jul 20 '18 at 17:10
The two options are somehow similar, but generally `execute()` with arguments is a bit safer in context of potential SQL injection. See e.g. [psycopg2 and SQL injection security.](https://stackoverflow.com/a/45128973/1995738) – klin Jul 20 '18 at 18:37
I don't know much about SQL injection, but if you say the first option is safer, i'll make the changes to my code =) – Jul 20 '18 at 19:09

score 0 · Answer 2 · answered Jul 20 '18 at 16:54

Believe it or not it was fixed by adding more quotes to the string.

this finally worked.

x = '00000201000012EB'
sql = """ SELECT * FROM table WHERE id = {} """.format("'" + x + "'")

Since the sql statement required another set of quotes i just added them to ensure it was treated as its own string.

2 Answers2