examples - Insertar diccionario de Python usando Psycopg2

psycopg2 insert

Question

¿Cuál es la mejor manera de insertar un diccionario de python con muchas claves en una base de datos de Postgres sin tener que enumerar todas las claves?

Me gustaría hacer algo como ...

song = dict()
song[''title''] = ''song 1''
song[''artist''] = ''artist 1''
...
cursor.execute(''INSERT INTO song_table (song.keys()) VALUES (song)'')

Answer 1

Algo en este sentido debería hacerlo:

song = dict()
song[''title''] = ''song 1''
song[''artist''] = ''artist 1''
cols=song.keys();
vals = [song[x] for x in cols]
vals_str_list = ["%s"] * len(vals)
vals_str = ", ".join(vals_str_list)
cursor.execute("INSERT INTO song_table ({cols}) VALUES ({vals_str})".format(
cols = cols, vals_str = vals_str), vals)

La parte clave es la cadena generada de %s elementos, y usándola en format , con la lista pasada directamente a la llamada de execute , para que psycopg2 pueda interpolar cada elemento en la lista de valores (evitando así una posible inyección de SQL ).

Otra variación, al pasar el dict de execute , sería usar estas líneas en lugar de vals , vals_str_list y vals_str desde arriba:

vals_str2 = ", ".join(["%({0})s".format(x) for x in cols])
cursor.execute("INSERT INTO song_table ({cols}) VALUES ({vals_str})".format(
cols = cols, vals_str = vals_str2), song)

Answer 2

El nuevo módulo sql se creó para este propósito y se agregó en la versión 2.7 de psycopg2. Según la documentación:

Si necesita generar dinámicamente una consulta SQL (por ejemplo, elegir dinámicamente un nombre de tabla) puede usar las facilidades proporcionadas por el módulo psycopg2.sql.

Dos ejemplos se dan en la documentación: http://initd.org/psycopg/docs/sql.html

names = [''foo'', ''bar'', ''baz'']
q1 = sql.SQL("insert into table ({}) values ({})").format(
sql.SQL('', '').join(map(sql.Identifier, names)),
sql.SQL('', '').join(sql.Placeholder() * len(names)))
print(q1.as_string(conn))

insertar en la tabla ("foo", "bar", "baz") los valores (% s,% s,% s)

q2 = sql.SQL("insert into table ({}) values ({})").format(
sql.SQL('', '').join(map(sql.Identifier, names)),
sql.SQL('', '').join(map(sql.Placeholder, names)))
print(q2.as_string(conn))

insertar en la tabla ("foo", "bar", "baz") valores (% (foo) s,% (bar) s,% (baz) s)

Aunque la concatenación de cuerdas produciría el mismo resultado, no debe utilizarse para este propósito, de acuerdo con la documentación de psycopg2:

Advertencia : Nunca, nunca , NUNCA utilice concatenación de cadenas de Python ( + ) o interpolación de parámetros de cadenas ( % ) para pasar variables a una cadena de consulta SQL. Ni siquiera a punta de pistola.

Answer 3

También puede insertar varias filas utilizando un dictionary . Si tuvieras lo siguiente:

namedict = ({"first_name":"Joshua", "last_name":"Drake"},
{"first_name":"Steven", "last_name":"Foo"},
{"first_name":"David", "last_name":"Bar"})

Puedes insertar las tres filas dentro del diccionario usando:

cur = conn.cursor()
cur.executemany("""INSERT INTO bar(first_name,last_name) VALUES (%(first_name)s, %(last_name)s)""", namedict)

La instrucción cur.executemany automáticamente a través del diccionario y ejecutará la consulta INSERT para cada fila.

PD: Este ejemplo es tomado de here

Answer 4

otro enfoque para la consulta a mySQL o pgSQL desde el diccionario es con la construcción %(dic_key)s , será reemplazado por el valor del diccionario correspondiente por dic_key como {''dic_key'': ''dic value''} funcionando perfectamente, y evitará la prueba de sqlInjection: Python 2.7 ver abajo:

# in_dict = {u''report_range'': None, u''report_description'': None, ''user_id'': 6, u''rtype'': None, u''datapool_id'': 1, u''report_name'': u''test suka 1'', u''category_id'': 3, u''report_id'': None} cursor.execute(''INSERT INTO report_template (report_id, report_name, report_description, report_range, datapool_id, category_id, rtype, user_id) VALUES '' / ''(DEFAULT, %(report_name)s, %(report_description)s, %(report_range)s, %(datapool_id)s, %(category_id)s, %(rtype)s, %(user_id)s) '' / ''RETURNING "report_id";'', in_dict) OUT: INSERT INTO report_template (report_id, report_name, report_description, report_range, datapool_id, category_id, rtype, user_id) VALUES (DEFAULT, E''test suka 1'', NULL, NULL, 1, 3, NULL, 6) RETURNING "report_id";

Answer 5

from psycopg2.extensions import AsIs
song = {
''title'': ''song 1'',
''artist'': ''artist 1''
}
columns = song.keys()
values = [song[column] for column in columns]
insert_statement = ''insert into song_table (%s) values %s''
# cursor.execute(insert_statement, (AsIs('',''.join(columns)), tuple(values)))
print cursor.mogrify(insert_statement, (AsIs('',''.join(columns)), tuple(values)))

Huellas dactilares:

insert into song_table (artist,title) values (''artist 1'', ''song 1'')

Psycopg adapta una tuple a un record y AsIs hace lo que haría la sustitución de cadenas de Python.