Pandas向MariaDB插入数据时含空格子查询条件值的转义问题

问题描述 #

使用Python+Pandas将CSV文件导入MariaDB时，插入语句包含两个子查询。当子查询条件值（如供应商名称“example dealer”）包含空格时，子查询仅匹配“example”并返回NULL，触发Column 'Fornitori_idFornitori' cannot be null错误。已尝试修改CSV编码、查询转义，问题未解决。

用户代码片段：

empdata = pd.read_csv(
    "static/files/testfile.csv",
    index_col=False, delimiter=";", on_bad_lines="skip"
)

if conn.is_connected():
    cursor = conn.cursor()
    cursor.execute("select database();")
    record = cursor.fetchone()
    print("You're connected to database: ", record)
    # loop through the data frame
    for i, row in empdata.iterrows():
        sql = "INSERT INTO PRODOTTI (PROD_ATTIVO,EAN13,prod_nome,Prezzo,CAT_IVA_idCAT_IVA,Costo,Quantita,Fornitori_idFornitori,Data_ins) \
                VALUES (%s,%s,%s,%s,(select idCAT_IVA from CAT_IVA where CAT_IVA_aliquota = %s),%s,%s,(select idFornitori from Fornitori where Fornitori_nome = %s),%s)"

解决方案 #

1. 修复参数化查询的传递逻辑 #

你的代码仅定义了SQL语句，缺少cursor.execute()的参数传递步骤。若错误使用字符串拼接而非参数绑定传递带空格的字段值，会导致字段被拆分。正确做法是将DataFrame行数据作为元组传入execute()：

for i, row in empdata.iterrows():
    sql = "INSERT INTO PRODOTTI (PROD_ATTIVO,EAN13,prod_nome,Prezzo,CAT_IVA_idCAT_IVA,Costo,Quantita,Fornitori_idFornitori,Data_ins) \
            VALUES (%s,%s,%s,%s,(select idCAT_IVA from CAT_IVA where CAT_IVA_aliquota = %s),%s,%s,(select idFornitori from Fornitori where Fornitori_nome = %s),%s)"
    # 替换为DataFrame中对应的列名，确保顺序与SQL中的%s一一对应
    values = (
        row['PROD_ATTIVO'], row['EAN13'], row['prod_nome'], row['Prezzo'],
        row['CAT_IVA_aliquota'], row['Costo'], row['Quantita'],
        row['Fornitori_nome'], row['Data_ins']
    )
    cursor.execute(sql, values)
conn.commit()  # 必须提交事务才能保存数据

2. 处理数据中的前后空格 #

若Fornitori表的Fornitori_nome字段或CSV中的供应商名称存在前后空格，会导致匹配失败。修改子查询，用TRIM()去除两端空格：

(select idFornitori from Fornitori where TRIM(Fornitori_nome) = TRIM(%s))

3. 预加载映射字典，避免重复子查询 #

循环中重复执行子查询效率低且易出匹配问题，建议先从数据库读取映射关系，直接在循环中查找：

# 预加载供应商名称与ID的映射
cursor.execute("SELECT idFornitori, Fornitori_nome FROM Fornitori")
fornitori_map = {nome.strip(): id_ for id_, nome in cursor.fetchall()}

# 预加载税目比例与ID的映射
cursor.execute("SELECT idCAT_IVA, CAT_IVA_aliquota FROM CAT_IVA")
iva_map = {str(aliquota): id_ for id_, aliquota in cursor.fetchall()}

# 循环插入数据
for i, row in empdata.iterrows():
    fornitori_id = fornitori_map.get(row['Fornitori_nome'].strip())
    iva_id = iva_map.get(str(row['CAT_IVA_aliquota']))
    
    if not fornitori_id:
        print(f"警告：未找到供应商 {row['Fornitori_nome']}，跳过该行")
        continue
    
    sql = "INSERT INTO PRODOTTI (PROD_ATTIVO,EAN13,prod_nome,Prezzo,CAT_IVA_idCAT_IVA,Costo,Quantita,Fornitori_idFornitori,Data_ins) \
            VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    values = (
        row['PROD_ATTIVO'], row['EAN13'], row['prod_nome'], row['Prezzo'],
        iva_id, row['Costo'], row['Quantita'], fornitori_id, row['Data_ins']
    )
    cursor.execute(sql, values)
conn.commit()

4. 验证CSV读取的完整性 #

确认Pandas读取的供应商名称是否完整：

print(empdata['Fornitori_nome'].head())

若输出名称被截断或拆分，检查CSV是否用引号包裹带空格的字段（如"example dealer";...），若未包裹，添加quoting参数：

import csv

empdata = pd.read_csv(
    "static/files/testfile.csv",
    index_col=False, delimiter=";", on_bad_lines="skip",
    quoting=csv.QUOTE_ALL
)

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