By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,934 Members | 1,692 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,934 IT Pros & Developers. It's quick & easy.

calling Postgresql stored procedure

P: n/a
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

May 30 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
May 30 '07 #2

P: n/a
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
May 31 '07 #3

P: n/a
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?

Jun 1 '07 #4

P: n/a
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
Jun 1 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.