Trying to import from CSV to Oracle Database in Python

Question

I am new to Python, and was trying to insert excel sheet contents into an oracle database using Python.

I use PowerShell

Here is what I have tried so far without any luck:

import cx_Oracle
import csv
import sys

cx_Oracle.init_oracle_client(lib_dir=r"C:\Users\User\AppData\Local\Programs\Python\Python39")
connection = cx_Oracle.connect( user="username", password="password", dsn="hostname:port/dbname")
cursor=connection.cursor()
    

with open('c:\oracle\output.csv') as f:

reader=csv.DictReader(f,delimiter=',')
for row in reader:
sqlquery="INSERT INTO table VALUES (%d,'%s','%s','%s')" %(x,row['ORG_NAME'],row['ID'],row['ORGANIZATION_ID'])
cursor.execute(sqlquery)
x=x+1

conn.commit()

Excel sheet only has 3 rows though I am doing it for a test.

Any hints would be much appreciated.

Also tried csv2db but always get the following error:

Error connecting to the database: DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library

I don't get this error when I connect to the database from Python, but when I do csv2db from Powershell I always get this error.

Thanks for taking care if it. Sorry its my first time posting here. — Nashnash
– Nashnash, Commented Jul 9, 2021 at 23:26
I don't understand what is your problem. If you don't get error message then what is the problem? If it doesn't insert data then maybe check if you really run code. And you should rather ise execute(query, values) instead of using % to generate SQL — furas
– furas, Commented Jul 10, 2021 at 1:02
what is csv2db? Is this some program installed with Oracle or script in Python? Error can means you have to install some program, and it has to be 64-bit version, not 32-bit version. — furas
– furas, Commented Jul 10, 2021 at 1:04
When installing Oracle Instant Client, make sure you add sqlplus to the mix. From within Powershell, make sure you can launch sqlplus and connect to the database. I mean, you need to fix you PS environment issue "cannot locate client library". From there on you do a test: PS C:\>python -c "import cx_Oracle". When your environment is right, csv2db will work. — Bjarte Brandt
– Bjarte Brandt, Commented Jul 10, 2021 at 8:47

Christopher Jones · Accepted Answer · 2021-07-10 07:03:52Z

1

If you want to read a CSV file, you could do worse than reading the cx_Oracle manual Loading CSV Files into Oracle Database:

import cx_Oracle
import csv

. . .

# Predefine the memory areas to match the table definition
cursor.setinputsizes(None, 25)

# Adjust the batch size to meet your memory and performance requirements
batch_size = 10000

with open('testsp.csv', 'r') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',')
    sql = "insert into test (id,name) values (:1, :2)"
    data = []
    for line in csv_reader:
        data.append((line[0], line[1]))
        if len(data) % batch_size == 0:
            cursor.executemany(sql, data)
            data = []
    if data:
        cursor.executemany(sql, data)
    con.commit()

answered Jul 10, 2021 at 7:03

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Christopher Jones Over a year ago

Update your question with details such as what "didn't work" and we will help.

Nashnash Over a year ago

Think I am going to take a different route where I am going to import the CSV using SQL Developer

Christopher Jones Over a year ago

I suggest using Oracle's SQL*Loader which is purpose designed for the task of loading data, and has various options for doing so very quickly. It is free as part of Oracle Instant Client.

Collectives™ on Stack Overflow

Trying to import from CSV to Oracle Database in Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related