8

I looked at similar questions having this IndexError but didn't find an explanation to my case. Can someone explain why I get the error?

The following code

mySF2[0]=['000browser', '1', 'Floor', '0.92', '1.74', 'con', 'None']

insertfmt = ' '.join([
"INSERT INTO mySchema.myTable_{}_name (col1, col2, col3, col4, col5, col6)",  
"VALUES ({}, {}, NULLIF({},'None')::decimal, NULLIF({},'None')::decimal, {}, NULLIF({},'None')::int)"
         ])

insertfmt.format(mySF2[0])

Gives this error

IndexError: tuple index out of range

However, I count 7 placeholders (i.e. curly brackets {}) and 7 items to input. Why the error then?

martineau
  • 119,623
  • 25
  • 170
  • 301
sc28
  • 1,163
  • 4
  • 26
  • 48
  • 6
    Check out http://stackoverflow.com/questions/7527849/how-to-extract-parameters-from-a-list-and-pass-them-to-a-function-call – SethMMorton Mar 09 '17 at 21:42
  • 2
    `'{}{}'.format([1,2])` doesn't work. You need to do `'{}{}'.format(*[1,2])` to expand the `[1,2]`. This is the same as `'{}{}'.format(1,2)` – Patrick Haugh Mar 09 '17 at 21:47
  • Also using string formatting to construct queries is inherently unsafe - far better to use the proper SQL interface praceholder function (ie. a single `?` character and passing the tuple as an extra parameter to the execute or executemany method. – Tony Suffolk 66 Mar 29 '21 at 10:50

2 Answers2

16

str.format() accepts a variable number of arguments corresponding to the number of "holes" in your format string. In your case, you are passing in a single argument (a list) to .format(), which causes an error because it expects seven arguments.

To pass in an array to a function as separate arguments, you need to use the * operator like so:

insertfmt.format(*mySF2[0])
Richard Xia
  • 664
  • 7
  • 14
  • Thanks, this explanation was clear and the correction worked perfectly. If I may, I have a follow-up question. Is there a way to specify that some of the arguments (e.g. 'Floor') should remain strings, i.e. including the single quotes? In the current form, all arguments are stripped of the quotes, which hinders the SQL syntax. I looked up the doc of .format but didn't find a solution :/ – sc28 Mar 09 '17 at 22:07
  • 2
    To directly answer your question: if you are absolutely sure that your string won't contain any special characters such as an escaped single quote, you can use the `repr()` function to print out the Python representation of the object. E.g. `repr('Floor') == "'Floor'"`. However, I'd like to point out that inserting strings into a format string representing a SQL query like you are is prone to SQL injection attacks. I'd highly advise that you use a library that safely escapes parameters into SQL statements so that malicious inputs cannot execute arbitrary SQL statements. – Richard Xia Mar 09 '17 at 23:00
  • Awesome! Thanks so much. I take note of your warning, though as now it is mainly for personal use it should be fine. More concretely, I solved the issue by placing a few {!r} where needed. – sc28 Mar 09 '17 at 23:37
  • how to expand dicts instead of lists? – MeadowMuffins Dec 01 '17 at 17:49
  • 1
    To expand dicts, use `**` instead of `*`. – Richard Xia Dec 04 '17 at 22:59
-1

Had the same issue while trying to plot with seaborn and annotations. I was using

sns.heatmap(df, annot=True, fmt="{.0%}")

Removing the braces fixed it :

sns.heatmap(df, annot=True, fmt=".0%")
Skippy le Grand Gourou
  • 6,976
  • 4
  • 60
  • 76