Как я могу выполнить пакетную вставку в базу данных Oracle с помощью Python?

Вот что я придумал и который, похоже, работает хорошо (но, пожалуйста, прокомментируйте, есть ли способ улучшить это):

# 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()

Используйте Cursor.prepare() и Cursor.executemany().

Из документации cx_Oracle:

Cursor.prepare (инструкция [, тег])

Это можно использовать перед вызовом execute () для определения оператора, который будет выполняться. Когда это будет сделано, этап подготовки не будет выполняться, если вызов execute () выполняется с параметром None или тем же строковым объектом, что и оператор. [...]

Cursor.executemany (инструкция, параметры)

Подготовьте оператор для выполнения в базе данных, а затем выполните его для всех сопоставлений параметров или последовательностей, найденных в параметрах последовательности. Оператор управляется так же, как и метод execute ().

Таким образом, используя две указанные выше функции, ваш код становится:

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()

См. Также серию Oracle Освоение Oracle + Python статьи.

fyi мой результат теста:

Вставляю в 5000 рядов. 3 столбца в строке.

1. запустить insert 5000 раз, это стоит 1,24 минуты.
2. запустить с executemany, это стоит 0,125 секунды.
3. запустить со вставкой всего кода: это стоит 4,08 минуты.

код python, который настраивает sql как вставить все в t (a, b, c) select: 1,: 2,: 3 from dual union all select: 4,: 5:: 6 from daul ...

Код python для установки этого длинного sql стоит 0,145329 секунды.

Я тестирую свой код на очень старой солнечной машине. процессор: 1415 MH.

в третьем случае я проверил сторону базы данных, событие ожидания - «SQL * Net more data from client». Это означает, что сервер ожидает дополнительных данных от клиента.

Результат третьего метода для меня без теста невероятен.

так что короткое предложение от меня - просто использовать executemany.

Как говорится в одном из комментариев, подумайте об использовании INSERT ALL. Предположительно это будет значительно быстрее, чем при использовании executemany().

Например:

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

Я бы создал большой оператор вставки SQL с помощью 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

Вы можете построить строку в Python и передать ее оракулу как один оператор для выполнения.