Compatibility Issue: singer-sdk 0.43.1 Requires SQLAlchemy > 2.0 Due to ObjectKind Usage

[Ronnos]

After upgrading to singer sdk 0.43.1, we encountered a compatibility issue because of meltano/sdk@d3dcc8e

This change introduces the use of sqlalchemy.engine.reflection.ObjectKind, which requires SQLAlchemy 2.0+.

Currently, this package does not enforce sqlalchemy>=2.0, which leads to the following error when running with an older version of SQLAlchemy:

site-packages/singer_sdk/connectors/sql.py", line 998

AttributeError: module 'sqlalchemy.engine.reflection' has no attribute 'ObjectKind' 

Suggested Fix:

Please update pyproject.toml to explicitly require:
sqlalchemy >=2.0

[guusfrenken-wk]

The requirement of SQLAlchemy 2 seems to have been introduced in Singer SDK 0.43.0: https://github.com/meltano/sdk/pull/2797/files

[coetzeevs]

Hey - has anyone managed a work-around to this? I'm running into the same thing, using a clean local install using all latest versions available, per the docs, and I'm still getting this. Checking the libs in .meltano/extractors/tap-mysql/venv I can see it's still using sqlalchemy<2.0 hence the error as noted by @Ronnos. No other variants are working locally either.

I see there's a dependabot PR (#105) open since June last year to bump to 2.0.

As a hack I tried forcing the version by just copy-pasting the content of sqlalchemy from another virtual environment to the venv folder, but I end up with different errors, though related I suspect.

[visch]

PRs are accepted if someone wants to do it!

[guusfrenken-wk]

@visch I will look into implementing SQLAlchemy 2.0 support and submit a PR if it works out

[coetzeevs]

#115 started something here. 3 tests still failing. Will continue with it in time, or please feel free to contribute to the branch. @visch @guusfrenken-wk

Haven't tried using this in a local project yet though, so relying on the tests for validity of the changes.

[guusfrenken-wk]

Apologies, me and @Ronnos intended to work on this during our current sprint at work but we had to prioritize other tasks