search

snowflakedb / snowflake-sqlalchemy

Snowflake SQLAlchemy
https://pypi.python.org/pypi/snowflake-sqlalchemy/
Apache License 2.0
233 stars 152 forks source link

Add basic compatibility with SQLAlchemy 2.0 #440

Closed saulbein closed 4 months ago

saulbein commented 1 year ago

Recreating #414 after mistakedly deleting the commits in my fork. Sorry!

Please answer these questions before submitting your pull requests. Thanks!

  1. What GitHub issue is this PR addressing? Make sure that there is an accompanying issue to your PR.

    Fixes (partially) https://github.com/snowflakedb/snowflake-sqlalchemy/issues/380

  2. Fill out the following pre-review checklist:

    • [ ] I am adding a new automated test(s) to verify correctness of my new code
    • [ ] I am adding new logging messages
    • [ ] I am adding new credentials
    • [ ] I am adding a new dependency

  3. Please describe how your code solves the related issue.

    Updates code which is incompatible with SQLAlchemy 2.0. See comments for why specific changes were done.

ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_basic - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c0c-0102-7af6-0051-ed01deaa18f3: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_delete_rowcount - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c0c-0102-7af6-0051-ed01deaa18ff: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_multi_delete_rowcount - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c0c-0102-7bbb-0051-ed01deaa25a7: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_multi_update_rowcount - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c0c-0102-7af6-0051-ed01deaa191f: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_raw_sql_rowcount - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c0c-0102-7bbb-0051-ed01deaa25cf: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_text_rowcount - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c0c-0102-7af6-0051-ed01deaa196f: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_update_rowcount1 - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c0c-0102-7af6-0051-ed01deaa197f: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_update_rowcount2 - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c0c-0102-7bbb-0051-ed01deaa263f: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_update_rowcount_return_defaults - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c0c-0102-7af6-0051-ed01deaa19bf: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_update_rowcount_return_defaults - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 002003 (42S02): 01ac2c0c-0102-7af6-0051-ed01deaa19f3: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_basic - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c5e-0102-7af6-0051-ed01deb2d333: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_delete_rowcount - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c5e-0102-7af6-0051-ed01deb2d33b: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_multi_delete_rowcount - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c5e-0102-7af6-0051-ed01deb2d38b: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_multi_update_rowcount - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c5e-0102-7bbb-0051-ed01deb2cfdf: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_raw_sql_rowcount - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c5e-0102-7af6-0051-ed01deb2d3f3: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_text_rowcount - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c5e-0102-7af6-0051-ed01deb2d427: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_update_delete_rowcount_return_defaults[delete-implicit_returning] - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c5e-0102-7af6-0051-ed01deb2d44f: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_update_delete_rowcount_return_defaults[delete-not_implicit_returning] - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c5e-0102-7bbb-0051-ed01deb2e053: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_update_delete_rowcount_return_defaults[update-implicit_returning] - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c5e-0102-7af6-0051-ed01deb2d4a7: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_update_delete_rowcount_return_defaults[update-not_implicit_returning] - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c5e-0102-7bbb-0051-ed01deb2e07f: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_update_rowcount1 - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c5e-0102-7af6-0051-ed01deb2d50f: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_update_rowcount2 - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001759 (42601): 01ac2c5e-0102-7af6-0051-ed01deb2d523: SQL compilation error:
ERROR tests/sqlalchemy_test_suite/test_suite.py::RowCountTest_snowflake+snowflake::test_update_rowcount2 - sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 002003 (42S02): 01ac2c5e-0102-7bbb-0051-ed01deb2e0eb: SQL compilation error:
FAILED tests/sqlalchemy_test_suite/test_suite.py::DateHistoricTest_snowflake+snowflake::test_select_direct - AssertionError: '1727-04-01' != datetime.date(1727, 4, 1)
FAILED tests/sqlalchemy_test_suite/test_suite.py::DateTest_snowflake+snowflake::test_select_direct - AssertionError: '2012-10-15' != datetime.date(2012, 10, 15)
FAILED tests/sqlalchemy_test_suite/test_suite.py::DateTimeHistoricTest_snowflake+snowflake::test_select_direct - AssertionError: '1850-11-10 11:52:35' != datetime.datetime(1850, 11, 10, 11, 52, 35)
FAILED tests/sqlalchemy_test_suite/test_suite.py::HasSequenceTest_snowflake+snowflake::test_has_sequence_cache - AssertionError: True != False
FAILED tests/sqlalchemy_test_suite/test_suite.py::HasTableTest_snowflake+snowflake::test_has_table_cache - AssertionError: True is not False
FAILED tests/sqlalchemy_test_suite/test_suite.py::TimeMicrosecondsTest_snowflake+snowflake::test_select_direct - AssertionError: '12:57:18.000396' != datetime.time(12, 57, 18, 396)
FAILED tests/sqlalchemy_test_suite/test_suite.py::TimeTest_snowflake+snowflake::test_select_direct - AssertionError: '12:57:18' != datetime.time(12, 57, 18)
FAILED tests/sqlalchemy_test_suite/test_suite.py::TrueDivTest_snowflake+snowflake::test_floordiv_integer[15-10-1] - AssertionError: Decimal('1.500000') != 1
FAILED tests/sqlalchemy_test_suite/test_suite.py::TrueDivTest_snowflake+snowflake::test_floordiv_integer_bound - AssertionError: Decimal('1.500000') != 1

Not sure what to do about the failed tests - the row count ones failed during cleanup for me, so may work fine in CI. Datetime and integer-based tests are a bit more complicated as snowflake-sqlalchemy returns strings for datetimes and integer math works differently than decimal. The reflection caching could probably be fixed if I could figure out how it works :sweat_smile:

zmievsa commented 1 year ago

@sfc-gh-aling a gentle nudge to review this PR. We have bumped into the lack of support too. #451

aleyan commented 1 year ago

@saulbein Are you still working on this and/or do you need any assistance?

Context: jupyter depends on SQLAlchemy (^2.0.19), it is not currently possible work with snowflake-sqlalchemy in jypyter lab.

saulbein commented 1 year ago

I'm not actively working on this as while I can run most tests locally, some are very dependent on the CI setup, so I'm waiting on Snowflake folks to approve a CI run in their setup.

karanaggarwal1 commented 1 year ago

Is this on the roadmap for Snowflake? Would love for this to be resolved since we are also running into dependency issues here

joarobles commented 9 months ago

Hi there, is this on the roadmap for Snowflake?

saulbein commented 4 months ago

Closing as official support was implemented instead.