search

databricks / databricks-sql-python

Databricks SQL Connector for Python
Apache License 2.0
155 stars 90 forks source link

support new Cursor attribute that provides information on completed commands #388

Open dataders opened 5 months ago

dataders commented 5 months ago

similar to: https://github.com/aws/amazon-redshift-python-driver/issues/220

While a Cursor attribute providing SQL State Code is not officially a part of PEP 249: Python DB API 2.0 spec, it's a common enough convention and would greatly enhance the user experience of dbt-databricks users (👀 @benc-db)

Many database drivers provide this as a Cursor attribute, dbt was able to depend on these drivers to provide it for a ConnectionManager.get_response() method, which reports to users after successful queries the kind of operation performed (SELECT, INSERT, CREATE) and the numbers of rows affected.

However, this is not fully supported today in dbt-databrick, see DatabricksConnectionManager.get_response() where message is hardcoded as "OK" instead of returning more information.

Support for SQL state amongst popular analytics database drivers

Driver Cursor attribute (docs)
psycopg2 statusmessage
snowflake-connector-python sqlstate

Ideal implementation

Follow Postgres's CommandComplete message

Command Tag rows indicates the number of rows
INSERT INSERT 0 rows inserted
DELETE DELETE rows deleted
UPDATE UPDATE rows updated
MERGE MERGE rows inserted, updated, or deleted
SELECT / CREATE TABLE AS SELECT rows retrieved
MOVE MOVE rows ursor's position has been changed by
FETCH FETCH rows that have been retrieved from the cursor
COPY COPY rows copied, only in PostgreSQL 8.2 and later
kravets-levko commented 5 months ago

Hi @dataders! Thank you for this suggestion, it is indeed a great feature to have in the driver (and in our other drivers too) 🙂 Though, it will depend on server support, so we need to check what it can offer us. I'll get back to you once I have any updates on this