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.
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))
Try this code to call PostgreSQL Stored_Procedure in Python Script :
import pyodbc
import psycopg2
import io
from sqlalchemy import create_engine
from urllib.parse import quote
#define your PostgreSQL connection here:
host="Provide Host Name"
dbname="Provide Database Name"
user="Provide User"
password="Provide Password"
engine=create_engine('postgresql://{}:{}@{}:5432/{}.format(user,quote(password),host,dbname))
conn=engine.raw_connection()
cur=conn.cursor()
#This will be your code to call stored procedure:
cur.execute('''call storedProcedureName()''')
conn.commit() # This is mandatory because we want to commit changes to DB
cur.close()
conn.close()
time.sleep(60) # timeout (optional)
