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?