René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Support transactions (and pandas.to_sql / read_sql_table)","articleBody":"It's a great project, and truly helps me in a variety of ways. However, I am having a few issues when utilizing it with Pandas.\r\n\r\nWhen reading a table I would like to do:\r\n\r\n```python\r\n    def read(\r\n        self, catalog_name: str, schema_name: str, table_name: str\r\n    ) -\u003e DataFrame:\r\n        with self._get_connection(catalog_name) as connection:\r\n            iterator = pd.read_sql_table(\r\n                schema=schema_name,\r\n                table_name=table_name,\r\n                con=connection,\r\n            )\r\n```\r\nThis should return a pandas dataframe.\r\n\r\nHowever, I am required to do the following lacking features from your otherwise great connection. \r\n```python\r\ndef read(\r\n        self, catalog_name: str, schema_name: str, table_name: str\r\n    ) -\u003e pd.DataFrame:\r\n        with self._get_connection(catalog_name) as connection:\r\n            query = f\"SELECT * FROM {catalog_name}.{schema_name}.{table_name}\"\r\n            self.logger.debug(\"Running query '%s'\", query)\r\n            df = pd.read_sql(query, connection)\r\n            self.logger.debug(df)\r\n            return df\r\n```\r\n\r\n       \r\nSimilarly I cannot use the `to_sql` on a dataframe where i'd like to do something like the following:\r\n```python\r\n    def write(\r\n        self,\r\n        dataframe: pd.DataFrame,\r\n        catalog_name: str,\r\n        schema_name: str,\r\n        table_name: str,\r\n    ) -\u003e pd.DataFrame:\r\n        with self._get_connection(catalog_name) as connection:\r\n            dataframe.to_sql(name=table_name, con=connection, schema=schema_name)\r\n``` \r\n\r\nBoth fail\r\n```\r\ndatabricks.sql.exc.NotSupportedError: Transactions are not supported on Databricks\r\n````\r\n\r\n## Versions:\r\n\r\n```\r\npython 3.10.6\r\ndatabricks-sql-connector==2.2.1\r\npandas==1.5.2\r\n\r\n```","author":{"url":"https://github.com/C0DK","@type":"Person","name":"C0DK"},"datePublished":"2022-12-14T12:36:37.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":5},"url":"https://github.com/72/databricks-sql-python/issues/72"}