connecting python to Oracle using sqlalchemy and oracledb

Question

Need to connect to Oracle using Python that uses SQLAlchemy. I need to use the oracledb package. Using code from Github

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

print (oracledb.__file__)

username = os.environ.get("PYTHON_USERNAME")
password = os.environ.get("PYTHON_PASSWORD")

cp = oracledb.ConnectParams()
cp.parse_connect_string(os.environ.get("PYTHON_CONNECTSTRING"))

thick_mode = None

engine = create_engine(
    f'oracle+oracledb://{username}:{password}@{cp.host}:{cp.port}/?service_name={cp.service_name}',
    thick_mode=thick_mode)

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

The .env file contains

PYTHON_USERNAME="scott"
PYTHON_PASSWORD="tiger"
PYTHON_CONNECTSTRING="192.168.1.3:1521/orclpdb"

The error I see is below:

C:\Users\HI>C:\Users\HI\AppData\Local\Programs\Python\Python312\python.exe "C:\Users\HI\Desktop\New folder\streamlit\langchain\sa-pydb.py"
C:\Users\HI\AppData\Local\Programs\Python\Python312\Lib\site-packages\oracledb\__init__.py
Traceback (most recent call last):
  File "C:\Users\HI\Desktop\New folder\streamlit\langchain\sa-pydb.py", line 13, in <module>
    cp.parse_connect_string(os.environ.get("PYTHON_CONNECTSTRING"))
  File "C:\Users\HI\AppData\Local\Programs\Python\Python312\Lib\site-packages\oracledb\connect_params.py", line 666, in parse_connect_string
    self._impl.parse_connect_string(connect_string)
  File "src\\oracledb\\impl/base/connect_params.pyx", line 551, in oracledb.base_impl.ConnectParamsImpl.parse_connect_string
TypeError: descriptor 'strip' for 'str' objects doesn't apply to a 'NoneType' object

Below are the versions of the packages.

C:\Users\HI>pip show sqlalchemy
Name: SQLAlchemy
Version: 2.0.28

C:\Users\HI>pip show oracledb
Name: oracledb
Version: 2.0.1

What could be a working solution?

Answer 1

The problem you are experiencing is that the environment variable PYTHON_CONNECT_STRING is not set. So you get the value None which doesn't work when calling cp.parse_connect_string(). You can confirm that by using this code:

cp = oracledb.ConnectParams()
cp.parse_connect_string(os.environ["PYTHON_CONNECTSTRING"])

You should get a KeyError exception. The technique you are using otherwise should work just fine!