@mroeschke given there've been so many requests for this, thought I'd give this a try. The minimum version for SQLAlchemy was bumped from1.4.36 to2.0.0 about a year ago (#55524). I'm resetting it back to1.4.36.
My local tests ran fine with this version but is there a way to configure CI tests to run on both SQLAlchemy versions?

I don't know if just changing the versions will fix the underlying issues. When we use an old version of SA, functions likepandasSQL_builder don't parse the connection object properly and return SQLite (which causes other downstream issues, egparams to not work correctly)

I don't know if just changing the versions will fix the underlying issues. When we use an old version of SA, functions likepandasSQL_builder don't parse the connection object properly and return SQLite (which causes other downstream issues, egparams to not work correctly)

The reason it was incorrectly returning an SQLite object is because of this line:

If SQLAlchemy doesn't satisfy the minimum version, the import fails and it leads to creating an SQLite object instead. If the minimum version is updated, the code works fine. Tested it on my branch after updating SQLAlchemy to 1.4.

>>> import pandas as pd>>> from sqlalchemy import create_enginef = pd.DataFrame({'a': [0, 1, 2, 3]})df.to_sql('test', con)>>> con = create_engine('sqlite:///:memory:')>>> df = pd.DataFrame({'a': [0, 1, 2, 3]})>>> df.to_sql('test', con)4

I don't know if just changing the versions will fix the underlying issues. When we use an old version of SA, functions likepandasSQL_builder don't parse the connection object properly and return SQLite (which causes other downstream issues, egparams to not work correctly)

The reason it was incorrectly returning an SQLite object is because of this line:

pandas/pandas/io/sql.py

Line 898 inf46d853

sqlalchemy=import_optional_dependency("sqlalchemy",errors="ignore")

If SQLAlchemy doesn't satisfy the minimum version, the import fails and it leads to creating an SQLite object instead. If the minimum version is updated, the code works fine. Tested it on my branch after updating SQLAlchemy to 1.4.

>>> import pandas as pd>>> from sqlalchemy import create_enginef = pd.DataFrame({'a': [0, 1, 2, 3]})df.to_sql('test', con)>>> con = create_engine('sqlite:///:memory:')>>> df = pd.DataFrame({'a': [0, 1, 2, 3]})>>> df.to_sql('test', con)4

Ah wow, well gl! We would really appreciate this change

is there a way to configure CI tests to run on both SQLAlchemy versions?

There is aMinimum Versions build that will test with the pinned, minimum supported version of the dependencies (1.4.x), and the rest of the builds test with dependencies pinned as>=version so those builds should test with 2.0.x.

Thanks@mroeschke . Confirmed that the minimum version testshave passed. The failing Ubuntu tests are apparently due to an unrelated issue in libsqlite-3.49.1. (SeeAUTOMATIC1111/stable-diffusion-webui#16856)
I'm still not sure why these tests are passing for other PRs - they seem to be using the older 3.48.1 version - but my guess is that updating the minimum versions file may have triggered the build to re-fetch the latest versions of dependencies.

Could you add a whatsnew note inv2.3.0? I think it's reasonable to backport this since many users were reporting this issue

And after this PR, do you mind submitting one to our conda-feedstock to updating sqlalchemy version there?https://github.com/conda-forge/pandas-feedstock/blob/main/recipe/meta.yaml

@mroeschke should the whatsnew note go under the 'Other Enhancements' section? Or should I create a new section for minimum version updates?

should the whatsnew note go under the 'Other Enhancements' section?

Yup, I think this section is appropriate

Is this going to be merged?!?! I cannot tell you how excited this makes me. The amount of pain this issue has caused me knows no bound.

This pull request is stale because it has been open for thirty days with no activity. Pleaseupdate and respond to this comment if you're still interested in working on this.

Much appreciate this work. How can we help to get this merged and included in a release? 🙏🏼

pre-commit.ci autofix

This pull request is stale because it has been open for thirty days with no activity. Pleaseupdate and respond to this comment if you're still interested in working on this.