Insert/Update row with apostrophe in description

Moderator: NorbertKrupa

Post Reply
clv100
Beginner
Beginner
Posts: 27
Joined: Tue Oct 16, 2012 6:04 pm

Insert/Update row with apostrophe in description

Post by clv100 » Thu Mar 28, 2013 4:29 pm

I am updating a description column in a table from the values that were loaded into another table. Some of the descriptions have apostrophes in the description and I am returning errors when I try to update or insert into the table when there is an apostrophe in the description. I have googled and tried different methods but I am still getting the errors.

These are the queries being executed in separate functions:
qrystr2 = """insert into strgrp_mast (locgroupno, locgroupname ) values(%s, '%s');""" % (lgno, lgname)
or
qrystr3 = """update strgrp_mast set locgroupname = '%s' where locgroupno = %s ;""" % (lgname, lgno)

Results:

(These are debugging "print" from the output, the first line is a print of the query and this works with out a problem because there is NO apostrophe in the description)
update strgrp_mast set locgroupname = '2010 LATE GRAD STRS ' where locgroupno = 5700 ;

BUT THIS DOES NOT
'B' ModlStrCode 2013 --This is the description value being passed with quotes in the description

insert into strgrp_mast (locgroupno, locgroupname ) values(6001, ''B' ModlStrCode 2013');
Invalid : ('42601', '[42601] ERROR 4856: Syntax error at or near "B\' ModlStrCode 2013\'" at character 68\n (4856) (SQLExecDirectW)')
This did not work!

Please let me know if I did not explain this well!! I would appreciate any suggestions. Thanks!!
Cece V.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1800
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Insert/Update row with apostrophe in description

Post by JimKnicely » Thu Mar 28, 2013 4:38 pm

Maybe this post can help?

viewtopic.php?f=63&t=887

Maybe you can prepend (prefix) and append the double dollar signs to you locgroupname variable?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

clv100
Beginner
Beginner
Posts: 27
Joined: Tue Oct 16, 2012 6:04 pm

Re: Insert/Update row with apostrophe in description

Post by clv100 » Thu Mar 28, 2013 5:55 pm

Thank you SO MUCH!! That worked!!

qrystr2 = """insert into strgrp_mast (locgroupno, locgroupname ) values(%s, $$%s$$);""" % (lgno, lgname)

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1800
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Insert/Update row with apostrophe in description

Post by JimKnicely » Thu Mar 28, 2013 6:47 pm

Awesome!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Python”