Psycopg2 - Error during insert due to double quotes

Question

I am using Python + Psycopg2 to insert an array of texts. elems column is of type text[].

a = ["A ''B'' C"]

cursor.execute(f"""\
  INSERT INTO table(elems) \
  VALUES (ARRAY{a}::text[]) \
""")

Error:

column "A ''B'' C" does not exist

The error above is due to the double quotes from Python list.

Using a database tool, the exact query that works is:

INSERT INTO table(elems)
VALUES (ARRAY['A ''B'' C']::text[])

Now my question: What's the proper way of inserting a Python list of strings where an element may contain a single quote?

Answer 1

a = ["A ''B'' C"]

import psycopg2
con = psycopg2.connect(dbname="test", host='localhost', user='postgres', port=5432)
cur = con.cursor()

cur.execute("insert into array_test(id, text_array) values(%s, %s)", [6, a])
con.commit()

select text_array from array_test where id = 6;
  text_array   
---------------
 {"A ''B'' C"}

cur.execute("insert into array_test(id, text_array) values(%s, %s)", [7, []])
con.commit()

select text_array from array_test where id = 7;
 text_array 
------------
 {}