psycopg2: inserta varias filas con una consulta

Necesito insertar varias filas con una consulta (el número de filas no es constante), así que necesito ejecutar una consulta como esta:

INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6); 

La única forma que conozco es

 args = [(1,2), (3,4), (5,6)] args_str = ','.join(cursor.mogrify("%s", (x, )) for x in args) cursor.execute("INSERT INTO t (a, b) VALUES "+args_str) 

Pero quiero una forma más sencilla.

Construí un progtwig que inserta varias líneas en un servidor que estaba ubicado en otra ciudad.

Descubrí que el uso de este método era aproximadamente 10 veces más rápido que la executemany . En mi caso, tup es una tupla que contiene aproximadamente 2000 filas. Tomó unos 10 segundos al usar este método:

 args_str = ','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup) cur.execute("INSERT INTO table VALUES " + args_str) 

y 2 minutos al usar este método:

 cur.executemany("INSERT INTO table VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)", tup) 

Nuevo método execute_values ​​en Psycopg 2.7:

 data = [(1,'x'), (2,'y')] insert_query = 'insert into t (a, b) values %s' psycopg2.extras.execute_values ( cursor, insert_query, data, template=None, page_size=100 ) 

La forma pythonica de hacerlo en Psycopg 2.6:

 data = [(1,'x'), (2,'y')] records_list_template = ','.join(['%s'] * len(data)) insert_query = 'insert into t (a, b) values {}'.format(records_list_template) cursor.execute(insert_query, data) 

Explicación: si los datos que se insertarán se dan como una lista de tuplas como en

 data = [(1,'x'), (2,'y')] 

entonces ya está en el formato exacto requerido como

  1. la syntax de values de la cláusula de insert espera una lista de registros como en

    insert into t (a, b) values (1, 'x'),(2, 'y')

  2. Psycopg adapta una tuple Python a un record Postgresql.

El único trabajo necesario es proporcionar una plantilla de lista de registros para que la complete psycopg

 # We use the data list to be sure of the template length records_list_template = ','.join(['%s'] * len(data)) 

y colóquelo en la consulta de insert

 insert_query = 'insert into t (a, b) values {}'.format(records_list_template) 

Imprimiendo los resultados de insert_query

 insert into t (a, b) values %s,%s 

Ahora a la sustitución habitual de los argumentos de Psycopg

 cursor.execute(insert_query, data) 

O simplemente probando lo que se enviará al servidor

 print (cursor.mogrify(insert_query, data).decode('utf8')) 

Salida:

 insert into t (a, b) values (1, 'x'),(2, 'y') 

Actualización con psycopg2 2.7:

La executemany() clásica executemany() es aproximadamente 60 veces más lenta que la implementación de @ ant32 (llamada “plegada”) como se explica en este hilo: https://www.postgresql.org/message-id/20170130215151.GA7081%40deb76.aryehleib. com

Esta implementación se agregó a psycopg2 en la versión 2.7 y se llama execute_values() :

 from psycopg2.extras import execute_values execute_values(cur, "INSERT INTO test (id, v1, v2) VALUES %s", [(1, 2, 3), (4, 5, 6), (7, 8, 9)]) 

Respuesta anterior:

Para insertar varias filas, el uso de la syntax VALUE de varias filas con execute() es aproximadamente 10 veces más rápido que el uso de psycopg2 executemany() . De hecho, executemany() simplemente ejecuta muchas INSERT individuales.

El código de @ant32 funciona perfectamente en Python 2. Pero en Python 3, cursor.mogrify() devuelve bytes, cursor.execute() toma bytes o cadenas, y ','.join() espera una instancia de str .

Así que en Python 3 es posible que necesites modificar el código de @ ant32, agregando .decode('utf-8') :

 args_str = ','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x).decode('utf-8') for x in tup) cur.execute("INSERT INTO table VALUES " + args_str) 

O utilizando solo bytes (con b'' o b"" ):

 args_bytes = b','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup) cur.execute(b"INSERT INTO table VALUES " + args_bytes) 

Un fragmento de la página del tutorial de Psycopg2 en Postgresql.org (ver abajo) :

Un último elemento que me gustaría mostrarle es cómo insertar varias filas usando un diccionario. Si tuvieras lo siguiente:

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

Puede insertar fácilmente 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) 

No guarda mucho código, pero definitivamente se ve mejor.

cursor.copy_from es la solución más rápida que he encontrado para inserciones masivas con diferencia. Aquí hay una idea que hice conteniendo una clase llamada IteratorFile que permite que un iterador produzca cadenas para que se lean como un archivo. Podemos convertir cada registro de entrada en una cadena usando una expresión generadora. Entonces la solución sería

 args = [(1,2), (3,4), (5,6)] f = IteratorFile(("{}\t{}".format(x[0], x[1]) for x in args)) cursor.copy_from(f, 'table_name', columns=('a', 'b')) 

Para este tamaño trivial de argumentos, no habrá mucha diferencia de velocidad, pero veo grandes aceleraciones cuando se trata de miles + de filas. También será más eficiente en memoria que construir una cadena de consulta gigante. Un iterador solo mantendría un registro de entrada en la memoria a la vez, donde en algún momento se quedará sin memoria en su proceso de Python o en Postgres construyendo la cadena de consulta.

Todas estas técnicas se denominan “Inserciones extendidas” en la terminología de Postgres, y hasta el 24 de noviembre de 2016, aún es mucho más rápido que el archivo de psychopg2 () y todos los demás métodos enumerados en este hilo (que probé antes de llegar a este responder).

Aquí hay un código que no usa cur.mogrify y es agradable y simplemente para entenderlo:

 valueSQL = [ '%s', '%s', '%s', ... ] # as many as you have columns. sqlrows = [] rowsPerInsert = 3 # more means faster, but with diminishing returns.. for row in getSomeData: # row == [1, 'a', 'yolo', ... ] sqlrows += row if ( len(sqlrows)/len(valueSQL) ) % rowsPerInsert == 0: # sqlrows == [ 1, 'a', 'yolo', 2, 'b', 'swag', 3, 'c', 'selfie' ] insertSQL = 'INSERT INTO "twitter" VALUES ' + ','.join(['(' + ','.join(valueSQL) + ')']*rowsPerInsert) cur.execute(insertSQL, sqlrows) con.commit() sqlrows = [] insertSQL = 'INSERT INTO "twitter" VALUES ' + ','.join(['(' + ','.join(valueSQL) + ')']*len(sqlrows)) cur.execute(insertSQL, sqlrows) con.commit() 

Pero debe tenerse en cuenta que si puede usar copy_from (), debe usar copy_from;)

Para insertar correctamente en el DB una lista de filas, utilizando el tamaño de lote dado por el usuario y con psycopg2!

 def get_batch(iterable, size=100): for i in range(0, len(iterable), size): yield iterable[i: i + size] def insert_rows_batch(table, rows, batch_size=500, target_fields=None): """ A utility method to insert batch of tuples(rows) into a table NOTE: Handle data type for fields in rows yourself as per your table columns' type. :param table: Name of the target table :type table: str :param rows: The rows to insert into the table :type rows: iterable of tuples :param batch_size: The size of batch of rows to insert at a time :type batch_size: int :param target_fields: The names of the columns to fill in the table :type target_fields: iterable of strings """ conn = cur = None if target_fields: target_fields = ", ".join(target_fields) target_fields = "({})".format(target_fields) else: target_fields = '' conn = get_conn() # get connection using psycopg2 if conn: cur = conn.cursor() count = 0 for mini_batch in get_batch(rows, batch_size): mini_batch_size = len(mini_batch) count += mini_batch_size record_template = ','.join(["%s"] * mini_batch_size) sql = "INSERT INTO {0} {1} VALUES {2};".format( table, target_fields, record_template) cur.execute(sql, mini_batch) conn.commit() print("Loaded {} rows into {} so far".format(count, table)) print("Done loading. Loaded a total of {} rows".format(count)) if cur:cur.close() if conn:conn.close() 

Si desea UPSERT (Insertar + Actualizar) también en postgres con lotes: postgres_utilities

Otro enfoque agradable y eficiente es pasar filas para su inserción como 1 argumento, que es una matriz de objetos json.

Por ejemplo, usted pasa argumento:

 [ {id: 18, score: 1}, { id: 19, score: 5} ] 

Es una matriz, que puede contener cualquier cantidad de objetos dentro. Entonces su SQL se ve como:

 INSERT INTO links (parent_id, child_id, score) SELECT 123, (r->>'id')::int, (r->>'score')::int FROM unnest($1::json[]) as r 

Aviso: Su postgress debe ser lo suficientemente nuevo, para apoyar a json

He estado usando la respuesta de ant32 por varios años. Sin embargo, he encontrado que es un error en Python 3 porque mogrify devuelve una cadena de bytes.

La conversión explícita a cadenas bytse es una solución simple para hacer que el código Python 3 sea compatible.

 args_str = b','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup) cur.execute(b"INSERT INTO table VALUES " + args_str) 

Si está utilizando SQLAlchemy, no necesita meterse con la creación manual de la cadena porque SQLAlchemy admite la generación de una cláusula VALUES varias filas para una única INSERT :

 rows = [] for i, name in enumerate(rawdata): row = { 'id': i, 'name': name, 'valid': True, } rows.append(row) if len(rows) > 0: # INSERT fails if no rows insert_query = SQLAlchemyModelName.__table__.insert().values(rows) session.execute(insert_query) 

Si desea insertar varias filas dentro de un estado de inserción (suponiendo que no está usando ORM), la forma más fácil para mí sería utilizar la lista de diccionarios. Aquí hay un ejemplo:

  t = [{'id':1, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 6}, {'id':2, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 7}, {'id':3, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 8}] conn.execute("insert into campaign_dates (id, start_date, end_date, campaignid) values (%(id)s, %(start_date)s, %(end_date)s, %(campaignid)s);", t) 

Como se puede ver solo se ejecutará una consulta:

 INFO sqlalchemy.engine.base.Engine insert into campaign_dates (id, start_date, end_date, campaignid) values (%(id)s, %(start_date)s, %(end_date)s, %(campaignid)s); INFO sqlalchemy.engine.base.Engine [{'campaignid': 6, 'id': 1, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}, {'campaignid': 7, 'id': 2, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}, {'campaignid': 8, 'id': 3, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}] INFO sqlalchemy.engine.base.Engine COMMIT 

Usando aiopg – El siguiente fragmento funciona perfectamente bien

  # items = [10, 11, 12, 13] # group = 1 tup = [(gid, pid) for pid in items] args_str = ",".join([str(s) for s in tup]) # insert into group values (1, 10), (1, 11), (1, 12), (1, 13) yield from cur.execute("INSERT INTO group VALUES " + args_str) 

Finalmente, en la versión SQLalchemy1.2, esta nueva implementación se agrega para usar psycopg2.extras.execute_batch () en lugar de ejecutar muchos cuando inicialice su motor con use_batch_mode = Verdadero como:

 engine = create_engine( "postgresql+psycopg2://scott:tiger@host/dbname", use_batch_mode=True) 

http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#change-4109

Entonces, alguien tendría que usar SQLalchmey y no se molestaría en probar diferentes combinaciones de sqla y psycopg2 y directamente SQL juntos.