cx_Oracle 插入大量数据

通过 cx_Oracle 向数据库中写入大量数据

# -*- coding: utf-8 -*-
"""
性能测试
向Oracle插入 63391 行，耗时 3.03 秒
向Oracle插入 10439134 行，耗时 486.81 秒
"""

import time
import cx_Oracle


user = "****"
pw = "*****"
dsn = "14.29.1.27:11526/orcl"

# 控制每批次插入的数量
batch = 5000

connection = cx_Oracle.connect(user, pw, dsn, encoding = "UTF-8", nencoding = "UTF-8")

cursor = connection.cursor()

sql = '''
    INSERT INTO hsbi.hs_add_data_t1 
    (acc_nbr, created_date, completed_date, area_id, area_name, stb_code, stb_prop, group_id, group_name, name, cust_contact_tel)
    VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)
'''

        
start = time.time()

# 定义需要插入的文本路径
path = u'C:/Users/yepeng/Desktop/Samples/stock_data.txt'

dataset = list()

try:
    with open(path, 'r', encoding='UTF-8') as reader:
        for index, line in enumerate(reader):
            dataset.append(tuple(line.split('|')))
            if (index + 1) % batch == 0: 
                cursor.executemany(sql, dataset)
                connection.commit()
                dataset.clear()
                continue
except Exception as e:
    print(e)
finally:
    cursor.executemany(sql, dataset)
    connection.commit()
    dataset.clear()
    cursor.close()
    connection.close()

elapsed = (time.time() - start)

print('向Oracle插入 {} 行，耗时 {} 秒'.format(index+1, elapsed))