Sqlalchemy transactions with Repeatable_read

[StephenOTT]

Expected behavior

dding a isolation level in the SA Session causes a failure when using any of the levels with underscores.

Trino client is removing the underscore, but then SA requires the underscore in downstream methods for a enum lookup.

Looking at the tests, it seems like it was not tested against the isolation levels with underscores.

Thanks.

Actual behavior

Fails

Steps To Reproduce

Use isolation level REPEATABLE_READ with SA

Log output

No response

Operating System

Windoss

Trino Python client version

Latest

Trino Server version

429

Python version

3.9

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[hashhar]

Thank you for the report and being willing to submit a PR. I'll take a look at the PR once it's available.

[StephenOTT]

Hey @hashhar can you confirm for me: are transactions supported in the Postgres connector?

The docs seem to allude yes, but some ~random issues in this repo seem to indicate no.

[hashhar]

No. Transactions are emulated for the Hive connector only as far as I remember.

cc: @mosabua Maybe we should clarify this in the docs + check what's the long term plan around transactions - will they go away eventually since we can only try to "emulate" them?

[StephenOTT]

At the very least to add a big warning on this repos readme in the transaction section. :)

[ChrisGVE]

Still present in 0.338.0 against Trino 476, and I can add the exact mechanism plus a second symptom nobody has mentioned here yet.

Versions: trino 0.338.0 (trino[sqlalchemy]), SQLAlchemy 2.0.51, Python 3.12.9, Trino 476 in Docker.

SQLAlchemy normalises an isolation level to spaces before handing it over; the dialect looks it up in an enum keyed with underscores (IsolationLevel.READ_UNCOMMITTED). So every level whose name is two words raises at connect time, and the one-word one matches by accident:

asked for	result
SERIALIZABLE	accepted
READ UNCOMMITTED	KeyError: 'READ UNCOMMITTED' at connect
READ COMMITTED	KeyError: 'READ COMMITTED' at connect
REPEATABLE READ	KeyError: 'REPEATABLE READ' at connect

The practical consequence is worth spelling out, because it isn't obvious from the title: SERIALIZABLE is not a strictness choice, it's the only level reachable at all. Anyone who wants a transaction and doesn't care about the snapshot — which is my case, I only need writes to be undoable — is silently forced onto the strictest level. Once this is fixed the weakest level becomes reachable and is the better default for that use.

Second symptom, separate from the enum lookup: isolation_level is ignored as a URL query parameter. Passing it in the URL leaves the connection reporting AUTOCOMMIT, with no error and no warning. It only takes effect via create_engine(..., isolation_level=...). That one bit me first and cost more time than the KeyError, because a KeyError at least tells you something happened.

Happy to test a fix against my setup if that's useful.