define parameter in update query in python psycopg2

Question

This my python code which is used to perform update operation from reading the csv file. I tried with this also. It doesn't work out.

for i  in cin:
    try:
        conn=psycopg2.connect("dbname=pharmaflare user=postgres")
        cursor=conn.cursor()
        cursor.execute("UPDATE pharmaflare_drug_interaction SET se_interaction ='%s' WHERE primary_drug ='%s' AND secondary_drug ='%s' AND side_effect ='%s'"%(i[3],i[0],i[1],i[2]))
        conn.commit()
        cursor.close()
        conn.close()
        #print "done",i[0],i[1],i[2],i[3]
    except Exception as e:
        cerr.writerow(i)
        ferr.flush()
        traceback.print_exc(file=sys.stdout)
        continue

Here I am facing the exception like syntax error due to the QUOTE's problem: Wherever the single quotes presents this exception arises.

  Traceback (most recent call last):
  File "<ipython console>", line 5, in <module>
  ProgrammingError: syntax error at or near "S"
  LINE 1: ...secondary_drug ='NEUER' AND side_effect ='MENIERE'S DISEASE'

Is there any alternative way available to define query statement without troubling the Quotes problem?

score 0 · Accepted Answer · edited May 23 '17 at 11:48

0

There are a couple ways to solve it. The easy/hack way to do it is to employ the re.escape() function. That function can be thought of as an equivalent to PHP's addslashes() function, though it pains me to make that comparison.

Having said that, my reading indicates psycopg2 takes advantage of PEP 249. If that's true, then you should be able to pass in parameterized queries and have it escape them for you.

edited May 23 '17 at 11:48

Community

1
1

answered Jul 27 '12 at 15:31

Thomas

1,402
1
11
14

Never do the "easy/hack way"! Pass the parameters to psycopg2 and let it escape them for you. If you try to escape it you will do it wrong. It is helpful when passing lists/tuples/arrays: `cursor.execute("select * from mytable where number in %s and x = %s", (tuple(1,2,3,4,5), 'foo'))` – AlbertFerras Aug 15 '12 at 09:01
Oh, I agree about that - the easy/hack way is a cop out and should be avoided at all costs. In this case it was included for the sake of completeness. Passing parameters as PEP 249 and your comment indicate are the correct way to solve this problem. – Thomas Aug 15 '12 at 14:02

define parameter in update query in python psycopg2

1 Answers1