Add basic compatibility with SQLAlchemy 2.0

[saulbein]

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.

• With 1.4 pytest all tests succeeded.
• With 1.4 pytest tests/sqlalchemy_test_suite all tests succeeded.
• With 1.4 pytest --run_v20_sqlalchemy all tests succeeded.
• With 1.4 pytest --run_v20_sqlalchemy tests/sqlalchemy_test_suite row count tests failed:

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:

• With 2.0 pytest --run_v20_sqlalchemy (can't run without the flag, will error out) no tests failed.
• With 2.0 pytest --run_v20_sqlalchemy tests/sqlalchemy_test_suite row count, date format, integer and some reflection caching tests failed (I assume newly added in 2.0):

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]

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

[aleyan]

@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]

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]

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

[joarobles]

Hi there, is this on the roadmap for Snowflake?