使用Python将Excel文件导入PostgreSQL表的替代方案咨询

你当前使用的pandas.to_sql方法出现查询问题，大概率是自动推断的表字段类型不符合预期、未创建主键、空值转换异常导致的，以下是3种更稳定的实现方式：

方案1：psycopg2原生批量插入（可控性最高） #

手动定义表结构和字段映射，完全控制数据转换逻辑，避免自动类型推断错误。

import os
import pandas as pd
import psycopg2
from psycopg2.extras import execute_values

# 读取Excel数据
dir_path = os.path.dirname(os.path.realpath(__file__))
df = pd.read_excel(os.path.join(dir_path, file_name), sheet_name="Sheet1")

# 数据库连接配置，替换为实际参数
conn = psycopg2.connect(
    dbname="Database",
    user="postgres",
    password="!Password",
    host="localhost",
    port="5432"
)
cur = conn.cursor()

# 可选：提前建表，自定义字段类型和主键
cur.execute("""
CREATE TABLE IF NOT EXISTS identifier (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    create_time TIMESTAMP,
    amount NUMERIC(10,2)
    -- 其他字段按需定义
)
""")

# 批量插入数据，注意调整列顺序和表字段对应
data_tuples = [tuple(x) for x in df.to_numpy()]
insert_sql = "INSERT INTO identifier (id, name, create_time, amount) VALUES %s"
execute_values(cur, insert_sql, data_tuples)

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

方案2：CSV中转 + COPY命令（大文件性能最优） #

适合10万行以上的超大Excel文件，导入速度是逐行插入的10-100倍。

import os
import pandas as pd
import psycopg2

dir_path = os.path.dirname(os.path.realpath(__file__))
df = pd.read_excel(os.path.join(dir_path, file_name), sheet_name="Sheet1")

# 生成临时CSV文件
temp_csv = os.path.join(dir_path, "temp_import.csv")
df.to_csv(temp_csv, index=False, encoding="utf-8", na_rep="\\N")

conn = psycopg2.connect(
    dbname="Database",
    user="postgres",
    password="!Password",
    host="localhost",
    port="5432"
)
cur = conn.cursor()

# 清空目标表（可选，对应原来的if_exists='replace'逻辑）
cur.execute("TRUNCATE TABLE identifier")

# 调用COPY命令导入
with open(temp_csv, 'r', encoding='utf-8') as f:
    next(f)  # 跳过表头行
    cur.copy_from(f, 'identifier', sep=',', null='\\N')

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

# 删除临时CSV
os.remove(temp_csv)

方案3：to_sql显式指定字段类型（兼容原有pandas用法） #

如果不想修改现有逻辑太多，可以手动指定每个字段的PostgreSQL类型，解决自动推断的类型错误问题。

import os
import pandas as pd
from sqlalchemy import create_engine, Integer, String, DateTime, Numeric

dir_path = os.path.dirname(os.path.realpath(__file__))
df = pd.read_excel(os.path.join(dir_path, file_name), sheet_name="Sheet1")
engine = create_engine('postgresql://postgres:!Password@localhost/Database')

# 定义字段类型映射，按需调整
dtype_map = {
    "id": Integer(),
    "name": String(100),
    "create_time": DateTime(),
    "amount": Numeric(10,2)
}

df.to_sql(
    'identifier', 
    con=engine, 
    if_exists='replace', 
    index=False,
    dtype=dtype_map  # 加上类型映射即可
)

注意：导入前建议先核对Excel字段和PostgreSQL表的字段类型、长度、非空约束是否匹配，导入后可以校验导入行数和Excel行数是否一致，避免数据丢失。

内容的提问来源于stack exchange，提问作者daniel stafford