String formatting with a list of tuples?

Question

I'm trying to use format a string with a list of tuples, which has the following form:

formulas_and_labels = [('formula1', 'label1'),
 ('formula2', 'label2')]

This should be used to format the following string:

SQL_string = """SELECT 
 {} AS "{}", 
 {} AS "{}"
 FROM 
 schema.table""".format(*formulas_and_labels)

I understand that this produces a IndexError: tuple index out of range, because (*formulas_and_labels) only contains two elements (tuples), whereas the string requires 4 elements (4x{}).

I managed to find a workaround by first unpacking the list using

formulas_and_labels_unpacked = [v for t in formulas_and_labels for v in t]

(found here).

However, I was wondering if a more direct way existed to directly "double-unpack" the list and tuples using something like .format(**formulas_and_labels) or .format(*el) for el in formulas_and_labels) ?

• Azat Ibrakov: what is your expected output?
• Bryan Newman: is your final query supposed to be of a hard-coded length of 2 attributes in the SELECT clause?

Answer 1

You just have to be explicit about what sequence item you're referring to, like this:

formulas_and_labels = [('formula1', 'label1'), ('formula2', 'label2')]
SQL_string = """SELECT
 {0[0]} AS "{0[1]}",
 {1[0]} AS "{1[1]}"
 FROM
 schema.table""".format(*formulas_and_labels)
print(SQL_string)

Output:

SELECT
 formula1 AS "label1",
 formula2 AS "label2"
 FROM
 schema.table

This could be generalized to handle a variable number of formulas and labels by creating the needed format string on-the-fly based on how many pairs of values there were in the formulas_and_labels list:

# Generalized
formulas_and_labels = [('formula1', 'label1'),
 ('formula2', 'label2'),
 ('formula3', 'label3')]
SQL_string = """SELECT
 {}
 FROM
 schema.table"""
SELECT_string = '{{{0}[0]}} AS "{{{0}[1]}}"'
selects = ',\n '.join(SELECT_string.format(i)
 for i in range(len(formulas_and_labels)))
print(SQL_string.format(selects.format(*formulas_and_labels)))

Output:

SELECT
 formula1 AS "label1",
 formula2 AS "label2",
 formula3 AS "label3"
 FROM
 schema.table

Answer 2

Instead of saving the formulas and labels as pairs in tuples, you could instead save 'em in dictionaries:

formulas_and_labels = {"formula1" : "1", "label1": "1a",
 "formula2": "2", "label2": "2a"
 }

And then, you could use the .format(**formulas_and_labels) notation you asked about, provided the placeholders are properly named:

SQL_string = """SELECT 
 {formula1} AS "{label1}",
 {formula2} AS "{label2}"
 FROM 
 schema.table"""

Given this:

>>> print(SQL_string.format(**formulas_and_labels))
SELECT 
 1 AS "1a",
 2 AS "2a"
 FROM 
 schema.table