Create a temporary table in MySQL using Pandas

Question

Pandas has a great feature, where you can write your dataframe to a table in SQL.

df.to_sql(con=cnx, name='some_table_name', if_exists='replace', flavor='mysql', index=False)

Is there a way to make a temporary table this way?

There is nothing in the documentation as far as I can tell.

Answer 1

The DataFrame.to_sql() uses the built into pandas pandas.io.sql package, which itself relies on the SQLAlchemy as a database abstraction layer. In order to create a "temporary" table in SQLAlchemy ORM, you need to supply a prefix:

t = Table(
    't', metadata,
    Column('id', Integer, primary_key=True),
    # ...
    prefixes=['TEMPORARY'],
)

From what I see, pandas.io.sql does not allow you to specify the prefixes or easily change the way tables are created.

One way to approach this problem would be to create the temporary table beforehand and use to_sql() with if_exists="append" (all using the same database connection).

---

Here is also what I've tried to do: override the pandas.io.sql.SQLTable's _create_table_setup() method and pass the prefixes to the Table constructor. For some reason, the table was still created non-temporary. Not sure if it would help, but here is the code I was using: gist. This is kind of hacky, but I hope it would at least serve as an example code to get you started on this approach.