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

Details

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.