1

When I Try to connect oracle server with SQLAlchemy. I'm getting this error.

NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:oracle.oracledb

from sqlalchemy.engine import create_engine

DIALECT = 'oracle'
SQL_DRIVER = 'oracledb'
USERNAME = 'username' #enter your username
PASSWORD = 'password' #enter your password
HOST = 'host url' #enter the oracle db host url
PORT = 1533 # enter the oracle port number
SERVICE = 'service name' # enter the oracle db service name
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICE

engine = create_engine(ENGINE_PATH_WIN_AUTH)


#test query
import pandas as pd
test_df = pd.read_sql_query('SELECT * FROM global_name', engine)

any different method to connect?

ThunderCloud
  • 41
  • 1
  • 5
  • Maybe [this page](https://levelup.gitconnected.com/using-python-oracledb-1-0-with-sqlalchemy-pandas-django-and-flask-5d84e910cb19) can help you – gsalem Oct 17 '22 at 12:03
  • SQLAlchemy 2.0 is required to use python-oracledb -- at least without the directions given on the page by gsalem. – Anthony Tuininga Oct 17 '22 at 13:43

1 Answers1

7

For completeness (since the answer is already in comments): with SQLAlchemy 1.4 add this to your top level script file:

import sys
import oracledb
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb

and then proceed as if you were using cx_Oracle. The create_engine() should begin with oracle: like:

# SQLAlchemy 1.4 with python-oracledb or cx_Oracle
engine = create_engine('oracle://...

The sys.modules etc snippet isn't needed for SQLAlchemy 2.0. With this version create_engine() should begin with oracle+oracledb: like:

# SQLAlchemy 2.0 with python-oracledb
engine = create_engine('oracle+oracledb://...

These posts are good references:

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
  • Thanks for your reply. I tried above method. now I'm getting ```DatabaseError: (oracledb.exceptions.DatabaseError) ORA-12537: TNS:connection closed``` – ThunderCloud Oct 20 '22 at 08:40