Repository: pandas-dev/pandas

Issue:
API/INT: why is to_pydatetime handled differently in Series.dt accessor?
While reviewing: https://github.com/pandas-dev/pandas/pull/20198, I notices this was defined in the `DatetimeProperties` itself:

https://github.com/pandas-dev/pandas/blob/78ded2585efb3e4b5164af91c85927b99298f026/pandas/core/indexes/accessors.py#L114-L143

Is there any reason that we have it like that, while all others are just wrapped DatetimeIndex attributes/methods? 

Because that means this returns (a bit strangly) an ndarray instead of a Series of the datetime.datetime values (making it inconsistent with other methods)

cc @jbrockmendel @jreback


Discussion:
Ah, now I opened this issue, I thought of a probable reason: because otherwise the datetime.datetime values would quickly get inferred / coerced back to datetime64 ?
yes that is the reason
though we could return an object dtype Index


Could change this for 2.0.  Hard to deprecate since there isn't a good "do X instead."

Patch:
diff --git a/doc/source/whatsnew/v2.1.0.rst b/doc/source/whatsnew/v2.1.0.rst
index 38161a29a9ff7..daed6fbff71fc 100644
--- a/doc/source/whatsnew/v2.1.0.rst
+++ b/doc/source/whatsnew/v2.1.0.rst
@@ -168,6 +168,7 @@ Deprecations
 - Deprecated 'broadcast_axis' keyword in :meth:`Series.align` and :meth:`DataFrame.align`, upcast before calling ``align`` with ``left = DataFrame({col: left for col in right.columns}, index=right.index)`` (:issue:`51856`)
 - Deprecated the 'axis' keyword in :meth:`.GroupBy.idxmax`, :meth:`.GroupBy.idxmin`, :meth:`.GroupBy.fillna`, :meth:`.GroupBy.take`, :meth:`.GroupBy.skew`, :meth:`.GroupBy.rank`, :meth:`.GroupBy.cumprod`, :meth:`.GroupBy.cumsum`, :meth:`.GroupBy.cummax`, :meth:`.GroupBy.cummin`, :meth:`.GroupBy.pct_change`, :meth:`GroupBy.diff`, :meth:`.GroupBy.shift`, and :meth:`DataFrameGroupBy.corrwith`; for ``axis=1`` operate on the underlying :class:`DataFrame` instead (:issue:`50405`, :issue:`51046`)
 - Deprecated the "fastpath" keyword in :class:`Categorical` constructor, use :meth:`Categorical.from_codes` instead (:issue:`20110`)
+- Deprecated behavior of :meth:`Series.dt.to_pydatetime`, in a future version this will return a :class:`Series` containing python ``datetime`` objects instead of an ``ndarray`` of datetimes; this matches the behavior of other :meth:`Series.dt` properties (:issue:`20306`)
 - Deprecated passing a dictionary to :meth:`.SeriesGroupBy.agg`; pass a list of aggregations instead (:issue:`50684`)
 - Deprecated logical operations (``|``, ``&``, ``^``) between pandas objects and dtype-less sequences (e.g. ``list``, ``tuple``), wrap a sequence in a :class:`Series` or numpy array before operating instead (:issue:`51521`)
 - Deprecated the methods :meth:`Series.bool` and :meth:`DataFrame.bool` (:issue:`51749`)
diff --git a/pandas/core/indexes/accessors.py b/pandas/core/indexes/accessors.py
index 76666d62bc5c0..bf4da90efc17c 100644
--- a/pandas/core/indexes/accessors.py
+++ b/pandas/core/indexes/accessors.py
@@ -7,9 +7,12 @@
     TYPE_CHECKING,
     cast,
 )
+import warnings
 
 import numpy as np
 
+from pandas.util._exceptions import find_stack_level
+
 from pandas.core.dtypes.common import (
     is_datetime64_dtype,
     is_integer_dtype,
@@ -214,6 +217,15 @@ def _delegate_method(self, name: str, *args, **kwargs):
         return result
 
     def to_pydatetime(self):
+        # GH#20306
+        warnings.warn(
+            f"The behavior of {type(self).__name__}.to_pydatetime is deprecated, "
+            "in a future version this will return a Series containing python "
+            "datetime objects instead of an ndarray. To retain the old behavior, "
+            "call `np.array` on the result",
+            FutureWarning,
+            stacklevel=find_stack_level(),
+        )
         return cast(ArrowExtensionArray, self._parent.array)._dt_to_pydatetime()
 
     def isocalendar(self):
@@ -333,6 +345,15 @@ def to_pydatetime(self) -> np.ndarray:
         array([datetime.datetime(2018, 3, 10, 0, 0),
                datetime.datetime(2018, 3, 10, 0, 0)], dtype=object)
         """
+        # GH#20306
+        warnings.warn(
+            f"The behavior of {type(self).__name__}.to_pydatetime is deprecated, "
+            "in a future version this will return a Series containing python "
+            "datetime objects instead of an ndarray. To retain the old behavior, "
+            "call `np.array` on the result",
+            FutureWarning,
+            stacklevel=find_stack_level(),
+        )
         return self._get_values().to_pydatetime()
 
     @property
diff --git a/pandas/io/sql.py b/pandas/io/sql.py
index ca2676488dd11..49ec1a7545168 100644
--- a/pandas/io/sql.py
+++ b/pandas/io/sql.py
@@ -964,7 +964,13 @@ def insert_data(self) -> tuple[list[str], list[np.ndarray]]:
 
         for i, (_, ser) in enumerate(temp.items()):
             if ser.dtype.kind == "M":
-                d = ser.dt.to_pydatetime()
+                if isinstance(ser._values, ArrowExtensionArray):
+                    with warnings.catch_warnings():
+                        warnings.filterwarnings("ignore", category=FutureWarning)
+                        # GH#52459 to_pydatetime will return Index[object]
+                        d = np.asarray(ser.dt.to_pydatetime(), dtype=object)
+                else:
+                    d = ser._values.to_pydatetime()
             elif ser.dtype.kind == "m":
                 vals = ser._values
                 if isinstance(vals, ArrowExtensionArray):


Test patch:
diff --git a/pandas/conftest.py b/pandas/conftest.py
index 70e1c317c2043..669cfe9be5170 100644
--- a/pandas/conftest.py
+++ b/pandas/conftest.py
@@ -137,6 +137,10 @@ def pytest_collection_modifyitems(items, config) -> None:
     ignored_doctest_warnings = [
         # Docstring divides by zero to show behavior difference
         ("missing.mask_zero_div_zero", "divide by zero encountered"),
+        (
+            "to_pydatetime",
+            "The behavior of DatetimeProperties.to_pydatetime is deprecated",
+        ),
         (
             "pandas.core.generic.NDFrame.bool",
             "(Series|DataFrame).bool is now deprecated and will be removed "
diff --git a/pandas/tests/extension/test_arrow.py b/pandas/tests/extension/test_arrow.py
index 3ecbc723be2eb..99f2a7d820dcb 100644
--- a/pandas/tests/extension/test_arrow.py
+++ b/pandas/tests/extension/test_arrow.py
@@ -2287,12 +2287,16 @@ def test_dt_to_pydatetime():
     data = [datetime(2022, 1, 1), datetime(2023, 1, 1)]
     ser = pd.Series(data, dtype=ArrowDtype(pa.timestamp("ns")))
 
-    result = ser.dt.to_pydatetime()
+    msg = "The behavior of ArrowTemporalProperties.to_pydatetime is deprecated"
+    with tm.assert_produces_warning(FutureWarning, match=msg):
+        result = ser.dt.to_pydatetime()
     expected = np.array(data, dtype=object)
     tm.assert_numpy_array_equal(result, expected)
     assert all(type(res) is datetime for res in result)
 
-    expected = ser.astype("datetime64[ns]").dt.to_pydatetime()
+    msg = "The behavior of DatetimeProperties.to_pydatetime is deprecated"
+    with tm.assert_produces_warning(FutureWarning, match=msg):
+        expected = ser.astype("datetime64[ns]").dt.to_pydatetime()
     tm.assert_numpy_array_equal(result, expected)
 
 
diff --git a/pandas/tests/series/accessors/test_cat_accessor.py b/pandas/tests/series/accessors/test_cat_accessor.py
index a2ed590640465..cc64a9388fd7c 100644
--- a/pandas/tests/series/accessors/test_cat_accessor.py
+++ b/pandas/tests/series/accessors/test_cat_accessor.py
@@ -203,6 +203,9 @@ def test_dt_accessor_api_for_categorical(self, idx):
                 if func == "to_period":
                     # dropping TZ
                     warnings.simplefilter("ignore", UserWarning)
+                if func == "to_pydatetime":
+                    # deprecated to return Index[object]
+                    warnings.simplefilter("ignore", FutureWarning)
                 res = getattr(cat.dt, func)(*args, **kwargs)
                 exp = getattr(ser.dt, func)(*args, **kwargs)
 
diff --git a/pandas/tests/series/accessors/test_dt_accessor.py b/pandas/tests/series/accessors/test_dt_accessor.py
index fa8e184285616..3f957130b6020 100644
--- a/pandas/tests/series/accessors/test_dt_accessor.py
+++ b/pandas/tests/series/accessors/test_dt_accessor.py
@@ -115,7 +115,9 @@ def test_dt_namespace_accessor_datetime64(self, freq):
         for prop in ok_for_dt_methods:
             getattr(ser.dt, prop)
 
-        result = ser.dt.to_pydatetime()
+        msg = "The behavior of DatetimeProperties.to_pydatetime is deprecated"
+        with tm.assert_produces_warning(FutureWarning, match=msg):
+            result = ser.dt.to_pydatetime()
         assert isinstance(result, np.ndarray)
         assert result.dtype == object
 
@@ -152,7 +154,9 @@ def test_dt_namespace_accessor_datetime64tz(self):
         for prop in ok_for_dt_methods:
             getattr(ser.dt, prop)
 
-        result = ser.dt.to_pydatetime()
+        msg = "The behavior of DatetimeProperties.to_pydatetime is deprecated"
+        with tm.assert_produces_warning(FutureWarning, match=msg):
+            result = ser.dt.to_pydatetime()
         assert isinstance(result, np.ndarray)
         assert result.dtype == object