Using CREATE TEMPORARY TABLE with pandas

I am trying to create a temporary table or drop a table in MySQL using SQLAlchemy in Jupyter notebook. The SQL function does create table or drops the table but it results in an error

ResourceClosedError: This result object does not return rows. It has been closed automatically.

Is there anyway to suppress the error if the action is performed as expected?

Here is my code:

import pandas as pd
from sqlalchemy import create_engine
# Connect to the MySQL database
engine = create_engine('mysql+pymysql://root:pwd@localhost/bikestore')

# Get tables list as a DataFrame
tables = pd.read_sql("SHOW TABLES", engine)

# Print the table names
print(tables)

pd.read_sql("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",engine)

pd.read_sql("DROP TABLE customer_orders", engine)

I expected the SQL queries to work as stated since they worked fine on MySQL Workbench. Not sure how to avoid the error.

Solution

The issue here is that CREATE TEMPORARY TABLE … AS SELECT … does not return a result set, despite the fact that there's a SELECT in there. You need to create the table and then read from it.

A further complication is that a temporary table only lasts as long as the database session (connection) in which it was created. So, you will want to ensure that you use the same connection for all of the operations that involve the temp table.

with engine.begin() as conn:
    create = """\
    CREATE TEMPORARY TABLE zzz_temp AS
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mydb'
    """
    conn.exec_driver_sql(create)
    df = pd.read_sql_query("SELECT * FROM zzz_temp", conn)  # use conn, not engine
    #
    # do the rest of your work with the DataFrame
print("Done.")