为什么 psycopg2 不执行我的任何 SQL 函数？ （索引错误：元组索引超出范围）
我将以最简单的 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 的 shell）中，那么它执行时不会出现任何问题。
如果我编写一段像这样的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)
它似乎执行（它不会给我一个错误），但是当我查看数据库没有这个功能。
当我尝试将代码放入 app/sql/model.sql 文件中来执行 Django 中的代码时，我在syncdb期间收到以下错误：
IndexError: tuple index out of range
当我尝试编写自己的将执行sql的manage.py命令时，我得到同样的错误。
这是怎么回事？我将非常感谢任何能够阐明这一点的人:)当谈到Python和Django时，我仍然是一个新手，所以我可能忽略了一些明显的东西。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
默认情况下，psycopg2 使用
%符号标识参数占位符（通常字符串中会有%s）。因此，如果您使用
cursor.execute('... %s, %s ...', (arg1, arg2))那么那些%s就会变成分别是 arg1 和 arg2 的值。但由于您调用：
cursor.execute(sql_function_above)，没有额外的参数，并且您的 SQL 包含%标志，因此库正在尝试查找传递到函数中的第二个参数 - - 超出范围，因此出现 IndexError。解决方案：不要使用
%，而是在 SQL 变量中写入%%。在发送到 PostgreSQL 之前，它会被翻译成文字%。By default psycopg2 identifies argument placeholders using the
%symbol (usually you'd have%sin the string).So, if you use
cursor.execute('... %s, %s ...', (arg1, arg2))then those%sget 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.看起来您没有提交事务：
尝试将：
cursor.execute("COMMIT")放在最后一行之后，看看是否有效。
您还可以将隔离级别设置为自动提交，例如：
有关此内容的更多信息 答案
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:
More info on that in this answer
索引超出范围意味着您尝试访问（例如）只有两个元素的元组的第三个元素。请注意，Python 的索引从 0 开始，因此名为 myTuple 的二元素元组将具有元素 myTuple[0] 和 myTuple[1]，但没有元素 myTuple[2]。
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].