Skip to content

narwhals.sql

table 

table(name: str, schema: IntoSchema) -> SQLTable

Generate standalone LazyFrame which you can use to generate SQL.

Note that this requires DuckDB to be installed.

Parameters:

Name Type Description Default
name str

Table name.

 required
schema IntoSchema

Table schema.

 required

Examples:

>>> import narwhals as nw
>>> from narwhals.sql import table
>>> schema = {"date": nw.Date, "price": nw.List(nw.Int64), "symbol": nw.String}
>>> table("t", schema)
┌────────────────────────────┐
|     Narwhals LazyFrame     |
|----------------------------|
|┌──────┬─────────┬─────────┐|
|│ date │  price  │ symbol  │|
|│ date │ int64[] │ varchar │|
|├──────┴─────────┴─────────┤|
|│          0 rows          │|
|└──────────────────────────┘|
└────────────────────────────┘
Source code in narwhals/sql.py 
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
def table(name: str, schema: IntoSchema) -> SQLTable:
    """Generate standalone LazyFrame which you can use to generate SQL.

    Note that this requires DuckDB to be installed.

    Parameters:
        name: Table name.
        schema: Table schema.

    Examples:
        >>> import narwhals as nw
        >>> from narwhals.sql import table
        >>> schema = {"date": nw.Date, "price": nw.List(nw.Int64), "symbol": nw.String}
        >>> table("t", schema)
        ┌────────────────────────────┐
        |     Narwhals LazyFrame     |
        |----------------------------|
        |┌──────┬─────────┬─────────┐|
        |│ date │  price  │ symbol  │|
        |│ date │ int64[] │ varchar │|
        |├──────┴─────────┴─────────┤|
        |│          0 rows          │|
        |└──────────────────────────┘|
        └────────────────────────────┘
    """
    column_mapping = {
        col: narwhals_to_native_dtype(dtype, Version.MAIN, TZ)
        for col, dtype in schema.items()
    }
    dtypes = ", ".join(f'"{col}" {dtype}' for col, dtype in column_mapping.items())
    CONN.sql(f"""
        CREATE TABLE "{name}"
        ({dtypes});
        """)
    lf = from_native(CONN.table(name))
    return SQLTable(lf._compliant_frame, level=lf._level)

A LazyFrame with an additional to_sql method.

to_sql 

to_sql(*, pretty: bool = False) -> str

Convert to SQL query.

Parameters:

Name Type Description Default
pretty bool

Whether to pretty-print SQL query. If True, requires sqlparse to be installed.

 False

Examples:

>>> import narwhals as nw
>>> from narwhals.sql import table
>>> schema = {"date": nw.Date, "price": nw.Int64, "symbol": nw.String}
>>> assets = table("assets", schema)
>>> result = assets.filter(nw.col("price") > 100)
>>> print(result.to_sql())
SELECT * FROM main.assets WHERE (price > 100)