upgrade to sqlalchemy 2.0

[sanderegg]

and install sqlalchemy like so: _base.in sqlalchemy[postgresql_asyncpg] _test.in sqlalchemy[postgresql_asyncpg, mypy]

Includes async features through asyncpg engine.

Here the sqlaclhemy 2.0 migration guide: https://docs.sqlalchemy.org/en/20/changelog/migration_20.html

• SQLALCHEMY_WARN_20 enabled in all [unit/int] tests
• add the following in all the setup.cfg files as soon as the warnings are all gone to prevent going back to pre-2.0 style

  [tool:pytest]
  addopts = -W error::sqlalchemy.exc.SAWarning
  

TIPs:

sa.select([clusters.c.id, clusters.c.name]) # old style with array
sa.select(clusters.c.id, clusters.c.name) # new style with args

Tasks

• [x] https://github.com/ITISFoundation/osparc-simcore/pull/4384
• [x] https://github.com/ITISFoundation/osparc-simcore/pull/4330
• [x] enable the future flag on sqlalchemy

[pcrespov]

• Since https://github.com/ITISFoundation/osparc-simcore/pull/4374, testing postgres-database will now turn into errors anything related to pre-sql 2.0 code.

[sanderegg]

@sanderegg add progress list of packages/services

[pcrespov]

Quoting @sanderegg .

So the current idea is to remove ALL the SQLAlchemy2.0 warnings from the tests (I do hope this should remove >90% of the problems since we do not have full coverage maybe some stuff could slip by)

[GitHK]

@sanderegg @pcrespov should we maybe do this upgrade for the next sprint? like we did for mypy?

[pcrespov]

@sanderegg @pcrespov should we maybe do this upgrade for the next sprint? like we did for mypy?

I would do this together with https://github.com/ITISFoundation/osparc-simcore/issues/4529. BTW I started creating the infrastructure e.g. to test with asyncpg in https://github.com/ITISFoundation/osparc-simcore/pull/6281

[sanderegg]

@pcrespov @GitHK @giancarloromeo @matusdrobuliak66 I would first wait for pydantic v2 migration.

[pcrespov]

@sanderegg I think this can be confortably tacked once we have

• https://github.com/ITISFoundation/osparc-simcore/issues/4529 (now very easy to do)
• https://github.com/ITISFoundation/osparc-simcore/issues/4528

[mrnicegyu11]

Side note: opentelemetry has an auto-instrumentation for sqlalchemy2, but not for sqlalchemy1