Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Psycopg2 – Error during insert due to double quotes

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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?

>Solution :

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 
------------
 {}


Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading