Python SQLAlchemy Importing Table Names in Lowercase (Snowflake)

user_10935257 0
Viewed 781

Using both pandas.read_sql as well as pandas.read_sql_table, I keep getting the entire table back with all the column names in lowercase. Is there anyway around this?

I wanted to do some transformations on the data then replace the table in the DB, but it's a pain if doing so changes all the column names to lowercase.

#both of these produce the same lowercase columns
    sql = 'SELECT * from "DB"."SCHEMA"."'+"tablename"+'"; '
        
    df = pd.read_sql(
    sql,
    con=engine
    )

    df = pd.read_sql_table(
    "tablename",
    con=engine
    )

enter image description here

snowflake-cloud-data-platform pandas sqlalchemy sql python
2 Answers

Snowflake stores all case-insensitive object names in uppercase text. In contrast, SQLAlchemy considers all lowercase object names to be case-insensitive. Snowflake SQLAlchemy converts the object name case during schema-level communication, i.e. during table and index reflection. If you use uppercase object names, SQLAlchemy assumes they are case-sensitive and encloses the names with quotes. This behavior will cause mismatches agaisnt data dictionary data received from Snowflake, so unless identifier names have been truly created as case sensitive using quotes, e.g., "TestDb", all lowercase names should be used on the SQLAlchemy side.

https://github.com/snowflakedb/snowflake-sqlalchemy

lowercase is default behavior in Sqlalchemy. You should not using uppercase in Sqlalchemy or pandas if it is not necessary to. You can either use "..." or quote_names in Sqlalchemy to specify case-sensitive. If you insists on getting uppercase columns for all, this post of using events listener could be helpful https://stackoverflow.com/a/34322171/12032355

user_120323550
user_20199780
Related