pandas_temporary_table.py

import pandas as pd
import pandas.core.common as com
from pandas.io.sql import SQLTable, pandasSQL_builder
from sqlalchemy import create_engine


class TemporaryTable(SQLTable):
    """Overriding the _create_table_setup() method trying to make the table created temporary."""
    def _create_table_setup(self):
        from sqlalchemy import Table, Column, PrimaryKeyConstraint

        column_names_and_types = \
            self._get_column_names_and_types(self._sqlalchemy_type)

        columns = [Column(name, typ, index=is_index)
                   for name, typ, is_index in column_names_and_types]

        if self.keys is not None:
            if not com.is_list_like(self.keys):
                keys = [self.keys]
            else:
                keys = self.keys
            pkc = PrimaryKeyConstraint(*keys, name=self.name + '_pk')
            columns.append(pkc)

        schema = self.schema or self.pd_sql.meta.schema

        # At this point, attach to new metadata, only attach to self.meta
        # once table is created.
        from sqlalchemy.schema import MetaData
        meta = MetaData(self.pd_sql, schema=schema)

        #                                                                FIX HERE v
        return Table(self.name, meta, *columns, schema=schema, prefixes=['TEMPORARY'])


d = {'one' : [1., 2., 3., 4.],
     'two' : [4., 3., 2., 1.]}
df = pd.DataFrame(d)

engine = create_engine('mysql+pymysql://root:root@localhost/test', echo="debug")

with engine.connect() as conn, conn.begin():
    pandas_engine = pandasSQL_builder(conn, "mysql")
    
    # creating a table
    table = TemporaryTable("mytable", pandas_engine, frame=df, if_exists="replace")
    table.create()

    # dumping to the existing table
    df.to_sql("mytable", conn, "mysql", if_exists="append")

The function _create_table_setup is called only in __init__ to set the table property. To create a table with pandas.io.sql.SQLTable, you call create, which calls _execute_create, which overwrites the table property. That's why your attempt didn't work. Instead, you need to edit _execute_create:

def _execute_create(self):
    # Inserting table into database, add to MetaData object
    self.table = self.table.tometadata(self.pd_sql.meta)

    # allow creation of temporary tables
    self.table._prefixes.append('TEMPORARY')

    self.table.create()

@gcbeltramini that would make a nice pull request !

@gcbeltramini 👍