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.

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.

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).

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.