SQLAlchemy 2.0 migration

[AlexDo1]

SQLAlchemy 2.0 was released, introducing a lot of breaking changes.
We need to adapt metacatalog where SQLAlchemy is used to make metacatalog compatible to SQLAlchemy 2.0. There is an official migration guide by SQLAlchemy: https://docs.sqlalchemy.org/en/14/changelog/migration_20.html

Important:

• Turn on RemovedIn20Warnings to give warnings when code is not compatible to SQLAlchemy 2.0
• use SQLAlchemy 1.4.10 with RemovedIn20Warnings and fix all of the warnings.

Currently (27.01.2023), pandas is also not compatible to SQLAlchemy 2.0, as described in this issue: https://github.com/pandas-dev/pandas/issues/40686.

• [ ] update database revision
• [ ] get rid of all Session.bind usages
• [ ] ...

[mmaelicke]

Beside the use of Session.bind in pandas, I think we should be able to substitute all usages of:

with engine.connect() as con:
    con.execute(...)

with:

session.execute(...)

In the older versions there have been differences how the transaction is handled, we need to check the changelog to see if that is still the case in 2.0 and how it affects our code.