Call Postgres SQL stored procedure From Django

Question

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

Indentation problem? I'm not convinced this has anything to do with the SQL side at all. Separately, please use placement parameters rather than string concatenation; see bobby-tables.com, en.wikipedia.org/wiki/SQL_injection, initd.org/psycopg/docs/… — Craig Ringer
– Craig Ringer, Commented Jan 7, 2015 at 12:13

Michał Czapliński · Accepted Answer · 2015-01-07 13:18:16Z

6

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

edited Jan 7, 2015 at 13:18

answered Jan 7, 2015 at 13:01

Michał Czapliński

1,3421 gold badge14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Muhammad Taqi Over a year ago

As you said, i have update my code, now the different error arise, see the question now i have updated. it should accept the inputting argument because i no where define at it accept at most 2 arguments.

Michał Czapliński Over a year ago

@MuhammadTaqiHassanBukhari Updated the answer. the new error means that the callproc() method expects 2 arguments instead of 7. initd.org/psycopg/docs/cursor.html#cursor.callproc

Muhammad Taqi · Accepted Answer · 2015-01-09 05:40:52Z

1

    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

answered Jan 9, 2015 at 5:40

Muhammad Taqi

5,4247 gold badges39 silver badges65 bronze badges

Comments

catavaran · Accepted Answer · 2015-01-07 12:28:10Z

0

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.

answered Jan 7, 2015 at 12:28

catavaran

45.7k8 gold badges104 silver badges89 bronze badges

Collectives™ on Stack Overflow

Call Postgres SQL stored procedure From Django

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related