Python Programming Language :: calling Postgresql stored procedure


	calling Postgresql stored procedure 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 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 In article <1180516688.022056.70@q69g2000hsb.googlegroups.com>, Alchemist <alextab@gmail.com> wrote: > 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 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? In article <1180685023.802141.168@h2g2000hsg.googlegroups.com>, Alchemist <alextab@gmail.com> wrote: > 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