Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Python Programming Language

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

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc