Module functions and constants

The pgdb module defines a connect() function that allows to connect to a database, some global constants describing the capabilities of the module as well as several exception classes.

connect – Open a PostgreSQL connection

pgdb.connect([dsn][, user][, password][, host][, database][, **kwargs])

Return a new connection to the database

  • dsn (str) – data source name as string

  • user (str) – the database user name

  • password (str) – the database password

  • host (str) – the hostname of the database

  • database – the name of the database

  • kwargs (dict) – other connection parameters


a connection object

Return type



pgdb.OperationalError – error connecting to the database

This function takes parameters specifying how to connect to a PostgreSQL database and returns a Connection object using these parameters. If specified, the dsn parameter must be a string with the format 'host:base:user:passwd:opt'. All of the parts specified in the dsn are optional. You can also specify the parameters individually using keyword arguments, which always take precedence. The host can also contain a port if specified in the format 'host:port'. In the opt part of the dsn you can pass command-line options to the server. You can pass additional connection parameters using the optional kwargs keyword arguments.


con = connect(dsn='myhost:mydb', user='guido', password='234$')

Changed in version 5.0.1: Support for additional parameters passed as kwargs.

get/set/reset_typecast – Control the global typecast functions

PyGreSQL uses typecast functions to cast the raw data coming from the database to Python objects suitable for the particular database type. These functions take a single string argument that represents the data to be casted and must return the casted value.

PyGreSQL provides built-in typecast functions for the common database types, but if you want to change these or add more typecast functions, you can set these up using the following functions.


The following functions are not part of the DB-API 2 standard.


Get the global cast function for the given database type


typ (str) – PostgreSQL type name or type code


the typecast function for the specified type

Return type

function or None

New in version 5.0.

pgdb.set_typecast(typ, cast)

Set a global typecast function for the given database type(s)

  • typ (str or int) – PostgreSQL type name or type code, or list of such

  • cast – the typecast function to be set for the specified type(s)

The typecast function must take one string object as argument and return a Python object into which the PostgreSQL type shall be casted. If the function takes another parameter named connection, then the current database connection will also be passed to the typecast function. This may sometimes be necessary to look up certain database settings.

New in version 5.0.

As of version 5.0.3 you can also use this method to change the typecasting of PostgreSQL array types. You must run set_typecast('anyarray', cast) in order to do this. The cast method must take a string value and a cast function for the base type and return the array converted to a Python object. For instance, run set_typecast('anyarray', lambda v, c: v) to switch off the casting of arrays completely, and always return them encoded as strings.


Reset the typecasts for the specified (or all) type(s) to their defaults


typ (str, list or None) – PostgreSQL type name or type code, or list of such, or None to reset all typecast functions

New in version 5.0.

Note that database connections cache types and their cast functions using connection specific TypeCache objects. You can also get, set and reset typecast functions on the connection level using the methods TypeCache.get_typecast(), TypeCache.set_typecast() and TypeCache.reset_typecast() of the Connection.type_cache. This will not affect other connections or future connections. In order to be sure a global change is picked up by a running connection, you must reopen it or call TypeCache.reset_typecast() on the Connection.type_cache.

Module constants


The string constant '2.0', stating that the module is DB-API 2.0 level compliant.


The integer constant 1, stating that the module itself is thread-safe, but the connections are not thread-safe, and therefore must be protected with a lock if you want to use them from different threads.


The string constant pyformat, stating that parameters should be passed using Python extended format codes, e.g. " ... WHERE name=%(name)s".

Errors raised by this module

The errors that can be raised by the pgdb module are the following:

exception pgdb.Warning

Exception raised for important warnings like data truncations while inserting.

exception pgdb.Error

Exception that is the base class of all other error exceptions. You can use this to catch all errors with one single except statement. Warnings are not considered errors and thus do not use this class as base.

exception pgdb.InterfaceError

Exception raised for errors that are related to the database interface rather than the database itself.

exception pgdb.DatabaseError

Exception raised for errors that are related to the database.

In PyGreSQL, this also has a DatabaseError.sqlstate attribute that contains the SQLSTATE error code of this error.

exception pgdb.DataError

Exception raised for errors that are due to problems with the processed data like division by zero or numeric value out of range.

exception pgdb.OperationalError

Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, or a memory allocation error occurred during processing.

exception pgdb.IntegrityError

Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails.

exception pgdb.ProgrammingError

Exception raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement or wrong number of parameters specified.

exception pgdb.NotSupportedError

Exception raised in case a method or database API was used which is not supported by the database.