如何用Python处理PostgreSQL大小写敏感的带双引号列名问题

PostgreSQL对标识符（表名、列名）的大小写敏感规则是：未加双引号的标识符会被自动转为小写，而带双引号的大写列名必须始终用双引号包裹才能被正确识别。你之前修改DataFrame列名为小写的操作无效，就是因为生成的SQL会用未加引号的name去匹配表中带引号的"NAME"列，自然找不到。

以下是几种可行的Python代码解决方案：

方案1：用SQLAlchemy自定义插入语句（适配pandas to_sql） #

通过构造带双引号的列名映射，强制SQLAlchemy生成正确的插入语句：

from sqlalchemy import create_engine, text
import pandas as pd

# 示例DataFrame（列名为小写name）
trg_df = pd.DataFrame({'name': ['USD', 'EUR']})

# 初始化PostgreSQL连接
engine = create_engine('postgresql://user:password@host:port/db_name')

# 定义DataFrame列到目标列的映射（目标列必须用双引号包裹）
col_mapping = {
    'name': text('"NAME"')
}

# 自定义插入逻辑，确保列名带双引号
trg_df.to_sql(
    name='tblmexchange',
    con=engine,
    if_exists='append',
    index=False,
    method=lambda conn, frame, table_name, keys, data_iter: (
        conn.execute(
            text(f"INSERT INTO {table_name} ({', '.join([str(col_mapping[k]) for k in keys])}) VALUES ({', '.join([':' + k for k in keys])})"),
            [dict(zip(keys, row)) for row in data_iter]
        )
    )
)

方案2：修改DataFrame列名为带双引号的大写 #

直接将DataFrame列名设置为带双引号的格式，同时禁用pandas的自动引号处理：

import pandas as pd
from sqlalchemy import create_engine

# 将DataFrame列名替换为带双引号的大写
trg_df.columns = ['"NAME"']

engine = create_engine('postgresql://user:password@host:port/db_name')

# 写入时关闭自动引号生成，避免重复加引号
trg_df.to_sql(
    name='tblmexchange',
    con=engine,
    if_exists='append',
    index=False,
    quote_sqlident=False
)

方案3：原生psycopg2直接执行SQL #

如果不需要pandas的封装，直接用psycopg2构造带双引号的插入语句：

import psycopg2
import pandas as pd

trg_df = pd.DataFrame({'name': ['GBP', 'JPY']})

# 建立连接
conn = psycopg2.connect("dbname=your_db user=your_user password=your_pass host=your_host")
cur = conn.cursor()

# 逐行插入，列名用双引号包裹
for _, row in trg_df.iterrows():
    cur.execute('INSERT INTO tblmexchange ("NAME") VALUES (%s)', (row['name'],))

conn.commit()
cur.close()
conn.close()

内容的提问来源于stack exchange，提问作者Afif Pratama