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.