[Fixed]-Why isn't psycopg2 executing any of my SQL functions? (IndexError: tuple index out of range)


By default psycopg2 identifies argument placeholders using the % symbol (usually you’d have %s in the string).

So, if you use cursor.execute('... %s, %s ...', (arg1, arg2)) then those %s get turned into the values of arg1 and arg2 respectively.

But since you call: cursor.execute(sql_function_above), without extra arguments, and your SQL includes % signs the library is trying to find the 2nd argument passed into the function — which is out of range, hence an IndexError.

Solution: Instead of using %, write %% in your SQL variable. This gets translated into a literal % before it’s sent to PostgreSQL.



Looks like you aren’t committing the transaction:

Try putting:


After the last line and see if that works.

You can also set the isolation level to autocommit like:


More info on that in this answer


Index out of range implies you’ve tried to access (for example) the third element of a tuple which only has two elements. Note that Python’s indexes start at 0, so a two-element tuple named myTuple would have elements myTuple[0] and myTuple[1], but no element myTuple[2].

👤John Y

Leave a comment