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

is your final query supposed to be of a hard-coded length of 2 attributes in the `SELECT` clause? — Bryan Newman, May 04 '17 at 23:46

martineau · Answer 1 · 2017-05-12T15:59:03.730

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

score 0 · Answer 2 · edited Sep 26 '17 at 21:15

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

String formatting with a list of tuples?

2 Answers2