Skip to content

Tutorial: parameterized queries

Goal: Use placeholders in SQL safely (positional and named) with Session.sql(...).

Prereqs: Comfortable running basic Session.sql(...) queries (see Create, append, query).

What you’ll learn: - Positional ($1, $2, …) vs named ($name) placeholders - Which Python types are supported for parameters in v0

DataFusion SQL supports placeholders:

  • Positional: $1, $2, ...
  • Named: $name

This example shows both styles:

from __future__ import annotations

import pyarrow as pa

import timeseries_table_format as ttf


def run() -> list[pa.Table]:
    sess = ttf.Session()

    out_positional = sess.sql(
        "select cast($1 as bigint) as x, cast($2 as varchar) as y",
        params=[1, "hello"],
    )
    out_named = sess.sql(
        "select cast($a as bigint) as x, cast($b as varchar) as y",
        params={"a": 2, "b": "world"},
    )

    return [out_positional, out_named]


def main() -> None:
    out_positional, out_named = run()
    print(out_positional)
    print(out_named)


if __name__ == "__main__":
    main()

Supported parameter types

For v0, parameter values must be one of: - None, bool, int (i64 range), float, str, bytes

Note

If you use placeholders in a SELECT projection without type context, you may need an explicit cast (as shown in the example).

Next: - Concept: Buckets + overlap - Reference: Exceptions