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

Question

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?

Answer 1

SQLAlchemy 1.4

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() argument should begin with oracle: like:

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

SQLAlchemy 2.0

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

import oracledb

# SQLAlchemy 2.0 with python-oracledb
engine = create_engine(
    "oracle+oracledb://@",
    connect_args={
        "user": "scott",
        "password": "tiger",
        "dsn": "hostname:port/myservice?transport_connect_timeout=30&expire_time=60",
    },
)

I prefer using connect_args instead of passing a single, concatenated argument to create_engine() because it avoids problems of passwords with special characters, and also lets you pass other python-oracledb connection arguments.

If you have a multi-user app, then use python-oracledb's connection pooling as shown in the SQLAlchemy doc.

These links are good references:

• SQLAlchemy Oracle connection doc: Connecting
• python-oracledb doc: Connecting with SQLAlchemy
• Blog: Using python-oracledb 1.0 with SQLAlchemy, Pandas, Django and Flask
• Blog: Using SQLAlchemy 2.0 (development) with python-oracledb for Oracle Database