Sql Alchemy / Cx Oracle Installation/ Runtime Issue

Question

Can someone please try to help me understand what is going on here. I have been trying to run this from powershell and cannot get it to work. I have vscode and tried running it there as well as changing the cwd and interpreter. Instead I just get the error traceback. I have also tried installing oracleDB but it seems like cx_Oracle is being called from sqlalchemy and causing this error

My Coworker has no problem running, and it outputs the expected results

PS C:\Users\USERNAMEPLACEHOLDER\Downloads>python ib_newsletter_v2.py
Traceback (most recent call last):
  File "C:\Users\USERNAMEPLACEHOLDER\Downloads\ib_newsletter_v2.py", line 15, in <module>
    db = dl_db.DataLendDB('EQLVXRAIL-NJ-W2-ORCLDBPRD01.equilend.com', '1521', 'EQMRKT01', True)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USERNAMEPLACEHOLDER\Downloads\dl_IB.py", line 25, in __init__
    self.engine = create_engine(con_str)
                  ^^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 2, in create_engine
  File "C:\Users\USERNAMEPLACEHOLDER\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\sqlalchemy\util\deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USERNAMEPLACEHOLDER\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\sqlalchemy\engine\create.py", line 599, in create_engine
    dbapi = dbapi_meth(**dbapi_args)
            ^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\USERNAMEPLACEHOLDER\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\sqlalchemy\dialects\oracle\cx_oracle.py", line 1104, in import_dbapi
    import cx_Oracle
ModuleNotFoundError: No module named 'cx_Oracle'
PS C:\Users\USERNAMEPLACEHOLDER\Downloads> pip install sqlalchemy
Requirement already satisfied: sqlalchemy in c:\users\USERNAMEPLACEHOLDER\appdata\local\packages\pythonsoftwarefoundation.python.3.11_qbz5n2kfra8p0\localcache\local-packages\python311\site-packages (2.0.32)
Requirement already satisfied: typing-extensions>=4.6.0 in c:\users\USERNAMEPLACEHOLDER\appdata\local\packages\pythonsoftwarefoundation.python.3.11_qbz5n2kfra8p0\localcache\local-packages\python311\site-packages (from sqlalchemy) (4.12.2)
Requirement already satisfied: greenlet!=0.4.17 in c:\users\USERNAMEPLACEHOLDER\appdata\local\packages\pythonsoftwarefoundation.python.3.11_qbz5n2kfra8p0\localcache\local-packages\python311\site-packages (from sqlalchemy) (3.0.3)
PS C:\Users\USERNAMEPLACEHOLDER\Downloads>

Answer 1

Definitely head towards using python-oracledb instead of the old cx_Oracle driver which is effectively obsolete.

Install it with:

pip install oracledb

If you have the old SQLAlchemy 1.4, then do this:

# Using python-oracledb in SQLAlchemy 1.4

import os
import oracledb
from sqlalchemy import create_engine
from sqlalchemy import text

# These three lines let SQLAchemy 1.4 use python-oracledb
import sys
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb

# Get credentials
un = os.environ.get("PYTHON_USERNAME")
pw = os.environ.get("PYTHON_PASSWORD")
cs = os.environ.get("PYTHON_CONNECTSTRING")

engine = create_engine(f'oracle://@',
                       connect_args={
                           "user": un,
                           "password": pw,
                           "dsn": cs
                       }
         )

with engine.connect() as conn:
    print(conn.scalar(text(
           """SELECT UNIQUE CLIENT_DRIVER
              FROM V$SESSION_CONNECT_INFO
              WHERE SID = SYS_CONTEXT('USERENV', 'SID')""")))

I like using connect_args since it lets me pass any underlying driver oracledb.connect() properties.

If you have SQLAlchemy 2 then do this:

# Using python-oracledb in SQLAlchemy 2

import os
import oracledb
from sqlalchemy import create_engine
from sqlalchemy import text

# Get credentials
un = os.environ.get("PYTHON_USERNAME")
pw = os.environ.get("PYTHON_PASSWORD")
cs = os.environ.get("PYTHON_CONNECTSTRING")

# Note the first arg is different than for SQLAlchemy 1.4
engine = create_engine(f'oracle+oracledb://@',
                       connect_args={
                           "user": un,
                           "password": pw,
                           "dsn": cs
                       }
         )

with engine.connect() as conn:
    print(conn.scalar(text(
           """SELECT UNIQUE CLIENT_DRIVER
              FROM V$SESSION_CONNECT_INFO
              WHERE SID = SYS_CONTEXT('USERENV', 'SID')""")))

Note the create_engine() argument is different for the different SQLAlchemy versions.

Also see the blogs:

• https://medium.com/gitconnected/using-python-oracledb-1-0-with-sqlalchemy-pandas-django-and-flask-5d84e910cb19

• https://medium.com/oracledevs/using-the-development-branch-of-sqlalchemy-2-0-with-python-oracledb-d6e89090899c