Search code examples
pythonoraclesqlalchemykedro

SQLAlchemy Oracle - InvalidRequestError: could not retrieve isolation level

I am having problems accessing tables in an Oracle database over a SQLAlchemy connection. Specifically, I am using Kedro catalog.load('table_name') and getting the error message Table table_name not found. So I decided to test my connection using the method listed in this answer: How to verify SqlAlchemy engine object.

from sqlalchemy import create_engine
engine = create_engine('oracle+cx_oracle://USER:PASSWORD@HOST:PORT/?service_name=SERVICE_NAME')
engine.connect()

Error: InvalidRequestError: could not retrieve isolation level

I have tried explicitly adding an isolation level as explained in the documentation like this:

engine = create_engine('oracle+cx_oracle://USER:PASSWORD@HOST:PORT/?service_name=SERVICE_NAME', execution_options={'isolation_level': 'AUTOCOMMIT'})

and this:

engine.connect().execution_options(isolation_level='AUTOCOMMIT')

and this:

connection = engine.connect()
connection = connection.execution_options(
    isolation_level="AUTOCOMMIT"
)

but I get the same error in all cases.

Solution

  • Upgrading from SqlAlchemy 1.3.21 to 1.3.22 solved the problem.