Pandas设置索引后写入MySQL，shipment_num字段为何为空？

问题现象 #

执行提供的Python代码后：

• print(df_all)的输出仅显示默认整数索引，无shipment_num列：

  qty      
      self other
  11   3.0   6.0
  

• 程序执行成功（退出码0），但写入MySQL的dfx_test_table中，shipment_num字段值全为NULL：

  shipment_num      qty,self    qty,other
    <NULL>                 3            6
  

原因分析 #

1. compare方法的默认行为：pandas的compare方法默认只保留两个DataFrame中存在差异的列。如果shipment_num在df1和df2的对应行中值完全相同，该列会被自动排除在compare的结果之外。
2. 无效的set_index操作：你尝试在compare结果上调用.set_index(['shipment_num'])，但此时结果中已经没有shipment_num列，这行代码理论上会抛出KeyError，实际执行中因数据特殊情况未报错，最终导致df_all的索引仍是默认整数索引，而非shipment_num。
3. 写入数据库时的索引映射错误：调用to_sql时，虽然指定了index_label='shipment_num'，但原索引是无意义的整数，没有对应shipment_num的有效数据，因此写入后该字段全为NULL。

解决方法 #

在执行compare之前，先将df1和df2的索引设置为shipment_num，确保compare后的结果索引就是shipment_num：

# 读取数据时直接设置索引
df1 = pd.read_sql_query("select shipment_num, commit_status,cgo_cutoff_iodt,cgo_avail_iodt,f_load_iodt,l_dsch_iodt,qty "
                        "  from cs_ship1 "
                        "order by shipment_num",
                        conn2).set_index('shipment_num')
df2 = pd.read_sql_query("select shipment_num, commit_status, cgo_cutoff_iodt,cgo_avail_iodt,f_load_iodt,l_dsch_iodt,qty "
              "  from cs_ship2 "
              "order by shipment_num",conn2).set_index('shipment_num')

# 直接执行compare，结果索引即为shipment_num
df_all = df2.compare(df1)

print(df_all)
# 此时print输出会显示shipment_num作为索引

# 写入数据库
df_all.to_sql(con=conn2, name='dfx_test_table', if_exists='replace', 
index=True, index_label='shipment_num' )

这样处理后，df_all的索引就是shipment_num，写入数据库时会将索引值正确填充到shipment_num字段中，不会再出现NULL。

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