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

Question

I'll take the simplest of the SQL functions as an example:

CREATE OR REPLACE FUNCTION skater_name_match(INTEGER,VARCHAR)
RETURNS BOOL AS
$$
    SELECT $1 IN (SELECT skaters_skater.competitor_ptr_id FROM skaters_skater
    WHERE name||' '||surname ILIKE '%'||$2||'%' 
    OR surname||' '||name ILIKE '%'||$2||'%');
$$ LANGUAGE SQL;

If I copy and paste this into psql (PostgreSQL's shell) then it executes without any problems.

If I write a piece of Python code like this (with a real database name and user of course):

import psycopg2

sql_function_above = '''CREATE OR REPLACE FUNCTION skater_name_match(INTEGER,VARCHAR)
RETURNS BOOL AS
$$
    SELECT $1 IN (SELECT skaters_skater.competitor_ptr_id FROM skaters_skater
    WHERE name||' '||surname ILIKE '%'||$2||'%' 
    OR surname||' '||name ILIKE '%'||$2||'%');
$$ LANGUAGE SQL;'''

try:
    connection = psycopg2.connect("dbname='x' user='x' host='localhost' password='x'");
except:
    print "I am unable to connect to the database"

cursor = connection.cursor()
cursor.execute(sql_function_above)

It seems to execute (it doesn't give me an error), but when I look into the database the function is not there.

When I try to execute the code in Django by putting it into an app/sql/model.sql file I get the following error during syncdb:

IndexError: tuple index out of range

When I try to write my own manage.py command that would execute the sql, I get the same error.

What's going on here? Would be very grateful to anyone who could shed some light on this :) I'm still a newbie when it comes to Python and Django, so I may have overlooked something obvious.

Answer 1

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.

Answer 2

Looks like you aren't committing the transaction:

Try putting:

cursor.execute("COMMIT")

After the last line and see if that works.

You can also set the isolation level to autocommit like:

connection.set_isolation_level(0)

More info on that in this answer

Answer 3

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].