gfw.common.query

Utilities for repsenting SQL queries in a structured, reusable way.

This module defines the Query class, which provides:

• Automatic detection and setup of the Jinja2 environment using EnvironmentLoader.

• Helpers for rendering and formatting SQL queries.

• A utility to expand typing.NamedTuple schemas into SELECT clauses.

• Built-in conversion for datetime fields to BigQuery-compatible Unix timestamps.

• Support for dependency injection of custom jinja2.Environment instances.

This design centralizes SQL generation, improves testability, and ensures consistency across queries within pipelines or shared libraries.

Classes

Query

Abstract base class for SQL queries rendered via Jinja2 templates.

class Query

Abstract base class for SQL queries rendered via Jinja2 templates.

Subclasses must define:

• A Jinja2 template filename (via template_filename property).

• Variables to render into the template (via template_vars property).

The base class handles Jinja2 environment setup, SQL rendering, and optional query formatting.

DEFAULT_JINJA_FOLDER = 'assets/queries'

Default folder where Jinja2 templates are located.

classmethod datetime_to_timestamp(field)

Returns SQL expression to cast a datetime field to TIMESTAMP.

Parameters:

field (str) – The column name (string) to cast.

Returns:

A BigQuery SQL expression converting the datetime column to FLOAT64 seconds.

Return type:

str

property output_type: type[NamedTuple] | None

Optional schema for the query results.

Subclasses may override this property to return a NamedTuple type that models the expected rows. The fields of this type are automatically expanded into the generated SELECT clause.

Returns:

A subclass of NamedTuple describing the result schema, or None if no schema is defined.

abstract property template_filename: str

Name of the Jinja2 template file for this query.

Subclasses must override this property to point to the SQL template file (relative to the DEFAULT_JINJA_FOLDER).

Returns:

The filename of the template (e.g., messages.sql.j2).

abstract property template_vars: dict[str, str]

Variables to inject into the Jinja2 template.

Subclasses must override this property to return the dictionary of template context variables (e.g., start_date, end_date, …).

Returns:

A dictionary of key-value pairs for template rendering.

property top_level_package: str

Detects the top-level package name for this query class.

This is used to locate the query templates when using EnvironmentLoader.

Returns:

The name of the top-level package as a string.

property jinja_env: Environment

Returns or lazily creates a Jinja2 environment for this query.

If no environment was explicitly set with with_env(), one is created using from_package() and the detected package name.

with_env(env)

Injects a custom jinja2.Environment into this query.

This method enables dependency injection for testing or when using shared environments. Returns self for fluent chaining.

Parameters:

env (Environment) – A configured jinja2.Environment.

Returns:

self (the same query instance).

Return type:

Query

render(formatted=False)

Renders the Query using Jinja2.

Parameters:

formatted (bool) – If True, the rendered query is formatted with sqlparse. Defaults to False.

Returns:

The rendered query string (formatted if requested).

Return type:

str

get_select_fields(convert_datetime_to_timestamp=False)

Builds the SELECT clause fields from the output schema.

Parameters:

convert_datetime_to_timestamp (bool) – If True, fields typed as datetime are automatically cast to Unix float timestamps (via datetime_to_timestamp()). All other fields are passed through.

Returns:

A comma-separated string of SELECT fields.

Return type:

str

static sql_strings(strings)

Wraps each string in single quotes for safe SQL usage.

Parameters:

strings (list[str]) – A list of plain strings.

Returns:

A list of SQL-safe quoted string literals.

Return type:

list[str]

static format(query)

Formats a SQL query string for better readability.

Parameters:

query (str) – The raw SQL string.

Returns:

A neatly indented and uppercased SQL string.

Return type:

str