Repository: pandas-dev/pandas
Pull request #55702 (2023-10-26 10:14:37)

Backport PR #55690 on branch 2.1.x (REGR: fix roundtripping datetimes with sqlite type detection)
Backport PR #55690

Co-authored-by: Matthew Roeschke <10647082+mroeschke@users.noreply.github.com>
(cherry picked from commit aeb3644567a10296cc09aed03bf7e0a3780e67ee)



Patch:
diff --git a/doc/source/whatsnew/v2.1.2.rst b/doc/source/whatsnew/v2.1.2.rst
index 16f8e8d264..1d2d89e7ea 100644
--- a/doc/source/whatsnew/v2.1.2.rst
+++ b/doc/source/whatsnew/v2.1.2.rst
@@ -8,6 +8,14 @@ including other versions of pandas.
 
 {{ header }}
 
+.. ---------------------------------------------------------------------------
+.. _whatsnew_212.deprecations:
+
+Deprecations
+~~~~~~~~~~~~
+
+- Reverted deprecation of ``fill_method=None`` in :meth:`DataFrame.pct_change`, :meth:`Series.pct_change`, :meth:`DataFrameGroupBy.pct_change`, and :meth:`SeriesGroupBy.pct_change`; the values ``'backfill'``, ``'bfill'``, ``'pad'``, and ``'ffill'`` are still deprecated (:issue:`53491`)
+
 .. ---------------------------------------------------------------------------
 .. _whatsnew_212.regressions:
 
@@ -20,6 +28,7 @@ Fixed regressions
 - Fixed regression in :meth:`DataFrameGroupBy.agg` and :meth:`SeriesGroupBy.agg` where if the option ``compute.use_numba`` was set to True, groupby methods not supported by the numba engine would raise a ``TypeError`` (:issue:`55520`)
 - Fixed performance regression with wide DataFrames, typically involving methods where all columns were accessed individually (:issue:`55256`, :issue:`55245`)
 - Fixed regression in :func:`merge_asof` raising ``TypeError`` for ``by`` with datetime and timedelta dtypes (:issue:`55453`)
+- Fixed regression in :meth:`DataFrame.to_sql` not roundtripping datetime columns correctly for sqlite when using ``detect_types`` (:issue:`55554`)
 
 .. ---------------------------------------------------------------------------
 .. _whatsnew_212.bug_fixes:
diff --git a/pandas/core/generic.py b/pandas/core/generic.py
index f377fc3481..3281d245dc 100644
--- a/pandas/core/generic.py
+++ b/pandas/core/generic.py
@@ -11579,6 +11579,7 @@ class NDFrame(PandasObject, indexing.IndexingMixin):
             How to handle NAs **before** computing percent changes.
 
             .. deprecated:: 2.1
+                All options of `fill_method` are deprecated except `fill_method=None`.
 
         limit : int, default None
             The number of consecutive NAs to fill before stopping.
@@ -11685,32 +11686,33 @@ class NDFrame(PandasObject, indexing.IndexingMixin):
         APPL -0.252395 -0.011860   NaN
         """
         # GH#53491
-        if fill_method is not lib.no_default or limit is not lib.no_default:
+        if fill_method not in (lib.no_default, None) or limit is not lib.no_default:
             warnings.warn(
-                "The 'fill_method' and 'limit' keywords in "
-                f"{type(self).__name__}.pct_change are deprecated and will be "
-                "removed in a future version. Call "
-                f"{'bfill' if fill_method in ('backfill', 'bfill') else 'ffill'} "
-                "before calling pct_change instead.",
+                "The 'fill_method' keyword being not None and the 'limit' keyword in "
+                f"{type(self).__name__}.pct_change are deprecated and will be removed "
+                "in a future version. Either fill in any non-leading NA values prior "
+                "to calling pct_change or specify 'fill_method=None' to not fill NA "
+                "values.",
                 FutureWarning,
                 stacklevel=find_stack_level(),
             )
         if fill_method is lib.no_default:
-            cols = self.items() if self.ndim == 2 else [(None, self)]
-            for _, col in cols:
-                mask = col.isna().values
-                mask = mask[np.argmax(~mask) :]
-                if mask.any():
-                    warnings.warn(
-                        "The default fill_method='pad' in "
-                        f"{type(self).__name__}.pct_change is deprecated and will be "
-                        "removed in a future version. Call ffill before calling "
-                        "pct_change to retain current behavior and silence this "
-                        "warning.",
-                        FutureWarning,
-                        stacklevel=find_stack_level(),
-                    )
-                    break
+            if limit is lib.no_default:
+                cols = self.items() if self.ndim == 2 else [(None, self)]
+                for _, col in cols:
+                    mask = col.isna().values
+                    mask = mask[np.argmax(~mask) :]
+                    if mask.any():
+                        warnings.warn(
+                            "The default fill_method='pad' in "
+                            f"{type(self).__name__}.pct_change is deprecated and will "
+                            "be removed in a future version. Either fill in any "
+                            "non-leading NA values prior to calling pct_change or "
+                            "specify 'fill_method=None' to not fill NA values.",
+                            FutureWarning,
+                            stacklevel=find_stack_level(),
+                        )
+                        break
             fill_method = "pad"
         if limit is lib.no_default:
             limit = None
diff --git a/pandas/core/groupby/groupby.py b/pandas/core/groupby/groupby.py
index 2b83802944..187c62bc0c 100644
--- a/pandas/core/groupby/groupby.py
+++ b/pandas/core/groupby/groupby.py
@@ -5217,7 +5217,7 @@ class GroupBy(BaseGroupBy[NDFrameT]):
     def pct_change(
         self,
         periods: int = 1,
-        fill_method: FillnaOptions | lib.NoDefault = lib.no_default,
+        fill_method: FillnaOptions | None | lib.NoDefault = lib.no_default,
         limit: int | None | lib.NoDefault = lib.no_default,
         freq=None,
         axis: Axis | lib.NoDefault = lib.no_default,
@@ -5269,23 +5269,26 @@ class GroupBy(BaseGroupBy[NDFrameT]):
         goldfish    0.2  0.125
         """
         # GH#53491
-        if fill_method is not lib.no_default or limit is not lib.no_default:
+        if fill_method not in (lib.no_default, None) or limit is not lib.no_default:
             warnings.warn(
-                "The 'fill_method' and 'limit' keywords in "
-                f"{type(self).__name__}.pct_change are deprecated and will be "
-                "removed in a future version. Call "
-                f"{'bfill' if fill_method in ('backfill', 'bfill') else 'ffill'} "
-                "before calling pct_change instead.",
+                "The 'fill_method' keyword being not None and the 'limit' keyword in "
+                f"{type(self).__name__}.pct_change are deprecated and will be removed "
+                "in a future version. Either fill in any non-leading NA values prior "
+                "to calling pct_change or specify 'fill_method=None' to not fill NA "
+                "values.",
                 FutureWarning,
                 stacklevel=find_stack_level(),
             )
         if fill_method is lib.no_default:
-            if any(grp.isna().values.any() for _, grp in self):
+            if limit is lib.no_default and any(
+                grp.isna().values.any() for _, grp in self
+            ):
                 warnings.warn(
                     "The default fill_method='ffill' in "
-                    f"{type(self).__name__}.pct_change is deprecated and will be "
-                    "removed in a future version. Call ffill before calling "
-                    "pct_change to retain current behavior and silence this warning.",
+                    f"{type(self).__name__}.pct_change is deprecated and will "
+                    "be removed in a future version. Either fill in any "
+                    "non-leading NA values prior to calling pct_change or "
+                    "specify 'fill_method=None' to not fill NA values.",
                     FutureWarning,
                     stacklevel=find_stack_level(),
                 )
diff --git a/pandas/io/sql.py b/pandas/io/sql.py
index 2b139f8ca5..c1d68d71ac 100644
--- a/pandas/io/sql.py
+++ b/pandas/io/sql.py
@@ -2090,7 +2090,7 @@ class SQLiteTable(SQLTable):
         # Python 3.12+ doesn't auto-register adapters for us anymore
 
         adapt_date_iso = lambda val: val.isoformat()
-        adapt_datetime_iso = lambda val: val.isoformat()
+        adapt_datetime_iso = lambda val: val.isoformat(" ")
 
         sqlite3.register_adapter(time, _adapt_time)
 
@@ -2098,11 +2098,9 @@ class SQLiteTable(SQLTable):
         sqlite3.register_adapter(datetime, adapt_datetime_iso)
 
         convert_date = lambda val: date.fromisoformat(val.decode())
-        convert_datetime = lambda val: datetime.fromisoformat(val.decode())
-        convert_timestamp = lambda val: datetime.fromtimestamp(int(val))
+        convert_timestamp = lambda val: datetime.fromisoformat(val.decode())
 
         sqlite3.register_converter("date", convert_date)
-        sqlite3.register_converter("datetime", convert_datetime)
         sqlite3.register_converter("timestamp", convert_timestamp)
 
     def sql_schema(self) -> str:


Test patch:
diff --git a/pandas/tests/frame/methods/test_pct_change.py b/pandas/tests/frame/methods/test_pct_change.py
index ede212ae18..92b66e12d4 100644
--- a/pandas/tests/frame/methods/test_pct_change.py
+++ b/pandas/tests/frame/methods/test_pct_change.py
@@ -29,7 +29,7 @@ class TestDataFramePctChange:
         obj = frame_or_series(vals)
 
         msg = (
-            "The 'fill_method' and 'limit' keywords in "
+            "The 'fill_method' keyword being not None and the 'limit' keyword in "
             f"{type(obj).__name__}.pct_change are deprecated"
         )
         with tm.assert_produces_warning(FutureWarning, match=msg):
@@ -46,7 +46,7 @@ class TestDataFramePctChange:
         pnl.iat[2, 3] = 60
 
         msg = (
-            "The 'fill_method' and 'limit' keywords in "
+            "The 'fill_method' keyword being not None and the 'limit' keyword in "
             "DataFrame.pct_change are deprecated"
         )
 
@@ -59,12 +59,11 @@ class TestDataFramePctChange:
 
     def test_pct_change(self, datetime_frame):
         msg = (
-            "The 'fill_method' and 'limit' keywords in "
+            "The 'fill_method' keyword being not None and the 'limit' keyword in "
             "DataFrame.pct_change are deprecated"
         )
 
-        with tm.assert_produces_warning(FutureWarning, match=msg):
-            rs = datetime_frame.pct_change(fill_method=None)
+        rs = datetime_frame.pct_change(fill_method=None)
         tm.assert_frame_equal(rs, datetime_frame / datetime_frame.shift(1) - 1)
 
         rs = datetime_frame.pct_change(2)
@@ -110,7 +109,7 @@ class TestDataFramePctChange:
         self, datetime_frame, freq, periods, fill_method, limit
     ):
         msg = (
-            "The 'fill_method' and 'limit' keywords in "
+            "The 'fill_method' keyword being not None and the 'limit' keyword in "
             "DataFrame.pct_change are deprecated"
         )
 
@@ -144,11 +143,12 @@ def test_pct_change_with_duplicated_indices(fill_method):
         {0: [np.nan, 1, 2, 3, 9, 18], 1: [0, 1, np.nan, 3, 9, 18]}, index=["a", "b"] * 3
     )
 
+    warn = None if fill_method is None else FutureWarning
     msg = (
-        "The 'fill_method' and 'limit' keywords in "
+        "The 'fill_method' keyword being not None and the 'limit' keyword in "
         "DataFrame.pct_change are deprecated"
     )
-    with tm.assert_produces_warning(FutureWarning, match=msg):
+    with tm.assert_produces_warning(warn, match=msg):
         result = data.pct_change(fill_method=fill_method)
 
     if fill_method is None:
diff --git a/pandas/tests/groupby/transform/test_transform.py b/pandas/tests/groupby/transform/test_transform.py
index 062dfe3931..d51c618b3c 100644
--- a/pandas/tests/groupby/transform/test_transform.py
+++ b/pandas/tests/groupby/transform/test_transform.py
@@ -1063,7 +1063,7 @@ def test_pct_change(frame_or_series, freq, periods, fill_method, limit):
         expected = expected.to_frame("vals")
 
     msg = (
-        "The 'fill_method' and 'limit' keywords in "
+        "The 'fill_method' keyword being not None and the 'limit' keyword in "
         f"{type(gb).__name__}.pct_change are deprecated"
     )
     with tm.assert_produces_warning(FutureWarning, match=msg):
diff --git a/pandas/tests/io/test_sql.py b/pandas/tests/io/test_sql.py
index bfa93a4ff9..5fd6a52031 100644
--- a/pandas/tests/io/test_sql.py
+++ b/pandas/tests/io/test_sql.py
@@ -2970,6 +2970,24 @@ class TestSQLiteAlchemy(_TestSQLAlchemy):
         assert result == "2020-12-31 12:00:00.000000"
 
 
+@pytest.fixture
+def sqlite_builtin_detect_types():
+    with contextlib.closing(
+        sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
+    ) as closing_conn:
+        with closing_conn as conn:
+            yield conn
+
+
+def test_roundtripping_datetimes_detect_types(sqlite_builtin_detect_types):
+    # https://github.com/pandas-dev/pandas/issues/55554
+    conn = sqlite_builtin_detect_types
+    df = DataFrame({"t": [datetime(2020, 12, 31, 12)]}, dtype="datetime64[ns]")
+    df.to_sql("test", conn, if_exists="replace", index=False)
+    result = pd.read_sql("select * from test", conn).iloc[0, 0]
+    assert result == Timestamp("2020-12-31 12:00:00.000000")
+
+
 @pytest.mark.db
 class TestMySQLAlchemy(_TestSQLAlchemy):
     """
diff --git a/pandas/tests/series/methods/test_pct_change.py b/pandas/tests/series/methods/test_pct_change.py
index 6740b87568..9727ef3d5c 100644
--- a/pandas/tests/series/methods/test_pct_change.py
+++ b/pandas/tests/series/methods/test_pct_change.py
@@ -11,12 +11,11 @@ import pandas._testing as tm
 class TestSeriesPctChange:
     def test_pct_change(self, datetime_series):
         msg = (
-            "The 'fill_method' and 'limit' keywords in "
+            "The 'fill_method' keyword being not None and the 'limit' keyword in "
             "Series.pct_change are deprecated"
         )
 
-        with tm.assert_produces_warning(FutureWarning, match=msg):
-            rs = datetime_series.pct_change(fill_method=None)
+        rs = datetime_series.pct_change(fill_method=None)
         tm.assert_series_equal(rs, datetime_series / datetime_series.shift(1) - 1)
 
         rs = datetime_series.pct_change(2)
@@ -69,7 +68,7 @@ class TestSeriesPctChange:
         self, freq, periods, fill_method, limit, datetime_series
     ):
         msg = (
-            "The 'fill_method' and 'limit' keywords in "
+            "The 'fill_method' keyword being not None and the 'limit' keyword in "
             "Series.pct_change are deprecated"
         )
 
@@ -101,8 +100,12 @@ def test_pct_change_with_duplicated_indices(fill_method):
     # GH30463
     s = Series([np.nan, 1, 2, 3, 9, 18], index=["a", "b"] * 3)
 
-    msg = "The 'fill_method' and 'limit' keywords in Series.pct_change are deprecated"
-    with tm.assert_produces_warning(FutureWarning, match=msg):
+    warn = None if fill_method is None else FutureWarning
+    msg = (
+        "The 'fill_method' keyword being not None and the 'limit' keyword in "
+        "Series.pct_change are deprecated"
+    )
+    with tm.assert_produces_warning(warn, match=msg):
         result = s.pct_change(fill_method=fill_method)
 
     expected = Series([np.nan, np.nan, 1.0, 0.5, 2.0, 1.0], index=["a", "b"] * 3)