You may either choose to use the
"classic" PyGreSQL interface
provided by the pg module or else the
DB-API 2.0 compliant interface
provided by the pgdb module.
The following documentation covers only the older pg API.
The pg module handles three types of objects,
- the pgobject, which handles the connection
and all the requests to the database,
- the pglarge object, which handles
all the accesses to PostgreSQL large objects,
- the pgqueryobject that handles query results
and it provides a convenient wrapper class DB for the pgobject.
If you want to see a simple example of the use of some of these functions,
see http://ontario.bikerides.ca where you can find a link at the bottom to the
actual Python code for the page.
The pg module defines a few functions 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 the tag [DV].
All variables are set to None at module initialization, specifying that
standard environment variables should be used.
Syntax:
connect([dbname], [host], [port], [opt], [tty], [user], [passwd])
- Parameters:
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: | connection options (string/None) |
tty: | debug terminal (string/None) |
user: | PostgreSQL user (string/None) |
passwd: | password for user (string/None) |
- Return type:
pgobject: | If successful, the pgobject handling the connection |
- Exceptions raised:
TypeError: | bad argument type, or too many arguments |
SyntaxError: | duplicate argument definition |
pg.InternalError: |
| some error occurred during pg connection definition |
(plus all exceptions relative to object allocation)
- Description:
- This function 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.
Examples:
import pg
con1 = pg.connect('testdb', 'myhost', 5432, None, None, 'bob', None)
con2 = pg.connect(dbname='testdb', host='localhost', user='bob')
Syntax:
get_defhost()
- Parameters:
- None
- Return type:
string, None: | default host specification |
- Exceptions raised:
TypeError: | too many arguments |
- Description:
- This method returns the current default host specification,
or None if the environment variables should be used.
Environment variables won't be looked up.
Syntax:
set_defhost(host)
- Parameters:
host: | new default host (string/None) |
- Return type:
string, None: | previous default host specification |
- Exceptions raised:
TypeError: | bad argument type, or too many arguments |
- Description:
- This methods 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.
Syntax:
get_defport()
- Parameters:
- None
- Return type:
integer, None: | default port specification |
- Exceptions raised:
TypeError: | too many arguments |
- Description:
- This method returns the current default port specification,
or None if the environment variables should be used.
Environment variables won't be looked up.
Syntax:
set_defport(port)
- Parameters:
port: | new default port (integer/-1) |
- Return type:
integer, None: | previous default port specification |
- Description:
- This methods 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.
Syntax:
get_defopt()
- Parameters:
- None
- Return type:
string, None: | default options specification |
- Exceptions raised:
TypeError: | too many arguments |
- Description:
- This method returns the current default connection options specification,
or None if the environment variables should be used. Environment variables
won't be looked up.
Syntax:
set_defopt(options)
- Parameters:
options: | new default connection options (string/None) |
- Return type:
string, None: | previous default options specification |
- Exceptions raised:
TypeError: | bad argument type, or too many arguments |
- Description:
- This methods 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.
Syntax:
get_deftty()
- Parameters:
- None
- Return type:
string, None: | default debug terminal specification |
- Exceptions raised:
TypeError: | too many arguments |
- Description:
- This method returns the current default debug terminal specification, or
None if the environment variables should be used. Environment variables
won't be looked up.
Syntax:
set_deftty(terminal)
- Parameters:
terminal: | new default debug terminal (string/None) |
- Return type:
string, None: | previous default debug terminal specification |
- Exceptions raised:
TypeError: | bad argument type, or too many arguments |
- Description:
- This methods sets the default debug 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.
Syntax:
get_defbase()
- Parameters:
- None
- Return type:
string, None: | default database name specification |
- Exceptions raised:
TypeError: | too many arguments |
- Description:
- This method returns the current default database name specification, or
None if the environment variables should be used. Environment variables
won't be looked up.
Syntax:
set_defbase(base)
- Parameters:
base: | new default base name (string/None) |
- Return type:
string, None: | previous default database name specification |
- Exceptions raised:
TypeError: | bad argument type, or too many arguments |
- Description:
- This method sets the default database name 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.
Syntax:
escape_string(string)
- Parameters:
string: | the string that is to be escaped |
- Return type:
-
- Exceptions raised:
TypeError: | bad argument type, or too many arguments |
- Description:
- This function escapes a string for use within an SQL command.
This is useful when inserting data values as literal constants
in SQL commands. Certain characters (such as quotes and backslashes)
must be escaped to prevent them from being interpreted specially
by the SQL parser. escape_string performs this operation.
Note that there is also a pgobject method with the same name
which takes connection properties into account.
Caution!
It is especially important to do proper escaping when
handling strings that were received from an untrustworthy source.
Otherwise there is a security risk: you are vulnerable to "SQL injection"
attacks wherein unwanted SQL commands are fed to your database.
Example:
name = raw_input("Name? ")
phone = con.query("select phone from employees"
" where name='%s'" % escape_string(name)).getresult()
Syntax:
escape_bytea(datastring)
- Parameters:
datastring: | string containing the binary data that is to be escaped |
- Return type:
-
- Exceptions raised:
TypeError: | bad argument type, or too many arguments |
- Description:
- Escapes binary data for use within an SQL command with the type bytea.
As with escape_string, this is only used when inserting data directly
into an SQL command string.
Note that there is also a pgobject method with the same name
which takes connection properties into account.
Example:
picture = file('garfield.gif', 'rb').read()
con.query("update pictures set img='%s' where name='Garfield'"
% escape_bytea(picture))
Syntax:
unescape_bytea(string)
- Parameters:
datastring: | the bytea data string that has been retrieved as text |
- Return type:
str: | string containing the binary data |
- Exceptions raised:
TypeError: | bad argument type, or too many arguments |
- Description:
- Converts an escaped string representation of binary data into binary
data - the reverse of escape_bytea. This is needed when retrieving
bytea data with the getresult() or dictresult() method.
Example:
picture = unescape_bytea(con.query(
"select img from pictures where name='Garfield'").getresult[0][0])
file('garfield.gif', 'wb').write(picture)
Syntax:
set_decimal(cls)
- Parameters:
cls: | the Python class to be used for PostgreSQL numeric values |
- Description:
- This function can be used to specify the Python class that shall be
used by PyGreSQL to hold PostgreSQL numeric values. The default class
is decimal.Decimal if available, otherwise the float type is used.
Some constants are defined in the module dictionary.
They are intended to be used as parameters for methods calls.
You should refer to the libpq description in the PostgreSQL user manual
for more information about them. These constants are:
version, __version__: |
| constants that give the current version. |
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 |
This object handles a connection to a PostgreSQL database. It embeds and
hides all the parameters that define this connection, thus just leaving really
significant parameters in function calls.
Caution!
Some methods give direct access to the connection socket.
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.
These methods are specified by the tag [DA].
Note
Some other methods give access to large objects
(refer to PostgreSQL user manual for more information about these).
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].
Syntax:
query(command)
- Parameters:
command: | SQL command (string) |
- Return type:
pgqueryobject, None: |
| result values |
- Exceptions raised:
TypeError: | bad argument type, or too many arguments |
ValueError: | empty SQL query or lost connection |
pg.ProgrammingError: |
| error in query |
pg.InternalError': |
| error during query processing |
- Description:
- This method simply sends a SQL query to the database. If the query is an
insert statement that inserted exactly one row into a table that has OIDs, the
return value is the OID of the newly inserted row. If the query is an update
or delete statement, or an insert statement that did not insert exactly one
row in a table with OIDs, then the numer of rows affected is returned as a
string. If it is a statement that returns rows as a result (usually a select
statement, but maybe also an "insert/update ... returning" statement), this
method returns a pgqueryobject that can be accessed via the getresult()
or dictresult() method or simply printed. Otherwise, it returns None.
Syntax:
reset()
- Parameters:
- None
- Return type:
- None
- Exceptions raised:
TypeError: | too many (any) arguments |
- Description:
- This method resets the current database connection.
Syntax:
cancel()
- Parameters:
- None
- Return type:
- None
- Exceptions raised:
TypeError: | too many (any) arguments |
- Description:
- This method requests that the server abandon processing
of the current SQL command.
Syntax:
close()
- Parameters:
- None
- Return type:
- None
- Exceptions raised:
TypeError: | too many (any) arguments |
- Description:
- This 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.
Syntax:
fileno()
- Parameters:
- None
- Exceptions raised:
TypeError: | too many (any) arguments |
- Description:
- This method returns the underlying socket id used to connect
to the database. This is useful for use in select calls, etc.
Syntax:
getnotify()
- Parameters:
- None
- Return type:
tuple, None: | last notify from server |
- Exceptions raised:
TypeError: | too many parameters |
TypeError: | invalid connection |
- Description:
- This methods try 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.
Syntax:
inserttable(table, values)
- Parameters:
table: | the table name (string) |
values: | list of rows values (list) |
- Return type:
- None
- Exceptions raised:
TypeError: | invalid connection, bad argument type, or too many arguments |
MemoryError: | insert buffer could not be allocated |
ValueError: | unsupported values |
- Description:
- This method allow to quickly insert large blocks of data in a table:
It inserts the whole values list into the given table. Internally, it
uses the COPY command of the PostgreSQL database. 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.
Caution!
Be very careful:
This method doesn't typecheck the fields according to the table definition;
it just look whether or not it knows how to handle such types.
Syntax:
putline(line)
- Parameters:
line: | line to be written (string) |
- Return type:
- None
- Exceptions raised:
TypeError: | invalid connection, bad parameter type, or too many parameters |
- Description:
- This method allows to directly write a string to the server socket.
Syntax:
getline()
- Parameters:
- None
- Return type:
-
- Exceptions raised:
TypeError: | invalid connection |
TypeError: | too many parameters |
MemoryError: | buffer overflow |
- Description:
- This method allows to directly read a string from the server socket.
Syntax:
endcopy()
- Parameters:
- None
- Return type:
- None
- Exceptions raised:
TypeError: | invalid connection |
TypeError: | too many parameters |
- Description:
- The use of direct access methods may desynchonize client and server.
This method ensure that client and server will be synchronized.
Syntax:
locreate(mode)
- Parameters:
mode: | large object create mode |
- Return type:
pglarge: | object handling the PostGreSQL large object |
Exceptions raised:
TypeError: | invalid connection, bad parameter type, or too many parameters |
pg.OperationalError: |
| creation error |
- Description:
- This 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, INV_WRITE and
INV_ARCHIVE). Please refer to PostgreSQL user manual for a description of
the mode values.
Syntax:
getlo(oid)
- Parameters:
oid: | OID of the existing large object (integer) |
- Return type:
pglarge: | object handling the PostGreSQL large object |
- Exceptions raised:
TypeError: | invalid connection, bad parameter type, or too many parameters |
ValueError: | bad OID value (0 is invalid_oid) |
- Description:
- This method allows to reuse a formerly created large object through the
pglarge interface, providing the user have its OID.
Syntax:
loimport(name)
- Parameters:
name: | the name of the file to be imported (string) |
- Return type:
pglarge: | object handling the PostGreSQL large object |
- Exceptions raised:
TypeError: | invalid connection, bad argument type, or too many arguments |
pg.OperationalError: |
| error during file import |
- Description:
- This methods allows to create large objects in a very simple way. You just
give the name of a file containing the data to be use.
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) |
protocol_version: |
| the frontend/backend protocol being used (integer) |
server_version: | the backend version (integer, e.g. 80305 for 8.3.5) |
status: | the status of the connection (integer: 1 - OK, 0 - bad) |
error: | the last warning/error message from the server (string) |
The pgobject methods are wrapped in the class DB.
The preferred way to use this module is as follows:
import pg
db = pg.DB(...) # see below
for r in db.query( # just for example
"""SELECT foo,bar
FROM foo_bar_table
WHERE foo !~ bar"""
).dictresult():
print '%(foo)s %(bar)s' % r
This class can be subclassed as in this example:
import pg
class DB_ride(pg.DB):
"""This class encapsulates the database functions and the specific
methods for the ride database."""
def __init__(self):
"""Opens a database connection to the rides database"""
pg.DB.__init__(self, dbname = 'ride')
self.query("""SET DATESTYLE TO 'ISO'""")
[Add or override methods here]
The following describes the methods and variables of this class.
The DB class is initialized with the same arguments as the connect
function described in section 2. It also initializes a few
internal variables. The statement db = DB() will open the
local database with the name of the user just like connect() does.
You can also initialize the DB class with an existing _pg or pgdb
connection. Pass this connection as a single unnamed parameter, or as a
single parameter named db. This allows you to use all of the methods
of the DB class with a DB-API 2 compliant connection. Note that the
close() and reopen() methods are inoperative in this case.
Syntax:
pkey(table)
- Parameters:
-
- Return type:
string: | Name of the field which is the primary key of the table |
- Description:
- This method returns the primary key of a table. For composite primary
keys, the return value will be a frozenset. Note that this raises an
exception if the table does not have a primary key.
Syntax:
get_databases()
- Parameters:
- None
- Return type:
list: | all databases in the system |
- Description:
- Although you can do this with a simple select, it is added here for
convenience.
Syntax:
get_relations(kinds)
- Parameters:
kinds: | a string or sequence of type letters |
- Description:
- The type letters are r = ordinary table, i = index, S = sequence,
v = view, c = composite type, s = special, t = TOAST table.
If kinds is None or an empty string, all relations are returned (this is
also the default). Although you can do this with a simple select, it is
added here for convenience.
Syntax:
get_tables()
- Parameters:
- None
- Returns:
list: | all tables in connected database |
- Description:
- Although you can do this with a simple select, it is added here for
convenience.
Syntax:
get_attnames(table)
- Parameters:
-
- Returns:
dictionary: | The keys are the attribute names,
the values are the type names of the attributes. |
- Description:
- Given the name of a table, digs out the set of attribute names.
Syntax:
has_table_privilege(table, privilege)
- Parameters:
table: | name of table |
privilege: | privilege to be checked - default is 'select' |
- Description:
- Returns True if the current user has the specified privilege for the table.
Syntax:
get(table, arg, [keyname])
- Parameters:
table: | name of table or view |
arg: | either a dictionary or the value to be looked up |
keyname: | name of field to use as key (optional) |
- Return type:
dictionary: | The keys are the attribute names,
the values are the row values. |
- Description:
- 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.
For a composite key, keyname can also be a sequence of key names.
The OID is also put into the dictionary if the table has one, but in
order to allow the caller to work with multiple tables, it is munged
as oid(schema.table).
Syntax:
insert(table, [d,] [return_changes,] [key = val, ...])
- Parameters:
table: | name of table |
d: | optional dictionary of values |
return_changes: | Return values in new row - default True |
- Return type:
dictionary: | The dictionary of values inserted |
- Description:
This method inserts a row into a table. If the optional dictionary is
not supplied then the required values must be included as keyword/value
pairs. If a dictionary is supplied then any keywords provided will be
added to or replace the entry in the dictionary.
The dictionary is then, if possible, reloaded with the values actually
inserted in order to pick up values modified by rules, triggers, etc.
Due to the way that this function works in PostgreSQL versions below
8.2, you may find inserts taking longer and longer as your table gets
bigger. If this happens and it is a table with OID but no primary key
you can overcome this problem by simply adding an index onto the OID of
any table that you think may get large over time. You may also consider
using the inserttable() method described in section 3.
Note: With PostgreSQL versions before 8.2 the table being inserted to
must have a primary key or an OID to use this method properly. If not
then the dictionary will not be filled in as described. Also, if this
method is called within a transaction, the transaction will abort.
Note: The method currently doesn't support insert into views
although PostgreSQL does.
Syntax:
update(table, [d,] [key = val, ...])
- Parameters:
table: | name of table |
d: | optional dictionary of values |
- Return type:
-
- Description:
Similar to insert but updates an existing row. The update is based on the
OID value as munged by get or passed as keyword, or on the primary key of
the table. The dictionary is modified, if possible, to reflect any changes
caused by the update due to triggers, rules, default values, etc.
Like insert, the dictionary is optional and updates will be performed
on the fields in the keywords. There must be an OID or primary key
either in the dictionary where the OID must be munged, or in the keywords
where it can be simply the string "oid".
Syntax:
clear(table, [a])
- Parameters:
table: | name of table |
a: | optional dictionary of values |
- Return type:
-
- Description:
This method clears all the attributes to values determined by the types.
Numeric types are set to 0, Booleans are set to 'f', dates are set
to 'now()' 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.
If the dictionary is not supplied a new one is created.
Syntax:
delete(table, [d,] [key = val, ...])
- Parameters:
table: | name of table |
d: | optional dictionary of values |
- Returns:
- None
- Description:
- This method deletes the row from a table. It deletes based on the OID value
as munged by get or passed as keyword, or on the primary key of the table.
The return value is the number of deleted rows (i.e. 0 if the row did not
exist and 1 if the row was deleted).
Syntax:
escape_string(string)
- Parameters:
string: | the string that is to be escaped |
- Return type:
-
- Description:
- Similar to the module function with the same name, but the
behavior of this method is adjusted depending on the connection properties
(such as character encoding).
Syntax:
escape_bytea(datastring)
- Parameters:
datastring: | string containing the binary data that is to be escaped |
- Return type:
-
- Description:
- Similar to the module function with the same name, but the
behavior of this method is adjusted depending on the connection properties
(in particular, whether standard-conforming strings are enabled).
Syntax:
unescape_bytea(string)
- Parameters:
datastring: | the bytea data string that has been retrieved as text |
- Return type:
str: | string containing the binary data |
- Description:
- See the module function with the same name.
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 won't
deallocate it before the pglarge object dereference it.
All functions return a generic error message on call error, whatever the
exact error was. The error attribute of the object allow to get the exact
error message.
See also the PostgreSQL programmer's guide for more information about the
large object interface.
Syntax:
open(mode)
- Parameters:
mode: | open mode definition (integer) |
- Return type:
- None
- Exceptions raised:
TypeError: | invalid connection, bad parameter type, or too many parameters |
IOError: | already opened object, or open error |
- Description:
- This 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 pgmodule (INV_READ, INV_WRITE).
Syntax:
close()
- Parameters:
- None
- Return type:
- None
- Exceptions raised:
TypeError: | invalid connection |
TypeError: | too many parameters |
IOError: | object is not opened, or close error |
- Description:
- This method closes a previously opened large object, in the same way than
the Unix close() function.
Syntax:
read(size)
- Parameters:
size: | maximal size of the buffer to be read |
- Return type:
sized string: | the read buffer |
- Exceptions raised:
TypeError: | invalid connection, invalid object,
bad parameter type, or too many parameters |
ValueError: | if size is negative |
IOError: | object is not opened, or read error |
- Description:
- This function allows to read data from a large object, starting at current
position.
Syntax:
write(string)
- Parameters:
- (sized) string - buffer to be written
- Return type:
- None
- Exceptions raised:
TypeError: | invalid connection, bad parameter type, or too many parameters |
IOError: | object is not opened, or write error |
- Description:
- This function allows to write data to a large object, starting at current
position.
Syntax:
seek(offset, whence)
- Parameters:
offset: | position offset |
whence: | positional parameter |
- Return type:
integer: | new position in object |
- Exceptions raised:
TypeError: | binvalid connection or invalid object,
bad parameter type, or too many parameters |
IOError: | object is not opened, or seek error |
- Description:
- This method allows to move the position cursor 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).
Syntax:
tell()
- Parameters:
- None
- Return type:
integer: | current position in large object |
- Exceptions raised:
TypeError: | invalid connection or invalid object |
TypeError: | too many parameters |
IOError: | object is not opened, or seek error |
- Description:
- This method allows to get the current position in the large object.
Syntax:
unlink()
- Parameter:
- None
- Return type:
- None
- Exceptions raised:
TypeError: | invalid connection or invalid object |
TypeError: | too many parameters |
IOError: | object is not closed, or unlink error |
- Description:
- This methods unlinks (deletes) the PostgreSQL large object.
Syntax:
size()
- Parameters:
- None
- Return type:
integer: | the large object size |
- Exceptions raised:
TypeError: | invalid connection or invalid object |
TypeError: | too many parameters |
IOError: | object is not opened, or seek/tell error |
- Description:
- This (composite) method allows to get the size of a large object. It was
implemented because this function is very useful for a web interfaced
database. Currently, the large object needs to be opened first.
Syntax:
export(name)
- Parameters:
-
- Return type:
- None
- Exceptions raised:
TypeError: | invalid connection or invalid object,
bad parameter type, or too many parameters |
IOError: | object is not closed, or export error |
- Description:
- This methods 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.
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 |
Caution!
Be careful:
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.