The DB wrapper class¶

class pg.DB¶

The Connection methods are wrapped in the class DB which also adds convenient higher level methods for working with the database. It also serves as a context manager for the connection. The preferred way to use this module is as follows:

import pg

with pg.DB(...) as db:  # for parameters, see below
    for r in db.query(  # just for example
            "SELECT foo, bar FROM foo_bar_table WHERE foo !~ bar"
            ).dictresult():
        print('{foo} {bar}'.format(**r))

This class can be subclassed as in this example:

import pg

class DB_ride(pg.DB):
    """Ride database wrapper

    This class encapsulates the database functions and the specific
    methods for the ride database."""

def __init__(self):
    """Open 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.

Initialization¶

The DB class is initialized with the same arguments as the connect() function described above. 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 DB.close() and DB.reopen() methods are inoperative in this case.

pkey – return the primary key of a table¶

DB.pkey(table)¶

Return the primary key of a table

Parameters:: table (str) – name of table
Returns:: Name of the field that is the primary key of the table
Return type:: str
Raises:: KeyError – the table does not have a primary key

This method returns the primary key of a table. Single primary keys are returned as strings unless you set the composite flag. Composite primary keys are always represented as tuples. Note that this raises a KeyError if the table does not have a primary key.

pkeys – return the primary keys of a table¶

DB.pkeys(table)¶

Return the primary keys of a table as a tuple

Parameters:: table (str) – name of table
Returns:: Names of the fields that are the primary keys of the table
Return type:: tuple
Raises:: KeyError – the table does not have a primary key

This method returns the primary keys of a table as a tuple, i.e. single primary keys are also returned as a tuple with one item. Note that this raises a KeyError if the table does not have a primary key.

Added in version 6.0.

get_databases – get list of databases in the system¶

DB.get_databases()¶

Get the list of databases in the system

Returns:: all databases in the system
Return type:: list

Although you can do this with a simple select, it is added here for convenience.

get_relations – get list of relations in connected database¶

DB.get_relations([kinds][, system])¶

Get the list of relations in connected database

Parameters:

kinds (str) – a string or sequence of type letters
system (bool) – whether system relations should be returned

Returns:

all relations of the given kinds in the database

Return type:

list

This method returns the list of relations in the connected database. Although you can do this with a simple select, it is added here for convenience. You can select which kinds of relations you are interested in by passing type letters in the kinds parameter. 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). If system is set to True, then system tables and views (temporary tables, toast tables, catalog views and tables) will be returned as well, otherwise they will be ignored.

get_tables – get list of tables in connected database¶

DB.get_tables([system])¶

Get the list of tables in connected database

Parameters:: system (bool) – whether system tables should be returned
Returns:: all tables in connected database
Return type:: list

This is a shortcut for get_relations('r', system) that has been added for convenience.

get_attnames – get the attribute names of a table¶

DB.get_attnames(table)¶

Get the attribute names of a table

Parameters:: table (str) – name of table
Returns:: an ordered dictionary mapping attribute names to type names

Given the name of a table, digs out the set of attribute names.

Returns a read-only dictionary of attribute names (the names are the keys, the values are the names of the attributes’ types) with the column names in the proper order if you iterate over it.

By default, only a limited number of simple types will be returned. You can get the registered types instead, if enabled by calling the DB.use_regtypes() method.

get_generated – get the generated columns of a table¶

DB.get_generated(table)¶

Get the generated columns of a table

Parameters:: table (str) – name of table
Returns:: an frozenset of column names

Given the name of a table, digs out the set of generated columns.

Added in version 5.2.5.

has_table_privilege – check table privilege¶

DB.has_table_privilege(table, privilege)¶

Check whether current user has specified table privilege

Parameters:

table (str) – the name of the table
privilege (str) – privilege to be checked – default is ‘select’

Returns:

whether current user has specified table privilege

Return type:

bool

Returns True if the current user has the specified privilege for the table.

Added in version 4.0.

get/set_parameter – get or set run-time parameters¶

DB.get_parameter(parameter)¶

Get the value of run-time parameters

Parameters:

parameter – the run-time parameter(s) to get

Returns:

the current value(s) of the run-time parameter(s)

Return type:

str, list or dict

Raises:

TypeError – Invalid parameter type(s)
pg.ProgrammingError – Invalid parameter name(s)

If the parameter is a string, the return value will also be a string that is the current setting of the run-time parameter with that name.

You can get several parameters at once by passing a list, set or dict. When passing a list of parameter names, the return value will be a corresponding list of parameter settings. When passing a set of parameter names, a new dict will be returned, mapping these parameter names to their settings. Finally, if you pass a dict as parameter, its values will be set to the current parameter settings corresponding to its keys.

By passing the special name 'all' as the parameter, you can get a dict of all existing configuration parameters.

Note that you can request most of the important parameters also using Connection.parameter() which does not involve a database query, unlike DB.get_parameter() and DB.set_parameter().

Added in version 4.2.

DB.set_parameter(parameter[, value][, local])¶

Set the value of run-time parameters

Parameters:

parameter – the run-time parameter(s) to set
value – the value to set

Raises:

TypeError – Invalid parameter type(s)
ValueError – Invalid value argument(s)
pg.ProgrammingError – Invalid parameter name(s) or values

If the parameter and the value are strings, the run-time parameter will be set to that value. If no value or None is passed as a value, then the run-time parameter will be restored to its default value.

You can set several parameters at once by passing a list of parameter names, together with a single value that all parameters should be set to or with a corresponding list of values. You can also pass the parameters as a set if you only provide a single value. Finally, you can pass a dict with parameter names as keys. In this case, you should not pass a value, since the values for the parameters will be taken from the dict.

By passing the special name 'all' as the parameter, you can reset all existing settable run-time parameters to their default values.

If you set local to True, then the command takes effect for only the current transaction. After DB.commit() or DB.rollback(), the session-level setting takes effect again. Setting local to True will appear to have no effect if it is executed outside a transaction, since the transaction will end immediately.

Added in version 4.2.

begin/commit/rollback/savepoint/release – transaction handling¶

DB.begin([mode])¶

Begin a transaction

Parameters:: mode (str) – an optional transaction mode such as ‘READ ONLY’

This initiates a transaction block, that is, all following queries will be executed in a single transaction until DB.commit() or DB.rollback() is called.

Added in version 4.1.

DB.start()¶: This is the same as the DB.begin() method.

DB.commit()¶

Commit a transaction

This commits the current transaction.

DB.end()¶: This is the same as the DB.commit() method.

Added in version 4.1.

DB.rollback([name])¶

Roll back a transaction

Parameters:: name (str) – optionally, roll back to the specified savepoint

This rolls back the current transaction, discarding all its changes.

DB.abort()¶: This is the same as the DB.rollback() method.

Added in version 4.2.

DB.savepoint(name)¶

Define a new savepoint

Parameters:: name (str) – the name to give to the new savepoint

This establishes a new savepoint within the current transaction.

Added in version 4.1.

DB.release(name)¶

Destroy a savepoint

Parameters:: name (str) – the name of the savepoint to destroy

This destroys a savepoint previously defined in the current transaction.

Added in version 4.1.

get – get a row from a database table or view¶

DB.get(table, row[, keyname])¶

Get a row from a database table or view

Parameters:

table (str) – name of table or view
row – either a dictionary or the value to be looked up
keyname (str) – name of field to use as key (optional)

Returns:

A dictionary - the keys are the attribute names, the values are the row values.

Raises:

pg.ProgrammingError – table has no primary key or missing privilege
KeyError – missing key value for the row

This method is the basic mechanism to get a single row. It assumes that the keyname specifies a unique row. It must be the name of a single column or a tuple of column names. If keyname is not specified, then the primary key for the table is used.

If row is a dictionary, then the value for the key is taken from it. Otherwise, the row must be a single value or a tuple of values corresponding to the passed keyname or primary key. The fetched row from the table will be returned as a new dictionary or used to replace the existing values if the row was passed as a dictionary.

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(table) using the actual name of the table.

Note that since PyGreSQL 5.0 this will return the value of an array type column as a Python list by default.

insert – insert a row into a database table¶

DB.insert(table[, row][, col=val, ...])¶

Insert a row into a database table

Parameters:

table (str) – name of table
row (dict) – optional dictionary of values
col – optional keyword arguments for updating the dictionary

Returns:

the inserted values in the database

Return type:

dict

Raises:

pg.ProgrammingError – missing privilege or conflict

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 reloaded with the values actually inserted in order to pick up values modified by rules, triggers, etc.

Note that since PyGreSQL 5.0 it is possible to insert a value for an array type column by passing it as a Python list.

update – update a row in a database table¶

DB.update(table[, row][, col=val, ...])¶

Update a row in a database table

Parameters:

table (str) – name of table
row (dict) – optional dictionary of values
col – optional keyword arguments for updating the dictionary

Returns:

the new row in the database

Return type:

dict

Raises:

pg.ProgrammingError – table has no primary key or missing privilege
KeyError – missing key value for the row

Similar to insert, but updates an existing row. The update is based on the primary key of the table or the OID value as munged by DB.get() or passed as keyword. The OID will take precedence if provided, so that it is possible to update the primary key itself.

The dictionary is then modified 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 specified using the 'oid' keyword or in the dictionary, in which case the OID must be munged.

upsert – insert a row with conflict resolution¶

DB.upsert(table[, row][, col=val, ...])¶

Insert a row into a database table with conflict resolution

Parameters:

table (str) – name of table
row (dict) – optional dictionary of values
col – optional keyword arguments for specifying the update

Returns:

the new row in the database

Return type:

dict

Raises:

pg.ProgrammingError – table has no primary key or missing privilege

This method inserts a row into a table, but instead of raising a ProgrammingError exception in case of violating a constraint or unique index, an update will be executed instead. This will be performed as a single atomic operation on the database, so race conditions can be avoided.

Like the insert method, the first parameter is the name of the table and the second parameter can be used to pass the values to be inserted as a dictionary.

Unlike the insert und update statement, keyword parameters are not used to modify the dictionary, but to specify which columns shall be updated in case of a conflict, and in which way:

A value of False or None means the column shall not be updated, a value of True means the column shall be updated with the value that has been proposed for insertion, i.e. has been passed as value in the dictionary. Columns that are not specified by keywords but appear as keys in the dictionary are also updated like in the case keywords had been passed with the value True.

So if in the case of a conflict you want to update every column that has been passed in the dictionary d , you would call upsert(table, d). If you don’t want to do anything in case of a conflict, i.e. leave the existing row as it is, call upsert(table, d, **dict.fromkeys(d)).

If you need more fine-grained control of what gets updated, you can also pass strings in the keyword parameters. These strings will be used as SQL expressions for the update columns. In these expressions you can refer to the value that already exists in the table by writing the table prefix included. before the column name, and you can refer to the value that has been proposed for insertion by writing excluded. as table prefix.

The dictionary is modified in any case to reflect the values in the database after the operation has completed.

Note

The method uses the PostgreSQL “upsert” feature which is only available since PostgreSQL 9.5. With older PostgreSQL versions, you will get a ProgrammingError if you use this method.

Added in version 5.0.

query – execute a SQL command string¶

DB.query(command[, arg1[, arg2, ...]])¶

Execute a SQL command string

Parameters:

command (str) – SQL command
arg* – optional positional arguments

Returns:

result values

Return type:

Query, None

Raises:

TypeError – bad argument type, or too many arguments
TypeError – invalid connection
ValueError – empty SQL query or lost connection
pg.ProgrammingError – error in query
pg.InternalError – error during query processing

Similar to the Connection function with the same name, except that positional arguments can be passed either as a single list or tuple, or as individual positional arguments. These arguments will then be used as parameter values of parameterized queries.

Example:

name = input("Name? ")
phone = input("Phone? ")
num_rows = db.query("update employees set phone=$2 where name=$1",
    name, phone)
# or
num_rows = db.query("update employees set phone=$2 where name=$1",
    (name, phone))

query_formatted – execute a formatted SQL command string¶

DB.query_formatted(command[, parameters][, types][, inline])¶

Execute a formatted SQL command string

Parameters:

command (str) – SQL command
parameters (tuple, list or dict) – the values of the parameters for the SQL command
types (tuple, list or dict) – optionally, the types of the parameters
inline (bool) – whether the parameters should be passed in the SQL

Return type:

Query, None

Raises:

TypeError – bad argument type, or too many arguments
TypeError – invalid connection
ValueError – empty SQL query or lost connection
pg.ProgrammingError – error in query
pg.InternalError – error during query processing

Similar to DB.query(), but using Python format placeholders of the form %s or %(names)s instead of PostgreSQL placeholders of the form $1. The parameters must be passed as a tuple, list or dict. You can also pass a corresponding tuple, list or dict of database types in order to format the parameters properly in case there is ambiguity.

If you set inline to True, the parameters will be sent to the database embedded in the SQL command, otherwise they will be sent separately.

If you set inline to True or don’t pass any parameters, the command string can also include multiple SQL commands (separated by semicolons). You will only get the result for the last command in this case.

Note that the adaptation and conversion of the parameters causes a certain performance overhead. Depending on the type of values, the overhead can be smaller for inline queries or if you pass the types of the parameters, so that they don’t need to be guessed from the values. For best performance, we recommend using a raw DB.query() or DB.query_prepared() if you are executing many of the same operations with different parameters.

Example:

name = input("Name? ")
phone = input("Phone? ")
num_rows = db.query_formatted(
    "update employees set phone=%s where name=%s",
    (phone, name))
# or
num_rows = db.query_formatted(
    "update employees set phone=%(phone)s where name=%(name)s",
    dict(name=name, phone=phone))

Example with specification of types:

db.query_formatted(
    "update orders set info=%s where id=%s",
    ({'customer': 'Joe', 'product': 'beer'}, 'id': 7),
    types=('json', 'int'))
# or
db.query_formatted(
    "update orders set info=%s where id=%s",
    ({'customer': 'Joe', 'product': 'beer'}, 'id': 7),
    types=('json int'))
# or
db.query_formatted(
    "update orders set info=%(info)s where id=%(id)s",
    {'info': {'customer': 'Joe', 'product': 'beer'}, 'id': 7},
    types={'info': 'json', 'id': 'int'})

query_prepared – execute a prepared statement¶

DB.query_prepared(name[, arg1[, arg2, ...]])¶

Execute a prepared statement

Parameters:

name (str) – name of the prepared statement
arg* – optional positional arguments

Returns:

result values

Return type:

Query, None

Raises:

TypeError – bad argument type, or too many arguments
TypeError – invalid connection
ValueError – empty SQL query or lost connection
pg.ProgrammingError – error in query
pg.InternalError – error during query processing
pg.OperationalError – prepared statement does not exist

This methods works like the DB.query() method, except that instead of passing the SQL command, you pass the name of a prepared statement created previously using the DB.prepare() method.

Passing an empty string or None as the name will execute the unnamed statement (see warning about the limited lifetime of the unnamed statement in DB.prepare()).

The functionality of this method is equivalent to that of the SQL EXECUTE command. Note that calling EXECUTE would require parameters to be sent inline, and be properly sanitized (escaped, quoted).

Added in version 5.1.

prepare – create a prepared statement¶

DB.prepare(name, command)¶

Create a prepared statement

Parameters:

command (str) – SQL command
name (str) – name of the prepared statement

Return type:

None

Raises:

TypeError – bad argument types, or wrong number of arguments
TypeError – invalid connection
pg.ProgrammingError – error in query or duplicate query

This method creates a prepared statement with the specified name for later execution of the given command with the DB.query_prepared() method.

If the name is empty or None, the unnamed prepared statement is used, in which case any pre-existing unnamed statement is replaced.

Otherwise, if a prepared statement with the specified name is already defined in the current database session, a pg.ProgrammingError is raised.

The SQL command may optionally contain positional parameters of the form $1, $2, etc instead of literal data. The corresponding values must then be passed to the Connection.query_prepared() method as positional arguments.

The functionality of this method is equivalent to that of the SQL PREPARE command.

Example:

db.prepare('change phone',
    "update employees set phone=$2 where ein=$1")
while True:
    ein = input("Employee ID? ")
    if not ein:
        break
    phone = input("Phone? ")
    db.query_prepared('change phone', ein, phone)

Note

We recommend always using named queries, since unnamed queries have a limited lifetime and can be automatically replaced or destroyed by various operations on the database.

Added in version 5.1.

describe_prepared – describe a prepared statement¶

DB.describe_prepared([name])¶

Describe a prepared statement

Parameters:

name (str) – name of the prepared statement

Return type:

Query

Raises:

TypeError – bad argument type, or too many arguments
TypeError – invalid connection
pg.OperationalError – prepared statement does not exist

This method returns a Query object describing the prepared statement with the given name. You can also pass an empty name in order to describe the unnamed statement. Information on the fields of the corresponding query can be obtained through the Query.listfields(), Query.fieldname() and Query.fieldnum() methods.

Added in version 5.1.

delete_prepared – delete a prepared statement¶

DB.delete_prepared([name])¶

Delete a prepared statement

Parameters:

name (str) – name of the prepared statement

Return type:

None

Raises:

TypeError – bad argument type, or too many arguments
TypeError – invalid connection
pg.OperationalError – prepared statement does not exist

This method deallocates a previously prepared SQL statement with the given name, or deallocates all prepared statements if you do not specify a name. Note that prepared statements are always deallocated automatically when the current session ends.

Added in version 5.1.

clear – clear row values in memory¶

DB.clear(table[, row])¶

Clear row values in memory

Parameters:

table (str) – name of table
row (dict) – optional dictionary of values

Returns:

an empty row

Return type:

dict

This method clears all the attributes to values determined by the types. Numeric types are set to 0, Booleans are set to False, and everything else is set to the empty string. If the row argument is present, it is used as the row dictionary and any entries matching attribute names are cleared with everything else left unchanged.

If the dictionary is not supplied a new one is created.

delete – delete a row from a database table¶

DB.delete(table[, row][, col=val, ...])¶

Delete a row from a database table

Parameters:

table (str) – name of table
d (dict) – optional dictionary of values
col – optional keyword arguments for updating the dictionary

Return type:

None

Raises:

pg.ProgrammingError – table has no primary key, row is still referenced or missing privilege
KeyError – missing key value for the row

This method deletes the row from a table. It deletes based on the primary key of the table or the OID value as munged by DB.get() or passed as keyword. The OID will take precedence if provided.

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).

Note that if the row cannot be deleted because e.g. it is still referenced by another table, this method will raise a ProgrammingError.

truncate – quickly empty database tables¶

DB.truncate(table[, restart][, cascade][, only])¶

Empty a table or set of tables

Parameters:

table (str, list or set) – the name of the table(s)
restart (bool) – whether table sequences should be restarted
cascade (bool) – whether referenced tables should also be truncated
only (bool or list) – whether only parent tables should be truncated

This method quickly removes all rows from the given table or set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.

If restart is set to True, sequences owned by columns of the truncated table(s) are automatically restarted. If cascade is set to True, it also truncates all tables that have foreign-key references to any of the named tables. If the parameter only is not set to True, all the descendant tables (if any) will also be truncated. Optionally, a * can be specified after the table name to explicitly indicate that descendant tables are included. If the parameter table is a list, the parameter only can also be a list of corresponding boolean values.

Added in version 4.2.

get_as_list/dict – read a table as a list or dictionary¶

DB.get_as_list(table[, what][, where][, order][, limit][, offset][, scalar])¶

Get a table as a list

Parameters:

table (str) – the name of the table (the FROM clause)
what (str, list, tuple or None) – column(s) to be returned (the SELECT clause)
where (str, list, tuple or None) – conditions(s) to be fulfilled (the WHERE clause)
order (str, list, tuple, False or None) – column(s) to sort by (the ORDER BY clause)
limit (int) – maximum number of rows returned (the LIMIT clause)
offset (int) – number of rows to be skipped (the OFFSET clause)
scalar (bool) – whether only the first column shall be returned

Returns:

the content of the table as a list

Return type:

list

Raises:

TypeError – the table name has not been specified

This gets a convenient representation of the table as a list of named tuples in Python. You only need to pass the name of the table (or any other SQL expression returning rows). Note that by default this will return the full content of the table which can be huge and overflow your memory. However, you can control the amount of data returned using the other optional parameters.

The parameter what can restrict the query to only return a subset of the table columns. The parameter where can restrict the query to only return a subset of the table rows. The specified SQL expressions all need to be fulfilled for a row to get into the result. The parameter order specifies the ordering of the rows. If no ordering is specified, the result will be ordered by the primary key(s) or all columns if no primary key exists. You can set order to False if you don’t care about the ordering. The parameters limit and offset specify the maximum number of rows returned and a number of rows skipped over.

If you set the scalar option to True, then instead of the named tuples you will get the first items of these tuples. This is useful if the result has only one column anyway.

Added in version 5.0.

DB.get_as_dict(table[, keyname][, what][, where][, order][, limit][, offset][, scalar])¶

Get a table as a dictionary

Parameters:

table (str) – the name of the table (the FROM clause)
keyname (str, list, tuple or None) – column(s) to be used as key(s) of the dictionary
what (str, list, tuple or None) – column(s) to be returned (the SELECT clause)
where (str, list, tuple or None) – conditions(s) to be fulfilled (the WHERE clause)
order (str, list, tuple, False or None) – column(s) to sort by (the ORDER BY clause)
limit (int) – maximum number of rows returned (the LIMIT clause)
offset (int) – number of rows to be skipped (the OFFSET clause)
scalar (bool) – whether only the first column shall be returned

Returns:

the content of the table as a list

Return type:

dict

Raises:

TypeError – the table name has not been specified
KeyError – keyname(s) are invalid or not part of the result
pg.ProgrammingError – no keyname(s) and table has no primary key

This method is similar to DB.get_as_list(), but returns the table as a Python dict instead of a Python list, which can be even more convenient. The primary key column(s) of the table will be used as the keys of the dictionary, while the other column(s) will be the corresponding values. The keys will be named tuples if the table has a composite primary key. The rows will be also named tuples unless the scalar option has been set to True. With the optional parameter keyname you can specify a different set of columns to be used as the keys of the dictionary.

The dictionary will be ordered using the order specified with the order parameter or the key column(s) if not specified. You can set order to False if you don’t care about the ordering.

Added in version 5.0.

escape_literal/identifier/string/bytea – escape for SQL¶

The following methods escape text or binary strings so that they can be inserted directly into an SQL command. Except for DB.escape_bytea(), you don’t need to call these methods for the strings passed as parameters to DB.query(). You also don’t need to call any of these methods when storing data using DB.insert() and similar.

DB.escape_literal(string)¶

Escape a string for use within SQL as a literal constant

Parameters:: string (str) – the string that is to be escaped
Returns:: the escaped string
Return type:: str

This method 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.

Added in version 4.1.

DB.escape_identifier(string)¶

Escape a string for use within SQL as an identifier

Parameters:: string (str) – the string that is to be escaped
Returns:: the escaped string
Return type:: str

This method escapes a string for use as an SQL identifier, such as a table, column, or function name. This is useful when a user-supplied identifier might contain special characters that would otherwise be misinterpreted by the SQL parser, or when the identifier might contain upper case characters whose case should be preserved.

Added in version 4.1.

DB.escape_string(string)¶

Escape a string for use within SQL

Parameters:: string (str) – the string that is to be escaped
Returns:: the escaped string
Return type:: str

Similar to the module function pg.escape_string() with the same name, but the behavior of this method is adjusted depending on the connection properties (such as character encoding).

DB.escape_bytea(datastring)¶

Escape binary data for use within SQL as type bytea

Parameters:: datastring (bytes/str) – the binary data that is to be escaped
Returns:: the escaped string
Return type:: bytes/str

Similar to the module function pg.escape_bytea() 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).

unescape_bytea – unescape data retrieved from the database¶

DB.unescape_bytea(string)¶

Unescape bytea data that has been retrieved as text

Parameters:: string (str) – the bytea string that has been retrieved as text
Returns:: byte string containing the binary data
Return type:: bytes

Converts an escaped string representation of binary data stored as bytea into the raw byte string representing the binary data – this is the reverse of DB.escape_bytea(). Since the Query results will already return unescaped byte strings, you normally don’t have to use this method.

encode/decode_json – encode and decode JSON data¶

The following methods can be used to encode end decode data in JSON format.

DB.encode_json(obj)¶

Encode a Python object for use within SQL as type json or jsonb

Parameters:: obj (dict, list or None) – Python object that shall be encoded to JSON format
Returns:: string representation of the Python object in JSON format
Return type:: str

This method serializes a Python object into a JSON formatted string that can be used within SQL. You don’t need to use this method on the data stored with DB.insert() and similar, only if you store the data directly as part of an SQL command or parameter with DB.query(). This is the same as the json.dumps() function from the standard library.

Added in version 5.0.

DB.decode_json(string)¶

Decode json or jsonb data that has been retrieved as text

Parameters:: string (str) – JSON formatted string shall be decoded into a Python object
Returns:: Python object representing the JSON formatted string
Return type:: dict, list or None

This method deserializes a JSON formatted string retrieved as text from the database to a Python object. You normally don’t need to use this method as JSON data is automatically decoded by PyGreSQL. If you don’t want the data to be decoded, then you can cast json or jsonb columns to text in PostgreSQL or you can set the decoding function to None or a different function using pg.set_jsondecode(). By default this is the same as the json.loads() function from the standard library.

Added in version 5.0.

use_regtypes – choose usage of registered type names¶

DB.use_regtypes([regtypes])¶

Determine whether registered type names shall be used

Parameters:: regtypes (bool) – if passed, set whether registered type names shall be used
Returns:: whether registered type names are used

The DB.get_attnames() method can return either simplified “classic” type names (the default) or more fine-grained “registered” type names. Which kind of type names is used can be changed by calling DB.get_regtypes(). If you pass a boolean, it sets whether registered type names shall be used. The method can also be used to check through its return value whether registered type names are currently used.

Added in version 4.1.

notification_handler – create a notification handler¶

class DB.notification_handler(event, callback[, arg_dict][, timeout][, stop_event])¶

Create a notification handler instance

Parameters:

event (str) – the name of an event to listen for
callback – a callback function
arg_dict (dict) – an optional dictionary for passing arguments
timeout (int, float or None) – the time-out when waiting for notifications
stop_event (str) – an optional different name to be used as stop event

This method creates a pg.NotificationHandler object using the DB connection as explained under The Notification Handler.

Added in version 4.1.1.

Attributes of the DB wrapper class¶

DB.db¶: The wrapped Connection object

You normally don’t need this, since all of the members can be accessed from the DB wrapper class as well.

DB.dbname¶: The name of the database that the connection is using

DB.dbtypes¶: A dictionary with the various type names for the PostgreSQL types

This can be used for getting more information on the PostgreSQL database types or changing the typecast functions used for the connection. See the description of the DbTypes class for details.

Added in version 5.0.

DB.adapter¶: A class with some helper functions for adapting parameters

This can be used for building queries with parameters. You normally will not need this, as you can use the DB.query_formatted method.

Added in version 5.0.

The DB wrapper class¶

Initialization¶

pkey – return the primary key of a table¶

pkeys – return the primary keys of a table¶

get_databases – get list of databases in the system¶

get_relations – get list of relations in connected database¶

get_tables – get list of tables in connected database¶

get_attnames – get the attribute names of a table¶

get_generated – get the generated columns of a table¶

has_table_privilege – check table privilege¶

get/set_parameter – get or set run-time parameters¶

begin/commit/rollback/savepoint/release – transaction handling¶

get – get a row from a database table or view¶

insert – insert a row into a database table¶

update – update a row in a database table¶

upsert – insert a row with conflict resolution¶

query – execute a SQL command string¶

query_formatted – execute a formatted SQL command string¶

query_prepared – execute a prepared statement¶

prepare – create a prepared statement¶

describe_prepared – describe a prepared statement¶

delete_prepared – delete a prepared statement¶

clear – clear row values in memory¶

delete – delete a row from a database table¶

truncate – quickly empty database tables¶

get_as_list/dict – read a table as a list or dictionary¶

escape_literal/identifier/string/bytea – escape for SQL¶

unescape_bytea – unescape data retrieved from the database¶

encode/decode_json – encode and decode JSON data¶

use_regtypes – choose usage of registered type names¶

notification_handler – create a notification handler¶

Attributes of the DB wrapper class¶

PyGreSQL

Navigation

Related Topics