Introduction

pyPgSQL is a Python DB-API 2.0 compliant interface for PostgreSQL databases.DB-API 2.0 (Python Database API Specification v2.0) is a specification for connecting to databases (not only pyPgSQL) from Python that has been developed by the Python DB-SIG. You can download PyPgSQL? from http://pypgsql.sourceforge.net .

Getting Started

To begin, first import the pyPgSQL Python module for connecting to the database product being used.The next step is to create an object that represents a database connection. The information required to open a connection includes a user name and password, data source and some indication of how to find the database server, hostname.Here is an example:

>>> from pyPgSQL import PgSQL
>>> db = PgSQL.connect(database='database_name',
                       user='user',
                       password='password',
                       host='host')
>>> db
<pyPgSQL.PgSQL.Connection instance at 0x401f37ec>

Cursor Objects

Next, you should create a cursor object.A cursor object acts as a handle for a SQL query and it provide an execute() statement that accepts a string containing an SQL statement to be executed. The results are retrieved by calling fetchone/fetchmany/fetchall() , which returns one or more matching rows or None if there are no more rows to retrieve. The fetchone() method always returns a single row, while fetchmany() returns a small number of rows and fetchall() returns all the rows that match.

For example, to list all the users from table tbl_user, do the following:

>>> cr = db.cursor()
>>> cr.execute("select * from tbl_user")
>>> rst = cursor.fetchall()
>>> print rst 
[[1L, 'admin', 'admin123'], [1L, 'akm', 'asd123']]

A row is represented as a list, so the first row returned is:

[1L, 'admin', 'admin123']