Psycopg2 single quote exception

I use psycopg2 for my application. Everything is fine but this: When i insert a data includes single quote ' then python throw the exception below:

<class 'psycopg2.ProgrammingError'>: syntax error at or near "s"

and the content is like this "Kid's page"

I could not find a solution to fix this issue. I use python string literals like:

"""INSERT INTO table (field_name) VALUES ('%s');"""

I catch exception and return the query. I run this query from posgtre shell and it works fine. What is wrong with psycopg2?

3 Answers

Old thread, but after reading this I found a solution to this problem. Removing the single quotes will often not work, because the string may contain spaces. If that's the case, you can escape the quote by adding another single quote to the string, right behind the existing single quote: string.replace("'", "''"), so

Kid's page

will become

Kid''s page

So it will try to insert """INSERT INTO table (field_name) VALUES ('Kid''s page');"""

Just remove the quotes around the bound variable placeholder %s:

"""INSERT INTO table (field_name) VALUES (%s);"""

psycopg will quote all the bound variable values for you.

Remove quotes surrounding %s. Parameters are quoted, escaped automatically if you pass query parameters.

"INSERT INTO table (field_name) VALUES (%s)"