Written by D'Arcy J.M. Cain (darcy@druid.net). Based heavily on code written by Pascal Andre andre@chimay.via.ecp.fr. Copyright © 1995, Pascal Andre. Further modifications Copyright © 1997-2000 by D'Arcy J.M. Cain. If you are on NetBSD, look in the packages directory under databases. If it isn't there yet, it should be there shortly. You can also pick up the package files from ftp://ftp.druid.net/pub/distrib/pygresql.pkg.tgz. There is also a package in the FreeBSD ports collection but as I write this it is at version 2.1. I will try to get that updated as well. For Linux installation look at README.linux. If you're on an x86 system that uses RPMs, then you can pick up an RPM at ftp://ftp.druid.net/pub/distrib/pygresql.i386.rpm. Note that if you are using the DB-API module you must also install mxDateTime from http://starship.python.net/~lemburg/mxDateTime.html. Also, check out setup.py for an alternate method of installing the package using Python's Distutils. You have two options. You can compile PyGreSQL as a stand-alone module or you can build it into the Python interpreter. You must first have installed Python and PostgreSQL on your system. The header files and developer's libraries for both Python and PostgreSQL must be installed on your system before you can build PyGreSQL. If you built both Python and PostgreSQL from source, you should be fine. If your system uses some package mechanism (such as RPM or NetBSD packages), then you probably need to install packages such as Python-devel in addition to the Python package. PyGreSQL is implemented as three parts, a C module labeled _pg and two Python wrappers called pg.py and pgdb.py. This changed between 2.1 and 2.2 and again in 3.0. These changes should not affect any existing programs but the installation is slightly different. Download and unpack the PyGreSQL tarball if you haven't already done so. 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 where: [pyInc] = path of the Python include (usually Python.h) [pgInc] = path of the PostgreSQL include (usually postgres.h) [pgLib] = path of the PostgreSQL libraries (usually libpq.so or libpq.a) Some options may be added to this line: -DNO_DEF_VAR - no default variables support -DNO_DIRECT - no direct access methods -DNO_LARGE - no large object support -DNO_SNPRINTF - if running a system with no snprintf call -DNO_PQSOCKET - if running an older PostgreSQL On some systems you may need to include -lcrypt in the list of libraries to make it compile. Define DNO_PQSOCKET if you are using a version of PostgreSQL before 6.4 that does not have the PQsocket function. The other options will be described in the next sections. Test the new module. Something like the following should work. $ python >>> import _pg >>> db = _pg.connect('thilo', 'localhost') >>> db.query("INSERT INTO test VALUES ('ping', 'pong');") 18304 >>> db.query("SELECT * FROM test;") eins | zwei ------+------ ping | pong (1 row) Finally, move the _pg.so, pg.py, and pgdb.py to a directory in your PYTHONPATH. A good place would be /usr/lib/python1.5/site-python if your Python modules are in /usr/lib/python1.5. Find the directory where your Setup file lives (usually ??/Modules) in the Python source hierarchy and copy or symlink the pgmodule.c file there. Add the following line to your Setup file _pg pgmodule.c -I[pgInc] -L[pgLib] -lpq # -lcrypt # needed on some systems where: [pgInc] = path of the PostgreSQL include (usually postgres.h) [pgLib] = path of the PostgreSQL libraries (usually libpq.so or libpq.a) Some options may be added to this line: -DNO_DEF_VAR - no default variables support -DNO_DIRECT - no direct access methods -DNO_LARGE - no large object support -DNO_SNPRINTF - if running a system with no snprintf call -DNO_PQSOCKET - if running an older PostgreSQL Define DNO_PQSOCKET if you are using a version of PostgreSQL before 6.4 that does not have the PQsocket function. The other options will be described in the next sections. If you want a shared module, make sure that the *shared* keyword is uncommented and add the above line below it. You used to need to install your shared modules with make sharedinstall but this no longer seems to be true. Copy pg.py to the lib directory where the rest of your modules are. For example, that's /usr/local/lib/Python on my system. Rebuild Python from the root directory of the Python source hierarchy by running make -f Makefile.pre.in boot make && make install For more details read the documentation at the top of Makefile.pre.in ]]> You may either choose to use the old mature interface provided by the pg module or otherwise the newer pgdb interface compliant with the DB-API 2.0 specification developed by the Python DB-SIG. Here we describe only the older pg API. As long as PyGreSQL does not contain a description of the DB-API you should read about the API at http://www.python.org/topics/database/DatabaseAPI-2.0.html. A tutorial-like introduction to the DB-API can be found at http://www2.linuxjournal.com/lj-issues/issue49/2605.html The pg module defines three objects: pgobject, which handles the connection and all the requests to the database, pglargeobject, which handles all the accesses to PostgreSQL large objects, and pgqueryobject that handles query results. If you want to see a simple example of the use of some of these functions, see http://www.druid.net/rides where I have a link at the bottom to the actual Python code for the page. Some constants are defined in the pg module dictionary. They are intended to be used as a parameters for methods calls. You should refer to the libpq description () for more information about them. These constants are: INV_READ INV_WRITE large objects access modes, used by (pgobject.)locreate and (pglarge.)open. SEEK_SET SEEK_CUR SEEK_END positional flags, used by (pglarge.)seek. version __version__ constants that give the current version pg module defines only a few methods that allow to connect to a database and to define default variables that override the environment variables used by PostgreSQL. These default variables were designed to allow you to handle general connection parameters without heavy code in your programs. You can prompt the user for a value, put it in the default variable, and forget it, without having to modify your environment. The support for default variables can be disabled by setting the -DNO_DEF_VAR option in the Python Setup file. Methods relative to this are specified by te tag [DV]. All variables are set to None at module initialization, specifying that standard environment variables should be used. connect PYGRESQL - Connection Management connect opens a connection to the database server connect(dbname, host, port, opt, tty, user, passwd) dbname Name of connected database (string/None). host Name of the server host (string/None). port Port used by the database server (integer/-1). opt Options for the server (string/None). tty File or tty for optional debug output from backend (string/None). user PostgreSQL user (string/None). passwd Password for user (string/None). pgobject If successful, an object handling a database connection is returned. TypeError Bad argument type, or too many arguments. SyntaxError Duplicate argument definition. pg.error Some error occurred during pg connection definition. (+ all exceptions relative to object allocation) This method opens a connection to a specified database on a given PostgreSQL server. You can use keywords here, as described in the Python tutorial. The names of the keywords are the name of the parameters given in the syntax line. For a precise description of the parameters, please refer to the PostgreSQL user manual. import pg con1 = pg.connect('testdb', 'myhost', 5432, None, None, 'bob', None) con2 = pg.connect(dbname='testdb', host='localhost', user='bob') get_defhost PYGRESQL - Connection Management get_defhost get default host name [DV] get_defhost() none string or None Default host specification SyntaxError Too many arguments. get_defhost() returns the current default host specification, or None if the environment variables should be used. Environment variables will not be looked up. set_defhost PYGRESQL - Connection Management set_defhost set default host name [DV] set_defhost(host) host New default host (string/None). string or None Previous default host specification. TypeError Bad argument type, or too many arguments. set_defhost() sets the default host value for new connections. If None is supplied as parameter, environment variables will be used in future connections. It returns the previous setting for default host. get_defport PYGRESQL - Connection Management get_defport get default port [DV] get_defport() none integer or None Default port specification SyntaxError Too many arguments. get_defport() returns the current default port specification, or None if the environment variables should be used. Environment variables will not be looked up. set_defport PYGRESQL - Connection Management set_defport set default port [DV] set_defport(port) port New default host (integer/-1). integer or None Previous default port specification. TypeError Bad argument type, or too many arguments. set_defport() sets the default port value for new connections. If -1 is supplied as parameter, environment variables will be used in future connections. It returns the previous setting for default port. get_defopt PYGRESQL - Connection Management get_defopt get default options specification [DV] get_defopt() none string or None Default options specification SyntaxError Too many arguments. get_defopt() returns the current default connection options specification, or None if the environment variables should be used. Environment variables will not be looked up. set_defopt PYGRESQL - Connection Management set_defopt set options specification [DV] set_defopt(options) options New default connection options (string/None). string or None Previous default opt specification. TypeError Bad argument type, or too many arguments. set_defopt() sets the default connection options value for new connections. If None is supplied as parameter, environment variables will be used in future connections. It returns the previous setting for default options. get_deftty PYGRESQL - Connection Management get_deftty get default connection debug terminal specification [DV] get_deftty() none string or None Default debug terminal specification SyntaxError Too many arguments. get_deftty() returns the current default debug terminal specification, or None if the environment variables should be used. Environment variables will not be looked up. set_deftty PYGRESQL - Connection Management set_deftty set default debug terminal specification [DV] set_deftty(terminal) terminal New default debug terminal (string/None). string or None Previous default debug terminal specification. TypeError Bad argument type, or too many arguments. set_deftty() sets the default terminal value for new connections. If None is supplied as parameter, environment variables will be used in future connections. It returns the previous setting for default terminal. get_defbase PYGRESQL - Connection Management get_defbase get default database name specification [DV] get_defbase() none string or None Default debug database name specification SyntaxError Too many arguments. get_defbase() returns the current default database name specification, or None if the environment variables should be used. Environment variables will not be looked up. set_defbase PYGRESQL - Connection Management set_defbase set default database name specification [DV] set_defbase(database) database New default database name (string/None). string or None Previous default database name specification. TypeError Bad argument type, or too many arguments. set_defbase() sets the default database name for new connections. If None is supplied as parameter, environment variables will be used in future connections. It returns the previous setting for default database name. This object handles a connection to the PostgreSQL database. It embeds and hides all the parameters that define this connection, leaving just really significant parameters in function calls. Some methods give direct access to the connection socket. They are specified by the tag [DA]. Do not use them unless you really know what you are doing. If you prefer disabling them, set the -DNO_DIRECT option in the Python Setup file. Some other methods give access to large objects. if you want to forbid access to these from the module, set the -DNO_LARGE option in the Python Setup file. These methods are specified by the tag [LO]. Every pgobject defines a set of read-only attributes that describe the connection and its status. These attributes are: host the host name of the server (string) port the port of the server (integer) db the selected database (string) options the connection options (string) tty the connection debug terminal (string) user user name on the database system (string) status the status of the connection (integer: 1 - OK, 0 - BAD) error the last warning/error message from the server (string) query PYGRESQL - Query query executes a SQL command query(command) command SQL command (string). pgqueryobject or None Result values. TypeError Bad argument type, or too many arguments. ValueError Empty SQL query. pg.error Error during query processing, or invalid connection. query() method sends a SQL query to the database. If the query is an insert statement, the return value is the OID of the newly inserted row. If it is otherwise a query that does not return a result (i.e., is not a some kind of SELECT statement), it returns None. Otherwise, it returns a pgqueryobject that can be accessed via the getresult() or dictresult() methods or simply printed. reset PYGRESQL - Reset reset resets the connection reset() none none TypeError Too many (any) arguments. reset() method resets the current database. close PYGRESQL - Close close close the database connection close() none none TypeError Too many (any) arguments. close() method closes the database connection. The connection will be closed in any case when the connection is deleted but this allows you to explicitly close it. It is mainly here to allow the DB-SIG API wrapper to implement a close function. fileno PYGRESQL - Fileno fileno returns the socket used to connect to the database fileno() none socket id The underlying socket id used to connect to the database. TypeError Too many (any) arguments. fileno() method returns the underlying socket id used to connect to the database. This is useful for use in select calls, etc. getnotify PYGRESQL - Getnotify getnotify gets the last notify from the server getnotify() none tuple, None Last notify from server TypeError Too many (any) arguments. pg.error Invalid connection. getnotify() method tries to get a notify from the server (from the SQL statement NOTIFY). If the server returns no notify, the methods returns None. Otherwise, it returns a tuple (couple) (relname, pid), where relname is the name of the notify and pid the process id of the connection that triggered the notify. Remember to do a listen query first otherwise getnotify will always return None. inserttable PYGRESQL - Inserttable inserttable inserts a list into a table inserttable(table, values) table The table name (string). values The list of rows values to insert (list). none TypeError Bad argument type or too many (any) arguments. pg.error Invalid connection. inserttable() method allows to quickly insert large blocks of data in a table: it inserts the whole values list into the given table. The list is a list of tuples/lists that define the values for each inserted row. The rows values may contain string, integer, long or double (real) values. Be very careful: this method does not typecheck the fields according to the table definition; it just look whether or not it knows how to handle such types. putline PYGRESQL - Putline putline writes a line to the server socket [DA] putline(line) line Line to be written (string). none TypeError Bad argument type or too many (any) arguments. pg.error Invalid connection. putline() method allows to directly write a string to the server socket. getline PYGRESQL - Getline getline gets a line from server socket [DA] getline() none string The line read. TypeError Bad argument type or too many (any) arguments. pg.error Invalid connection. getline() method allows to directly read a string from the server socket. endcopy PYGRESQL - Endcopy endcopy synchronizes client and server [DA] endcopy() none none TypeError Bad argument type or too many (any) arguments. pg.error Invalid connection. The use of direct access methods may desynchronize client and server. This method ensure that client and server will be synchronized. locreate PYGRESQL - Locreate locreate creates of large object in the database [LO] locreate(mode) mode Large object create mode. pglarge Object handling the PostgreSQL large object. TypeError Bad argument type or too many arguments. pg.error Invalid connection, or creation error. locreate() method creates a large object in the database. The mode can be defined by OR-ing the constants defined in the pg module (INV_READ and INV_WRITE). getlo PYGRESQL - Getlo getlo builds a large object from given oid [LO] getlo(oid) oid OID of the existing large object (integer). pglarge Object handling the PostgreSQL large object. TypeError Bad argument type or too many arguments. pg.error Invalid connection. getlo() method allows to reuse a formerly created large object through the pglarge interface, providing the user have its oid. loimport PYGRESQL - Loimport loimport imports a file to a PostgreSQL large object [LO] loimport(filename) filename The name of the file to be imported (string). pglarge Object handling the PostgreSQL large object. TypeError Bad argument type or too many arguments. pg.error Invalid connection, or error during file import. loimport() method allows to create large objects in a very simple way. You just give the name of a file containing the data to be use. pg module contains a class called DB. All pgobject methods are included in this class also. A number of additional DB class methods are described below. The preferred way to use this module is as follows (See description of the initialization method below.): import pg db = pg.DB(...) for r in db.query( "SELECT foo,bar FROM foo_bar_table WHERE foo !~ bar" ).dictresult(): print '%(foo)s %(bar)s' % r The following describes the methods and variables of this class. The DB class is initialized with the same arguments as the pg.connect method. It also initializes a few internal variables. The statement db = DB() will open the local database with the name of the user just like pg.connect() does. pkey PYGRESQL - Pkey pkey returns the primary key of a table pkey(table) table name of table. string Name of field which is the primary key of the table. pkey() method returns the primary key of a table. Note that this raises an exception if the table does not have a primary key. get_databases PYGRESQL - Get_databases get_databases get list of databases in the system get_databases() none list List of databases in the system. Although you can do this with a simple select, it is added here for convenience get_tables PYGRESQL - get_tables get_tables get list of tables in connected database get_tables() none list List of tables in connected database. Although you can do this with a simple select, it is added here for convenience get_attnames PYGRESQL - Get_Attnames get_attnames returns the attribute names of a table get_attnames(table) table name of table. dictionary The dictionary's keys are the attribute names, the values are the type names of the attributes. Given the name of a table, digs out the set of attribute names and types. get PYGRESQL - Get get get a tuple from a database table get(table, arg, keyname) table Name of table. arg Either a dictionary or the value to be looked up. keyname Name of field to use as key (optional). dictionary A dictionary mapping attribute names to row values. This method is the basic mechanism to get a single row. It assumes that the key specifies a unique row. If keyname is not specified then the primary key for the table is used. If arg is a dictionary then the value for the key is taken from it and it is modified to include the new values, replacing existing values where necessary. The oid is also put into the dictionary but in order to allow the caller to work with multiple tables, the attribute name is munged to make it unique. It consists of the string oid_ followed by the name of the table. insert PYGRESQL - Insert insert insert a tuple into a database table insert(table, a) table Name of table. a A dictionary of values. integer The OID of the newly inserted row. This method inserts values into the table specified filling in the values from the dictionary. It then reloads the dictionary with the values from the database. This causes the dictionary to be updated with values that are modified by rules, triggers, etc. update PYGRESQL - Update update update a database table update(table, a) table Name of table. a A dictionary of values. integer The OID of the newly updated row. Similar to insert but updates an existing row. The update is based on the OID value as munged by get. The array returned is the one sent modified to reflect any changes caused by the update due to triggers, rules, defaults, etc. clear PYGRESQL - Clear clear clear a database table clear(table, a) table Name of table. a A dictionary of values. dictionary A dictionary with an empty row. This method clears all the attributes to values determined by the types. Numeric types are set to 0, dates are set to 'today' and everything else is set to the empty string. If the array argument is present, it is used as the array and any entries matching attribute names are cleared with everything else left unchanged. delete PYGRESQL - Delete delete deletes the row from a table delete(table, a) table Name of table. a A dictionary of values. none This method deletes the row from a table. It deletes based on the OID as munged as described above. getresult PYGRESQL - Getresult getresult gets the values returned by the query getresult() none list List of tuples. SyntaxError Too many arguments. pg.error Invalid previous result. getresult() method returns the list of the values returned by the query. More information about this result may be accessed using listfields, fieldname and fieldnum methods. dictresult PYGRESQL - Dictresult dictresult like getresult but returns a list of dictionaries dictresult() none list List of dictionaries. SyntaxError Too many arguments. pg.error Invalid previous result. dictresult() method returns the list of the values returned by the query with each tuple returned as a dictionary with the field names used as the dictionary index. listfields PYGRESQL - Listfields listfields lists the fields names of the query result listfields() none list field names SyntaxError Too many arguments. pg.error Invalid query result, or invalid connection. listfields() method returns the list of field names defined for the query result. The fields are in the same order as the result values. fieldname PYGRESQL - Fieldname fieldname field number-name conversion fieldname(i) i field number (integer). string field name. TypeError Bad parameter type, or too many arguments. ValueError Invalid field number. pg.error Invalid query result, or invalid connection. fieldname() method allows to find a field name from its rank number. It can be useful for displaying a result. The fields are in the same order than the result values. fieldnum PYGRESQL - Fieldnum fieldnum field name-number conversion fieldnum(name) name field name (string). integer field number (integer). TypeError Bad parameter type, or too many arguments. ValueError Unknown field name. pg.error Invalid query result, or invalid connection. fieldnum() method returns a field number from its name. It can be used to build a function that converts result list strings to their correct type, using a hardcoded table definition. The number returned is the field rank in the result values list. ntuples PYGRESQL - Ntuples ntuples returns the number of tuples in query object ntuples() none integer The number of tuples in query object. SyntaxError Too many arguments. ntuples() method returns the number of tuples found in a query. This object handles all the request concerning a PostgreSQL large object. It embeds and hides all the recurrent variables (object oid and connection), exactly in the same way pgobjects do, thus only keeping significant parameters in function calls. It keeps a reference to the pgobject used for its creation, sending requests though with its parameters. Any modification but dereferencing the pgobject will thus affect the pglarge object. Dereferencing the initial pgobject is not a problem since Python will not deallocate it before the large object dereference it. All functions return a generic error message on call error, whatever the exact error was. The error attribute of the object allows to get the exact error message. pglarge objects define a read-only set of attributes that allow to get some information about it. These attributes are: oid the oid associated with the object pgcnx the pgobject associated with the object error the last warning/error message of the connection In multithreaded environments, error may be modified by another thread using the same pgobject. Remember these object are shared, not duplicated; you should provide some locking to be able if you want to check this. The oid attribute is very interesting because it allow you reuse the oid later, creating the pglarge object with a pgobject getlo() method call. See also for more information about the PostgreSQL large object interface. open PYGRESQL - Open open opens a large object open(mode) mode open mode definition (integer). none TypeError Bad parameter type, or too many arguments. IOError Already opened object, or open error. pg.error Invalid connection. open() method opens a large object for reading/writing, in the same way than the UNIX open() function. The mode value can be obtained by OR-ing the constants defined in the pg module (INV_READ, INV_WRITE). close PYGRESQL - Close close closes the large object close() none none SyntaxError Too many arguments. IOError Object is not opened, or close error. pg.error Invalid connection. close() method closes previously opened large object, in the same way than the UNIX close() function. read PYGRESQL - Read read reads from the large object read(size) size Maximal size of the buffer to be read (integer). string The read buffer. TypeError Bad parameter type, or too many arguments. IOError Object is not opened, or read error. pg.error Invalid connection or invalid object. read() method allows to read data from the large object, starting at current position. write PYGRESQL - Write write writes to the large object write(string) string Buffer to be written (string). none TypeError Bad parameter type, or too many arguments. IOError Object is not opened, or write error. pg.error Invalid connection or invalid object. write() method allows to write data to the large object, starting at current position. seek PYGRESQL - Seek seek change current position in the large object seek(offset, whence) offset Position offset (integer). whence Positional parameter (integer). integer New current position in the object. TypeError Bad parameter type, or too many arguments. IOError Object is not opened, or seek error. pg.error Invalid connection or invalid object. seek() method allows to move the cursor position in the large object. The whence parameter can be obtained by OR-ing the constants defined in the pg module (SEEK_SET, SEEK_CUR, SEEK_END). tell PYGRESQL - Tell tell returns current position in the large object tell() none integer Current position in the object. SyntaxError Too many arguments. IOError Object is not opened, or seek error. pg.error Invalid connection or invalid object. tell() method allows to get the current position in the large object. unlink PYGRESQL - Unlink unlink deletes the large object unlink() none none SyntaxError Too many arguments. IOError Object is not closed, or unlink error. pg.error Invalid connection or invalid object. unlink() method unlinks (deletes) the large object. size PYGRESQL - Size size gives the large object size size() none integer The large object size. SyntaxError Too many arguments. IOError Object is not opened, or seek/tell error. pg.error Invalid connection or invalid object. size() method allows to get the size of the large object. It was implemented because this function is very useful for a WWW interfaced database. Currently the large object needs to be opened. export PYGRESQL - Export export saves the large object to file export(filename) filename The file to be created. none TypeError Bad argument type, or too many arguments. IOError Object is not closed, or export error. pg.error Invalid connection or invalid object. export() method allows to dump the content of a large object in a very simple way. The exported file is created on the host of the program, not the server host. This section needs to be written. See http://www.python.org/topics/database/DatabaseAPI-2.0.html for a description of the DB-API 2.0.