如何使用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屋！