-3

I am trying to do an insert in postgres through python (psycopg2). I need to include both single and double quotes in the string that does the insert. This is my code:

table_name = "my_table"
values_to_insert = ["""O'neal""", '''"The Film "''']
column_name_list = ["UpperAndLowercase", "otherColumn"]

"INSERT INTO {} ".format(table_name) + ", ".join(['''"{}"'''.format(i) for i in 
column_name_list]) + " VALUES(" + ", ".join("""'''{}'''"""
.format(i).encode("utf-8").decode('unicode_escape') for i in values_to_insert)

I expected this:

'INSERT INTO my_table "UpperAndLowercase", "otherColumn" VALUES('''O'neal''', '''"The Film "''''

But got this:

'INSERT INTO my_table "UpperAndLowercase", "otherColumn" VALUES(\'\'\'O\'neal\'\'\', \'\'\'"The Film "\'\'\''
Simonidas
  • 43
  • 6
  • why do you encode and decode on the same line. – Benoît P Feb 10 '19 at 22:13
  • unicode_escape should not be involved. Use query parameters. If your column name isn’t user-provided you shouldn’t need to escape it; if it is, *why*? – Ry- Feb 10 '19 at 22:13
  • Isn't this the same question you posted two hours ago? – Valentino Feb 10 '19 at 22:15
  • @Ry- not sure if I got your question. My column names are in a list variable, but in the end they need to show up in the string between double quotes, the reason why is because I need to have upper and lower characters in postgres column names and therefore whenever I call them they need to be between double quotes. – Simonidas Feb 10 '19 at 22:18
  • @Valentino yes, but more elaborated. – Simonidas Feb 10 '19 at 22:18
  • 1
    [Quoting backslashes in string literals](https://stackoverflow.com/questions/301068/quoting-backslashes-in-python-string-literals) is closely related. – Charles Duffy Feb 10 '19 at 22:21

2 Answers2

0

Are you using the python interpreter? Notice how x looks vs print(x) below.

If I put your code in a script and print it out it looks fine to me. 

>>> table_name = "my_table"
>>> values_to_insert = ["""O'neal""", '''"The Film "''']
>>> column_name_list = ["UpperAndLowercase", "otherColumn"]
>>> 
>>> x = "INSERT INTO {} ".format(table_name) + ", ".join(['''"{}"'''.format(i) for i in
... column_name_list]) + " VALUES(" + ", ".join("""'''{}'''"""
... .format(i).encode("utf-8").decode('unicode_escape') for i in values_to_insert)
>>> x
'INSERT INTO my_table "UpperAndLowercase", "otherColumn" VALUES(\'\'\'O\'neal\'\'\', \'\'\'"The Film "\'\'\''
>>> print(x)
INSERT INTO my_table "UpperAndLowercase", "otherColumn" VALUES('''O'neal''', '''"The Film "'''
>>>

Note also that you can just use triple """ instead of also using '''

s = """ this has 'single' and "double" quotes """
Charles Duffy
  • 280,126
  • 43
  • 390
  • 441
MarkReedZ
  • 1,421
  • 4
  • 10
  • Yes, indeed. that must be a psycopg2 problem then, because when I run the cursor (ex: cursor.execute(x)) it doesn't read the string that way. – Simonidas Feb 10 '19 at 22:23
0

You can simplify your code a lot:

table_name = "my_table"
values_to_insert = ["O'neal", '"The Film "']
column_name_list = ["UpperAndLowercase", "otherColumn"]

print "INSERT INTO {} ".format(table_name) + ", ".join(['"{}"'.format(i) for i in column_name_list]) + " VALUES(" + ", ".join(["'''{}'''".format(i) for i in values_to_insert])

Outputs your desired result:

INSERT INTO my_table "UpperAndLowercase", "otherColumn" VALUES('''O'neal''', '''"The Film "'''
Keldorn
  • 1,980
  • 15
  • 25
  • 2
    ...not that anyone should ever use this code in production; using string concatenation substituting values into SQL queries is prone to major security risks. – Charles Duffy Feb 10 '19 at 22:22