| PyGreSQL is a Python interface to the PostgreSQL database. It was written by D'Arcy J.M. Cain and was based heavily on code written by Pascal Andre. PyGreSQL is implemented as three parts : C shared module: _pg.so Two Python wrappers: pg.py and pgdb.py Compiling PyGreSQL In the directory containing pgmodule.c , run the following command: cc -fpic -shared -o _pg.so -I[pyInc] --I[pgInc] --L[pgLib] --lpq pgmodule.c Compile-time options include the following: [pyInc] = Python include path ( Python.h ) [pgInc] = PostgreSQL include path ( Postgres.h ) [pgLib] = PostgreSQL libraries path ( libpq.so /libpq.a ) Some of the following keywords can be specified: | -DNO_DEF_VAR | Disable default variables support. | | -DNO_DIRECT | Disable direct access methods . | | -DNO_LARGE | Disable large object support. | | -DNO_PQSOCKET | Older PostgreSQL version. | | -DNO_SNPRINTF | No snprintf call available. | Python Configuration Locate the directory where the dynamic loading packages are located for Python (for example, usr/lib/python/libdynload ). Copy the resulting _pg.so file to this location. Copy the pg.py and pgdb.py files to Python's standard library directory (for example, /usr/local/lib/Python ). The pg.py file uses the traditional interface, whereas the pgdb.py file is compliant with the DB-API 2.0 specification developed by the Python DB-SIG. The remainder of this section describes only the older pg API. You can read about the new DB-SIG API at the following: www.python.org/topics/database/DatabaseAPI-2.0.html Or a tutorial is located at the following: www2.linuxjournal.com/lj-issues/issue49/2605.html PyGreSQL Interfaces The PyGreSQL module provides two separate interfaces to a PostgreSQL database server.Access is provided via one of the two included wrapper modules: Although most of the new development effort is to further define the DBI-compliant interface, the standard PyGreSQL interface is currently more standard. This section will focus on the standard interface, although information on the DBI 2.0 interface can be found at the following: www.python.org/topics/database/DatabaseAPI-2.0.html The standard pg module provides the following attributes: -
connect(dbname, host, port, opt, tty, user, passwd) Description: Opens a PostgreSQL connection. Parameters: | dbname | The name of the connected database. | | host | The name of the server host. | | port | The port used by the database server. | | opt | The connection options. | | tty | The debug terminal. | | user | The PostgreSQL user. | | passwd | The password for the user. | For instance: >>>import pg >>>database=pg.connect(dbname="newriders", host=127.0.0.1) -
get_defhost() Description: Returns the current default host information. -
get_defport() Description: Returns the current default port information. -
get_defopt() Description: Returns the current default connection options. -
get_deftty() Description: Returns the current default debug terminal. -
set_deftty(tty) Parameters: | tty | The new debug terminal | Description: Sets the debug terminal value for new connections. If None is supplied as a parameter, environment variables will be used in future connections. -
get_defbase() Description: Returns the current database name. Sending Queries to a Database Object Once connected to a database, a pgobject is returned. This object embeds specific parameters that define this connection. The following parameters are available through function calls: -
query(command) Parameters: | command | The SQL command string | Description: Sends the specified SQL query (command) to the database. If the query is an insert statement, the return value is the OID of the new row. If it is a query that does not return a result, None is returned. For SELECT statements, a pgqueryobject object is returned that can be accessed via the getresult or dictresult method. For instance: >>>import pg >>>database=pg.connect("newriders") >>>result=database.query("SELECT * FROM authors") -
close Description: Closes the database connection. The connection is automatically closed when the connection is deleted, but this method enables an explicit close to be issued. -
fileno Description: Returns the underlying socket ID used to connect to the database. -
getnotify Description: Receives NOTIFY messages from the server. If the server returns no notify, the methods returns None . Otherwise , it returns a tuple ( relname , pid ), where relname is the name of the notify and pid is the process ID of the connection that triggered the notify. Remember to do a listen query first; otherwise, getnotify will always return None . -
inserttable Description: Allows quick insertion of large blocks of data in a table. The list is a list of tuples/lists that define the values for each inserted row. -
putline Description: Writes a string directly to the connection socket. -
getline Description:This method reads a string directly from the server socket. -
endcopy Description: Ensures that the client and server will be synchronized, in case direct access methods cause communications to get out of sync. Accessing Large Objects from a Database Connection To access large objects via a pg connection to a database, the following functions are used: -
getlo Description: Gets a large object through the object's OID. -
locreate Description: Creates a large object in the database. -
loimport Description: This method enables you to create large objects in a very simple way. You just give the name of a file containing the data to be used. -
open Description: This method opens a large object for reading/writing, in the same way as the UNIX open() function. -
close Description: This method closes a previously opened large object, in the same way as the UNIX close() function. -
read Description: This function enables you to read a large object, starting at the current position. -
write Description: This function enables writing a large object, starting at the current position. -
tell Description: This method gets the current position of the large object. -
seek Description: Moves the position cursor in the large object. -
unlink Description: Deletes a PostgreSQL large object. -
size Description: Returns the size of a large object. Currently, the large object needs to be opened. -
export Description: Dumps the content of a large object on the host of the running Python program, not the server host. Accessing Results from a pgobject Once a query has been issued to the database, if results are returned, they can be accessed in the following ways: -
getresult Description: Returns the list of the values contained in pgqueryobject . More information about this result can be accessed using listfields , fieldname , or fieldnum methods. -
dictresult Description: Returns the list of the values contained in pgqueryobject , returned as a dictionary with the field names used as the key indexes. -
listfields Description: Lists the field names of the previous query result. The fields are in the same order as the result values. -
fieldname(int) Description: Finds a field name from its ordinal sequence number (integer). The fields are in the same order as the result values. -
fieldnum(str) Description: Returns the field number from its name (string). -
ntuples Description: Returns the number of tuples found in a query. -
reset Description: Resets the current database. For example: >>>import pg >>>database=pg.connect("newriders") >>>results=database.query("SELECT * FROM payroll") >>>results.ntuples() 2340 >>>mydict=results.dictresult() The DB Wrapper The preceding functions are wrapped within the pg module. This module also provides a special wrapper named DB . This wrapper streamlines much of the connection and access mechanics needed to interact with the database. The preceding functions are also included in the name space, so it isn't necessary to import both modules. The preferred way to use this module is as follows : >>>import pg >>>db=pg.DB('payroll','localhost') >>>db.query("INSERT INTO checks VALUES ('Erica',200)") >>>db.query("SELECT * FROM checks") Name Amount ------------- Erica 200 The following list describes the methods and variables of this class (these are very similar to the base pg method, with some slight exceptions): -
pkey(table) Description: This method returns the primary key of a table. Note that this raises an exception if the table doesn't have a primary key. -
get_databases Description: Although you can do this with a simple select, it is added here for convenience. -
get_tables Description: Returns a list of tables available in the current database. -
get_attnames Description: Returns a list of attribute names. -
get(table, arg, [keyname]) Parameters: | table | The name of the table. | | arg | Either a dictionary or the value to be looked up. | | keyname | The name of field to use as key (optional). | | Description: Gets a single row. It assumes that the key specifies a unique row; if keyname is not specified, the primary key for the table is used. | -
insert(table, a) Parameters: | table | The name of the table. | | a | A dictionary of values. | | Description: Inserts values into the specified table, using values from the dictionary. Then the dictionary is updated with values modified by rules, triggers, and so on. | -
update(table, a) Parameters: | table | The name of the table. | | a | A dictionary of values. | | Description: Updates an existing row. The update is based on the OID value from get . An array is returned that reflects any changes caused by the update due to triggers, rules, defaults, and so on. | -
clear(table, [a]) Parameters: | table | The name of the table. | | a | A dictionary of values. | | Description: Clears all the fields to clear values, which is determined by the data type. Numeric types are set to 0, dates are set to TODAY , and everything else is set to NULL . If the argument a is present, it is used as the array, and any entries matching attribute names are cleared with everything else left unchanged. | -
delete(table, a) Parameters: | table | The name of the table. | | a | A dictionary of values. | | Description: Deletes the row from a table based on the OID from get . |  |
