psycopg2 Programming Error when using .format()

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?

2 answers

  • answered 2018-07-20 16:54 JonJon

    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.

  • answered 2018-07-20 16:54 klin

    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)