Contents
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.