calling Postgresql stored procedure

[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

[Weinhandl Herbert]

Alchemist schrieb:

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

[Nikita the Spider]

In article <11*********************@q69g2000hsb.googlegroups. com>,
Alchemist <al********@gmail.comwrote:

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

--
Philip
http://NikitaTheSpider.com/
Whole-site HTML validation, link checking and more

[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?

[Nikita the Spider]

In article <11**********************@h2g2000hsg.googlegroups. com>,
Alchemist <al********@gmail.comwrote:

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

--
Philip
http://NikitaTheSpider.com/
Whole-site HTML validation, link checking and more