SNOW-364583: Inconsistent column name case with pd.read_sql

[adamerose]

1. What version of Python are you using (python --version)? Python 3.8.9

2. What operating system and processor architecture are you using (python -c 'import platform; print(platform.platform())')? Windows-10-10.0.19041-SP0

3. What are the component versions in the environment (pip freeze)?

pandas==1.2.4
snowflake==0.0.3
snowflake-connector-python==2.4.3
snowflake-sqlalchemy==1.2.4
SQLAlchemy==1.4.11

4. What did you do?

from snowflake.connector.pandas_tools import write_pandas
import snowflake.connector
from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine
import pandas as pd

def get_connection():
    return snowflake.connector.connect(
        account="my_account",
        user="my_user",
        password="my_password",
        database="my_database",
        CLIENT_SESSION_KEEP_ALIVE=True,
    )

def get_engine():
    return create_engine(URL(
        account="my_account",
        user="my_user",
        password="my_password",
        database="my_database",
        CLIENT_SESSION_KEEP_ALIVE=True,
    ))

engine = get_engine()
conn1 = engine.connect()
conn2 = get_connection()

# Column title is lowercase
pd.read_sql("SELECT 1 AS COLUMN_NAME", conn1)
# Column title is uppercase
pd.read_sql("SELECT 1 AS COLUMN_NAME", conn2)

5. What did you expect to see? Both statements should return the same column names

6. What did you see instead? Using a connection creating from create_engine gives lowercase column names

[sfc-gh-mkeller]

I traced the bug back to our SQLAlchemy library, here in specific: https://github.com/snowflakedb/snowflake-sqlalchemy/blob/66ab45e43e90e6cdae59e07e3c5dd34a23603755/snowdialect.py#L216

@adamerose Unfortunately this more of a design/implementation difference with the way you would like to use the library. If you require this to work then we could work on a patch to disable name normalization but you'd need to build and distribute your own version of snowflake-sqlalchemy.

@sfc-gh-hkapre I think we should not be normalizing everything, but this would be a breaking change

Otherwise, let's use this ticket to track our decision on name normalization.

[github-actions[bot]]

To clean up and re-prioritize bugs and feature requests we are closing all issues older than 6 months as of March 1, 2023. If there are any issues or feature requests that you would like us to address, please re-create them. For urgent issues, opening a support case with this link Snowflake Community is the fastest way to get a response