0

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.

Pierre Delecto
  • 455
  • 1
  • 7
  • 26
  • 1
    Make sure you have the latest SQL Alchemy. There were some recent changes in it regarding isolation level and Oracle. – Christopher Jones Jan 21 '21 at 23:21
  • 2
    hey there - the error you are seeing may occur upon connect() in SQLAlchemy 1.3.21 only. in 1.3.22, the error above is internally captured and not raised upon connect(); you would only see it if you called the get_isolation_level() method explicitly. if you are seeing this error with 1.3.22 or above, please post the complete stack trace preferably at the [issue tracker](https://github.com/sqlalchemy/sqlalchemy/issues) – zzzeek Jan 22 '21 at 14:38
  • Thanks @zzzeek updating SQLAlchemy solved my problem. – Pierre Delecto Jan 22 '21 at 16:24

1 Answers1

0

Upgrading from SqlAlchemy 1.3.21 to 1.3.22 solved the problem.

Pierre Delecto
  • 455
  • 1
  • 7
  • 26