Bulk insert to Oracle with Python
Overview
There is an example code to insert data in bulk to Oracle database with python cx_Oracle
Environments
python 3.7.3
Oracle 18c Express Edition
Step1 : Creating the table
Creating the table ‘oracle_insert’ in the schema ‘USER01’.
01 02 03 04 05 | CREATE TABLE USER01.ORACLE_INSERT( col1 int ,col2 int ,col3 int) |
Step2 : Insert in bulk with executemany
- dataset is the dataset to be inserted
- Making a connection to database with cx_Oracle
- Inserting in bulk with using cur.executemany()
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | import cx_Oracledataset =[ [1,2,3] ,[4,5,6] ,[7,8,9] ,[10,11,12] ,[13,14,15] ,[16,17,18] ,[19,20,21]] HOST = 'localhost'PORT = '1521'DB_NAME = 'xepd1'USER = 'user01'PASSWORD = 'password01'SERVICE_NAME = 'xepdb1' tns = cx_Oracle.makedsn(HOST, PORT, service_name =SERVICE_NAME)conn = cx_Oracle.connect(USER,PASSWORD,tns)cur = conn.cursor()cur.executemany("insert into user01.oracle_insert (col1, col2, col3) values (:1, :2 ,:3)", dataset)conn.commit()conn.close() |
Summary
The code to insert some rows in bulk is not difficult and simple. But the performance about it is not tested in here, I’ll try it in future.
