calling Postgresql stored procedure

A

Alchemist

I am using Python 2.4 and Postgresql 8.2 database server.

On the database I have created a stored function, example,
CREATE OR REPLACE FUNCTION calculateaverage()

I created a new python script and would like to call my database
stored function.

How can I call a database stored function/procedure in python?

Thanks
 
W

Weinhandl Herbert

Alchemist said:
I am using Python 2.4 and Postgresql 8.2 database server.

On the database I have created a stored function, example,
CREATE OR REPLACE FUNCTION calculateaverage()

I created a new python script and would like to call my database
stored function.

How can I call a database stored function/procedure in python?

with :

SELECT calculateaverage() FROM ... WHERE ... ;

happy pythoning

Herbert
 
N

Nikita the Spider

Alchemist said:
I am using Python 2.4 and Postgresql 8.2 database server.

On the database I have created a stored function, example,
CREATE OR REPLACE FUNCTION calculateaverage()

I created a new python script and would like to call my database
stored function.

How can I call a database stored function/procedure in python?

You need a layer in between Python and Postgres so that they can talk to
one another. If you don't have one, try this one (use version 2, not
version 1.x):
http://www.initd.org/tracker/psycopg

Good luck
 
A

Alchemist

Thanks for your help.

My stored procedure is written in pythonpl. I noticed that SELECT
queries are executed correctly (results are returned to my script)
whereas UPDATE queries are not being performed as the data is not
updated.

I am using a database user with read/write access to the database.

Is there a commit statement in plpython? (e.g. plpy.commit())
Why are UPDATEs failing?
 
N

Nikita the Spider

Alchemist said:
Thanks for your help.

My stored procedure is written in pythonpl. I noticed that SELECT
queries are executed correctly (results are returned to my script)
whereas UPDATE queries are not being performed as the data is not
updated.

Aha! So the problem is not really with how to call Postgres stored
procs, but that you're not getting the results you expect from some
calls.
I am using a database user with read/write access to the database.

Is there a commit statement in plpython? (e.g. plpy.commit())

Did you try that? Did you check the documentation?
Why are UPDATEs failing?

I'm not familiar with plpy but if it is compliant with the Python DBAPI
(PEP 249) specification then, yes, it has a .commit() method and yes,
you must call it after DDL statements.

From the PEP: "Note that if the database supports an auto-commit
feature, this must be initially off."
http://www.python.org/dev/peps/pep-0249/

In short, either turn on autocommit or start calling .commit().
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top