Support transactions (and pandas.to_sql / read_sql_table) about databricks-sql-python

[susodapop]

Final: you can follow the development of the internal dialect here #57. Here's an e2e test that uses read_sql_table successfully: https://github.com/databricks/databricks-sql-python/pull/57/files#diff-8390b059d76e4a7f773fb17db2682302d2fbe1768408b5dc666a1bab0e576ad1R43-R56

[susodapop]

I'm not surprised you're seeing issues here. databricks-sql-connector doesn't support SQLAlchemy as of 2.2.1. We are working on a native dialect in a feature branch. And in our e2e tests with this native dialect read_sql works as you'd expect. We'll see if your issue reproduces once we merge to main. For now I'm closing this as we would not expect it to work unless you've installed one of our dev builds.

[susodapop]

Also note that Databricks doesn't support transactions. But this isn't required for SQLAlchemy to work. In our native dialect we disable these warnings 👍

[C0DK]

do you know how i'd be able to write a table then? I'd rather not have to write the whole SQL statement by hand. I can use the read_sql fine, but I don't have a similar thing working where it can write a table.

[susodapop]

You can use the experimental dialect or wait til it merges to main (should be a few weeks, we're in late beta now). With the dialect installed you just call to_sql and it works.