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.

https://github.com/snowflakedb/snowflake-sqlalchemy/blob/7c8effdb8c38f37084d5165961df75cbbbc10ddf/setup.cfg#L50

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.

[3065923]

How do we still have no update???

[andrewelamb]

I'm also very interested in using this package, but can't until a SQLAlchemy2.0 compatible verison is ready.

[jacobsieradzki]

+1 - SQLAlchemy 2 is essential for my project and this is becoming a blocker!

[sfc-gh-dszmolka]

hi folks, first of all thank you everyone so much for bearing with us while the SQLAlchemy 2.0 compatibility is implemented!

I can confirm the implementation is currently in progress and we plan to release it by end of Q1 (April 2024). Please note this is not a committed-to date, just a rough estimation which is subject to change.

Will keep this thread posted on the progress.

[g24swint]

With the release of Pandas 2.2, please be aware that Pandas seems to require SQLAlchemy 2.0+ with potential that they revert to support SA 1.4 in a later maintenance release. snowflake-sqlalchemy is specificially mentioned as a problem by some users in that it does not support SA 2.0 https://github.com/pandas-dev/pandas/issues/57049

[YarShev]

Is there an ETA of this issue?

[sfc-gh-dszmolka]

thank you all for your continued patience - no fixed ETA yet, but we're very close to making this available to everyone !

Speaking about which - if you are

• already a Snowflake customer, with private preview legal documents signed
• and willing to give the new functionality an early try and also willing to provide detailed feedback please do reach out to your Account Team to take this for a spin.

after this private preview phase closes, we'll release the SQLAlchemy 2.0 support for everyone after such a long wait. Appreciate everyone your patience and bearing with us while the last steps are completed, really should not take much longer.