ENH: DataFrame.to_sql with if_exists='replace' should do truncate table instead of drop table

[tokorhon]

Is your feature request related to a problem?

Dropping table when if_exists=‘replace’ will fail if the table has any objects, like a view, depending on it. Also, some databases like Oracle will end a transaction implicitly when a DLL statement like 'drop table' is issued.

Describe the solution you'd like

Better alternative would be to issue 'truncate table' command instead.

API breaking implications

Should not have any changes on API

Describe alternatives you've considered

As above or a new flag if_exists=‘truncate’

[ukarroum]

One of the problems i can see with using truncate instead of drop is that this add the limitation the the user's dataframe need to have the same columns as the table (which may not always be the case, depending on the usecase).

[erfannariman]

Yes I agree with @ukarroum , also the type of a column can change over time. But then again OP is suggesting to add a new method if_exists='truncate' which is a bit confusing, because the title states otherwise.

I can see the benefit of truncate over drop, also since the first will be more efficient.

[tokorhon]

There are pros and cons of truncate vs. drop. So maybe new method is the answer. Call it if_exists='delete' or something else. If truncate table is not supported by DBMS then this method should lead to 'delete from table' (which is slower than truncate).

[fiendish]

Could it use truncate if the columns are the same and drop otherwise?

[mancunian1792]

One of the problems i can see with using truncate instead of drop is that this add the limitation the the user's dataframe need to have the same columns as the table (which may not always be the case, depending on the usecase).

I would assume that if the structure of the table changes a lot, then its not a right use case for a relational table in the first place ? Although, i can understand the datatype changing over time would cause an issue. I feel truncate use case is more common and would benefit more people including me.

[igonro]

This is a great idea. I think is better to have both options "replace" and "truncate", sometimes you will need "replace" when you want to delete and insert new columns. But "truncate" is a needed option when you need to keep some columns options in the SQL database. For example, with replace the autoincrement option in the "id" column will disappear.

TLDR:

• replace: when your db table columns change
• truncate: when you need to keep your db table columns

[igonro]

Also @lam-juice in #8673 pointed out a behaviour that I was also having in my SQL database:

Is it possible to make pandas delete all the rows instead of dropping the table for if_exists='replace'?
Having to drop the table results in deadlocks when there's a simultaneous SELECT going on, while a simple deletion would avoid such deadlocks.

So why not add truncate option, it solves some problems (deadlocks, column information loss), it preserves the current behaviour of replace, and I don't think it the implementation would be hard.

[kylemcmearty]

+1 for @igonro 's solution.

I'm having issues with my table view object because replace drops the table.

Having truncate and replace options would be great.

I'm gonna try out this solution for now and see if it works: https://stackoverflow.com/a/67235797

[igonro]

Yes, @kmcmearty; that's an easy workaround (truncating manually and then doing the to_sql with append).