feat: add parameterized queries

[MrPowers]

I am currently writing code like this:

cities.to_view("cities", overwrite=True)
countries.to_view("countries", overwrite=True)

sd.sql("""
select * from cities
join countries
where ST_Intersects(cities.geometry, countries.geometry)
""").show()

This could be cool to skip the explicit view registration:

sd.sql("""
select * from cities
join countries
where ST_Intersects(cities.geometry, countries.geometry)
""", cities=cities, countries=countries).show()

It would be especially sweet if other objects (like dates & Shapely objects) could be parameterized too!

[paleolimbot]

Agreed! It's safer and more pythony to do the parameterized bits.

It would be especially sweet if other objects (like dates & Shapely objects) could be parameterized too!

Everything except spatial objects aren't much of a technical challenge (anything that "just works" in pyarrow.array()). Getting extension type objects are a bit trickier, particularly if we want to handle nested lists or structs that contain them.

Some prior art from DuckDB:

import duckdb

duckdb.sql(
    "SELECT * FROM (VALUES (1), (2), (3)) AS t(v) WHERE v > $cutoff",
    params={"cutoff": 1.5},
)
#> ┌───────┐
#> │   v   │
#> │ int32 │
#> ├───────┤
#> │     2 │
#> │     3 │
#> └───────┘

A related concept is how to broadly deal with expressions in a Pythonic way (e.g., sd.sql("...").filter(_.v > 1.5)). Expressions are a bit of a rabbit hole but I think we'd be better off for doing it right!