Python从ODK Central API导入MySQL仅插入10条数据问题排查

问题 #

尝试将ODK Central API的提交数据插入MySQL表，23751条数据仅成功插入10条。也试过从下载的JSON文件插入，循环能遍历所有记录且无超时错误。代码如下：

import json
import mysql.connector
from datetime import datetime

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database="mapped_customers"
)

f = open("submissions.json", "r")

data=f.read()

data= json.loads(data)
count=0
try:
   for customer in data['value']:

      count+=1
      print(count)

      # Input timestamp in ISO 8601 format
      timestamp = customer['__system']['submissionDate']

      # Convert to datetime object
      dt_object = datetime.strptime(timestamp, '%Y-%m-%dT%H:%M:%S.%fZ')

      # Format datetime object as MySQL datetime string
      mysql_datetime = dt_object.strftime("%Y-%m-%d %H:%M:%S")

      cust_name=customer['shop_name']
      cust_contact=customer['daily_contact_number']
      contact_person=customer['shop_owner']
      cust_category=customer['customer_category']
      latitude=customer['store_gps']['coordinates'][1]
      longitude=customer['store_gps']['coordinates'][0]
      cust_img=customer['photo']
      location=customer['zone_name']
      landmark=customer['landmark']
      mapper=customer['mapper']
      submission_date=mysql_datetime
      importance=customer['importance']
      sales=customer['sales']
      purchases=customer['purchases']
      alt_contact=customer['alternative_phone_number']

      cursor = db.cursor()
      sql="INSERT INTO gsmrt_cstmr_vrfctn(CSTMR_NME,CST_CNTCT,CNTCT_PRSN,CSTMR_CLSS,CSTMR_LTTD,CSTMR_LNGTD,CSTMR_IMG,CSTMR_LCTN,CSTMR_LND_MRK,MAPPER,SUBMSN_DTE,IMPORTNCE,SLS,PURCHSES,ALT_CNTCT) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
      val=(cust_name,cust_contact,contact_person,cust_category,latitude,longitude,cust_img,location,landmark,mapper,submission_date,importance,sales,purchases,alt_contact)
      cursor.execute(sql,val)
      db.commit()
   db.close()
except (mysql.connector.Error, mysql.connector.Warning) as e:
      print(e)
     
print("Done!") 

请问哪里操作有误？

分析与解决 #

核心问题：异常捕获范围过窄 #

你的except块仅捕获了MySQL连接器相关的错误，但遍历JSON数据时，字段缺失、格式异常（比如某条数据没有store_gps字段，或coordinates数组长度不足2）会抛出KeyError、IndexError等非MySQL异常，这类异常会直接终止整个循环，导致后续数据无法插入。

比如前10条数据的所有字段都完整，第11条缺失某个必填字段，循环就会在这里中断，最终只插入10条数据。

修复步骤 #

1. 扩大异常捕获范围，记录错误数据的位置，方便排查，同时跳过错误数据继续执行：

   try:
       # 循环逻辑...
   except (mysql.connector.Error, mysql.connector.Warning) as e:
       print(f"MySQL错误，第{count}条数据: {e}")
       continue
   except Exception as e:
       print(f"数据解析错误，第{count}条数据: {e}")
       continue
   

2. 优化Cursor创建，避免每次循环新建Cursor，提升效率：

   cursor = db.cursor()  # 将Cursor初始化移到循环外
   for customer in data['value']:
       # ...数据处理...
       cursor.execute(sql, val)
   

3. 批量提交数据，单条提交会大幅降低插入速度，建议每N条提交一次：

   batch_size = 100
   for idx, customer in enumerate(data['value'], 1):
       # ...数据处理...
       cursor.execute(sql, val)
       if idx % batch_size == 0:
           db.commit()
           print(f"已提交{idx}条数据")
   db.commit()  # 提交剩余未批量的数据
   

4. 处理可选字段的默认值，避免因字段缺失抛出KeyError：

   cust_name = customer.get('shop_name', '')  # 字段不存在则设为空字符串
   latitude = customer.get('store_gps', {}).get('coordinates', [0,0])[1]
   

额外建议 #

• 打印第11条数据的完整内容，检查是否存在字段缺失或格式异常，快速定位问题来源。
• 确认MySQL表的字段类型与插入值匹配，比如CSTMR_LTTD和CSTMR_LNGTD应为浮点型，SUBMSN_DTE应为datetime类型。

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