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’.
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()
import cx_Oracle
dataset =[
[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.
