installed - Psycopg2, Postgresql, Python: la forma más rápida de inserción masiva

psycopg2 python 3

Question

Estoy buscando la manera más eficiente de insertar a granel algunos millones de tuplas en una base de datos. Estoy usando Python, PostgreSQL y psycopg2 .

He creado una larga lista de tulipanes que deberían insertarse en la base de datos, a veces con modificadores como Simplify geométrico.

La forma ingenua de hacerlo sería formatear en cadena una lista de INSERT , pero hay otros tres métodos sobre los que he leído:

1. Uso de estilo de enlace de pyformat para inserción paramétrica
2. Usando executemany en la lista de tuplas, y
3. Usando escribir los resultados en un archivo y usando COPY .

Parece que la primera forma es la más eficiente, pero agradecería sus ideas y fragmentos de código diciéndome cómo hacerlo bien.

Answer 1

Cualquiera que use SQLalchemy podría probar la versión 1.2, que agregó soporte de inserción masiva para usar psycopg2.extras.execute_batch () en lugar de executemany cuando inicializa su motor con use_batch_mode = True 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 debería usar SQLalchmey, no se molestará en probar diferentes combinaciones de sqla y psycopg2 y dirigir SQL juntos.

Answer 2

Después de algunas pruebas, lo más unnest parece ser una opción extremadamente rápida, como aprendí de la respuesta de @Clodoaldo Neto a una pregunta similar.

data = [(1, 100), (2, 200), ...]  # list of tuples

cur.execute("""CREATE TABLE table1 AS
               SELECT u.id, u.var1
               FROM unnest(%s) u(id INT, var1 INT)""", (data,))

Sin embargo, puede ser complicado con datos extremadamente grandes .

Answer 3

El primero y el segundo se usarían juntos, no por separado. El tercero sería el servidor más eficiente, ya que el servidor haría todo el trabajo duro.

Answer 4

Hay un nuevo manual de psycopg2 que contiene ejemplos para todas las opciones.

La opción COPY es la más eficiente. Entonces el ejecutormany. Luego, ejecuta con pyformat.

Answer 5

Puede usar una nueva biblioteca de inserción :

$ pip install upsert

(Puede que tenga que pip install decorator primero)

conn = psycopg2.connect(''dbname=mydatabase'')
cur = conn.cursor()
upsert = Upsert(cur, ''mytable'')
for (selector, setter) in myrecords:
    upsert.row(selector, setter)

Donde selector es un objeto dict como {''name'': ''Chris Smith''} y setter es un dict como { ''age'': 28, ''state'': ''WI'' }

Es casi tan rápido como escribir código personalizado INSERTAR [/ ACTUALIZAR] y ejecutarlo directamente con psycopg2 ... y no explotará si la fila ya existe.

Answer 6

Sí, votaría por COPY, siempre que pueda escribir un archivo en el disco duro del servidor (no en la unidad en la que se ejecuta la aplicación), ya que COPY solo leerá en el servidor.

Answer 7

Una pregunta muy relacionada: inserción masiva con SQLAlchemy ORM

Todos los caminos conducen a Roma , pero algunos de ellos cruzan montañas, requieren transbordadores, pero si quiere llegar rápidamente, tome la autopista.

En este caso, la autopista utilizará la función execute_batch() de initd.org/psycopg . La documentación dice que es la mejor:

La implementación actual de executemany() es (utilizando una subestimación extremadamente caritativa) que no funciona particularmente. Estas funciones se pueden usar para acelerar la ejecución repetida de una declaración contra un conjunto de parámetros. Al reducir el número de viajes de ida y vuelta del servidor, el rendimiento puede ser de órdenes de magnitud mejor que con executemany() .

En mi propia prueba execute_batch() es aproximadamente dos veces más rápido que executemany() , y le da la opción de configurar el tamaño de la página para realizar más ajustes (si desea exprimir el último 2-3% de rendimiento del controlador).

La misma función se puede habilitar fácilmente si está utilizando SQLAlchemy estableciendo use_batch_mode=True como parámetro cuando use_batch_mode=True una instancia del motor con create_engine()

Answer 8

en mi experiencia, executemany no es más rápido que ejecutar muchos insertos, la forma más rápida es formatear un solo INSERT con muchos valores, tal vez en el futuro, executemany lo mejoren, pero por ahora es bastante lento

Subclase una list y sobrecargo el método de agregar, de modo que cuando la lista alcanza un cierto tamaño, formateo el INSERT para ejecutarlo