skip to Main Content

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

2

Answers


  1. 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();
    
    Login or Signup to reply.
  2. 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')

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search