Call Postgresql Proocedure using python

Question

Trying to run a postgresql procedure using python but not able to rectify the error..

def call_procedure_without_arguments():
    try:
        connection = create_connection()
        if connection:
            cursor = connection.cursor()

            # Call the procedure without any input arguments
            procname = "call proc_test"
            cursor.callproc(procname)
            results = cursor.fetchall()
            print("Results:", results)

            # Commit the changes (if any)
            connection.commit()

            # Close the cursor and connection
            cursor.close()
            connection.close()

            print("Procedure executed successfully.")
        else:
            print("Connection failed. Unable to call the procedure.")
    except Exception as e:
        print("Error: Unable to call the procedure.", e)


if __name__ == "__main__":
    call_procedure_without_arguments()

The argument to cursor.callproc() should just be the procedure name, it shouldn't have call before it. So procname = 'proc_test' — Barmar
– Barmar, Commented Jul 24, 2023 at 7:32
Does this answer your question? How To Call Postgres 11 Stored Procedure From Python — Danila Ganchar
– Danila Ganchar, Commented Jul 24, 2023 at 9:20
@Barmar, that is wrong callproc() can't be used with procedures only functions. See my answer for doc reference. — Adrian Klaver
– Adrian Klaver, Commented Jul 24, 2023 at 14:05

Adrian Klaver · Accepted Answer · 2023-07-24 14:04:40Z

0

Contrary to the name callproc is not for procedures. It is for functions only:

https://www.psycopg.org/docs/cursor.html#cursor.callproc

Note

callproc() can only be used with PostgreSQL functions, not with the procedures introduced in PostgreSQL 11, which require the CALL statement to run. Please use a normal execute() to run them.

Therefore:

cur.execute('call proc_test')

answered Jul 24, 2023 at 14:04

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

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

Comments

Huzaifa · Accepted Answer · 2023-07-24 09:41:08Z

0

I am expecting that no arguments need to be passed. The documentation for this is different at different resources.

According to Geeks for Geeks

 engine = connector.cursor()
 engine.callproc('your_procedure')
 result = cursor.fetchall()

If does not work try adding parenthesis (), according to Postgresql Tutorial:

conn = psycopg2.connect(dsn)
cur.execute("CALL your_procedure();")
conn.commit();

answered Jul 24, 2023 at 9:41

Huzaifa

4254 silver badges8 bronze badges

Collectives™ on Stack Overflow

Call Postgresql Proocedure using python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related