0

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?

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
Rafa.Rugamas
  • 87
  • 1
  • 3
  • 9
  • I solved it using the OJDBC Driver (the same that Dbeaver uses) and the libraries jpype and jaydebeapi. With that combination I was able to stablish the connection. – Rafa.Rugamas Jul 28 '22 at 19:23
  • You should simply use the latest cx_Oracle version since it no longer always needs Instant Client. See the [release announcement](https://cjones-oracle.medium.com/open-source-python-thin-driver-for-oracle-database-e82aac7ecf5a) and also see [Using python-oracledb 1.0 with SQLAlchemy, Pandas, Django and Flask](https://levelup.gitconnected.com/using-python-oracledb-1-0-with-sqlalchemy-pandas-django-and-flask-5d84e910cb19) and [Using SQLAlchemy 2.0 (development) with python-oracledb](https://medium.com/oracledevs/using-the-development-branch-of-sqlalchemy-2-0-with-python-oracledb-d6e89090899c). – Christopher Jones Jul 28 '22 at 23:27

1 Answers1

2

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.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48