Psycopg2 single quote exception

Question

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?

check this links its similar to your questioin http://stackoverflow.com/questions/935/string-literals-and-escape-characters-in-postgresql http://stackoverflow.com/questions/12316953/insert-varchar-with-single-quotes-in-postgresql — splucena, Feb 20 '14 at 01:54

score 7 · Answer 1 · answered Sep 15 '15 at 12:59

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');"""

score 3 · Answer 2 · answered Dec 27 '13 at 15:23

3

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.

answered Dec 27 '13 at 15:23

fog

3,266
1
25
31

score 0 · Answer 3 · answered Dec 27 '13 at 14:16

0

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

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

answered Dec 27 '13 at 14:16

falsetru

357,413
63
732
636

Psycopg2 single quote exception

3 Answers3

Linked