CODEWITHSUNDEEP
pythondjangopostgresqlstored-procedures
Muhammad Taqi
Muhammad Taqi

Reputation: 5424

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

Upvotes: 6

Views: 16304

Answers (3)

Muhammad Taqi
Muhammad Taqi

Reputation: 5424

    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

Upvotes: 1

Michał Czapliński
Michał Czapliński

Reputation: 1342

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

Upvotes: 6

catavaran
catavaran

Reputation: 45595

There is a missing closing parenthesis at the c.execute("SELECT fn_save_message3(... line. Add ) after last quote symbol.

But anyway it is a wrong method of executing SQL from python code. You should use placeholders to prevent sql injection attacks. Read the documentation with examples of the proper use of SQL in django.

Upvotes: 0

Related Questions