Python迁移MySQL表数据时NULL转None触发1054语法错误如何解决

1. 核心错误来自手动拼接SQL语句与数据参数：Python中对应MySQL NULL值的类型是None，直接将包含None的元组转为字符串拼接进SQL时，None会被转为字符串'None'，而MySQL可识别的空值关键字为NULL，SQL解析时会把None识别为字段名，直接触发Unknown column 'None' in 'field list'报错。
2. 除此之外，代码中直接拼接Python datetime类型对象的字符串形式，也不符合MySQL时间类型的语法要求，会额外触发语法错误。
3. 隐藏问题：mycursor.execute("SELECT id FROM workspaces")查询后用fetchall()拿到的每行结果是元组格式（比如(1,)），直接拼接进库名字符串会生成错误的库名，导致后续库操作异常。
4. 手动拼接SQL本身存在SQL注入风险，属于不规范的数据库操作写法。

使用MySQL Connector原生支持的参数化查询代替手动SQL拼接，驱动会自动完成不同语言类型的适配转换，同时规避SQL注入风险：

• 数据操作（INSERT/SELECT等带值的操作）用%s作为参数占位符，将数据元组作为execute()方法的第二个参数传入即可，驱动会自动把None转为MySQL的NULL、datetime对象转为符合要求的时间格式、自动转义字符串特殊字符。
• 库名、表名这类SQL标识符无法参数化，可加反引号包裹避免特殊字符引发的语法问题。
• 修正id取值逻辑，从返回的行元组中取出实际id值使用。

修正后的核心代码 #

import mysql.connector
import os
import configparser

mydb = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="testdb")
mycursor = mydb.cursor()
# 导出schema
os.popen("mysqldump -h localhost -u root -p1234 --no-data testdb > testdb.sql")
mycursor.execute("SELECT id FROM workspaces")
myresult = mycursor.fetchall()

# 预定义INSERT语句，用%s做参数占位符，占位符数量需和workspaces表字段数量对应
INSERT_SQL = "INSERT INTO workspaces VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
# 也可以指定字段写法更稳妥：INSERT INTO workspaces(字段1,字段2,字段3...) VALUES (%s,%s,%s...)

for row in myresult:
    # 取出实际id值
    ws_id = row[0]
    dbname = f"ak_shc_{ws_id}"
    print(dbname)
    input_db = mysql.connector.connect(host="localhost", user="root", passwd="1234")
    inputcursor = input_db.cursor()
    # 库名用反引号包裹
    inputcursor.execute(f"CREATE DATABASE IF NOT EXISTS `{dbname}`")
    os.popen(f"mysql -h localhost -u root -p1234 `{dbname}` < testdb.sql")
    
    insert_data = mysql.connector.connect(host="localhost", user="root", passwd="1234", database=dbname)
    insertcursor = insert_data.cursor()
    
    # 查询源数据用参数化
    mycursor.execute("SELECT * FROM workspaces WHERE id = %s", (ws_id,))
    values = mycursor.fetchall()
    for a in values:
        print(a)
        # 传入参数元组，不要手动拼接
        insertcursor.execute(INSERT_SQL, a)
    # 提交事务，否则插入的数据不会生效
    insert_data.commit()

额外注意事项 #

• 参数化查询的占位符%s不需要加引号，驱动会自动根据参数类型处理。
• 批量插入数据时可使用executemany()方法提升性能。
• 所有修改数据的操作执行后需要调用连接对象的commit()方法，否则事务不会提交，数据不会写入数据库。

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