在Jupyter中用SQLAlchemy操作MySQL临时表时遇ResourceClosedError

这个错误的根源是pd.read_sql()设计用来执行返回结果集的SQL语句（比如SELECT、SHOW TABLES），但CREATE TABLE、DROP TABLE这类DDL操作不会返回行数据，所以pandas尝试读取结果时就会触发这个错误。

解决方法很简单：直接用SQLAlchemy的连接对象执行这些无返回的SQL，而不是通过pandas的read_sql。

具体步骤 #

1. 从SQLAlchemy导入text函数，用来封装SQL语句
2. 使用上下文管理器获取数据库连接，执行目标SQL并提交（MySQL默认自动提交，但显式提交更可靠）

示例代码 #

创建临时表

from sqlalchemy import create_engine, text
import pandas as pd

# 初始化连接
engine = create_engine('mysql+pymysql://root:pwd@localhost/bikestore')

# 执行创建临时表的SQL
with engine.connect() as conn:
    conn.execute(text("""
        CREATE TEMPORARY TABLE customer_orders 
        AS SELECT x.*, y.order_status, z.item_id, z.product_id, z.quantity, z.list_price, z.discount, (quantity*list_price) as sale 
        FROM bikestore.customers x 
        LEFT JOIN  bikestore.orders y on x.customer_id = y.customer_id 
        LEFT JOIN bikestore.order_items z on y.order_id = z.order_id
    """))
    conn.commit()  # 提交操作

删除表

with engine.connect() as conn:
    conn.execute(text("DROP TABLE customer_orders"))
    conn.commit()

补充说明 #

• 如果你之后需要查询这个临时表的数据，仍然可以用pd.read_sql()来读取：

  df = pd.read_sql("SELECT * FROM customer_orders", engine)
  

• SHOW TABLES这类返回结果集的语句，继续用pd.read_sql()完全没问题。

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