Rework Relational API documentation page

[szarnyasg]

https://duckdb.org/docs/api/python/relational_api.html currently mostly has SQL queries instead of the Relational API 😐.

Also, taken from #1398 by @Tishj:

In the first paragraph it is explained that execution is lazy

until a method that triggers execution is called.

But we never really clarify which methods trigger execution. At the very bottom we talk about write_... which are some examples of methods that trigger execution, but this isn't made obvious

And in general the methods that trigger execution are missing:

• execute
• to_df/df/fetch_df
• arrow
• pl
• fetch_numpy
• fetchall/fetchmany/fetchone
• tf
• to_table
• torch

Probably not a complete list ^

[Tishj]

The main reasoning behind this is that the relational API mostly speaks to people that are not fond of SQL, so using the relational version wherever we can will speak much more to the people this feature attracts

Maybe we want to split this into multiple pages

Relational API / Creation Transformations Result format

The Creation page can show examples of different ways to construct relations

• pandas dataframe, polars dataframe
• csv, parquet, json
• arrow arrays / numpy columns
• values
• sql

Transformations:

• join
• filter
• limit
• order
• project
• window
• aggregate
• set operations (except, union (all), intersect)

Result Format:

• csv, parquet, json
• pandas dataframe, polars dataframe
• arrow table, arrow record batch
• native python (fetchone, fetchmany, fetchall)
• insert/update/create table/view (back into the database)

[Tishj]

I'm not entirely sure yet how to weave the new expression API into these docs though It might just need a separate page and then we can extend the Transformations sections with the variation that accepts Expression(s) instead of raw strings