Feat: Wrap the generated API in a python layer for complete control of type hints, docstrings, and docs generation

[NickCrews]

Currently, if I understand correctly, we

• handwrite the https://github.com/duckdb/duckdb-python/blob/636c3bce185ead2fecac9a896fd28003ddcd3b16/duckdb/__init__.py by just re-exporting from the generated C layer, eg from _duckdb import *
• For type hints, we use https://github.com/duckdb/duckdb-python/blob/636c3bce185ead2fecac9a896fd28003ddcd3b16/scripts/regenerate_python_stubs.sh and mypy to autogenerate the https://github.com/duckdb/duckdb-python/blob/636c3bce185ead2fecac9a896fd28003ddcd3b16/duckdb/__init__.pyi, with some manual overrides in there as well

I propose instead:

• We handwrite out the .pyi file. This would allow us to
  • write better type hints, eg currently DuckDBPyConnection.create_function has this rather terse signature: def create_function(name: str, function: function, parameters: Optional[List[DuckDBPyType]] = None, return_type: Optional[DuckDBPyType] = None, *, type: Optional[PythonUDFType] = PythonUDFType.NATIVE, null_handling: Optional[FunctionNullHandling] = FunctionNullHandling.DEFAULT, exception_handling: Optional[PythonExceptionHandling] = PythonExceptionHandling.DEFAULT, side_effects: bool = False, connection: DuckDBPyConnection = ...) -> DuckDBPyConnection: ..., but instead this would be much more readable (and correct for mypy) in my opinion if it used eg type: Literal["native", "arrow"] = "native" instead of the enum PythonUDFType.
  • Write out nicely formatted docstrings with actual argument and return type descriptions.
  • Both of these would really improve the IDE experience. I currently see in vscode

which is at least formatted nicely, but gives no suggestions as to argument completions and has no docstring.

• Optionally, if we still wanted to verify that the handwritten types in the .pyi file actually matched the runtime types, we could at build time still run mypy to generate the suggested types, and use griffe to check for API compatibility between the two. This would almost definitely need some hand tweaking, but I think it could use the same # stubgen override system that regenerate_python_stubs.sh uses

I'm not sure if we could also just push the docstrings and other documentation down into the C++ layer and have pybind translate it for us?

[evertlammerts]

There is definitely a lot we can do to improve the devexp here. We're making some small steps in the direction (e.g I have a branch where I'm doing a lot of cleaning with the help of ruff) but we're a long way out.

That said:

• Without having given too much thought to this I'm tending towards not using stub files at all and instead produce a clean init.py for the duckdb package. The pybind11 module is not top-level (it's _duckdb), so unless we want to make that the top-level package we have to import it in duckdb anyway. We may as well put everything in __init__.py, is my thinking? But if there's a good reason to use a stub file instead I'd love to hear it.
• init.py was once (mostly) generated by scripts/generate_connection_wrapper_methods.py. But basically all scripts are defunct since we moved the package out of duckdb's main tree. It probably makes sense to revive at least some of that, if only to help with the initial generation and also for extracting the docstrings, but I agree that we need the rigor of handwriting to make sure the API definitions are really correct and make most sense for use with LSP's (note to self: Zuban seems hot right now, might be worth looking into.)
• It looks like init.pyi takes preference of runtime exposed docstrings in vscode (and other IDEs / LSPs?). create_function's definition in pyconnection.cpp includes a (minimal) docstring. help(duckdb.create_function) picks it up.
• I'm not too optimistic about our ability to prioritize this any time soon, so (incremental) contributions in this area are very welcome. It might make sense to get some form of automated extraction up and running again to extract up to date definitions, then do an initial cleanup of init.py (meaning: cleanup the import mess that it currently is), then start adding definitions there and removing them from init.pyi batch by batch.

[NickCrews]

not using stub files at all and instead produce a clean init.py

This was my thought too, and I didn't suggest it because I couldn't think of a way to properly wrap the connection objects. But now I'm thinking of it more, just a plain wrapper, with the public instead creating/managing/proxying the real _duckdb instance, should be just fine. So in that case, yes I think reducing to one file sounds great!

create_function's definition in pyconnection.cpp includes a (minimal) docstring.

Hm, so to reduce duplication, I would think we should put all public docs in the python layer, and remove all of these docs from the cpp layer? See any problem with that?

(incremental) contributions in this area are very welcome.

I might tae a stab at this. Do you think the automated extractions and verifcation of API compatibility is a hard requirement? It sounds like this isn't working right now, so I guess not. If it's not a requirement, then I think honestly a port of the existing stuff might not be too bad. Maybe you'll see a PR from me??

[NickCrews]

ehh, I want to let your ruff branch land before I start mucking around or we will get all sorts of merge conflicts. Anything I can do yo help that along?

[evertlammerts]

That's probably a good idea :-) I'm hoping to merge the ruff stuff (and pre-commit + some hooks) over the next two weeks - we need to get linting and formatting back up and running.

Re automation: I do think that's going to be a requirement. But don't let that hold you back. My thinking is the following:

• The C++ code is the source of truth in terms of typing and documentation. So we leave / improve docstrings and type hints there.
• We create a test that is skipped by default but can be run by hand, and does the following:
  • Extract the complete API using pybind11-stubgen, mypy's stubgen (this is what is used in the stubgeneration scripts), or something els.
  • Check that all methods and functions exist in init.py, that they have the correct docstrings, the correct parameters, etc. This should check everything that we know must be true given the pybind11 definitions. The test reports on every function that is missing, docstring that is incorrect, etc.

This would give a great starting point of incrementally adding the definitions. After cleaning up the imports, we could put everything that's currently in the stub file into init.py, run the test, and see everything that is missing and different.

For param and return types that we want to be overridden in our init.py we can do a couple of things. This requires a little bit of thought and insight in the type of changes we need to make, but I can see a couple of options:

• Annotate the methods in C++ / look for ways to pass better type hints in the pybind11 definitions
• Annotate the functions in init.py to let the test know we've changed something

Wdyt?

[evertlammerts]

Closing this for now, in favor of #57 . I'll update on current progress re typechecking there, discussion also welcome there.