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

У меня есть месячные данные о погоде, которые я хочу вставить в таблицу базы данных Oracle, но я хочу вставить соответствующие записи в пакет, чтобы быть более эффективными. Может ли кто-нибудь посоветовать, как я буду делать это в Python?

Например, скажем, моя таблица имеет четыре поля: идентификатор станции, дату и два поля значений. Записи однозначно идентифицируются по идентификаторам станции и по дате (составному ключу). Значения, которые мне нужно будет вставлять для каждой станции, будут храниться в списке с Х-числом полной стоимости лет, поэтому, например, если есть два года значений, то списки значений будут содержать 24 значения.

Я предполагаю, что ниже я буду делать это, если захочу вставить записи по одному:

connection_string = "scott/[email protected]"
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()

Есть ли способ сделать то, что я делаю выше, но таким образом, что выполняет пакетную вставку для повышения эффективности? Кстати, мой опыт связан с Java/JDBC/Hibernate, поэтому, если кто-то может дать объяснение/пример, который сравнивается с подходом Java, тогда это было бы особенно полезно.

EDIT: Возможно, мне нужно использовать cursor.executemany(), как описано здесь?

Заранее благодарим за любые предложения, комментарии и т.д.

Ответ 1

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

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

Ответ 2

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

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

Cursor.prepare (statement [, tag])

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

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

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

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

connection_string = "scott/[email protected]"
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.

Ответ 3

Я бы создал большой оператор вставки 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 и передать ее oracle как один оператор для выполнения.

Ответ 4

Как говорится в одном из комментариев, используйте 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

Ответ 5

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

Я вставляю в 5000 строк. 3 столбца на строку.

запустить вставку 5000 раз, она стоит 1,24 минуты.
выполняется с исполнением, это стоит 0.125 секунд.
запустить с вставкой весь код: он стоит 4.08 минут.

код python, который настраивает sql как вставить все в t (a, b, c) выберите: 1,: 2,: 3 из двойного союза все выберите: 4,: 5:: 6 от daul...

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

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

в третьем случае, я проверил сторону базы данных, событие ожидания - это "SQL * Net больше данных от клиента". что означает, что сервер ожидает больше данных от клиента.

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

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