¿Cómo puedo hacer una inserción por lotes en una base de datos Oracle utilizando Python?

python-2.7 cx-oracle

Question

Tengo algunos datos meteorológicos mensuales que quiero insertar en una tabla de base de datos de Oracle, pero quiero insertar los registros correspondientes en un lote para ser más eficiente. ¿Alguien puede aconsejarme sobre cómo haré esto en Python?

Por ejemplo, digamos que mi tabla tiene cuatro campos: una ID de estación, una fecha y dos campos de valor. Los registros se identifican de manera exclusiva por la ID de la estación y los campos de fecha (clave compuesta). Los valores que tendré que insertar para cada estación se mantendrán en una lista con un número X de años completos de datos, por lo que, por ejemplo, si hay dos años de valores, las listas de valores contendrán 24 valores.

Supongo que a continuación es la forma en que haría esto si quisiera insertar los registros de a uno por vez:

connection_string = "scott/tiger@testdb"
connection = cx_Oracle.Connection(connection_string)
cursor = cx_Oracle.Cursor(connection)
station_id = ''STATION_1''
start_year = 2000

temps = [ 1, 3, 5, 7, 9, 1, 3, 5, 7, 9, 1, 3 ]
precips = [ 2, 4, 6, 8, 2, 4, 6, 8, 2, 4, 6, 8 ]
number_of_years = len(temps) / 12
for i in range(number_of_years):
    for j in range(12):
        # make a date for the first day of the month
        date_value = datetime.date(start_year + i, j + 1, 1)
        index = (i * 12) + j
        sql_insert = ''insert into my_table (id, date_column, temp, precip) values (%s, %s, %s, %s)'', (station_id, date_value, temps[index], precips[index]))
        cursor.execute(sql_insert)
connection.commit()

¿Hay alguna manera de hacer lo que estoy haciendo arriba, pero de una manera que realiza una inserción de lote para aumentar la eficiencia? Por cierto, mi experiencia es con Java / JDBC / Hibernate por lo que si alguien puede dar una explicación / ejemplo que se compare con el enfoque de Java, sería especialmente útil.

EDITAR: ¿Quizás necesite usar cursor.executemany () como se describe aquí ?

Gracias de antemano por cualquier sugerencia, comentario, etc.

Answer 1

Como dice uno de los comentarios, considere usar INSERT ALL . Supuestamente será significativamente más rápido que usar executemany() .

Por ejemplo:

INSERT ALL
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;

http://www.techonthenet.com/oracle/questions/insert_rows.php

Answer 2

Crearía una declaración de inserción de SQL grande usando union:

insert into mytable(col1, col2, col3)
select a, b, c from dual union
select d, e, f from dual union
select g, h, i from dual

Puedes construir la cadena en python y dársela a Oracle como una declaración para ejecutar.

Answer 3

Esto es lo que se me ocurrió que parece funcionar bien (pero por favor coméntenos si hay una manera de mejorar esto):

# build rows for each date and add to a list of rows we''ll use to insert as a batch 
rows = [] 
numberOfYears = endYear - startYear + 1
for i in range(numberOfYears):
    for j in range(12):
        # make a date for the first day of the month
        dateValue = datetime.date(startYear + i, j + 1, 1)
        index = (i * 12) + j
        row = (stationId, dateValue, temps[index], precips[index])
        rows.append(row)

# insert all of the rows as a batch and commit
ip = ''192.1.2.3'' 
port = 1521
SID = ''my_sid''
dsn = cx_Oracle.makedsn(ip, port, SID)
connection = cx_Oracle.connect(''username'', ''password'', dsn)
cursor = cx_Oracle.Cursor(connection)
cursor.prepare(''insert into '' + database_table_name + '' (id, record_date, temp, precip) values (:1, :2, :3, :4)'')
cursor.executemany(None, rows)
connection.commit()
cursor.close()
connection.close()

Answer 4

Use Cursor.prepare() y Cursor.executemany() .

De la documentación de cx_Oracle :

Cursor.prepare ( instrucción [, etiqueta ])

Esto se puede usar antes de una llamada a execute () para definir la declaración que se ejecutará. Cuando se hace esto, la fase de preparación no se realizará cuando la llamada a execute () se realice con None o con el mismo objeto de cadena que la instrucción. [...]

Cursor.executemany ( instrucción , parámetros )

Prepare una declaración para ejecución contra una base de datos y luego ejecútela contra todas las asignaciones de parámetros o secuencias encontradas en los parámetros de secuencia. La sentencia se gestiona de la misma manera que el método execute () la gestiona.

Por lo tanto, utilizando las dos funciones anteriores, su código se convierte en:

connection_string = "scott/tiger@testdb"
connection = cx_Oracle.Connection(connection_string)
cursor = cx_Oracle.Cursor(connection)
station_id = ''STATION_1''
start_year = 2000

temps = [ 1, 3, 5, 7, 9, 1, 3, 5, 7, 9, 1, 3 ]
precips = [ 2, 4, 6, 8, 2, 4, 6, 8, 2, 4, 6, 8 ]
number_of_years = len(temps) / 12

# list comprehension of dates for the first day of the month
date_values = [datetime.date(start_year + i, j + 1, 1) for i in range(number_of_years) for j in range(12)]

# second argument to executemany() should be of the form:
# [{''1'': value_a1, ''2'': value_a2}, {''1'': value_b1, ''2'': value_b2}]
dict_sequence = [{''1'': date_values[i], ''2'': temps[i], ''3'': precips[i]} for i in range(1, len(temps))]

sql_insert = ''insert into my_table (id, date_column, temp, precip) values (%s, :1, :2, :3)'', station_id)
cursor.prepare(sql_insert)
cursor.executemany(None, dict_sequence)
connection.commit()

Consulte también la serie de artículos Oracle''s Mastering Oracle + Python .

Answer 5

fyi mi resultado de la prueba:

Inserté en 5000 filas. 3 columnas por fila.

1. ejecutar inserción 5000 veces, cuesta 1,24 minutos.
2. ejecutar con executemany, cuesta 0.125 segundos.
3. ejecutar con un código insert all: cuesta 4.08 minutos.

código python, que configura el sql como insertar todo en t (a, b, c) seleccionar: 1,: 2,: 3 de dual union all select: 4,: 5:: 6 from daul ...

El código python para configurar este sql largo cuesta 0.145329 segundos.

Probé mi código en una máquina solar muy antigua. CPU: 1415 MH.

en el tercer caso, verifiqué el lado de la base de datos, el evento de espera es "SQL * Net más datos del cliente". lo que significa que el servidor está esperando más datos del cliente.

El resultado del tercer método es increíble para mí sin la prueba.

así que la breve sugerencia de mi parte es solo utilizar executemany.