Lista de Python para PostgreSQL Array

Tengo una lista:

[u'ABC', u'DEF', u'GHI'] 

Tengo que insertarlo en una matriz postgresql: ( ALTER TABLE "aTable" ADD COLUMN "Test" text[]; )

La syntax para agregar datos al postgresql es:

 update "aTable" SET "Test" = '{"ABC", "DEF", "GHI"}' 

¿Cómo puedo convertir la lista al formato correcto?

 list = [u'ABC', u'DEF', u'GHI'] list = str(map(str, list)) list = list.replace('[', '{').replace(']', '}').replace('\'', '\"') query = '''update "aTable" SET "Test" = '%s\'''' %(list) print query 

Resultará en que,

 update "aTable" SET "Test" = '{"ABC", "DEF", "GHI"}' 

Tenga en cuenta que con psycopg2 no necesita realizar ningún procesamiento de cadenas para los arreglos. Esto se considera una mala práctica, ya que es propenso a errores y, en el peor de los casos, puede provocar la apertura de ataques de inyección. Siempre debe utilizar parámetros enlazados. En el código a continuación, crearé una nueva tabla con una sola columna con el tipo TEXT[] (como en su pregunta original). Luego agregaré una nueva fila y actualizaré todas ellas. Así que verá una operación INSERT y UPDATE (aunque ambas son prácticamente idénticas).

Sin embargo, si se actualiza con solo un valor, hay una cur.execute : cur.execute espera que la statement de SQL sea el primer argumento y una iterable que contenga los parámetros que se unirán como segundo argumento. Lo siguiente no funcionará:

 stmt = 'UPDATE foo SET example_value=%s' new_values = ['a', 'b', 'c'] cur.execute(stmt, (new_values)) 

La razón es que (new_values) es visto por python como new_values (los parens se eliminan en este caso, no se ven como tuplas). Esto dará como resultado el error de que proporcione 3 valores ( 'a' , 'b' y 'c' ) como valores a vincular, pero solo hay un marcador de posición ( %s ) en la consulta. En su lugar, debe especificarlo de la siguiente manera (observe la coma agregada al final):

 stmt = 'UPDATE foo SET example_value=%s' new_values = ['a', 'b', 'c'] cur.execute(stmt, (new_values,)) 

Esto hará que Python vea (new_values,) como una tupla (que es iterable) con un elemento, que coincide con los marcadores de posición de la consulta. Para obtener una explicación más detallada de la coma final, consulte los documentos oficiales sobre las tuplas .

Alternativamente, también puede escribir [new_values] lugar de (new_values,) , pero, en mi opinión, (new_values,) está más limpio ya que las tuplas son inmutables, mientras que las listas son mutables.


Aquí está la tabla con la que he probado:

 CREATE TABLE foo ( values TEXT[] ); 

Y aquí está el código Python insertando y actualizando valores:

 from psycopg2 import connect conn = connect('dbname=exhuma') cur = conn.cursor() cur.execute('INSERT INTO foo VALUES (%s)', (['a', 'b'], )) print('>>> Before update') cur.execute('SELECT * FROM foo') for row in cur: print(type(row[0]), repr(row[0])) print('>>> After update') cur.execute('UPDATE foo SET example_values = %s', (['new', 'updated', 'values'],)) cur.execute('SELECT * FROM foo') for row in cur: print(type(row[0]), repr(row[0])) cur.close() conn.commit() conn.close() 

En cada ejecución, el código insertará una nueva fila con los mismos valores de matriz, luego ejecutará una actualización sin la cláusula WHERE , por lo que todos los valores se actualizarán. Después de un par de ejecuciones, esto da el siguiente resultado:

 >>> Before update (, "['new', 'updated', 'values']") (, "['new', 'updated', 'values']") (, "['new', 'updated', 'values']") (, "['new', 'updated', 'values']") (, "['new', 'updated', 'values']") (, "['a', 'b']") >>> After update (, "['new', 'updated', 'values']") (, "['new', 'updated', 'values']") (, "['new', 'updated', 'values']") (, "['new', 'updated', 'values']") (, "['new', 'updated', 'values']") (, "['new', 'updated', 'values']") 

Simplemente pase esa lista como un parámetro para execute . No necesitas hacer nada especial. Psycopg convierte las listas de Python en un literal de matriz PostgreSQL adecuado.

 import psycopg2 as dbapi conn = dbapi.connect(dsn='') c = conn.cursor() x = [u'ABC', u'DEF', u'GHI'] c.execute('update "aTable" SET "Test" = %s', [x]) 

La forma correcta de manejar las variables en SQL es el uso de variables de enlace. Que yo sepa, esto es crucial para MySQL y Oracle DB, y creo que para PostgreSQL también.

Así que mejor código estaría en la línea

 def as_array(l): l2str = ','.join('"{}"'.format(x) for x in l) return '{{{}}}'.format(l2str) query = '''update "aTable" SET "Test" = %s''' lst = [u'ABC', u'DEF', u'GHI'] cur = conn.cursor() cur.execute(query, as_array(lst)) 

donde conn es una conexión a db PostgreSQL.