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
- [ ] https://github.com/ITISFoundation/osparc-simcore/issues/4348
- [ ] https://github.com/ITISFoundation/osparc-simcore/pull/4384
- [ ] https://github.com/ITISFoundation/osparc-simcore/pull/4330
- [ ] https://github.com/ITISFoundation/osparc-simcore/issues/4528
- [ ] https://github.com/ITISFoundation/osparc-simcore/issues/4529
- [ ] enable the future flag on sqlalchemy
- [ ] upgrade to sqlalchemy 2.0

[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.