Single or Multiple { (curly brackets) in Python for formatting an string

Question

In the following ETL query we see the use of double curly brackets.

query_data = """
SELECT {}
FROM {}
WHERE '{{}}'
ORDER BY '{{}}'
"""

Normally, one uses a single pair of bracket with format to customize a string. So, I'm wondering which functionality carries using single or multiple curly brackets for formatting a string?.

Answer 1

The number of curly brackets that we're going to use depends on the number of times that we're going to be formatting a string. In your example, for the first time we're formatting the query we use the single brackets as follows:

first_format = query_data.format("id", "table")

Now, for the second time we format

second_format = first_format.format("id != '1'", "id")

Which should render the following string

SELECT id
FROM table
WHERE id != '1'
ORDER BY id

However, if you're formatting three or more times you're string, managing the number of curly brackets becomes untractable because you need 2^(n-1) number of curly brackets for each n-time you're formatting. Let's illustrate this through an example

query_data = """
    SELECT {}
    FROM {}
    WHERE {{}}
    AND {{}} > {{{{}}}} 
    OR {{}} != {{{{{{{{}}}}}}}}
    ORDER BY {{}}
    """

first_format = query_data.format("id", "table")
second_format = first_format.format("id != '1'","date", "name", "id")
third_format = second_format.format("2020-01-07")
fourth_format = third_format.format("john")

Should render the following output

 SELECT id
 FROM table    
 WHERE id != '1'   
 AND date > 2020-01-07    
 OR name != john
 ORDER BY id