Create a temporary table in MySQL using Pandas

Question

Pandas has a great feature where you can write your data file 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

DataFrame.to_sql() uses the built-in pandas pandas.io.sql package, which itself uses SQLAlchemy as a database abstraction layer. To create a "temporary" table in SQLAlchemy ORM, you need to specify the 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 prefixes or easily change the way tables are created.

One way to solve this problem would be to create a temporary table in advance and use to_sql() with if_exists="append" (everyone uses the same database connection).

---

Here's what I was trying to do: override the pandas.io.sql.SQLTable _create_table_setup() method and pass the prefixes constructor to the Table constructor. For some reason, the table was still not created temporarily. Not sure if this will help, but here is the code I used: gist . This is a kind of hacking, but I hope that at least it will serve as an example of code so that you start using this approach.

Answer 2

It may be a bit hacked and not technically create a temporary table, it just acts as one, but you can create the @contextmanager decorator to create the table when the context opens and close it when it is closed. It might look something like this:

 from contextlib import contextmanager import numpy as np import sqlalchemy as sqla import pandas as pd @contextmanager def temp_table(frame, tbl, eng, *args, **kwargs): frame.to_sql(tbl, eng, *args, **kwargs) yield eng.execute('DROP TABLE {}'.format(tbl)) df = pd.DataFrame(np.random.randint(21, size=(10, 10))) cnx = sqla.create_engine(conn_string) with temp_table(df, 'some_table_name', cnx, if_exists='replace', flavor='mysql', index=False): # do stuff with "some_table_name" 

I tested it with Teradata and it works great. I don't have MySQL around which I can test it, but as long as DROP statements work in MySQL, they should work as intended.