Forming an insert statement with single quotes in Python with Postgres

Question

I am having a field called comments. I am effectively trying to read values from one large table into multiple tables. Hence, my select query fetches the comment field for me.

I am constructing a Python script to do the copying from table to table. My insert query fails when it encounters a comment field like "Sorry! we can't process your order" because of the single quote.

I have tried using $ quotes but in vain

Here is what I am trying

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect("dbname='postgres' user='postgres' host='localhost' )
mark=conn.cursor()
/* fectching the rows and doing other stuff */

addthis="insert into my_table(something) values("$$"+str(row[8])+"$$")
mark.execute(addthis)
conn.commit()

Appreciate the help!

You need to find a new database tutorial, as your current one is failing you badly. — Ignacio Vazquez-Abrams
– Ignacio Vazquez-Abrams, Commented Apr 14, 2012 at 5:07
Ignacio Vazquez-Abrams: I have no option! Do u have any other workarounds? — Hitesh Dharamdasani
– Hitesh Dharamdasani, Commented Apr 14, 2012 at 5:11

mechanical_meat · Accepted Answer · 2012-04-14 05:45:01Z

6

Your insert statement should use a placeholder. In the case of psycopg2, it is %s.
You should pass the parameter(s) as a second argument to execute(). That way you don't have quoting issues and you guard against SQL-injection attack.

For example:

addthis = "INSERT INTO my_table (something) VALUES (%s);"
mark.execute(addthis, ('a string you wish to insert',))

edited Apr 14, 2012 at 5:45

answered Apr 14, 2012 at 5:18

mechanical_meat

170k25 gold badges238 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BrtH · Accepted Answer · 2012-04-14 21:31:23Z

-3

You could use a placeholder, as suggested by bernie. This is the preferred way.
There are however situations where using a placeholder is not possible. You then have to escape the qoutes manually. This is done by backslashing them:

addthis="insert into my_table(something) values(%s)" % str(row[8]).replace('"', r'\"').replace("'", r"\'")
mark.execute(addthis)

edited Apr 14, 2012 at 21:31

answered Apr 14, 2012 at 12:26

BrtH

2,70519 silver badges29 bronze badges

1 Comment

Richard Huxton Over a year ago

No - he should use a placeholder. If you're asking questions like this, then the last thing you should do is start putting together your own ad-hoc escaping. Yours doesn't work anyway.

Collectives™ on Stack Overflow

Forming an insert statement with single quotes in Python with Postgres

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related