Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Call Postgres SQL stored procedure From Django

I am working on a Django Project with a Postgres SQL Database. I have written a stored procedure that runs perfectly on Postgres.

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.

As i have used results = c.fetchone() to fetch the out parameters. it returns (13,)

but I only want to get 13 as a string or integer, how can I get only the value.

Updated:

the problem is solved using this

for item in results:
        message_id = item
like image 662
Muhammad Taqi Avatar asked Jan 07 '15 12:01

Muhammad Taqi


2 Answers

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 cursor instead of c and also had an issue with indentation. You should also use the callproc() function as documented here.

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")

like image 193
Michał Czapliński Avatar answered Oct 11 '22 19:10

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.fetchone()
        c.execute("COMMIT")
    finally:
        c.close()
    for item in results:
        message_id = item
like image 30
Muhammad Taqi Avatar answered Oct 11 '22 19:10

Muhammad Taqi