BUG: to_sql does not get the correct dialect

[sls-mdr]

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

import urllib.parse

import pandas as pd
import sqlalchemy

username = "SOME_USERNAME"
password = urllib.parse.quote("SOME_PASSWORD")
host = "SOME_HOST"
port = SOME_PORT

# connection to denodo plattform
conn_str = f"denodo://{username}:{password}@{host}:{port}/dv_tutorial"

print("________________________")
print(conn_str)
print(type(conn_str))

engine = sqlalchemy.create_engine(conn_str, echo=True)

# read is possible
result_set = engine.execute("SELECT * FROM dv_tutorial.test")
for row in result_set:
    print(row)

# write is not possible
with engine.connect() as conn:
    print("BBBBBBBBBBBBBB")
    print(type(conn))
    df1 = pd.DataFrame({"name": ["User 4", "User 5"]})
    # :arrowdown: this produces the error
    df1.to_sql(
        name="test_table",
        schema="dv_tutorial",
        con=conn.connection,
        index=False,
    )

Issue Description

We want to connect to denodo Platform and create a table with pd.to_sql(). The output shows in our opinion that it can not get the correct dialect. It tries to check if the table already exists in a SQL Lite DB.

(dbaccess) []$ /bin/python3.11 ./dbaccess/test.py
________________________
denodo://SOME_PARAMS/dv_tutorial
<class 'str'>
 ./dbaccess/test.py:20: RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to "sqlalchemy<2.0". Set environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings.  Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  result_set = engine.execute("SELECT * FROM dv_tutorial.test")
2024-03-04 16:23:16,383 INFO sqlalchemy.engine.Engine select current_schema()
2024-03-04 16:23:16,383 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-03-04 16:23:16,428 INFO sqlalchemy.engine.Engine SELECT * FROM dv_tutorial.test
2024-03-04 16:23:16,429 INFO sqlalchemy.engine.Engine [raw sql] {}
(1,)
BBBBBBBBBBBBBB
<class 'sqlalchemy.engine.base.Connection'>
 ./dbaccess/test.py:29: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df1.to_sql(
Traceback (most recent call last):
  File "/home/s901193/.local/lib/python3.11/site-packages/pandas/io/sql.py", line 2672, in execute
    cur.execute(sql, *args)
psycopg2.OperationalError: Syntax error: Invalid parameterized expression
DETAIL:  java.sql.SQLException: Syntax error: Invalid parameterized expression


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/s901193/tkdbaccess/silas.py", line 29, in <module>
    df1.to_sql(
  File "/home/s901193/.local/lib/python3.11/site-packages/pandas/util/_decorators.py", line 333, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/home/s901193/.local/lib/python3.11/site-packages/pandas/core/generic.py", line 3084, in to_sql
    return sql.to_sql(
           ^^^^^^^^^^^
  File "/home/s901193/.local/lib/python3.11/site-packages/pandas/io/sql.py", line 842, in to_sql
    return pandas_sql.to_sql(
           ^^^^^^^^^^^^^^^^^^
  File "/home/s901193/.local/lib/python3.11/site-packages/pandas/io/sql.py", line 2848, in to_sql
    table.create()
  File "/home/s901193/.local/lib/python3.11/site-packages/pandas/io/sql.py", line 984, in create
    if self.exists():
       ^^^^^^^^^^^^^
  File "/home/s901193/.local/lib/python3.11/site-packages/pandas/io/sql.py", line 970, in exists
    return self.pd_sql.has_table(self.name, self.schema)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/s901193/.local/lib/python3.11/site-packages/pandas/io/sql.py", line 2863, in has_table
    return len(self.execute(query, [name]).fetchall()) > 0
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/s901193/.local/lib/python3.11/site-packages/pandas/io/sql.py", line 2684, in execute
    raise ex from exc
pandas.errors.DatabaseError: Execution failed on sql '
        SELECT
            name
        FROM
            sqlite_master
        WHERE
            type IN ('table', 'view')
            AND name=?;
        ': Syntax error: Invalid parameterized expression
DETAIL:  java.sql.SQLException: Syntax error: Invalid parameterized expression

Expected Behavior

We want to use pd.to_sql() to write a table into denodo plattform and use the dialect from the string.

Installed Versions

INSTALLED VERSIONS

commit : bdc79c1
python : 3.11.5.final.0
python-bits : 64
OS : Linux
OS-release : 4.18.0-513.11.1.el8_9.x86_64
Version : #1 SMP Thu Dec 7 03:06:13 EST 2023
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.2.1
numpy : 1.26.4
pytz : 2024.1
dateutil : 2.9.0.post0
setuptools : 69.0.3
pip : 24.0
Cython : None
pytest : 8.0.2
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : 2.9.9
jinja2 : None
IPython : None
pandas_datareader : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : None
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2024.1
qtpy : None
pyqt5 : None

AND pip list

Package Version

---

denodo-sqlalchemy 20240229
greenlet 3.0.3
hdbcli 2.19.21
numpy 1.26.4
pandas 2.2.1
pip 23.3.1
polars 0.20.13
psycopg2-binary 2.9.9
pyarrow 15.0.0
python-dateutil 2.9.0.post0
pytz 2024.1
setuptools 69.0.2
six 1.16.0
SQLAlchemy 1.4.51
sqlalchemy-hana 1.3.0
typing_extensions 4.10.0
tzdata 2024.1
wheel 0.42.0

[asishm]

pandas 2.2 doesn't support sqlalchemy < 2 https://pandas.pydata.org/docs/getting_started/install.html#sql-databases

I think there are open issues under discussion to re-add support for 1.4.x

[mkalinna]

I came across the same issue today when I wanted to upload data to Snowflake using the .to_sql() method.

Looks like neither snowflake-sqlalchemy nor the latest Apache Airflow release support SQLAlchemy 2 yet. Having support for 1.4.x re-added would be really helpful.

[asishm]

xref: #57049

[sls-mdr]

So @asishm should I close this one?

[asishm]

If you can confirm that it works with pandas<2.2 and sqla 1.4.x, then imo it can be closed as a duplicate of the linked issue. But if you want to wait for a pandas core dev to respond, that's probably fine as well.