如何检测CSV空单元格并在SQL查询中替换为NULL

我有一个动态CSV文件，部分单元格初始为空，后续会被填充数据。我将该CSV文件实时转换为SQL并上传至数据库，但CSV中的空单元格在数据库中显示为空值而非NULL。请问能否在SQL上传前检测空单元格并将其设置为NULL？

import MySQLdb
import csv
import sys

db = MySQLdb.connect("localhost", "root", "", "point")
cur = db.cursor()
csv_data = csv.reader(open("/home/dboo/data1/data/Fiverphy/tablecells222.csv"))
header = next(csv_data)

cur.execute('DROP TABLE IF EXISTS tablecells222')
cur.execute('''CREATE TABLE tablecells222(
   id               INTEGER  NOT NULL PRIMARY KEY 
  ,less_id        INTEGER  NOT NULL
  ,datetime         VARCHAR(25) NOT NULL
  ,status           VARCHAR(4) NOT NULL
  ,less              VARCHAR(30)
  ,team1            VARCHAR(15) NOT NULL
  ,team2            VARCHAR(15) NOT NULL
  ,team1         INTEGER  NOT NULL
  ,team2         INTEGER  NOT NULL
  ,team1_code       VARCHAR(3) NOT NULL
  ,team2_code       VARCHAR(3) NOT NULL
  ,prob1            VARCHAR(19) NOT NULL
  ,prob2            VARCHAR(20) NOT NULL
  ,prob3          VARCHAR(19) NOT NULL
  ,round            VARCHAR(30)
  ,day         VARCHAR(30)
  ,score1           NUMERIC(3)
  ,score2           NUMERIC(3)
  ,adj_score1       VARCHAR(18)
  ,adj_score2       VARCHAR(18)
  ,chances1         VARCHAR(18)
  ,chances2         VARCHAR(19)
  ,moves1           VARCHAR(19)
  ,moves2           VARCHAR(19)
  ,aggregate VARCHAR(30)
  ,shootout  VARCHAR(30)
);
''')

for row in csv_data:
    print(row)
    cur.execute("INSERT IGNORE INTO tablecells222(id,less_id,datetime,status,less,team1,team2,team1_id,team2_id,team1_code,team2_code,prob1,prob2,prob3,round,day,score1,score2,adj_score1,adj_score2,chances1,chances2,moves1,moves2,aggregate,shootout) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);", row)

db.commit()
cur.close()
print("Done")

可以在SQL上传前处理CSV行数据，将空单元格转为Python的None，MySQLdb会自动将None映射为SQL的NULL。

修改后的核心代码 #

在遍历CSV行的循环中，新增数据处理步骤：

for row in csv_data:
    # 将空字符串（含仅空白字符的单元格）转为None
    processed_row = [None if cell.strip() == '' else cell for cell in row]
    print(processed_row)
    cur.execute("INSERT IGNORE INTO tablecells222(id,less_id,datetime,status,less,team1,team2,team1_id,team2_id,team1_code,team2_code,prob1,prob2,prob3,round,day,score1,score2,adj_score1,adj_score2,chances1,chances2,moves1,moves2,aggregate,shootout) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);", processed_row)

额外注意事项 #

1. 字段名修正：原建表语句中存在字段名错误，team1 INTEGER NOT NULL和team2 INTEGER NOT NULL应该改为team1_id INTEGER NOT NULL和team2_id INTEGER NOT NULL，否则插入时会因字段不匹配报错。
2. NOT NULL约束：对于建表时标记为NOT NULL的字段，确保CSV中这些字段不会出现空值，否则INSERT IGNORE会直接忽略该行数据。

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