DEPS: Revert SQLAlchemy minimum version back to 1.4.36
State: closed

- [ ] closes #57049
- [ ] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [ ] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [ ] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [ ] Added an entry in the latest `doc/source/whatsnew/v2.3.0.rst` file if fixing a bug or adding a new feature.


Labels:
name: IO SQL
description: to_sql, read_sql, read_sql_query
name: Dependencies
description: Required and optional dependencies
name: Stale

Comments:
@mroeschke given there've been so many requests for this, thought I'd give this a try. The minimum version for SQLAlchemy was bumped from `1.4.36` to `2.0.0` about a year ago (#55524). I'm resetting it back to `1.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 like [pandasSQL_builder](https://github.com/pandas-dev/pandas/blob/f46d853365b5c7b91594205730b5db3d5430bd81/pandas/io/sql.py#L883) don't parse the connection object properly and return SQLite (which causes other downstream issues, eg [params](https://github.com/pandas-dev/pandas/blob/f46d853365b5c7b91594205730b5db3d5430bd81/pandas/io/sql.py#L2697) 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 like [pandasSQL_builder](https://github.com/pandas-dev/pandas/blob/f46d853365b5c7b91594205730b5db3d5430bd81/pandas/io/sql.py#L883) don't parse the connection object properly and return SQLite (which causes other downstream issues, eg [params](https://github.com/pandas-dev/pandas/blob/f46d853365b5c7b91594205730b5db3d5430bd81/pandas/io/sql.py#L2697) to not work correctly)

The reason it was incorrectly returning an SQLite object is because of this line:
https://github.com/pandas-dev/pandas/blob/f46d853365b5c7b91594205730b5db3d5430bd81/pandas/io/sql.py#L898

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_engine
f = 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 like [pandasSQL_builder](https://github.com/pandas-dev/pandas/blob/f46d853365b5c7b91594205730b5db3d5430bd81/pandas/io/sql.py#L883) don't parse the connection object properly and return SQLite (which causes other downstream issues, eg [params](https://github.com/pandas-dev/pandas/blob/f46d853365b5c7b91594205730b5db3d5430bd81/pandas/io/sql.py#L2697) to not work correctly)
> 
> The reason it was incorrectly returning an SQLite object is because of this line:
> 
> https://github.com/pandas-dev/pandas/blob/f46d853365b5c7b91594205730b5db3d5430bd81/pandas/io/sql.py#L898
> 
> 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_engine
> f = 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 a `Minimum 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 tests [have passed](https://github.com/pandas-dev/pandas/actions/runs/13464255693/job/37626471840?pr=60977). The failing Ubuntu tests are apparently due to an unrelated issue in libsqlite-3.49.1. (See https://github.com/AUTOMATIC1111/stable-diffusion-webui/issues/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 in `v2.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. Please [update](https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#updating-your-pull-request) 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. Please [update](https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#updating-your-pull-request) and respond to this comment if you're still interested in working on this.
Sorry @snitish this wasn't merged. Can you move the release note back to 3.0 and resolve the conflicts please, so we can still get this for 3.0. Thanks!
@snitish why closing this? Should we understand that SQLAlchemy compatibility will never be restored?