Python SQLAlchemy 以小写形式导入表名 (Snowflake)

Question

同时使用 pandas.read_sql 和 pandas.read_sql_table，我不断取回整个表，其中所有列名均为小写。有什么办法吗？

我想对数据进行一些转换，然后替换数据库中的表，但如果这样做会将所有列名更改为小写，那会很痛苦。

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

Answer 1

Snowflake stores all case-insensitive object names in uppercase text.In contrast, SQLAlchemy considers all lowercase object names to becase-insensitive. Snowflake SQLAlchemy converts the object name caseduring schema-level communication, i.e. during table and indexreflection. If you use uppercase object names, SQLAlchemy assumes theyare case-sensitive and encloses the names with quotes. This behaviorwill cause mismatches agaisnt data dictionary data received fromSnowflake, so unless identifier names have been truly created as casesensitive using quotes, e.g., "TestDb", all lowercase names should beused on the SQLAlchemy side.

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

lowercase 是 Sqlalchemy 中的默认行为。如果没有必要，你不应该在 Sqlalchemy 或 pandas 中使用大写字母。您可以在 Sqlalchemy 中使用 "..." 或 quote_names 来指定区分大小写。如果您坚持为所有列获取大写字母，这篇使用事件监听器的帖子可能会有所帮助 https://stackoverflow.com/a/34322171/12032355