通过python加速对Oracle数据库的加载

Question

我正试图使用cx_Oracle和python将csv文件转储到我的表中。但速度慢得令人难以忍受(336秒内创下500条记录(。如果有比这更快的其他方法，请告诉我。代码低于

import pandas as pd
import cx_Oracle
import time

connection_string = '{}/{}@//{}:{}/{}'.format(user_name, password, host_name, port, service_name)
engine = cx_Oracle.connect(connection_string)

start_time = time.time()
t = pd.read_sql(con=engine, sql='select * from students where rownum < 18000')
print(t.shape)
t.to_sql(con=engine, name='students_new', if_exists='append', index=False)
print("Finished in : " + str(round(time.time() - start_time, 2)))

Answer 1

您给出的示例代码与书面问题不匹配。

如果要使用Python将数据从CSV文件加载到Oracle数据库中，直接的cx_Oracle示例位于手册的"将CSV文件加载至Oracle数据库"部分。您需要使用executemany()在每次调用数据库时上传尽可能多的数据。

剪切&从手册粘贴：

import cx_Oracle
import csv
. . .
# Predefine the memory areas to match the table definition
cursor.setinputsizes(None, 25)
# Adjust the batch size to meet your memory and performance requirements
batch_size = 10000
with open('testsp.csv', 'r') as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',')
sql = "insert into test (id,name) values (:1, :2)"
data = []
for line in csv_reader:
data.append((line[0], line[1]))
if len(data) % batch_size == 0:
cursor.executemany(sql, data)
data = []
if data:
cursor.executemany(sql, data)
con.commit()