Почему psycopg2 не выполняет какие-либо мои SQL-функции? (IndexError: индекс tuple выходит за пределы диапазона)

Я возьму простейшую из SQL-функций в качестве примера:

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;

Если я копирую и вставляю это в psql (оболочку PostgreSQL), то он выполняется без каких-либо проблем.

Если я напишу часть кода Python, как это (с реальным именем базы данных и пользователем, конечно):

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)

Кажется, выполняется (это не дает мне ошибку), но когда я смотрю в базу данных, функции там нет.

Когда я пытаюсь выполнить код в Django, поместив его в файл app/sql/model.sql, во время syncdb я получаю следующую ошибку:

IndexError: tuple index out of range

Когда я пытаюсь написать свою собственную команду manage.py, которая будет выполнять sql, я получаю ту же ошибку.

Что здесь происходит? Был бы очень благодарен всем, кто мог бы пролить свет на это:) Я все еще новичок, когда дело доходит до Python и Django, поэтому я, возможно, пропустил что-то очевидное.

Ответ 1

По умолчанию psycopg2 идентифицирует местозаполнители аргументов, используя символ % (обычно у вас есть %s в строке).

Итак, если вы используете cursor.execute('... %s, %s ...', (arg1, arg2)), тогда те %s превращаются в значения arg1 и arg2 соответственно.

Но так как вы вызываете: cursor.execute(sql_function_above), без дополнительных аргументов, а ваш SQL включает в себя % признаки, библиотека пытается найти второй аргумент, переданный в функцию - вне диапазона, следовательно, IndexError.

Решение: Вместо использования % напишите %% в своей переменной SQL. Это преобразуется в литерал % перед отправкой в PostgreSQL.

Ответ 2

Похоже, вы не совершаете транзакцию:

Попробуйте поставить:

cursor.execute("COMMIT")

После последней строки и посмотрите, работает ли это.

Вы также можете установить уровень изоляции для автозапуска, например:

connection.set_isolation_level(0)

Подробнее о том, что в этом ответе

Ответ 3

Индекс вне диапазона означает, что вы пытались получить доступ (например) к третьему элементу кортежа, который имеет только два элемента. Обратите внимание, что индексы Python начинаются с 0, поэтому двухэлементный кортеж с именем myTuple будет иметь элементы myTuple [0] и myTuple [1], но ни один элемент myTuple [2].