Repository: googleapis/python-bigquery

Issue:
Make DB API Cursors iterable
Thanks for stopping by to let us know something could be better!

**PLEASE READ**: If you have a support contract with Google, please create an issue in the [support console](https://cloud.google.com/support/) instead of filing on GitHub. This will ensure a timely response.

Please run down the following list and make sure you've tried the usual "quick fixes":

  - Search the issues already opened: https://github.com/googleapis/python-bigquery/issues
  - Search StackOverflow: https://stackoverflow.com/questions/tagged/google-cloud-platform+python

If you are still having issues, please be sure to include as much information as possible:

#### Environment details

  - OS type and version: Ununtu 20.4
  - Python version: `python --version` 3.9.4
  - pip version: `pip --version` pip 21.0.1 from /home/jim/p/g/python-bigquery/env/3.9/lib/python3.9/site-packages/pip (python 3.9)
  - `google-cloud-bigquery` version: `pip show google-cloud-bigquery` 2.13.1

#### Steps to reproduce

  1. 
  2. ?

#### Code example

```python

>>> import google.cloud.bigquery.dbapi
>>> conn = google.cloud.bigquery.dbapi.connect()
/home/jim/p/g/python-bigquery/google/cloud/bigquery/client.py:444: UserWarning: Cannot create BigQuery Storage client, the dependency google-cloud-bigquery-storage is not installed.
  warnings.warn(
>>> cursor = conn.cursor()
>>> cursor.execute("select 1")
>>> list(cursor)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: 'Cursor' object is not iterable
>>> 
```
Iterability is optional, but widely implemented in dbapi implementations, because it's so useful.


Discussion:
plamut: The "is closed" check is not applied to `__iter__()`, will improve that.
jimfulton: Actually, I'd change the PR name to something like:

"feat: DB API cursors are now iterable"




Patch:
diff --git a/google/cloud/bigquery/dbapi/_helpers.py b/google/cloud/bigquery/dbapi/_helpers.py
index 69694c98..beb3c5e7 100644
--- a/google/cloud/bigquery/dbapi/_helpers.py
+++ b/google/cloud/bigquery/dbapi/_helpers.py
@@ -276,7 +276,7 @@ def raise_on_closed(
         """Apply ``_raise_on_closed()`` decorator to public instance methods.
         """
         for name in dir(klass):
-            if name.startswith("_"):
+            if name.startswith("_") and name != "__iter__":
                 continue
 
             member = getattr(klass, name)
diff --git a/google/cloud/bigquery/dbapi/cursor.py b/google/cloud/bigquery/dbapi/cursor.py
index ee09158d..7e544971 100644
--- a/google/cloud/bigquery/dbapi/cursor.py
+++ b/google/cloud/bigquery/dbapi/cursor.py
@@ -365,6 +365,10 @@ class Cursor(object):
     def setoutputsize(self, size, column=None):
         """No-op, but for consistency raise an error if cursor is closed."""
 
+    def __iter__(self):
+        self._try_fetch()
+        return iter(self._query_data)
+
 
 def _format_operation_list(operation, parameters):
     """Formats parameters in operation in the way BigQuery expects.


Test patch:
diff --git a/tests/unit/test_dbapi_cursor.py b/tests/unit/test_dbapi_cursor.py
index 0f44e389..8ca4e9b6 100644
--- a/tests/unit/test_dbapi_cursor.py
+++ b/tests/unit/test_dbapi_cursor.py
@@ -178,6 +178,7 @@ class TestCursor(unittest.TestCase):
             "fetchone",
             "setinputsizes",
             "setoutputsize",
+            "__iter__",
         )
 
         for method in method_names:
@@ -611,6 +612,29 @@ class TestCursor(unittest.TestCase):
         self.assertIsNone(cursor.description)
         self.assertEqual(cursor.rowcount, 12)
 
+    def test_is_iterable(self):
+        from google.cloud.bigquery import dbapi
+
+        connection = dbapi.connect(
+            self._mock_client(rows=[("hello", "there", 7), ("good", "bye", -3)])
+        )
+        cursor = connection.cursor()
+        cursor.execute("SELECT foo, bar, baz FROM hello_world WHERE baz < 42;")
+
+        rows_iter = iter(cursor)
+
+        row = next(rows_iter)
+        self.assertEqual(row, ("hello", "there", 7))
+        row = next(rows_iter)
+        self.assertEqual(row, ("good", "bye", -3))
+        self.assertRaises(StopIteration, next, rows_iter)
+
+        self.assertEqual(
+            list(cursor),
+            [],
+            "Iterating again over the same results should produce no rows.",
+        )
+
     def test__format_operation_w_dict(self):
         from google.cloud.bigquery.dbapi import cursor