databricks
support new Cursor attribute that provides information on completed commands
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 |
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
