Feature Request: Implicit virtual_table_name when chaining in the Relational API #13672

Hi folks, I'm really enjoying playing around with DuckDB Relational API in Python as a way to incrementally build up queries.

However, the DuckDBPyRelation.query() function requires a virtual_table_name string that can be a little cumbersome to work with, and I'm wondering if it would be easy to add functionality to the API to make it unnecessary in some situations.

For example, I can load the iris data set and chain several SQL statements together with something like the following:

import duckdb
iris = duckdb.read_csv('https://raw.githubusercontent.com/mwaskom/seaborn-data/master/iris.csv')

(iris
 .query('t1', 'from t1 select species, sepal_length as sl, sepal_width as sw')
 .query('t2', 'from t2 where sl > 4.5')
 .query('t3', 'from t3 where sw between 3.1 and 3.5')
 .query('t4', 'from t4 select species, avg(sl), max(sw) group by all')
 .query('t5', 'from t5 order by 2')
 .query('t6', 'from t6 limit 2')
)

However, each statement requires the somewhat repetitive pattern of .query(<unique_table_name>, 'from <unique_table_name> ...'). When I've done this in the past, I've found it error-prone and tricker to reorder or refactor statements.

Ideally, I'm thinking we could drop the from <unique_table_name> part, making it implied and unique so that we avoid name conflicts. For example, we could do some monkey patching to add a DuckDBPyRelation.sql() function (I'm surprised this name is still free!) like the following:

from duckdb.duckdb import DuckDBPyRelation

def _sql(self: DuckDBPyRelation, stmt: str) -> DuckDBPyRelation:
    """
    Run the given SQL statement on the relation object, with an implied `FROM <temp_table_name>` clause.
    """
    # create a temporary table name
    # TODO: ideally, duckdb would do this for us, so we can avoid the need to come up with a unique name
    import random
    import string
    temp_table_name = '_tbl_' + ''.join(random.choices(string.ascii_lowercase, k=10))

    # insert the implied `from <temp_table_name>`, followed by the given SQL statement
    rel = self.query(
        temp_table_name,
        f'from {temp_table_name}\n{stmt}'
    )
    
    return rel

DuckDBPyRelation.sql = _sql

The chaining example above then simplifies to the following, without the need to come up with and specify table names:

(iris
 .sql('select species, sepal_length as sl, sepal_width as sw')
 .sql('where sl > 4.5')
 .sql('where sw between 3.1 and 3.5')
 .sql('select species, avg(sl), max(sw) group by all')
 .sql('order by 2')
 .sql('limit 2')
)

While this works as a hack, it would be nice if functionality like this was included in DuckDB by default. I'm also assuming you could do something more intelligent to uniquely identify tables than my temp_table_name = '_tbl_' + ''.join(random.choices(string.ascii_lowercase, k=10)).

I've found the pattern above super ergonomic to work with, especially when I'm doing exploratory data analysis and can just write short SQL snippets like where sl > 4.5, order by 2, or limit 2. This functionality would also be helpful to someone who wanted to do something funky with Python magic methods :)

Would it make sense to add a function along these lines to the relational API?