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

snowflake-sqlalchemy/setup.cfg

Line 50 in 61975bf

sqlalchemy<2.0.0,>=1.4.0

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