Repository: googleapis/python-bigquery

Issue:
Percents de-escaping isn't handled when no parameters are used in the dbapi.

```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 'foo %%', %(x)s", dict(x=1))
>>> cursor.fetchall()
[Row(('foo %', 1), {'f0_': 0, 'f1_': 1})]
>>> cursor.execute("select 'foo %%'", dict(x=1))
>>> cursor.fetchall()
[Row(('foo %',), {'f0_': 0})]
>>> cursor.execute("select 'foo %%'")
>>> cursor.fetchall()
[Row(('foo %%',), {'f0_': 0})]
```

Bonus:

if people don't escape:

```
>>> cursor.execute("select 'foo %'")
>>> cursor.fetchall()
[Row(('foo %',), {'f0_': 0})]
>>> cursor.execute("select 'foo %'", dict(x=1))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/jim/p/g/python-bigquery/google/cloud/bigquery/dbapi/_helpers.py", line 273, in with_closed_check
    return method(self, *args, **kwargs)
  File "/home/jim/p/g/python-bigquery/google/cloud/bigquery/dbapi/cursor.py", line 172, in execute
    formatted_operation = _format_operation(operation, parameters=parameters)
  File "/home/jim/p/g/python-bigquery/google/cloud/bigquery/dbapi/cursor.py", line 460, in _format_operation
    return _format_operation_dict(operation, parameters)
  File "/home/jim/p/g/python-bigquery/google/cloud/bigquery/dbapi/cursor.py", line 434, in _format_operation_dict
    return operation % formatted_params
ValueError: unsupported format character ''' (0x27) at index 13
```



Discussion:

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..ca78d390 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.
@@ -389,7 +393,7 @@ def _format_operation_list(operation, parameters):
 
     try:
         return operation % tuple(formatted_params)
-    except TypeError as exc:
+    except (TypeError, ValueError) as exc:
         raise exceptions.ProgrammingError(exc)
 
 
@@ -419,7 +423,7 @@ def _format_operation_dict(operation, parameters):
 
     try:
         return operation % formatted_params
-    except KeyError as exc:
+    except (KeyError, ValueError, TypeError) as exc:
         raise exceptions.ProgrammingError(exc)
 
 
@@ -441,7 +445,7 @@ def _format_operation(operation, parameters=None):
             ``parameters`` argument.
     """
     if parameters is None or len(parameters) == 0:
-        return operation
+        return operation.replace("%%", "%")  # Still do percent de-escaping.
 
     if isinstance(parameters, collections_abc.Mapping):
         return _format_operation_dict(operation, parameters)


Test patch:
diff --git a/tests/unit/test_dbapi_cursor.py b/tests/unit/test_dbapi_cursor.py
index 0f44e389..039ef3b4 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
 
@@ -633,6 +657,14 @@ class TestCursor(unittest.TestCase):
             {"somevalue-not-here": "hi", "othervalue": "world"},
         )
 
+    def test__format_operation_w_redundant_dict_key(self):
+        from google.cloud.bigquery.dbapi import cursor
+
+        formatted_operation = cursor._format_operation(
+            "SELECT %(somevalue)s;", {"somevalue": "foo", "value-not-used": "bar"}
+        )
+        self.assertEqual(formatted_operation, "SELECT @`somevalue`;")
+
     def test__format_operation_w_sequence(self):
         from google.cloud.bigquery.dbapi import cursor
 
@@ -652,8 +684,53 @@ class TestCursor(unittest.TestCase):
             ("hello",),
         )
 
+    def test__format_operation_w_too_long_sequence(self):
+        from google.cloud.bigquery import dbapi
+        from google.cloud.bigquery.dbapi import cursor
+
+        self.assertRaises(
+            dbapi.ProgrammingError,
+            cursor._format_operation,
+            "SELECT %s, %s;",
+            ("hello", "world", "everyone"),
+        )
+
     def test__format_operation_w_empty_dict(self):
         from google.cloud.bigquery.dbapi import cursor
 
         formatted_operation = cursor._format_operation("SELECT '%f'", {})
         self.assertEqual(formatted_operation, "SELECT '%f'")
+
+    def test__format_operation_wo_params_single_percent(self):
+        from google.cloud.bigquery.dbapi import cursor
+
+        formatted_operation = cursor._format_operation("SELECT '%'", {})
+        self.assertEqual(formatted_operation, "SELECT '%'")
+
+    def test__format_operation_wo_params_double_percents(self):
+        from google.cloud.bigquery.dbapi import cursor
+
+        formatted_operation = cursor._format_operation("SELECT '%%'", {})
+        self.assertEqual(formatted_operation, "SELECT '%'")
+
+    def test__format_operation_unescaped_percent_w_dict_param(self):
+        from google.cloud.bigquery import dbapi
+        from google.cloud.bigquery.dbapi import cursor
+
+        self.assertRaises(
+            dbapi.ProgrammingError,
+            cursor._format_operation,
+            "SELECT %(foo)s, '100 %';",
+            {"foo": "bar"},
+        )
+
+    def test__format_operation_unescaped_percent_w_list_param(self):
+        from google.cloud.bigquery import dbapi
+        from google.cloud.bigquery.dbapi import cursor
+
+        self.assertRaises(
+            dbapi.ProgrammingError,
+            cursor._format_operation,
+            "SELECT %s, %s, '100 %';",
+            ["foo", "bar"],
+        )