SNOW-934880: Snowflake-sqlalchemy installation force-downgrades SQLAlchemy to v1.4.49

[mulenitin]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

   Python 3.10.1 (tags/v3.10.1:2cd268a, Dec 6 2021, 19:10:37) [MSC v.1929 64 bit (AMD64)]

2. What operating system and processor architecture are you using?

   Windows-10-10.0.19043-SP0

3. What are the component versions in the environment (pip freeze)?

   altair==4.2.2 annotated-types==0.6.0 anyio==4.0.0 argon2-cffi==23.1.0 argon2-cffi-bindings==21.2.0 arrow==1.3.0 asn1crypto==1.5.1 asttokens==2.4.0 async-lru==2.0.4 attrs==23.1.0 Babel==2.13.0 backcall==0.2.0 beautifulsoup4==4.12.2 bleach==6.1.0 certifi==2023.7.22 cffi==1.16.0 charset-normalizer==2.1.1 click==8.1.7 colorama==0.4.6 comm==0.1.4 cryptography==38.0.4 cx-Oracle==8.3.0 debugpy==1.8.0 decorator==5.1.1 defusedxml==0.7.1 entrypoints==0.4 exceptiongroup==1.1.3 executing==2.0.0 fastjsonschema==2.18.1 filelock==3.12.4 fqdn==1.5.1 great-expectations==0.17.21 great-expectations-cloud==0.0.3.dev5 greenlet==3.0.0 idna==3.4 ipykernel==6.25.2 ipython==8.16.1 ipywidgets==8.1.1 isoduration==20.11.0 jedi==0.19.1 Jinja2==3.1.2 json5==0.9.14 jsonpatch==1.33 jsonpointer==2.4 jsonschema==4.19.1 jsonschema-specifications==2023.7.1 jupyter-events==0.7.0 jupyter-lsp==2.2.0 jupyter_client==8.3.1 jupyter_core==5.3.2 jupyter_server==2.7.3 jupyter_server_terminals==0.4.4 jupyterlab==4.0.6 jupyterlab-pygments==0.2.2 jupyterlab-widgets==3.0.9 jupyterlab_server==2.25.0 makefun==1.15.1 MarkupSafe==2.1.3 marshmallow==3.20.1 matplotlib-inline==0.1.6 mistune==3.0.2 nbclient==0.8.0 nbconvert==7.9.2 nbformat==5.9.2 nest-asyncio==1.5.8 notebook==7.0.4 notebook_shim==0.2.3 numpy==1.26.0 oracledb==1.4.1 orjson==3.9.7 oscrypto==1.3.0 overrides==7.4.0 packaging==23.2 pandas==2.1.1 pandocfilters==1.5.0 parso==0.8.3 pickleshare==0.7.5 pika==1.3.2 platformdirs==3.11.0 prometheus-client==0.17.1 prompt-toolkit==3.0.39 psutil==5.9.5 pure-eval==0.2.2 pycparser==2.21 pycryptodomex==3.19.0 pydantic==2.4.2 pydantic_core==2.10.1 Pygments==2.16.1 PyJWT==2.8.0 pyOpenSSL==22.1.0 pyparsing==3.1.1 python-dateutil==2.8.2 python-json-logger==2.0.7 pytz==2023.3.post1 pywin32==306 pywinpty==2.0.12 PyYAML==6.0.1 pyzmq==25.1.1 referencing==0.30.2 requests==2.31.0 rfc3339-validator==0.1.4 rfc3986-validator==0.1.1 rpds-py==0.10.4 ruamel.yaml==0.17.17 scipy==1.11.3 Send2Trash==1.8.2 six==1.16.0 sniffio==1.3.0 snowflake-connector-python==2.9.0 snowflake-sqlalchemy==1.4.1 sortedcontainers==2.4.0 soupsieve==2.5 SQLAlchemy==1.4.49 stack-data==0.6.3 terminado==0.17.1 tinycss2==1.2.1 tomli==2.0.1 tomlkit==0.12.1 toolz==0.12.0 tornado==6.3.3 tqdm==4.66.1 traitlets==5.11.2 types-python-dateutil==2.8.19.14 typing_extensions==4.8.0 tzdata==2023.3 tzlocal==5.1 uri-template==1.3.0 urllib3==1.26.17 wcwidth==0.2.8 webcolors==1.13 webencodings==0.5.1 websocket-client==1.6.3 widgetsnbextension==4.0.9

4. What did you do?

   Step 1: Install SQLAlchemy 2.0.21 using 'pip install sqlalchemy' Step 2: Install snowflake-sqlalchemy using 'pip install snowflake-sqlalchemy'

5. What did you expect to see?

   I would have expected snowflake-sqlalchemy to be installed without downgrading SQLAlchemy 2.0.21. Instead, the pip install detects SQLAlchemy 2.0.21 and downgrades it to 1.4.49. If I perform pip install using the '--no-deps' option, then the snowflake connection does not work with SQLAlchemy 2.0.21. I get the following error when I use snowflake-sqlalchemy with SQLAlchemy 2.0.21 - ImportError: cannot import name 'string_types' from 'sqlalchemy.util.compat'.

   If snowflake-sqlalchemy is compatible with sqlalchemy 2.0.21, then it should not force downgrade to 1.4.49, and it should work seamlessly with the latest version.

6. Can you set logging to DEBUG and collect the logs? N/A

[sfc-gh-achandrasekaran]

Yes, this is because snowflake-sqlalchemy does not support SQLAlchemy 2.0. This is on our roadmap

[mulenitin]

How soon can I expect the support to be enabled?

Also, this is from your changelog -

v1.4.1(Aug 18, 2022)

snowflake-sqlalchemy is now SQLAlchemy 2.0 compatible. Fixed a bug that DATE should not be removed from SnowflakeDialect.ischema_names. Fixed breaking changes introduced in release 1.4.0 that: changed the behavior of processing numeric, datetime and timestamp values returned from service. changed the sequence order of primary/foreign keys in list returned by inspect.get_foreign_keys and inspect.get_pk_constraint.

[reyes-c1]

Note that sqlalchemy 1.x has a known vulnerability, so there will be more pressure on folks to upgrade to sqlalchemy 2.x than there might otherwise be.

[mattgiles]

Respectful bump to this issue.

It's been almost a year since the release of sqlalchemy 2.0, and, owing to the popularity of snowflake, the version pin here risks becoming a drag on ecosystem tooling.

[willsthompson]

Yes, this is because snowflake-sqlalchemy does not support SQLAlchemy 2.0. This is on our roadmap

@sfc-gh-achandrasekaran Can you help clear this up? I don't understand what's going on. You wrote that Oct 9, but in the 1.4.1 release notes from Aug 23, it says snowflake-sqlalchemy is now SQLAlchemy 2.0 compatible. Why did that release claim 2.0 compatibility? The actual requirements for all releases are still pinned < 2.0 https://github.com/snowflakedb/snowflake-sqlalchemy/blob/61975bf607434fe8427aaae926549e8ffd3e9bb8/setup.cfg#L50

[ghost]

We face the same problem. Any updates on this? @sfc-gh-achandrasekaran

[sfc-gh-aling]

hi folks, thanks for all your feedbacks. let me explain a bit:

TL;DR: snowflake-sqlalchemy doesn't support the GAed v2 sqlalchemy. the past release notes is inaccurate and we will fix it to clear the confusion. we hear the need for v2 sqlalchemy compatibility, we will put it into account when planning

Long story:

by the time when we developed snowflake-sqlalchemy v1.4.1 in which version the release notes claimed "v2 support", SQLALchemy didn't GA v2 yet.

snowflake-sqlalchemy v1.4.1 was developed against a so-called "future version of v2" in which version v2 APIs were hosted inside v1.4.49, and the way to use v2 APIs is to use future=True when creating an engine.

so our release notes is wrong, it supports the future version of v2 inside the v1.4.49 sqlalchemy, not the GA version

[sk-]

@sfc-gh-achandrasekaran @sfc-gh-aling is there any chance you could take a look at #440 and approve the CI run (see https://github.com/snowflakedb/snowflake-sqlalchemy/pull/440#issuecomment-1757053954).

[miried]

Has there been any progress on this issue?

Pandas moved its minimum supported version for sqlalchemy to 2.0.0, which is now in conflict with snowflake-sqlalchemy requirement. see https://github.com/pandas-dev/pandas/issues/57049

[femi-anthony]

Still no progess on this issue ?

[willsthompson]

Due to conflicts piling up, we couldn't wait any longer to upgrade, so we forked #440 and swapped our PyPI requirement for the fork. We've been running that for several months and haven't had any issues. @sfc-gh-achandrasekaran @sfc-gh-aling has anyone considered this PR?

[sfc-gh-dszmolka]

hi - we have an open tracking Issue at https://github.com/snowflakedb/snowflake-sqlalchemy/issues/380 to track SQLAlchemy 2.0 basic compatibility implementation; this issue looks like something which is originating the library not being compatible with SQLAlchemy 2 at the moment - thus a duplicate.

Closing it therefore, but please if you need further help with this, comment and we can reopen