CODEWITHSUNDEEP
pythonpython-3.xpostgresqlstored-procedurespsycopg2
Toh
Toh

Reputation: 99

How To Call Postgres 11 Stored Procedure From Python

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.

Upvotes: 7

Views: 13068

Answers (2)

Not_needed
Not_needed

Reputation: 29

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)

Upvotes: 0

Lucas
Lucas

Reputation: 630

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

Upvotes: 13

Related Questions