aws / aws/amazon-redshift-python-driver

The `Cursor.fetch_dataframe` method doesn't respect case-sensitivity

未關閉
#238 2 則留言 0 個 reaction 已指派 0 人 在 GitHub 檢視
主要語言
Python
星號
220
分支
86
PR 合併指標
30 天內沒有已合併 PR

描述

## Driver version
2.1.3

## Redshift version
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.76169

## Client Operating System
macos

## Python version
3.12.6

## Problem description

The `Cursor.fetch_dataframe` method [always lowercases](https://github.com/aws/amazon-redshift-python-driver/blob/64cbd54ef6a5e71d98ce193630d09267cc154379/redshift_connector/cursor.py#L526) column names, irrespective of the [case-sensitivity configuration value](https://docs.aws.amazon.com/redshift/latest/dg/r_enable_case_sensitive_identifier.html). This behavior is unexpected, because the resulting `DataFrame`'s columns may be treated as case-insensitive, even when that flag is set to `true`.

One example where this can be problematic is demonstrated below:

```python
from redshift_connector import connect

conn = connect()
cursor = conn.cursor()

cursor.execute('SET enable_case_sensitive_identifier TO true')
cursor.execute('WITH t AS (SELECT 1 AS "C", 2 AS "c") SELECT * FROM t')

# cursor.fetch_dataframe()
# c c
# 0 1 2

# cursor.fetch_dataframe().to_dict()
# :1: UserWarning: DataFrame columns are not unique, some columns will be omitted.
# {'c': {0: 2}}
```

## Possible solutions

I see that there is an [open PR](https://github.com/aws/amazon-redshift-python-driver/pull/232) related to this issue, but I don't think it solves it. I believe that the correct way to solve this is to get rid of the `lower()` call in line 526 altogether. That would mean that the columns produced by the driver reflect those returned by Redshift, hence respecting the case-sensitivity configuration value (see image below). I plan to open a PR with this fix soon.

Screenshot 2024-10-18 at 12 29 24 AM

----

P.S.: I see that the line of interest was introduced in the [first commit](https://github.com/aws/amazon-redshift-python-driver/blame/64cbd54ef6a5e71d98ce193630d09267cc154379/redshift_connector/cursor.py#L526) of this repo (!) and hasn't changed since. My hunch is that this was most likely an oversight, since Redshift's [documentation](http://web.archive.org/web/20200804231541/https://docs.aws.amazon.com/redshift/latest/dg/welcome.html) at the date of that commit had no mention of the `enable_case_sensitive_identifier` flag, so the driver must've not been updated to take it into account after it was introduced.

貢獻指南

開啟貢獻指南

研究方向

從 redshift_connector/cursor.py 中第 526 行附近的 fetch_dataframe 實作開始,這裡會將欄位名稱轉換為小寫。將 enable_case_sensitive_identifier 設為 true,並使用同時回傳 "C" 和 "c" 的查詢來重現此問題。完成的標準是,產生的 DataFrame 保留 Redshift 回傳的欄位名稱,包括不同的大小寫。

由索引模型根據 Issue 內容生成。

評估

技術堆疊
postgresql, python
領域
databases
Issue 類型
缺陷
難度
2/5
預估耗時
1-3 小時
活躍度
停滯
描述清晰度
描述清楚
新手友好度
50/100

把新 issue 寄到你的電子郵件信箱

精選適合新手參與的 GitHub issue 摘要。