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)

友情链接