BUG: Regression in .to_sql() from 2.1 -> 2.2

[Ealameda31]

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

I am trying to use .to_sql() function to insert rows into a postgres database. Before Pandas 2.2, I could pass the engine as follows:

from sqlalchemy import create_engine

engine = create_engine(url_string)

df.to_sql(con=engine)

With the latest version of pandas, the same code gives me the following error:

AttributeError: 'Engine' object has no attribute 'cursor'

Looking at the docs, it does not seem like the con argument has changed, but for some reason it no longer works. Why?

I have tried using engine.connect(), engine.begin() and other variations all to no avail. I get either the error above or this error:

pandas.errors.DatabaseError: Execution failed on sql '
        SELECT
            name
        FROM
            sqlite_master
        WHERE
            type IN ('table', 'view')
            AND name=?;
        ': relation "sqlite_master" does not exist
LINE 5:             sqlite_master
                    ^

Can someone help me troubleshoot?

Issue Description

Pandas 2.2 not working with .to_sql() function despite no major changes in documentation and source code.

Expected Behavior

Would insert the rows into the data table.

Installed Versions

Name: pandas Version: 2.2.0

Name: SQLAlchemy
Version: 1.4.46

[Ealameda31]

Seems to be an issue with 2.2. Just tested on 2.1.4 and worked like a charm.

[mroeschke]

The minimum sqlalchemy version for pandas 2.2 is 2.0. I believe if you bump your sqlalchemy version this should be fixed: https://pandas.pydata.org/docs/whatsnew/v2.2.0.html#increased-minimum-versions-for-dependencies

Going to close but let me know if that doesn't solve your problem

[amotl]

Hi. JFYI: @mroeschke signaled willingness to bring back support for SQLAlchemy 1.4.

-- #57049 (comment)