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.
Insert/Update row with apostrophe in description
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Insert/Update row with apostrophe in description
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?
viewtopic.php?f=63&t=887
Maybe you can prepend (prefix) and append the double dollar signs to you locgroupname variable?
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Insert/Update row with apostrophe in description
Thank you SO MUCH!! That worked!!
qrystr2 = """insert into strgrp_mast (locgroupno, locgroupname ) values(%s, $$%s$$);""" % (lgno, lgname)
qrystr2 = """insert into strgrp_mast (locgroupno, locgroupname ) values(%s, $$%s$$);""" % (lgno, lgname)
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Insert/Update row with apostrophe in description
Awesome!
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.