如何使用Python批量插入Oracle数据库？

Question

我有一些每月的天气数据，想把它们放进一个Oracle数据库的表里，但我希望能批量插入，这样效率会更高。有没有人能告诉我在Python中该怎么做？

比如说，我的表有四个字段：一个站点ID，一个日期，还有两个数值字段。记录是通过站点ID和日期这两个字段唯一识别的（这叫复合键）。我需要插入的每个站点的值会保存在一个列表里，里面有X年完整的数据，比如如果有两年的数据，那么这个值列表就会有24个值。

我想如果我想一个一个插入记录，下面的方式是可以的：

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方法对比的解释或例子，那就特别有帮助了。

编辑：也许我需要使用cursor.executemany()，就像这里描述的那样？

提前感谢任何建议、评论等。

Answer 1

正如其中一个评论所说，可以考虑使用 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

Answer 2

使用 Cursor.prepare() 和 Cursor.executemany()。

根据 cx_Oracle 的文档：

Cursor.prepare(statement[, tag])

这个方法可以在调用 execute() 之前使用，用来定义要执行的语句。当你这样做时，如果在调用 execute() 时传入 None 或者和语句相同的字符串，就不需要再进行准备阶段了。[...]

Cursor.executemany(statement, parameters)

这个方法是为了准备一个要在数据库上执行的语句，然后对所有在参数序列中的参数映射或序列进行执行。这个语句的管理方式和 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 系列文章。

Answer 3

这是我想到的一个方法，看起来效果不错（不过如果有更好的方法，请大家留言告诉我）：

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