How to format a formatted string in Python? Either with .format or f-string

I have a string (sql query) in which I want to pass a variable at one point, then pass another variable at another point (list of variables, but just focusing on one for now).

The expected would be something like this:

sql_query = 'SELECT {{field}} FROM {table} WHERE {{field}} IS NULL'.format(table=table_name)

sql_query should now be: 'SELECT {field} FROM table_name WHERE {field} IS NULL' Then format for field

sql_query = sql_query.format(field_name)

In theory I want sql_query to now be: 'SELECT field_name FROM table_name WHERE field_name IS NULL'

But the above ignores the .format and I get: 'SELECT {field} FROM table_name WHERE {field} IS NULL'

I have tried combining f-strings and .format in multiple ways and the closest to my goal is:

field = field_name
sql_query = f'SELECT {field} FROM {{0}} WHERE {field} IS NULL'.format(table_name)

# Works and I get sql_query : 'SELECT field_name FROM table_name WHERE field_name IS NULL'

The above works but it happens all in the same place and separating where each one happens is the true goal of mine.

Solution

sql = "SELECT {{column}} FROM {table}"

sql = sql.format(table="my_table")

print(sql)

sql = sql.format(column="my_column")

print(sql)

Or...

sql = "SELECT {column} FROM {table}"

sql = sql.format(table="my_table", column="{column}")

print(sql)

sql = sql.format(column="my_column")

print(sql)

That said, I'd recommend not actually passing the string around and doing partial replacements on it.

Instead, pass a dictionary around, add the replacements to the dictionary, and call format just once...

Then you don't need to add arbitrary {} around the token you don't want to replace, or be aware that it exists in order to replace it with itself.

sql = "SELECT {column} FROM {table}"

parts = dict()

parts["table"] = "my_table"

# more code here

parts["column"] = "my_column"

print(sql.format(**parts))

NOTE:

As per other warnings here...

NEVER do this with user supplied text.

Literal values should be supplied using parameterisation / prepared statements.

Only ever do this when you are in complete control of the potential values in the dictionary, such as deriving the columns, etc, from ORM meta-data, or some other white list.