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

[brunolnetto]

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 pandas as pd
from os import getcwd, path, getenv
from dotenv import load_dotenv
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base


# Get environment variables
host='localhost'
port=5432
user='postgres'
passw='postgres'
database_name='mydb'

# Connect to the database
db_uri = f'postgresql://{user}:{passw}@{host}:{port}/{database_name}'

# Create the database engine and session maker
engine = create_engine(db_uri)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

df=pd.DataFrame({'a': [1,2], 'b': [3,4]})

columns=[
    'a', 
    'b'
]

# Break the dataframe into chunks
df.to_sql(
    name='teste',
    if_exists='append',
    con=engine,
    index=False
)

Issue Description

I use pandas method to_sql often. On sqlalchemy update 2.2.0 and beyond, I got following error AttributeError: 'Engine' object has no attribute 'cursor'. It seems, the interface has changed compared to previous versions.

Expected Behavior

I expect to load data on table 'mydb' or append in case it exists already and have compatible columns.

Installed Versions

INSTALLED VERSIONS

commit : d9cdd2e
python : 3.12.3.final.0
python-bits : 64
OS : Windows
OS-release : 11
Version : 10.0.22621
machine : AMD64
processor : Intel64 Family 6 Model 154 Stepping 4, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : pt_BR.cp1252

pandas : 2.2.2
numpy : 1.26.4
pytz : 2024.1
dateutil : 2.9.0.post0
setuptools : 69.2.0
pip : 24.0
Cython : None
pytest : 8.1.1
hypothesis : None
...
zstandard : None
tzdata : 2024.1
qtpy : None
pyqt5 : None

[asishm]

Thanks for the report. Can you please post the full output of pd.show_versions() as required in the template (or at least your sqlalchemy version)?

[brunolnetto]

I got error AttributeError: module 'sqlalchemy' has no attribute 'show_versions' for sqlalchemy.

[asishm]

either the output of pd.show_versions() or sqlalchemy.__version__.

I'm guessing that it (sqlalchemy version) might be lower than 2.0. pandas 2.2 increased the minimum sqlalchemy version supported to 2.0

[brunolnetto]

You are correct. I will close and mark as resolved for future reference

[asishm]

fwiw - there's an open issue discussing if the minimum supported sqlalchemy version can be reversed back to the 1.4.x series - #57049