Connection with Oracle cx_Oracle problem with AWS Glue Python Shell

Question

I am working on AWS Glue Python Shell. I want to connect python shell with Oracle. I am successful installing psycopg2 and mysql libraries but when I tried to connect Oracle using cx_Oracle, I have successfully installed the library but I am facing the error

DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory"

I have tried following things

I have downloaded so files from S3 and placed it in lib folder in parallel to the code file
I have set the LD_LIBRARY_PATH, ORACLE_HOME using os.environ

I am using following code

import boto3
import os
import sys
import site
from setuptools.command import easy_install

s3 = boto3.client('s3')
dir_path = os.path.dirname(os.path.realpath(__file__))
#os.path.dirname(sys.modules['__main__'].__file__)

install_path = os.environ['GLUE_INSTALLATION']
easy_install.main( ["--install-dir", install_path, "cx_Oracle"] )

importlib.reload(site)

import cx_Oracle

conn_str = u'{username}/{password}@{host}:{port}/{sid}'
conn = cx_Oracle.connect(conn_str)
c = conn.cursor()
c.execute(u'select * from hr.countries')
for row in c:
    print(row[0], "-", row[1])
conn.close()
print('hello I am here');

I should be able to connect with oracle on aws glue python shell

LD_LIBRARY_PATH needs to be set before the process starts. If you're using Oracle Instant Client, then don't set ORACLE_HOME. Check the instructions on whichever OS download page you got Instant Client from oracle.com/database/technologies/instant-client/downloads.html — Christopher Jones
I have tried to set LD_LIBRARY_PATH using os.environ['LD_LIBRARY_PATH'] = 'path/so/files' but not working on aws glue python shell — Nouman Khalid
os.environ() is evaluated after the process starts, isn't it? LD_LIBRARY_PATH needs to be set before it starts. — Christopher Jones

Harjeet Singh Harjeet Singh · Accepted Answer · 2019-12-24T10:03:20

As it has already been mentioned in the responses. LD_LIBRARY_PATH needs to be set before the script starts. So, a way to avoid using LD_LIBRARY_PATH is setting rpath in the so file. Below are the steps needed.

You will need to update rpath in your so file. This can be done using patchelf package.

Please also include your libaio.so.1 in your so files which you might have generated by running sudo apt-get install libaio1

Installing patchelf

sudo apt-get update sudo apt-get install patchelf

To update rpath to your lib directory

patchelf --set-rpath <absolute_path_to_library_dir> libclntsh.so

Upload the so files with updated rpath to your glue env lib directory.

In your script you can then load the library.

from ctypes import *
cdll.LoadLibrary('<absolute_path_to_library_dir>/libclntsh.so')

Connection with Oracle cx_Oracle problem with AWS Glue Python Shell

1 Answers