BUG/FEATURE REQUEST: DataFrame.to_sql() tries to create table when it exists

[vladidobro]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

This example requires an Oracle 19c database


engine = sqlalchemy.create_engine('oracle+oracledb://...', echo=True)
con = engine.connect()
c.execute(text('''
CREATE PRIVATE TEMPORARY TABLE ORA$PTT_TEMP (
  a INT
) ON COMMIT DROP DEFINITION
'''))
pd.DataFrame({'a': [1]}).to_sql('ORA$PTT_TEMP', engine)

-05-09 11:10:00,967 INFO sqlalchemy.engine.Engine SELECT tables_and_views.table_name
FROM (SELECT a_tables.table_name AS table_name, a_tables.owner AS owner
FROM all_tables a_tables UNION ALL SELECT a_views.view_name AS table_name, a_views.owner AS owner
FROM all_views a_views) tables_and_views
WHERE tables_and_views.table_name = :table_name AND tables_and_views.owner = :owner
2025-05-09 11:10:00,967 INFO sqlalchemy.engine.Engine [cached since 533.2s ago] {'table_name': 'ORA$PTT_TEMP', 'owner': '...'}
2025-05-09 11:10:00,993 INFO sqlalchemy.engine.Engine
CREATE TABLE ORA$PTT_TEMP (
        curve_id INT
)
DatabaseError: (oracledb.exceptions.DatabaseError) ORA-32463: cannot create an object with a name matching private temporary table prefix

Issue Description

Hello Pandas!
I am trying to use DataFrame.to_sql with Oracle "PRIVATE TEMPORARY" tables.
The catch is that these tables for whatever reason cannot be detected with the inspector.has_table() method, so pandas is trying to create the table, and then fails.

The issue is quite annoying, because the error is in the pandas.SQLDatabase.prep_table() method, which is called unconditionally in the pandas.SQLDatabase.to_sql(), and there is no way to override it with a custom "method: callable" parameter to pandas.DataFrame.to_sql().

Though one could argue that this is a bug in the SQLAlchemy Oracle dialect, rather than Pandas. But IMHO it should be possible to skip the table check and creation altogether in the pandas.DataFrame.to_sql() call.
It looks like it would be easy to add a skip_table_creation: bool = False argument to the to_sql() method, that would just skip the prep_table call in SQLDatabase.to_sql().
The downside would be that pandas would not have the reflected information about target database types, but this could potentially be solved by passing a custom sqlalchemy.Table object?

What do you think about this? Is this a direction that Pandas would like to go in, or do you think about the .to_sql() method more as a handy feature for ad-hoc operations, that should not be used much in production? Do you think it is better to write my own insert methods and not rely on .to_sql() for production use?

Expected Behavior

I expect that it will not try to create a table if it exists, or an option to skip table creation if I know that it does not exist.

Installed Versions

INSTALLED VERSIONS

commit : 0691c5c
python : 3.11.3
python-bits : 64
OS : Darwin
OS-release : 24.4.0
Version : Darwin Kernel Version 24.4.0: Fri Apr 11 18:33:47 PDT 2025; root:xnu-11417.101.15~117/RELEASE_ARM64_T6000
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.UTF-8

pandas : 2.2.3
numpy : 1.26.4
pytz : 2024.2
dateutil : 2.8.2
pip : 24.0
Cython : None
sphinx : None
IPython : 8.21.0
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.3
blosc : None
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : 2024.3.1
html5lib : 1.1
hypothesis : None
gcsfs : None
jinja2 : 3.1.3
lxml.etree : 5.1.0
matplotlib : 3.10.1
numba : None
numexpr : None
odfpy : None
openpyxl : 3.1.2
pandas_gbq : None
psycopg2 : 2.9.9
pymysql : 1.4.6
pyarrow : 15.0.0
pyreadstat : None
pytest : 8.3.3
python-calamine : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : 2.0.40
tables : None
tabulate : 0.9.0
xarray : None
xlrd : None
xlsxwriter : None
zstandard : None
tzdata : 2024.1
qtpy : None
pyqt5 : None

[rhshadrach]

Thanks for the request!

Though one could argue that this is a bug in the SQLAlchemy Oracle dialect, rather than Pandas.

Has this been reported to SQLAlchemy?

[vladidobro]

No, I believe it is not reported to SQLAlchemy, I guess I will do that.

Though I feel a little bit like there should still be an escape hatch in pandas that will not rely on sqlalchemy being able to reflect all special table types. There will probably not be many of them, but even then I think it would be good to skip the table reflection if I know the table upfront, for performance reasons.

[vladidobro]

What do you think about making it possible to pass as argument a sqlalchemy.Table instead of table name?
It seems like it would be minimal changes, the sql backend would just check if the table is a Table object and if yes, don't do the reflection.
That looks on first sight like a really simple change, I would maybe try it as my first PR in pandas, if pandas would like this.
@rhshadrach

[rhshadrach]

@vladidobro - I am not very familiar with the SQL layer in pandas, if you're willing, I'd suggest putting up a proof-of-concept PR and we can discuss further.