pythonpandasoracle-databasesqlalchemyinstantclient

Extract data from Oracle Database with Pandas without Oracle Instant Client


I'm trying to connect to an Oracle Database using pandas + sqlalchemy using this code:

from sqlalchemy.engine import create_engine
import pandas as pd 
DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'USER'   
PASSWORD = 'PASS'   
HOST = 'HOST'       
PORT = 1521         
SERVICE = 'Service' 
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICE
engine = create_engine(ENGINE_PATH_WIN_AUTH)
#test query
test_df = pd.read_sql_query('SELECT * FROM table a WHERE rownum < 2', engine)

But I'm receiving this message:

DatabaseError: (cx_Oracle.DatabaseError) DPI-1047: Cannot locate a 64-bit Oracle Client library: "The specified module could not be found". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help (Background on this error at: https://sqlalche.me/e/14/4xp6)

As stated in the installation guide I must install the Oracle instant_client libraries. However, I'm trying to make the connection in my company's laptop and they have restricted access to any Oracle webpage. Also I can't get the files from another computer (the USB ports are blocked, no access to cloud storage, etc.). So is there a workaround to connect to the Oracle Database without the instant_client libraries? I can query the database using DBeaver so I assume that the connection to the database can be made but I don't know how to do it using Python, do you have an idea?


Solution

  • You should simply use the latest cx_Oracle version since it no longer always needs Instant Client. See the release announcement and also see Using python-oracledb 1.0 with SQLAlchemy, Pandas, Django and Flask and Using SQLAlchemy 2.0 (development) with python-oracledb.