BUG: SQLAlchemy 1.4.46 emits warning with pandas 1.5.2, SQLAlchemy 1.4.45 has no warning

[Dr-Irv]

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 sqlalchemy
import pandas as pd
from pandas._testing import ensure_clean
import warnings

warnings.filterwarnings("error")

DF = pd.DataFrame({"a": [1, 2, 3], "b": [0.0, 0.0, 0.0]})

with ensure_clean() as path:
    db_uri = "sqlite:///" + path
    engine = sqlalchemy.create_engine(db_uri)

    with engine.connect() as conn:
        DF.to_sql("test", con=conn)
        print("written")

Issue Description

This was discovered in the pandas-stubs testing. SQLAlchemy went from version 1.4.45 to 1.4.46, and we treat warnings as errors, and a new warning popped up. With SQLAlchemy 1.4.45, no warning is issued. With 1.4.46, with the above code, you get:

Traceback (most recent call last):
  File "c:\Code\pandas-stubs\alchemy.py", line 15, in <module>
    DF.to_sql("test", con=conn)
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\pandas\core\generic.py", line 2987, in to_sql
    return sql.to_sql(
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\pandas\io\sql.py", line 695, in to_sql
    return pandas_sql.to_sql(
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\pandas\io\sql.py", line 1728, in to_sql
    table = self.prep_table(
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\pandas\io\sql.py", line 1631, in prep_table
    table.create()
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\pandas\io\sql.py", line 838, in create
    self._execute_create()
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\pandas\io\sql.py", line 824, in _execute_create
    self.table.create(bind=self.pd_sql.connectable)
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\sql\schema.py", line 962, in create
    bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\engine\base.py", line 2211, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\sql\visitors.py", line 524, in traverse_single
    return meth(obj, **kw)
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\sql\ddl.py", line 895, in visit_table
    self.connection.execute(
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\engine\base.py", line 1380, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\sql\ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\engine\base.py", line 1472, in _execute_ddl
    ret = self._execute_context(
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\engine\base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\engine\base.py", line 2128, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\util\compat.py", line 211, in raise_
    raise exception
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\engine\base.py", line 1930, in _execute_context
    self._commit_impl(autocommit=True)
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\engine\base.py", line 1069, in _commit_impl
    util.warn_deprecated_20(
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\util\deprecations.py", line 128, in warn_deprecated_20
    _warn_with_version(
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\util\deprecations.py", line 47, in _warn_with_version
    _emit_uber_warning(type_, stacklevel)
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\util\deprecations.py", line 105, in _emit_uber_warning
    _warnings_warn(warn, stacklevel=stacklevel + 1)
  File "C:\Anaconda3\envs\pandasstubs\lib\site-packages\sqlalchemy\util\langhelpers.py", line 1679, in _warnings_warn
    warnings.warn(message, stacklevel=stacklevel + 1)
sqlalchemy.exc.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)

Expected Behavior

We should change our code to be compatible with SQLAlchemy 2.0 so that this warning is not issued, or pin SQLAlchemy to 1.4.45.

Installed Versions

INSTALLED VERSIONS

commit : 8dab54d
python : 3.9.13.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19045
machine : AMD64
processor : Intel64 Family 6 Model 158 Stepping 13, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : English_United States.1252

pandas : 1.5.2
numpy : 1.23.5
pytz : 2022.7
dateutil : 2.8.2
setuptools : 65.6.3
pip : 22.3.1
Cython : 0.29.32
pytest : 7.2.0
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : 3.0.5
lxml.etree : 4.8.0
html5lib : 1.1
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : 3.6.2
numba : None
numexpr : 2.8.4
odfpy : None
openpyxl : 3.0.10
pandas_gbq : None
pyarrow : 10.0.1
pyreadstat : 1.2.0
pyxlsb : 1.0.10
s3fs : None
scipy : 1.10.0
snappy : None
sqlalchemy : 1.4.46
tables : 3.8.0
tabulate : 0.9.0
xarray : 2022.12.0
xlrd : 2.0.1
xlwt : None
zstandard : None
tzdata : None

[MarcoGorelli]

Yeah, this is failing CI now

=================================== FAILURES ===================================
____________________ test_to_sql[None-mysql_pymysql_engine] ____________________
[gw0] linux -- Python 3.8.15 /home/runner/micromamba/envs/test/bin/python3.8

conn = Engine(mysql+pymysql://root@localhost:3306/pandas), method = None
test_frame1 =                  index         A         B         C         D
0  2000-01-03 00:00:00  0.980269  3.685731 -0.364217 -1...0-01-05 00:00:00  0.498581  0.731168 -0.537677  1.346270
3  2000-01-06 00:00:00  1.120202  1.567621  0.003641  0.675253
request = <FixtureRequest for <Function test_to_sql[None-mysql_pymysql_engine]>>

    @pytest.mark.db
    @pytest.mark.parametrize("conn", all_connectable)
    @pytest.mark.parametrize("method", [None, "multi"])
    def test_to_sql(conn, method, test_frame1, request):
        conn = request.getfixturevalue(conn)
        with pandasSQL_builder(conn) as pandasSQL:
>           pandasSQL.to_sql(test_frame1, "test_frame", method=method)

pandas/tests/io/test_sql.py:512: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
pandas/io/sql.py:1864: in to_sql
    table = self.prep_table(
pandas/io/sql.py:1768: in prep_table
    table.create()
pandas/io/sql.py:919: in create
    self._execute_create()
pandas/io/sql.py:905: in _execute_create
    self.table.create(bind=self.pd_sql.con)
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/sql/schema.py:962: in create
    bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/engine/base.py:2211: in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py:524: in traverse_single
    return meth(obj, **kw)
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py:895: in visit_table
    self.connection.execute(
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1380: in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py:80: in _execute_on_connection
    return connection._execute_ddl(
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1472: in _execute_ddl
    ret = self._execute_context(
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1943: in _execute_context
    self._handle_dbapi_exception(
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/engine/base.py:2128: in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/util/compat.py:211: in raise_
    raise exception
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1930: in _execute_context
    self._commit_impl(autocommit=True)
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1069: in _commit_impl
    util.warn_deprecated_20(
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py:128: in warn_deprecated_20
    _warn_with_version(
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py:47: in _warn_with_version
    _emit_uber_warning(type_, stacklevel)
../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py:105: in _emit_uber_warning
    _warnings_warn(warn, stacklevel=stacklevel + 1)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

message = RemovedIn20Warning('Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prev...o show all deprecation warnings.  Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message.')
category = None, stacklevel = 13

    def _warnings_warn(message, category=None, stacklevel=2):
    
        # adjust the given stacklevel to be outside of SQLAlchemy
        try:
            frame = sys._getframe(stacklevel)
        except ValueError:
            # being called from less than 3 (or given) stacklevels, weird,
            # but don't crash
            stacklevel = 0
        except:
            # _getframe() doesn't work, weird interpreter issue, weird,
            # ok, but don't crash
            stacklevel = 0
        else:
            # using __name__ here requires that we have __name__ in the
            # __globals__ of the decorated string functions we make also.
            # we generate this using {"__name__": fn.__module__}
            while frame is not None and re.match(
                r"^(?:sqlalchemy\.|alembic\.)", frame.f_globals.get("__name__", "")
            ):
                frame = frame.f_back
                stacklevel += 1
    
        if category is not None:
            warnings.warn(message, category, stacklevel=stacklevel + 1)
        else:
>           warnings.warn(message, stacklevel=stacklevel + 1)
E           sqlalchemy.exc.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)

../../../micromamba/envs/test/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:1679: RemovedIn20Warning

[MarcoGorelli]

Is this just a matter of setting conn.begin()?

[phofl]

This is the actual state when turning warnings on:

https://github.com/pandas-dev/pandas/actions/runs/3845260990/jobs/6549203648

[phofl]

More generally, I think the initial warning above is correct. Our tests also assume that you call conn.begin() before entering to_sql.

[cdcadman]

sqlalchemy 2.0 requires some changes which might not show up in 1.4.46 warnings, such as this one which I noticed when 2.0.0b1 was released: sqlalchemy/sqlalchemy#8631 (comment). Interestingly, some of the sqlalchemy 1.4.46 warnings do not cause the tests to fail under sqlalchemy 2.0.0rc1, but I've updated my PR to avoid these warnings: #48576 .

[zzzeek]

hi pandas!

I added that warning in 1.4.46 as I realized we have lots of 2.0 warnings in 1.4 but they aren't turned on by default, and as 2.0 final is anticipated this month I felt it was a good idea to make sure people are pinned to <2.0 if they haven't gone through the upgrade steps (the upgrade steps are not hard esp. for pandas that has very little sqlalchemy code)

[lithomas1]

Should be fixed. I think we support sqlalchemy 2.0 now in pandas.