python - Syntax error on an f-string with nested quotation

Question

code:

schema = [{"name": "fb_table_1", "dimension": "department", "metrics": ["headcount"]},
          {"name": "fb_table_2", "dimension": "area", "metrics": ["sale"]},
          {"name": "fb_table_3", "dimension": "product", "metrics": ["quantity", "revenue"]}]

template = f"select date, '{table["dimension"]}', {table['dimension']}, '{metric}', {metric} from {table['name']}"
sql_2 = " union ".join([template for table in schema for metric in table["metrics"]])
print(sql_2)

get following errors:

template = f"select date, '{table["dimension"]}', {table['dimension']}, '{metric}', {metric} from {table['name']}"
                                               ^
SyntaxError: invalid syntax

Is that caused by the nested single/double quotations?

update: when I change to

template = f"select date, '{table['dimension']}', {table['dimension']}, '{metric}', {metric} from {table['name']}"

I get another error:

NameError: name 'table' is not defined

what's the cause of this one?

Answer 1

It might be the nested quotations, in which case you should just replace the double-quotes at the beginning and ending with ''' - this is still a valid string literal in Python.

The other issue is that your f-string depends on variables not yet declared. In this problem, you would perhaps like to encapsulate this in a function that takes table and metric and returns the formatted string.

Consider this implementation -

schema = [{"name": "fb_table_1", "dimension": "department", "metrics": ["headcount"]},
          {"name": "fb_table_2", "dimension": "area", "metrics": ["sale"]},
          {"name": "fb_table_3", "dimension": "product", "metrics": ["quantity", "revenue"]}]

def get_required_string(table, metric):
    template = f'''
    select date, '{table["dimension"]}', {table['dimension']}, '{metric}', {metric} from {table['name']}
    '''
    return template

sql_2 = " union ".join([get_required_string(table, metric) for table in schema for metric in table["metrics"]])
print(sql_2)