SNOW-739749: snowflake-sqlalchemy 1.4.4 requires sqlalchemy<2.0.0,>=1.4.0, but you have sqlalchemy 2.0.1 which is incompatible.

[sfc-gh-jgriffith]

Error when attempting to install snowflake-sqlalchemy with sqlalchemy 2.0. Error:
snowflake-sqlalchemy 1.4.4 requires sqlalchemy<2.0.0,>=1.4.0, but you have sqlalchemy 2.0.1 which is incompatible.

Version 1.4.1 release notes say snowflake-sqlalchemy is now SQLAlchemy 2.0 compatible.

snowflake-sqlalchemy/setup.cfg

Line 50 in 7c8effd

sqlalchemy<2.0.0,>=1.4.0

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

   Python 3.8.16

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

   macOS-10.16-x86_64-i386-64bit

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

   asn1crypto==1.5.1
   certifi @ file:///private/var/folders/sy/f16zz6x50xz3113nwtb9bvq00000gp/T/abs_477u68wvzm/croot/certifi_1671487773341/work/certifi
   cffi==1.15.1
   charset-normalizer==2.1.1
   cryptography==38.0.4
   filelock==3.9.0
   greenlet==2.0.2
   idna==3.4
   oscrypto==1.3.0
   pycparser==2.21
   pycryptodomex==3.17
   PyJWT==2.6.0
   pyOpenSSL==22.1.0
   pytz==2022.7.1
   requests==2.28.2
   snowflake-connector-python==2.9.0
   SQLAlchemy==2.0.1
   typing_extensions==4.4.0
   urllib3==1.26.14

4. What did you do?

   pip install snowflake-sqlalchemy

5. What did you expect to see?

   Successfully installed snowflake-sqlalchemy-1.4.4

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

[sfc-gh-aling]

hey @sfc-gh-jgriffith, we're aware of the V2 GA release of sqlalchemy.
We will plan time for testing it and do release accordingly.

[stuck1233333]

Do you have an estimated release timeframe for this?

[qwertystop]

Seconded the request for a timeframe, especially given that a previous version explicitly said it added compatibility.

[cladden]

We would like to use sqlalchemy 2.0 as well, but this is blocking - is there a release timeframe?

[cpcloud]

Any idea when sqlalchemy 2.0 support might land?

[cladden]

hey @sfc-gh-jgriffith, we're aware of the V2 GA release of sqlalchemy.

We will plan time for testing it and do release accordingly.

@sfc-gh-aling do you have any updates on this?

[hellobrett]

Hi @sfc-gh-aling -- I see an update to snowflake-sqlalchemy just went out! Support for 2.0 yet!? As of this morning, it's still restricted to <2.0 upon installation.

https://pypi.org/project/snowflake-sqlalchemy/

[benoneill-tenzo]

Would also quite like to know a release timeframe for 2.0 support :)

[cladden]

I was curious how close snowflake-sqlalchemy is from supporting sqlalchemy 2.0, so I manually installed it:

pip install --no-deps snowflake-sqlalchemy==1.4.6

It seems very close!

After some monkey-patching, I was able to create an engine and connect to snowflake:

def snowflake_sqlalchemy_20_monkey_patches():
    import sqlalchemy.util.compat

    # make strings always return unicode strings
    sqlalchemy.util.compat.string_types = (str,)
    sqlalchemy.types.String.RETURNS_UNICODE = True

    import snowflake.sqlalchemy.snowdialect

    snowflake.sqlalchemy.snowdialect.SnowflakeDialect.returns_unicode_strings = True

    # make has_table() support the `info_cache` kwarg
    import snowflake.sqlalchemy.snowdialect

    def has_table(self, connection, table_name, schema=None, info_cache=None):
        """
        Checks if the table exists
        """
        return self._has_object(connection, "TABLE", table_name, schema)

    snowflake.sqlalchemy.snowdialect.SnowflakeDialect.has_table = has_table

# usage: call this function before creating an engine:
snowflake_sqlalchemy_20_monkey_patches()

@sfc-gh-aling and snowflake team, for your 2.0 porting efforts, maybe 👆is helpful in assessing the gaps?

[rosomri]

@sfc-gh-aling Any updates?

[cpcloud]

Would love to see support for sqlalchemy 2 in snowflake-sqlalchemy!

[jamesroseman]

@sfc-gh-aling SQLAlchemy 2.0 support was supposedly added last August. Any reason why this still doesn't work 234 days later?

@cladden Your fix has worked wonderfully, so I'm even more confused what's taking so long.

[hellobrett]

@jamesroseman From our Snowflake rep: The August release note was a typo. It should have read,

snowflake-sqlalchemy syntax is now v2.0 compatible

[Econ808]

@jamesroseman From our Snowflake rep: The August release note was a typo. It should have read,

snowflake-sqlalchemy syntax is now v2.0 compatible

Funny - at least I am not the only one who was sure that it implied that snowflake-sqlalchemy was ready for sqlalchemy>=2.0.0

Can you at least provide a timeframe for when you expect snowflake-sqlalchemy to be up-to-date with sqlalchemy?

[lf-floriandin]

Is there a timeframe when sqlalchemy 2.0 will be supported?
Still doesn't seem to support it and require sqlalchemy 1.4.

[devholland]

Any further news on this? We'd love to start using sqlalchemy 2.0 with Snowflake, too.

[radrichard]

I was curious how close snowflake-sqlalchemy is from supporting sqlalchemy 2.0, so I manually installed it:

pip install --no-deps snowflake-sqlalchemy==1.4.6

It seems very close!

After some monkey-patching, I was able to create an engine and connect to snowflake:

def snowflake_sqlalchemy_20_monkey_patches():
    import sqlalchemy.util.compat

    # make strings always return unicode strings
    sqlalchemy.util.compat.string_types = (str,)
    sqlalchemy.types.String.RETURNS_UNICODE = True

    import snowflake.sqlalchemy.snowdialect

    snowflake.sqlalchemy.snowdialect.SnowflakeDialect.returns_unicode_strings = True

    # make has_table() support the `info_cache` kwarg
    import snowflake.sqlalchemy.snowdialect

    def has_table(self, connection, table_name, schema=None, info_cache=None):
        """
        Checks if the table exists
        """
        return self._has_object(connection, "TABLE", table_name, schema)

    snowflake.sqlalchemy.snowdialect.SnowflakeDialect.has_table = has_table

# usage: call this function before creating an engine:
snowflake_sqlalchemy_20_monkey_patches()

@sfc-gh-aling and snowflake team, for your 2.0 porting efforts, maybe 👆is helpful in assessing the gaps?

I still haven't had any issues with this approach besides the warning about using old dbapi methods.

Thanks

[oliverlambson]

Bump?

[mikwieczorek]

Is there any update on supporting SQL-Alchemy >= 2.0?

[danb27]

Any update on this timeline?

[dominictarro]

It seems many of us are stuck with dependencies that don't let us downgrade to v1. This has been open for a year now with a pending PR and little to no feedback nor input from Snowflake. What's the hold up? What do we need to do to get it over the finish line?

[juandiegopalomino]

Latest langchain version also needs sqlalchemy 2.0 to function. It's getting in the way of our OpenAI adoption 😅

[danb27]

Bump.

Can we get an update? This is becoming more and more of a problem.

[jose-cdevx]

here i am, waiting
(thanks for your work btw, you are amazing! congrats to the team)

[YarShev]

@sfc-gh-dszmolka, it's been about 3 weeks since your last message. I was wondering if you could give all us an update?

[sfc-gh-dszmolka]

hey folks according to the latest information I have from the product team, we intend to run the private preview (see above) for a while to get more feedback and hopefully iron out wrinkles. I don't have any timeline set in stone unfortunately but asked the team with the source of truth and will update if there's any news.

Until the feature is available in public preview, you're more than welcome to participate in the private preview as mentioned above, if that's an option for you. Thank you all for the understanding here.

edit 29 May 2024: I'm still trying to get definitive confirmation regarding the timelines from the authoritative sources in the Product team. Currently per the info I have (and already mentioned above) we intend to run the private preview phase for a while - and pending its results (and any unforeseen critical issues) we intend to release it to public preview by latest autumn 2024.

To sum this up:

• private preview phase - currently open for Snowflake customers, since April 2024. Reach out to your Snowflake account team please if you wish to be onboarded, give the feature a try, and willing to provide feedback on your tests! (for which, we are very thankful)
• public preview phase - date not confirmed, should be available latest by autumn 2024 (pending results from private preview phase)

The very moment I have any information which says otherwise or can provide a more precise estimation, I will update this thread.

⚠️ Disclaimer: I'm a guy tending to the community/issues, and while doing my best to update you folks as well as possible on the timeline, I cannot be an authoritative source of hard release dates on planned upcoming unreleased features. For that, you will need to reach out to your Snowflake account team. Definitely do not take any dependency in your plans or projects on timelines which are not confirmed, please.

[astronautas]

Up! Any updates? Sqlalchemy has been released for over a year now... :/

[pep-sanwer]

@sfc-gh-dszmolka
My apologies for pinging you directly given the product team's ownership - but just wanted to flag that given numpy >= 2.0 support in pandas is only supported in >= 2.2.2 and this blocks to pandas <= 2.1.4 , the general availability of this update is now more pressing

[sfc-gh-dszmolka]

hi folks - regarding any more granular timelines above which have been already shared, i really have nothing to say at this point, I wish I had, but then you'd already know it from here.

Regarding the above concern, thank you for sharing it. Should be a concern for Python 3.8 though; as numpy 2 now requires Python 3.9-3.12 and on Python 3.9 both libraries are in the required version (pandas 2.2.2, numpy 2.0.0)

[YarShev]

#469 is closed. Should we consider this issue resolved?

[sfc-gh-dszmolka]

Indeed! I'll close this issue once the version which is compatible with SQLAlchemy 2.0 is finally released.

[YarShev]

Should we expect that version latest by autumn 2024 as mentioned above?

[sfc-gh-dszmolka]

still don't have a definite and committed-to date from the Product team but I believe the plans to approach the release might have changed.
Just saw #514 so hopefully release v1.6.0 which is compatible with SQLAlchemy 2.0 is released way sooner than the original worst-case estimation of autumn 2024.

[YarShev]

Look forward to that version. Thanks!

[sfc-gh-dszmolka]

After such a long wait, release v1.6.0 is out ! This is the one bringing compatibility with SQLAlchemy 2.0 and can be right now downloaded directly from here.

For some reason it's not available on pip yet (still 1.5.3 is the latest) , we're working on this and will update this same thread.

edit: issue looks to be fixed, but due to the nature of the issue we'll need to release a new version (1.6.1) which will happen soon. will keep this thread updated as events evolve.

Thank you everyone again for bearing with us for such a long time !

[Mrfence97]

Hooray 🎉! Thanks to all the team for working on this.

[tvoipio]

Excellent news, thanks to the team! Could you kindly comment here when 1.6.0 is available on PyPI and conda-forge? Our engineers might look at me funny if deployments involve downloading packages from some GitHub repository... (and I completely understand there is some kind of a delay, just trying to avoid having to poll both channels to see when we can update)

[edgarrmondragon]

Excellent news, thanks to the team! Could you kindly comment here when 1.6.0 is available on PyPI and conda-forge? Our engineers might look at me funny if deployments involve downloading packages from some GitHub repository... (and I completely understand there is some kind of a delay, just trying to avoid having to poll both channels to see when we can update)

The publishing workflow was broken and they seem to have fixed it in #520, so I think it's just a matter of them creating a 1.6.1 release now.

[edgarrmondragon]

It's out now 🙂

https://pypi.org/project/snowflake-sqlalchemy/1.6.1/

[sfc-gh-dszmolka]

indeed ! aforementioned temporal issue with the release is now fixed, and v1.6.1 version of the library is now out.

This marks a big milestone, and again i would like to thank you all for your patience here ! Marking this issue as closed :)