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.

Koto

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

Bonacci

How it shall be call from python scripts ... cur.callproc – Koto

I've updated the answer, hopefully this is of some use now. – Bonacci

Thank you Lucas ! It works perfectly. You bless my day ! – Koto

Answer 2

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)  

Galen

A code-only answer is not high quality. While this code may be useful, you can improve it by saying why it works, how it works, when it should be used, and what its limitations are. Please edit your answer to include explanation and link to relevant documentation. – Ebonize