How To Call Postgres 11 Stored Procedure From Python

Question

I have a stored procedure in Postgres called sales, and it works well from pgadmin:

CALL sales();

However, when I call it from Python:

import psycopg2
conn = psycopg2.connect (host ....)
cur = conn.cursor()
cur.callproc('sales')
conn.commit()

I get the following error message:

psycopg2.ProgrammingError: sales() is a procedure
LINE 1: SELECT * FROM sales()
                 ^   
HINT:  To call a procedure, use CALL.

Answer 1

Assuming your procedure is called sales, you just need to "call" it e.g. CALL sales()

https://www.postgresql.org/docs/11/sql-call.html

I see what you are getting at, the python documentation here is misleading

"Calling a PostgreSQL stored procedure in Python steps" http://www.postgresqltutorial.com/postgresql-python/call-stored-procedures/

Essentially the callproc is currently outdated (written for postgres 10 and below) and still considers procedures to be a function. So unless they update this, you will need to execute your own SQL in this instance like so

cur.execute("CALL sales();")

or if the sales procedure required inputs:

cur.execute("CALL sales(%s, %s);", (val1, val2))