aws / aws/amazon-redshift-python-driver

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

Aperta
#238 2 commenti 0 reazioni 0 assegnatari Vedi su GitHub
Lingua principale
Python
Stelle
220
Fork
86
Metriche di merge delle PR
Nessuna PR unita negli ultimi 30g

Descrizione

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

Guida per i contributori

Apri la guida per i contributori

Valutazione

Questa issue non è ancora stata valutata.

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.