aws / aws/amazon-redshift-python-driver

support `Cursor` attribute to provide ANSI SQL State Code

Đang mở
#220 1 bình luận 0 reaction 0 người được giao Xem trên GitHub
Ngôn ngữ chính
Python
Star
220
Fork
86
Chỉ số merge pull request
Không có pull request nào được merge trong 30 ngày

Mô tả

While a `Cursor` attribute providing SQL State Code is not officially a part of [PEP 249: Python DB API 2.0 spec](https://peps.python.org/pep-0249/), there is an ANSI-standardized "SQL state code".

Many database drivers provide this as a `Cursor` attribute, dbt was able to depend on these drivers to provide it for `ConnectionManager.get_response()` method, which will report to users after successful queries the kind of operation performed (`SELECT`, `INSERT`, `CREATE`) and the numbers of rows affected. Originally the Redshift adapter for dbt, was supported by the `psycopg2` driver, which provides this information in `statusmessage`.

As reported in https://github.com/dbt-labs/dbt-redshift/issues/785, after migrating the driver dependency to `redshift-connector`, users are in a degraded state and receive less information than previously due to the SQL state not being available.

### Support for SQL state amongst popular analytics database drivers

| Driver | `Cursor` attribute (docs) |
|--------|--------|
| psycopg2 | [`statusmessage`](https://www.psycopg.org/docs/cursor.html#cursor.statusmessage) |
| `snowflake-connector-python` | [`sqlstate`](https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-api#id7) |

### Ideal implementation

[Postgres's `CommandComplete` message](https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-COMMANDCOMPLETE)

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

Hướng dẫn đóng góp

Mở hướng dẫn đóng góp

Hướng nghiên cứu

The issue names ConnectionManager.get_response() in dbt and PostgreSQL's CommandComplete message, but no repository files or tests. Start by locating the cursor implementation and its handling of completed command metadata, then compare the available driver behavior with the listed statusmessage and sqlstate attributes. Done means the cursor exposes SQL state information and the affected-operation details are preserved for supported commands.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Đánh giá

Công nghệ
python, sql
Lĩnh vực
databases
Loại issue
Tính năng
Độ khó
4/5
Thời gian dự kiến
3-5 ngày
Mức độ hoạt động
Đình trệ
Độ rõ ràng
Khá rõ ràng
Mức phù hợp với người mới
38/100

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.