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.

Would you be able to add the actual statement you're executing in "cursor.execute(sql_function_above)" (or show where you define 'sql_function_above' if you literally mean to use that identifier). — Jarret Hardie, Nov 14 '09 at 17:18
Show us how you have defined sql_function_above' in your python code. — nos, Nov 14 '09 at 18:44
Sorry, it was a simplification on my part. I tried writing the variable in various different ways. I also tried reading the text in from an sql file. All of these produced the same error. One of the many ways I wrote the variable edited into the original question now. P.S. Thanks for all the replies - it's my first time using stackoverflow.com and the helpfulness and speed of replies from you guys is amazing :) — Monika Sulik, Nov 16 '09 at 12:09

intgr · Accepted Answer · 2012-06-12T08:05:15.657

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.

score 3 · Answer 2 · edited May 23 '17 at 12:08

3

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

edited May 23 '17 at 12:08

Community

1
1

answered Nov 14 '09 at 17:18

Steven Graham

1,220
8
10

Although the tuple error didn't have anything to do with transactions, once the % sign problem was removed, the sql function still wasn't loading and this was naturally the reason, so thank you very much for this answer also :) – Monika Sulik Nov 16 '09 at 12:32
PS: You should use `connection.commit()` instead of executing "COMMIT" manually -- that way psycopg2 knows the current transaction state. – intgr Dec 13 '11 at 14:57

score 1 · Answer 3 · answered Nov 14 '09 at 17:02

1

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

answered Nov 14 '09 at 17:02

John Y

14,123
2
48
72

Yes... that's what confuses me all the more. It sounds like there's a tuple in the psycopg2 library which is the culprit and that would mean there's a bug in the psycopg2 library (or am I jumping to the wrong conclusions?). But if so, what's so special about my SQL that breaks a library which most people find perfectly stable? Part of the problem here is that I can't even isolate where exactly things are going wrong because when I try to execute the SQL outside of Django I get no error message and yet the SQL doesn't execute. – Monika Sulik Nov 14 '09 at 17:25
Your code does include litterals such as $2 and %. Some database libraries might interpret thoe as parameter place holders. In the python case, you probably just havn't commited the transaction. – nos Nov 14 '09 at 18:45

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

3 Answers3