Python迁移MySQL数据至PostgreSQL遇PostgreSQL语法错误

核心错误原因 #

1. 列定义不完整：从MySQL DESCRIBE 获取列信息时，只提取了列名，未添加PostgreSQL要求的数据类型，导致生成的CREATE TABLE语句语法无效（PostgreSQL要求每个列必须明确数据类型）。
2. 单execute执行多语句：psycopg2的游标默认不支持一次执行多条SQL语句（用分号分隔的CREATE和ALTER）。
3. SQL构造方式错误：混合使用sql.SQL和f-string直接拼接标识符，容易引发语法冲突和注入风险。

修复步骤与代码示例 #

1. 定义MySQL到PostgreSQL的数据类型映射 #

先整理常见类型的对应关系，根据实际表结构补充更多类型：

mysql_to_pg_types = {
    'int': 'INT',
    'varchar': 'VARCHAR',
    'text': 'TEXT',
    'datetime': 'TIMESTAMP',
    'decimal': 'NUMERIC',
    'float': 'FLOAT',
    'bool': 'BOOLEAN',
}

2. 修正迁移代码 #

for table in mysql_tables:
    table_name = table[0]
    mysql_cursor.execute(f"DESCRIBE {table_name}")
    mysql_columns = mysql_cursor.fetchall()
    
    # 转换列定义为PostgreSQL兼容格式（列名+数据类型）
    pg_columns = []
    for col in mysql_columns:
        col_name = col[0]
        # 提取MySQL数据类型的基础部分（如varchar(255)取varchar）
        mysql_type = col[1].split('(')[0].lower()
        # 映射到PostgreSQL类型，默认用TEXT兜底
        pg_type = mysql_to_pg_types.get(mysql_type, 'TEXT')
        pg_columns.append(f'"{col_name}" {pg_type}')
    
    # 构造并执行CREATE TABLE语句（单独执行）
    create_table_query = sql.SQL("CREATE TABLE IF NOT EXISTS incomes.{table} ({columns}) TABLESPACE pg_default;").format(
        table=sql.Identifier(table_name),
        columns=sql.SQL(', ').join(map(sql.SQL, pg_columns))
    )
    postgres_cursor.execute(create_table_query)
    
    # 单独执行ALTER TABLE语句
    alter_table_query = sql.SQL("ALTER TABLE IF EXISTS incomes.{table} OWNER TO postgres;").format(
        table=sql.Identifier(table_name)
    )
    postgres_cursor.execute(alter_table_query)
    
    # 迁移数据（用executemany批量插入，效率更高）
    mysql_cursor.execute(f"SELECT * FROM {table_name}")
    rows = mysql_cursor.fetchall()
    
    if rows:
        insert_query = sql.SQL("INSERT INTO incomes.{table} VALUES ({placeholders})").format(
            table=sql.Identifier(table_name),
            placeholders=sql.SQL(', ').join(sql.Placeholder() * len(rows[0]))
        )
        postgres_cursor.executemany(insert_query, rows)

关键修复点说明 #

• 补全数据类型：解决了CREATE TABLE语句的核心语法错误。
• 拆分SQL语句：将CREATE和ALTER分开执行，符合psycopg2的执行规则。
• 安全构造SQL：用sql.Identifier处理表名/列名，自动转义特殊字符和关键字，避免语法冲突。
• 批量插入：替换循环单条插入为executemany，大幅提升数据迁移效率。

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