将Pandas DataFrame导入PostgreSQL时的数据插入问题

核心问题点 #

• to_sql参数错误：lyrical_data.to_sql()的con参数传入了psycopg2原生连接对象，但Pandas的to_sql仅支持SQLAlchemy的引擎（Engine）或连接（Connection），原生psycopg2连接无法适配。
• SQL语法错误：创建表的SQL命令后直接拼接SELECT * from lyrical_data属于无效语法，且lyrical_data是本地DataFrame，并非数据库中已存在的表。
• 操作逻辑混乱：先尝试将数据写入data表，随后又删除并新建Lyrics_Table，前后操作无关联，导致数据无法写入目标表。

修复后的代码（推荐方案） #

import sqlalchemy as db

# 初始化SQLAlchemy引擎（仅需这一个连接方式即可）
alchemy_engine = db.create_engine('postgresql+psycopg2://postgres:dataEngineer01@localhost:5432/DrakeData')

# 直接将DataFrame写入目标表，自动处理表创建与数据插入
lyrical_data.to_sql(
    name='Lyrics_Table',
    con=alchemy_engine,
    if_exists='replace',
    index=False,
    dtype={
        'lyrics_title': db.Text,
        'lyrics': db.Text
    }
)

# 验证数据是否成功写入
with alchemy_engine.connect() as conn:
    query_result = conn.execute(db.text("SELECT current_database(), COUNT(*) FROM Lyrics_Table"))
    print(query_result.fetchone())

# 关闭引擎释放资源
alchemy_engine.dispose()

关键说明 #

• 统一使用SQLAlchemy连接：Pandas对SQLAlchemy的支持更完善，无需手动管理psycopg2的游标、事务，底层会自动处理提交逻辑。
• 明确字段类型映射：通过dtype参数指定DataFrame列与数据库字段的类型对应关系，避免自动推断出现类型不兼容问题。
• 简化操作流程：无需手动创建表，to_sql会根据DataFrame的结构自动生成表（当if_exists设为replace或append且表不存在时）。

备选方案（手动建表后插入） #

若需要手动控制表结构，可先创建表再插入数据：

import sqlalchemy as db

alchemy_engine = db.create_engine('postgresql+psycopg2://postgres:dataEngineer01@localhost:5432/DrakeData')

# 手动创建表
with alchemy_engine.connect() as conn:
    conn.execute(db.text('''
        CREATE TABLE IF NOT EXISTS Lyrics_Table (
            Lyrics_Title Text,
            Lyrics Text
        )
    '''))
    conn.commit()

# 向已创建的表中追加数据
lyrical_data.to_sql(
    name='Lyrics_Table',
    con=alchemy_engine,
    if_exists='append',
    index=False
)

# 验证数据条数
with alchemy_engine.connect() as conn:
    data_count = conn.execute(db.text("SELECT COUNT(*) FROM Lyrics_Table")).fetchone()[0]
    print(f"表中已存入{data_count}条数据")

alchemy_engine.dispose()

内容的提问来源于stack exchange，提问作者Randolf Gabrielle Uy