error when connecting oracle in python using cx_Oracle

Question

I was trying to connect oracle database using python like below.

import cx_Oracle
conn = cx_Oracle.connect('user/password@host:port/database')

I've faced an error when connecting oracle. DatabaseError: DPI-1047: 64-bit Oracle Client library cannot be loaded: "libclntsh.so: cannot open shared object file: No such file or directory". See https://oracle.github.io/odpi/doc/installation.html#linux for help.

I've been struggling to figure it out. I used my user name, password, host, port and database('orcl') for example,

'admin/[email protected]:1010/orcl'.

Why coudn't it connect?

Ahh, btw I'm running all the code in azure notebooks.

Answer 1

That error indicates that you are missing a 64-bit Oracle client installation or it hasn't been configured correctly. Take a look at the link mentioned in the error message. It will give instructions on how to perform the Oracle client installation and configuration.

Answer 2

This seems a problem with version 6.X.This problem didnot appeared in 5.X.But for my case a little workaround worked.I installed in my physical machine and only thing that i need to do was a pc reboot or reopen the terminal as i have added in the path of environment variables.You can try to install in physical machine instead using azure notebooks.

Answer 3

This error come when your Oracle Client is not installed or LD_LIBRARY_PATH is not set where libclntsh.so is present.

if you have Oracle client installed then search for libclntsh.so and set the LD_LIBRARY_PATH as

"export LD_LIBRARY_PATH=/app/bds/parcels/ORACLE_INSTANT_CLIENT/instantclient_11_2:$LD_LIBRARY_PATH"

Answer 4

Here is the full program to connect Oracle using python. First, you need to install cx_Oracle. to install it fire the below command.

pip install cx_Oracle

import cx_Oracle

def get_databse_coonection():
    try:
        host='hostName'
        port ='portnumber'
        serviceName='sid of you database'
        user = 'userName'
        password = 'password'
        dns = cx_Oracle.makedsn(host,port,service_name=serviceName)
        con = cx_Oracle.connect(user, password, dns)
        cursor = con.cursor()   
        query ="select * from table"
        cursor.execute(query)
        for c in cursor:
            print(c)
    except cx_Oracle.DatabaseError as e: 
        print("There is a problem with Oracle", e) 
    finally:
        if cursor:
            cursor.close()
        if con:
            con.close()

get_databse_coonection()