Use Python f-strings and Jinja at the same time

user_4007615 0
Viewed 7070

I am trying to write a concise SQL query string in Python, to make use of both f-strings and Jinja at the same time.
Background info: I am writing a query used in Airflow.

This did not work:

query_string = f"""
        SELECT
            COUNT(DISTINCT case_id) AS counts
        FROM
            `{{var.value.gcp_project}}.{{var.value.dataset_prefix}}user.person`
        WHERE
            identified_on = PARSE_DATE('%Y-%m-%d', '{YESTERDAY_DATE_STR}')
           """

It produced the query string as:

SELECT
    COUNT(DISTINCT case_id) AS counts
FROM
    `{var.value.gcp_project}.{var.value.dataset_prefix}user.person`
WHERE
    identified_on = PARSE_DATE('%Y-%m-%d', '2020-09-07')

So it did the f-string value replacement but not the Jinja.

How can I make both f-strings and Jinja work at the same time?

jinja2 python-3.x python
2 Answers

I found that doubling the curly brackets {{ and }} works.
The double curly bracket gets escaped to a single one, and since Jinja requires 2 of them, 4 brackets does the trick.

So this query:

query_string = f"""
        SELECT
            COUNT(DISTINCT case_id) AS counts
        FROM
            `{{{{var.value.gcp_project}}}}.{{{{var.value.dataset_prefix}}}}user.person`
        WHERE
            identified_on = PARSE_DATE('%Y-%m-%d', '{YESTERDAY_DATE_STR}')
           """

Returns a correctly formatted query:

SELECT
    COUNT(DISTINCT case_id) AS counts
FROM
    `gcp_project.dataset_user.person`
WHERE
    identified_on = PARSE_DATE('%Y-%m-%d', '2020-09-07')
user_40076150

You might wanna use the format method instead:

query_string = "
        SELECT
            COUNT(DISTINCT case_id) AS counts
        FROM
            `{gcp_project}.{dataset_prefix}user.person`
        WHERE
            identified_on = PARSE_DATE('%Y-%m-%d', '{yesterday}')
           ".format(**var.value, yesterday=YESTERDAY_DATE_STR)
user_136517010
Related