ensure soft_close occurs for fetchmany with server side cursor

Fixed regression where the :meth:`_engine.CursorResult.fetchmany` method
would fail to autoclose a server-side cursor (i.e. when ``stream_results``
or ``yield_per`` is in use, either Core or ORM oriented results) when the
results were fully exhausted.

All :class:`_result.Result` objects will now consistently raise
:class:`_exc.ResourceClosedError` if they are used after a hard close,
which includes the "hard close" that occurs after calling "single row or
value" methods like :meth:`_result.Result.first` and
:meth:`_result.Result.scalar`. This was already the behavior of the most
common class of result objects returned for Core statement executions, i.e.
those based on :class:`_engine.CursorResult`, so this behavior is not new.
However, the change has been extended to properly accommodate for the ORM
"filtering" result objects returned when using 2.0 style ORM queries,
which would previously behave in "soft closed" style of returning empty
results, or wouldn't actually "soft close" at all and would continue
yielding from the underlying cursor.

As part of this change, also added :meth:`_result.Result.close` to the base
:class:`_result.Result` class and implemented it for the filtered result
implementations that are used by the ORM, so that it is possible to call
the :meth:`_engine.CursorResult.close` method on the underlying
:class:`_engine.CursorResult` when the the ``yield_per`` execution option
is in use to close a server side cursor before remaining ORM results have
been fetched. This was again already available for Core result sets but the
change makes it available for 2.0 style ORM results as well.

Fixes: #7274
Change-Id: Id4acdfedbcab891582a7f8edd2e2e7d20d868e53

1 files changed, 62 insertions, 0 deletions

diff --git a/test/sql/test_resultset.py b/test/sql/test_resultset.py
index 47f26ddab..a9b7ee53f 100644
--- a/test/sql/test_resultset.py
+++ b/test/sql/test_resultset.py
@@ -2758,6 +2758,68 @@ class AlternateCursorResultTest(fixtures.TablesTest):
         # buffer of 98, plus buffer of 99 - 89, 10 rows
         eq_(len(result.cursor_strategy._rowbuffer), 10)
 
+    @testing.combinations(True, False, argnames="close_on_init")
+    @testing.combinations(
+        "fetchone", "fetchmany", "fetchall", argnames="fetch_style"
+    )
+    def test_buffered_fetch_auto_soft_close(
+        self, connection, close_on_init, fetch_style
+    ):
+        """test #7274"""
+
+        table = self.tables.test
+
+        connection.execute(
+            table.insert(),
+            [{"x": i, "y": "t_%d" % i} for i in range(15, 30)],
+        )
+
+        result = connection.execute(table.select().limit(15))
+        assert isinstance(result.cursor_strategy, _cursor.CursorFetchStrategy)
+
+        if close_on_init:
+            # close_on_init - the initial buffering will exhaust the cursor,
+            # should soft close immediately
+            result = result.yield_per(30)
+        else:
+            # not close_on_init - soft close will occur after fetching an
+            # empty buffer
+            result = result.yield_per(5)
+        assert isinstance(
+            result.cursor_strategy, _cursor.BufferedRowCursorFetchStrategy
+        )
+
+        with mock.patch.object(result, "_soft_close") as soft_close:
+            if fetch_style == "fetchone":
+                while True:
+                    row = result.fetchone()
+
+                    if row:
+                        eq_(soft_close.mock_calls, [])
+                    else:
+                        # fetchone() is also used by first(), scalar()
+                        # and one() which want to embed a hard close in one
+                        # step
+                        eq_(soft_close.mock_calls, [mock.call(hard=False)])
+                        break
+            elif fetch_style == "fetchmany":
+                while True:
+                    rows = result.fetchmany(5)
+
+                    if rows:
+                        eq_(soft_close.mock_calls, [])
+                    else:
+                        eq_(soft_close.mock_calls, [mock.call()])
+                        break
+            elif fetch_style == "fetchall":
+                rows = result.fetchall()
+
+                eq_(soft_close.mock_calls, [mock.call()])
+            else:
+                assert False
+
+        result.close()
+
     def test_buffered_fetchmany_yield_per_all(self, connection):
         table = self.tables.test