Pandas DataFrame.to_sql仅向MySQL写入表头问题求助

我正在编写程序，通过Arduino读取气象站数据，将其按列格式化后保存到MySQL表中（后续将搭建一个调用并展示最新数据的简单网站）。我的Python代码如下：

import serial
import time
import pandas as pd
import numpy
from io import StringIO
from dbfwrite import dftodbf
import pymysql
from sqlalchemy import create_engine

serialPort = serial.Serial(port = "COM5", baudrate=115200, timeout=3)

tableName="mainframe"
weatherstring = ""
startnumber=0
dfmain=pd.DataFrame()

while (startnumber<1):
    if(serialPort.in_waiting > 0):
        currentLine = serialPort.readline()
        weatherstring = currentLine.decode('utf-8')
        check = weatherstring[0:2] == "c:"
        if check == True:
            wsio = StringIO(weatherstring)
            df1=pd.read_csv(wsio, sep=",", header=None, names = ['PacketsLostPackets','PercentOfPackets','SignalStregnth','ErrorCheck','Rain','RainRate','Humidity','Solar','Temp','UV','vcap','vsolar','WindD','WindDRaw','WindGust','WindGustD','WindV'])
            print(df1)
            dfmain=dfmain.append(df1,ignore_index=True)
            dftodbf.dbfwrite(dfmain, "C:\\Users\\jjdef\\OneDrive\\Desktop\\weather\\output\\output.dbf")
            engine = create_engine('mysql+pymysql://root:lolguy123@127.0.0.1:3306')
            with engine.connect() as connection:
                try:
                    dfmain.to_sql(tableName, connection, schema='weather', if_exists='replace');
                except ValueError as vx:
                    print(vx)
                except Exception as ex:   
                    print(ex)
                else:
                    print("Table %s created successfully."%tableName);   
                    print(dfmain)
                finally:
                    connection.close()

运行时，dfmain DataFrame每次循环都能正确打印出对应行数的数据，但在MySQL Workbench中查询仅能看到表头行，无数据内容。

问题原因及解决方法 #

• 事务未提交：使用engine.connect()获取的连接默认不会自动提交事务，to_sql执行后数据仅在当前事务中可见，未持久化到数据库，这是核心问题。
• 冗余引擎创建：每次循环都创建新的SQLAlchemy引擎，造成不必要的资源消耗。
• df.append()已弃用：该方法在新版本Pandas中已被标记为弃用，存在兼容性隐患。
• CSV解析隐患：原代码直接解析带c:前缀的字符串，可能导致第一列数据格式异常。

具体修复步骤：

1. 将引擎初始化移到循环外：避免重复创建连接引擎，提升效率。
2. 使用事务自动管理：用engine.begin()替代engine.connect()，它会自动处理事务的提交与回滚，无需手动操作。
3. 清理CSV前缀：移除字符串开头的c:，确保CSV格式正确。
4. 不写入索引列：调用to_sql时添加index=False，避免生成多余的索引列。
5. 替换df.append()为pd.concat：符合Pandas新版本规范。

修改后的代码示例：

import serial
import pandas as pd
from io import StringIO
from dbfwrite import dftodbf
from sqlalchemy import create_engine

# 初始化串口
serialPort = serial.Serial(port="COM5", baudrate=115200, timeout=3)

tableName = "mainframe"
dfmain = pd.DataFrame()

# 提前创建SQLAlchemy引擎（直接指定weather数据库，避免后续传schema）
engine = create_engine('mysql+pymysql://root:lolguy123@127.0.0.1:3306/weather')

# 持续监听串口（若只需单次处理，可改为if语句）
while True:
    if serialPort.in_waiting > 0:
        currentLine = serialPort.readline()
        weatherstring = currentLine.decode('utf-8').strip()  # 去除首尾空白字符
        if weatherstring.startswith("c:"):
            # 移除开头的"c:"，确保CSV格式正确
            clean_data_str = weatherstring[2:]
            wsio = StringIO(clean_data_str)
            
            # 定义列名
            column_names = [
                'PacketsLostPackets','PercentOfPackets','SignalStregnth',
                'ErrorCheck','Rain','RainRate','Humidity','Solar','Temp',
                'UV','vcap','vsolar','WindD','WindDRaw','WindGust',
                'WindGustD','WindV'
            ]
            
            df1 = pd.read_csv(wsio, sep=",", header=None, names=column_names)
            print(df1)
            
            # 使用concat替代append，避免弃用警告
            dfmain = pd.concat([dfmain, df1], ignore_index=True)
            
            # 写入DBF文件
            dftodbf.dbfwrite(dfmain, "C:\\Users\\jjdef\\OneDrive\\Desktop\\weather\\output\\output.dbf")
            
            # 用engine.begin()自动管理事务，无需手动提交/关闭
            with engine.begin() as connection:
                try:
                    dfmain.to_sql(
                        tableName, 
                        connection, 
                        if_exists='replace', 
                        index=False  # 不写入DataFrame的索引列
                    )
                except ValueError as vx:
                    print(vx)
                except Exception as ex:
                    print(ex)
                else:
                    print(f"Table {tableName} updated successfully.")
                    print(dfmain)

额外检查点：

• 确认clean_data_str分割后的字段数量和column_names长度一致（17个），可添加print(len(clean_data_str.split(',')))验证。
• 检查MySQL用户权限：确保root用户拥有weather数据库的写入权限。

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