如何使用 Python 批量插入 Oracle 数据库? [英] How can I do a batch insert into an Oracle database using Python?

问题描述

我有一些每月的天气数据要插入到 Oracle 数据库表中，但我想批量插入相应的记录以提高效率.谁能建议我如何在 Python 中执行此操作?

I have some monthly weather data that I want to insert into an Oracle database table but I want to insert the corresponding records in a batch in order to be more efficient. Can anyone advise as to how I'd go about doing this in Python?

例如，假设我的表有四个字段:一个站 ID、一个日期和两个值字段.记录由站 ID 和日期字段(复合键)唯一标识.我必须为每个站插入的值将保存在一个包含 X 个完整年份数据的列表中，例如，如果有两年的值，那么值列表将包含 24 个值.

For example let's say my table has four fields: a station ID, a date, and two value fields. The records are uniquely identified by the station ID and date fields (composite key). The values I'll have to insert for each station will be kept in a list with X number of full years worth of data, so for example if there are two years of values then the value lists will contain 24 values.

我假设如果我想一次插入一个记录，我会这样做:

I assume that below is the way I'd do this if I wanted to insert the records one at a time:

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

有没有办法做我上面正在做的事情，但以执行批量插入的方式来提高效率?顺便说一句，我的经验是使用 Java/JDBC/Hibernate，所以如果有人可以给出与 Java 方法相比的解释/示例，那么它会特别有帮助.

Is there a way to do what I'm doing above but in a way that performs a batch insert in order to increase efficiency? BTW my experience is with Java/JDBC/Hibernate so if someone can give an explanation/example which compares to the Java approach then it'd be especially helpful.

也许我需要按照此处的描述使用 cursor.executemany()?

Perhaps I need to use cursor.executemany() as described here?

预先感谢您的任何建议、意见等

Thanks in advance for any suggestions, comments, etc.

推荐答案

以下是我提出的似乎效果很好的方案(但如果有改进的方法，请发表评论):

Here's what I've come up with which appears to work well (but please comment if there's a way to improve this):

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

这篇关于如何使用 Python 批量插入 Oracle 数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！