postgresql 从 Django 调用 Postgres SQL 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27819151/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Call Postgres SQL stored procedure From Django
提问by Muhammad Taqi
I am working on a Django Project with a Postgres SQL Database. I have written a stored procedure that runs perfectly on Postgres.
我正在使用 Postgres SQL 数据库处理 Django 项目。我编写了一个在 Postgres 上完美运行的存储过程。
Now I want to call that stored procedure from Django 1.5 .. I have written the code but it prompts errors.
现在我想从 Django 1.5 调用该存储过程.. 我已经编写了代码，但它提示错误。
CREATE FUNCTION fn_save_message3(IN msg_sub character varying, IN msg_cont text, IN msg_type character varying, IN msg_category character varying, IN msg_created_by character varying, IN msg_updated_by character varying) RETURNS integer AS
$BODY$ DECLARE msg_id integer := 0;
BEGIN
INSERT INTO tbl_messages
(message_subject, message_content, message_type, message_category,
created_on, created_by, updated_on, updated_by)
VALUES
(msg_sub, msg_cont, msg_type, msg_category, LOCALTIMESTAMP,
msg_created_by, LOCALTIMESTAMP, msg_updated_by);
Select into msg_id currval('tbl_messages_message_id_seq');
return msg_id;
END;$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF
COST 100;
ALTER FUNCTION public.fn_save_message(IN character varying, IN text, IN character varying, IN character varying, IN character varying, IN character varying)
OWNER TO gljsxdlvpgfvui;
The Stored Procedure is Ok and it returns results.
存储过程没问题，它返回结果。
c = connection.cursor()
try:
c.execute("BEGIN")
c.callproc("fn_save_message", [Message_Subject, Message_Content, Message_Type, Message_Category, Created_By, updated_by])
results = c.fetchone()
c.execute("COMMIT")
finally:
c.close()
print results
After all your suggestion my program is finally working. but one small issue left.
在您提出所有建议之后，我的程序终于可以运行了。但还剩下一个小问题。
As i have used results = c.fetchone()to fetch the out parameters.
it returns (13,)
正如我曾经用来results = c.fetchone()获取输出参数一样。它返回(13,)
but I only want to get 13as a string or integer, how can I get only the value.
但我只想得到13作为字符串或整数，我怎么能只得到这个值。
Updated:
更新：
the problem is solved using this
使用这个问题解决了
for item in results:
message_id = item
采纳答案by Micha? Czapliński
c = connection.cursor()
try:
c.execute("BEGIN")
c.callproc("fn_save_message3", (Message_Subject, Message_Content, Message_Type, Message_Category, Created_By, Updated_By))
results = c.fetchall()
c.execute("COMMIT")
finally:
c.close()
print results
You forgot the closing parens and were trying to call the functions on cursorinstead of cand also had an issue with indentation. You should also use the callproc()function as documented here.
您忘记了结束括号并试图调用函数cursor而不是c并且还遇到了缩进问题。您还应该使用此处callproc()记录的功能。
As catavaran said, you should read the documentation on executing custom SQL and use placeholders. Also, in Django 1.6+, the transactions are commited automatically so there is no need for c.execute("COMMIT")
正如 catavaran 所说，您应该阅读有关执行自定义 SQL 和使用占位符的文档。此外，在 Django 1.6+ 中，事务是自动提交的，因此不需要c.execute("COMMIT")
回答by Muhammad Taqi
c = connection.cursor()
try:
c.execute("BEGIN")
c.callproc("fn_save_message3", [Message_Subject, Message_Content, Message_Type, Message_Category, Created_By, updated_by])
results = c.fetchone()
c.execute("COMMIT")
finally:
c.close()
for item in results:
message_id = item
回答by catavaran
There is a missing closing parenthesis at the c.execute("SELECT fn_save_message3(...line. Add )after last quote symbol.
该c.execute("SELECT fn_save_message3(...行缺少右括号。)在最后一个引号后添加。
But anyway it is a wrong method of executing SQL from python code. You should use placeholders to prevent sql injection attacks. Read the documentationwith examples of the proper use of SQL in django.
但无论如何，这是从 python 代码执行 SQL 的错误方法。您应该使用占位符来防止 sql 注入攻击。阅读包含在 django 中正确使用 SQL 的示例的文档。
