psycopg2 write list of strings (with text delimiter) to a postgres array

Question

Objective:

I have a list containing strings, some have single quotes in them (as part of the string itself) ;

listOfStr = ['A sample string', "A second string with a ' single quote", 'a third string', ...]

Note that each entry does not necessarily use the same text delimiter, some are single quoted, other (the ones containing single quote as part of the string) are double quoted.

I want to insert my list as a postgresql ARRAY using psycopg2:

import psycopg2
connString = (...) # my DB parameters here.
conn = psycopg2.connect(connString)
curs = conn.cursor()

update_qry = ("""UPDATE "mytable" SET arraycolumn = {listofStr}::varchar[],
              timestamp = now() WHERE id = {ID}""".format(listofStr=listofStr, 
                                                          ID=ID))
curs.execute(update_qry)

The problem:

But I get this error:

SyntaxError: syntax error at or near "["
LINE 1: UPDATE "mytable" SET arraycolumn = ['A sample string'...

If I specify the ARRAY data type in the SQL query by adding the word 'ARRAY' in front of my list:

update_qry = ("""UPDATE "mytable" SET arraycolumn = ARRAY {listofStr}::varchar[],
              timestamp = now() WHERE id = {ID}""".format(listofStr=listofStr, 
                                                          ID=ID))

I get this error:

UndefinedColumn: column "A second string with a ' single quote" does not exist
LINE 1: 'A sample string', "A second string with a '...

I don't know how to fix it.

Environment:

• Ubuntu 18.04 64 bits 5.0.0-37-generic x86_64 GNU/Linux
• Python 3.6.9 (default, Nov 7 2019, 10:44:02)
• psycopg2 2.7.7
• psycopg2-binary 2.8.4
• "PostgreSQL 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit"

Related threads:

Postgres/psycopg2 - Inserting array of strings

Doc:

http://initd.org/psycopg/docs/usage.html -> # list adaptation

Answer 1

Basically the question should have been closed as a duplicate. However, you know Piro's answer and I think you have a problem with interpreting it.

id = 1
list_of_str = ['A sample string', "A second string with a ' single quote", 'a third string']
update_qry = """
    UPDATE mytable
    SET arraycolumn = %s,
    timestamp = now() 
    WHERE id = %s
    """

cur = conn.cursor()
cur.execute(update_qry, [list_of_str, id])
conn.commit()

Answer 2

I agree with @piro that you really want Bind Parameters, rather than attempting to do any crazy quoting.

You already know how to accomplish that when inserting one simple VARCHAR row per list element. I recommend you create a TEMP TABLE and send your data to the database in that way.

Then consult https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS and use this example to munge rows of the temp table into an array:

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');

You will want an expression like

SELECT ARRAY(SELECT my_text FROM my_temp_table);

It is possible that your temp table will also need an integer column, to preserve element order.