python - SQLAlchemy Oracle - InvalidRequestError: could not retrieve isolation level

Question

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.

question from:https://stackoverflow.com/questions/65830524/sqlalchemy-oracle-invalidrequesterror-could-not-retrieve-isolation-level

Answer 1

The Oracle database only supports READ COMMITTED, SERIALIZABLE or AUTOCOMMIT as isolation level.

Use the following for the isolation level for the connection:

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

For READ COMMITTED and SERIALIZABLE, the Oracle dialect sets the level at the session level using ALTER SESSION, which is reverted back to its default setting when the connection is returned to the connection pool.

See the docs for more details.