typing: use mypy to verify runtime behaviour matches type stubs

[AllSeeingEyeTolledEweSew]

I'm using apsw from pypi (thanks for that!)

I found some issues with the type stubs.

Some valid code that fails type-checking:

import apsw
conn = apsw.Connection(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE t (x INT PRIMARY KEY)")
cur.execute("SELECT * FROM t")
for row in cur:
    pass
row = cur.execute("SELECT * FROM t").fetchone()

$ pip freeze | grep -E '(mypy|apsw)=='
apsw==3.38.5.post1
mypy==0.961
$ mypy test.py
test.py:6: error: "Cursor" has no attribute "__iter__" (not iterable)
test.py:8: error: "Iterator[Any]" has no attribute "fetchone"
Found 2 errors in 1 file (checked 1 source file)

• Cursor.execute() returns Iterator
  • It should return Cursor. (Actually it should return typing.Self, but that's new in 3.11 beta, and there isn't a nice way to declare this in a backwards-compatible way yet)
• Cursor doesn't expose __iter__()/__next__() in type stubs.
  • The iterator protocol methods should be exposed in the type stubs, as well as any other methods that are part of the public API.
  • Cursor.__iter__() should return Cursor (or Self)
  • Cursor.__next__() should return Any, or perhaps a narrower row type (IMO the row type should be narrower than Any, but I'll file a separate issue about this)

[rogerbinns]

I did use stubtest, but that turns out to check stubs exists and are syntactically valid. The primary bug here is that I should also use mypy to verify that runtime behaviour matches the types. Changing title to match.

[AllSeeingEyeTolledEweSew]

FYI: It's probably "fine" to release changes to type stubs that break the type-checking of existing users, in a non-breaking update of apsw. This is just the state of type checking in python. Every release of mypy seems to include some breaking changes in their type stubs, so I always assume any update of any other typed package might also break.

[rogerbinns]

So far you've found bugs in the typing information, so I have no problem fixing them, just like any other bug. I am going to try to run the example code (exercises every api) under mypy --strict

I was a bit surprised at having to expose the __iter__/__next__ methods since that machinery is not normally exposed (for example doc doesn't mention them). The type stubs for sqlite3 do so that is the right thing to do. I'll have to make sure it stays out of the doc though - currently the stubs are generated from the docstrings.

[rogerbinns]

Closing because this is 99% done