Use Python f-strings and Jinja at the same time

Question

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?

Answer 1

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')

Answer 2

I believe it is more elegant to instead use the good old % operator here, which helps to avoid the confusion raised because of similar { notations.

Here, the jinja-template string is clear and unambiguous:

query_string = """
        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', '%s')
           """ % (YESTERDAY_DATE_STR)

->

        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')

In your case, you still need to escape the date parser parameters.