Make `cursor.rowcount` accessible (wontfix)

[simonw]

In building this Datasette feature on top of sqlite-utils I thought it might be useful to expose the number of rows that had been affected by a bulk insert or update - the cursor.rowcount:

• https://github.com/simonw/datasette/issues/1866

This isn't currently exposed by sqlite-utils.

[simonw]

This could work in a similar way to db.insert(...).last_rowid.

[simonw]

This may not make sense.

First, .last_rowid is a property on table - but that doesn't make sense for rowcount since it should clearly be a property on the database itself (you can run a query directly using db.execute() without going through a Table object).

So I tried this prototype:

diff --git a/docs/python-api.rst b/docs/python-api.rst
index 206e5e6..78d3a8d 100644
--- a/docs/python-api.rst
+++ b/docs/python-api.rst
@@ -186,6 +186,15 @@ The ``db.query(sql)`` function executes a SQL query and returns an iterator over
     # {'name': 'Cleo'}
     # {'name': 'Pancakes'}

+After executing a query the ``db.rowcount`` property on that database instance will reflect the number of rows affected by any insert, update or delete operations performed by that query:
+
+.. code-block:: python
+
+    db = Database(memory=True)
+    db["dogs"].insert_all([{"name": "Cleo"}, {"name": "Pancakes"}])
+    print(db.rowcount)
+    # Outputs: 2
+
 .. _python_api_execute:

 db.execute(sql, params)
diff --git a/sqlite_utils/db.py b/sqlite_utils/db.py
index a06f4b7..c19c2dd 100644
--- a/sqlite_utils/db.py
+++ b/sqlite_utils/db.py
@@ -294,6 +294,8 @@ class Database:

     _counts_table_name = "_counts"
     use_counts_table = False
+    # Number of rows inserted, updated or deleted
+    rowcount: Optional[int] = None

     def __init__(
         self,
@@ -480,9 +482,11 @@ class Database:
         if self._tracer:
             self._tracer(sql, parameters)
         if parameters is not None:
-            return self.conn.execute(sql, parameters)
+            cursor = self.conn.execute(sql, parameters)
         else:
-            return self.conn.execute(sql)
+            cursor = self.conn.execute(sql)
+        self.rowcount = cursor.rowcount
+        return cursor

     def executescript(self, sql: str) -> sqlite3.Cursor:
         """

But this happens:

>>> from sqlite_utils import Database
>>> db = Database(memory=True)
>>> db["dogs"].insert_all([{"name": "Cleo"}, {"name": "Pancakes"}])
<Table dogs (name)>
>>> db.rowcount
-1

Turning on query tracing demonstrates why:

>>> db = Database(memory=True, tracer=print)
PRAGMA recursive_triggers=on; None
>>> db["dogs"].insert_all([{"name": "Cleo"}, {"name": "Pancakes"}])
select name from sqlite_master where type = 'view' None
select name from sqlite_master where type = 'table' None
select name from sqlite_master where type = 'view' None
CREATE TABLE [dogs] (
   [name] TEXT
);
         None
select name from sqlite_master where type = 'view' None
INSERT INTO [dogs] ([name]) VALUES (?), (?); ['Cleo', 'Pancakes']
select name from sqlite_master where type = 'table' None
select name from sqlite_master where type = 'table' None
PRAGMA table_info([dogs]) None
<Table dogs (name)>
>>>

The .insert_all() function does a bunch of other queries too, so .rowcount is quickly over-ridden by the same result from extra queries that it executed.

[simonw]

The question I was originally trying to answer here was this: how many rows were actually inserted by that call to .insert_all()?

I don't know that .rowcount would ever be useful here, since the "correct" answer depends on other factors - had I determined to ignore or replace records with a primary key that matches an existing record for example?

So I think if people need rowcount they can get it by using a cursor directly.