PostgreSQL and PL/Pythonu

Beside SQL PostgreSQL has support for creating functions in many other so called procedural languages. We just need to install them and they are available within database. Standard distribution includes those four languages PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python. There are other externally maintained languages like Java, Scheme, PHP, Unix shell and so on. If that is not enough it is possible to write procedural language handler, it is open source and consequently fully customizable.
Hosted Python is very interesting option. It is quite powerful, versatile language and code is very concise. If it is executed within PostgreSQL it enjoys very fast communication avoiding overhead of DB calls which external programs suffer. Both Python 2 and Python 3 are supported. If you are using, for example, Ubuntu then postgresql-plpython-9.x is Python 2 and postgresql-plpython3-9.x is Python 3. It needs to be installed, server restarted and language created.

createlang plpythonu [dbname]

If we do not specify db, then it will be created on db to which are we currently connected. That u suffix have meaning that we are dealing with unrestricted/untrusted language. Power is given to us and it is our responsibility to use it to our benefit without causing damage. Once everything is in place we can write Hello World function in Python.

CREATE OR REPLACE FUNCTION lumberjack() RETURNS text AS $BODY$ a = "I'm a lumberjack, and I'm okay. " return a + "I sleep all night and I work all day." $BODY$ LANGUAGE plpythonu

From psql we try it

testpgdb=# select lumberjack(); lumberjack ----------------------------------------------------------------------- I'm a lumberjack, and I'm okay. I sleep all night and I work all day. (1 row)

Or if you prefer GUI, then use pgAdmin III start Query tool and execute it there.
To enjoy full comfort of Python one need to import modules. How do we use modules in PL/Pythonu? It is no different to ordinary Python code:

create or replace function get_response() returns text as $BODY$ import httplib2 h = httplib2.Http(".cache") resp, content = h.request("http://example.org/", "GET") return resp $BODY$ language 'plpythonu'; select get_response();

we should see response. If we see:

ERROR: ImportError: No module named httplib2 CONTEXT: Traceback (most recent call last): PL/Python function "get_response", line 2, in <module> import httplib2 PL/Python function "get_response"

That is sign that httplib2 is not in path, we can place it in path or if we do not want to reload server we can append to function:

from sys import path path.append('/path to httplib2')

There is significant quantity of examples on Internet to help you further. With some knowledge of Python one can do really huge amount of work directly from PostgreSQL without overhead of DB connection. In role of mailer of monthly report PL/Pythonu function beats Java Application Server and even C/C++ application connecting from outside to DB.