我正在使用Postgres SQL数据库进行Django项目。我已经编写了一个可以在Postgres上完美运行的存储过程。
I am working on a Django Project with a Postgres SQL Database. I have written a stored procedure that runs perfectly on Postgres.
现在,我想从Django 1.5调用该存储过程。.我已经编写了代码,但是提示错误。
Now I want to call that stored procedure from Django 1.5 .. I have written the code but it prompts errors.
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.
由于我使用 results = c.fetchone()来获取out参数。 返回(13,)
As i have used results = c.fetchone() to fetch the out parameters. it returns (13,)
但我只想将 13 作为字符串或整数,我如何只获取值。
but I only want to get 13 as a string or integer, how can I get only the value.
已更新:
使用此
for item in results: message_id = item推荐答案
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
您忘记了右括号,并试图在光标而不是 c ,并且缩进也有问题。您还应该使用callproc()函数
You forgot the closing parens and were trying to call the functions on cursor instead of c and also had an issue with indentation. You should also use the callproc() function as documented here.
正如catavaran所说,您应该阅读有关执行自定义SQL并使用占位符的文档。另外,在Django 1.6+中,事务是自动提交的,因此不需要 c.execute( COMMIT)
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")
更多推荐
从Django调用Postgres SQL存储过程
发布评论