pandas to_sql写入MySQL：创建空表且无法替换的问题求助

问题现象 #

• 首次执行Pandas的df.to_sql()仅创建空表，无数据写入
• 二次执行时程序卡住，MySQL进程列表显示DROP TABLE name处于Waiting for table metadata lock状态

用户代码 #

import pandas as pd
import numpy as np
from Modules import connect
from sqlalchemy.orm import close_all_sessions

df = pd.DataFrame(np.ones((3, 3)))
df.columns=['a','b','c']

con_=connect.local_connect(login,password,database)
df.to_sql(name='name',con=con_,if_exists='replace',index=False)
close_all_sessions()

首次执行查询结果 #

mysql> select * from name;
Empty set (0.00 sec)

二次执行时MySQL进程列表 #

mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+---------------------------------+------------------+
| Id | User            | Host            | db   | Command | Time | State                           | Info             |
+----+-----------------+-----------------+------+---------+------+---------------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  |  768 | Waiting on empty queue          | NULL             |
| 11 | root            | localhost:10003 | database | Sleep   |  146 |                                 | NULL             |
| 13 | root            | localhost:10839 | database | Query   |    0 | init                            | show processlist |
| 14 | root            | localhost:12673 | database | Query   |    2 | Waiting for table metadata lock | DROP TABLE name |
+----+-----------------+-----------------+------+---------+------+---------------------------------+------------------+
4 rows in set (0.00 sec)

问题原因 #

1. 事务未提交+连接未关闭：mysqlclient默认是手动提交事务模式，to_sql()执行后数据未提交，导致表为空；同时连接未关闭，持有表的元数据锁。
2. close_all_sessions()无效：该方法仅针对SQLAlchemy ORM会话生效，若connect.local_connect()返回的是原生mysqldb连接，此方法无法关闭连接，锁资源一直被占用。
3. 遗留Sleep连接占用锁：进程列表中的Sleep状态连接（ID11）是之前未关闭的连接，持续持有表锁，导致二次执行时DROP TABLE操作无法获取元数据锁而卡住。

修复方案 #

1. 正确处理数据库连接与事务 #

根据connect.local_connect()返回的连接类型，选择对应处理方式：

情况1：返回原生mysqldb连接

import pandas as pd
import numpy as np
from Modules import connect

df = pd.DataFrame(np.ones((3, 3)))
df.columns=['a','b','c']

# 创建连接并开启自动提交
con_ = connect.local_connect(login,password,database)
con_.autocommit(True)

# 写入数据
df.to_sql(name='name', con=con_, if_exists='replace', index=False)

# 手动关闭连接释放锁
con_.close()

情况2：返回SQLAlchemy引擎（推荐使用上下文管理器）

import pandas as pd
import numpy as np
from Modules import connect

df = pd.DataFrame(np.ones((3, 3)))
df.columns=['a','b','c']

# 使用上下文管理器自动管理连接与事务
engine = connect.local_connect(login,password,database)
with engine.connect() as con:
    df.to_sql(name='name', con=con, if_exists='replace', index=False)
    con.commit()  # 显式提交事务（部分版本SQLAlchemy需手动提交）

2. 清理遗留的Sleep连接 #

手动杀掉占用锁的Sleep进程：

KILL 11;

3. 移除无效的close_all_sessions()调用 #

直接删除代码中的from sqlalchemy.orm import close_all_sessions和close_all_sessions()语句，该操作对当前场景无意义。

验证修复结果 #

执行修复后的代码后，查询表数据：

mysql> select * from name;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 1 |
| 1 | 1 | 1 |
| 1 | 1 | 1 |
+---+---+---+
3 rows in set (0.00 sec)

二次执行代码不会出现卡住情况，表会被正常替换并写入数据。

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