ENH: Add table prefixes to to_sql method

[Diadochokinetic]

Feature Type

• Adding new functionality to pandas

• Changing existing functionality in pandas

• Removing existing functionality in pandas

Problem Description

Some DBMS support the use of temporary tables. These are tables that only exist within a session and get dropped at the end of a session. These tables are useful for storing intermediary results in longer ETL jobs or for uploading a list of IDs into a otherwise read-only database for further processing. They are created with a CREATE TEMPORARY TABLE statement. At the moment, the to_sql method does not support creating temporary tables or inserting values into a manually created temporary table. At its core, to_sql uses the sqlalchemy.Table class , which supports a parameter prefixes. This parameter inserts a list of keywords before the TABLE keyword and can be used to create temporary tables by setting prefixes=["TEMPORARY"]. I suggest to add a prefixes parameter to to_sql and pass it down to the sqlalchemy.Table.

Feature Description

Add a parameter prefixes to the to_sql method:

df.to_sql(
    name="CUSTOMERS",
    con=engine,
    index=False,
    if_exists="create",
    prefixes=["TEMPORARY"]
)

The parameter needs to work with all possible ways of if_exists, so you can switch between CREATE TEMPORARY TABLE and INSERT functionality.

Alternative Solutions

Thus far, I was not able to find another solution.

Additional Context

I'm already working on a suitable implementation of the feature: #60409