Pandas version checks
-
[X] I have checked that this issue has not already been reported.
-
[X] I have confirmed this bug exists on the latest version of pandas.
-
[X] 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 : 8dab54d6573f7186ff0c3b6364d5e4dd635ff3e7
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
Comment From: 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
Comment From: MarcoGorelli
Is this just a matter of setting conn.begin()?
Comment From: phofl
This is the actual state when turning warnings on:
https://github.com/pandas-dev/pandas/actions/runs/3845260990/jobs/6549203648
Comment From: phofl
More generally, I think the initial warning above is correct. Our tests also assume that you call conn.begin() before entering to_sql.
Comment From: 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: https://github.com/sqlalchemy/sqlalchemy/issues/8631#issuecomment-1293956886. 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 .
Comment From: 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)
Comment From: lithomas1
Should be fixed. I think we support sqlalchemy 2.0 now in pandas.