comdb2.cdb2 Thin, pythonic wrapper over cdb2api

This module provides a thin, pythonic wrapper over cdb2api.

Overview

Basic Usage

The main class used for interacting with a Comdb2 is Handle. A basic usage example looks like this:

from comdb2 import cdb2
hndl = cdb2.Handle('mattdb')
for row in hndl.execute("select 1, 'a' union all select 2, 'b'"):
    print(row)

Which would result in the following output:

[1, 'a']
[2, 'b']

Graceful Teardown and Error Handling

Non-trivial applications should guarantee that the handle is closed when it is no longer needed, preferably by using contextlib.closing. They should also be prepared to handle any errors returned by the database. Failures that are encountered when connecting to or querying the database are raised as instances of the Error class. So, a more thorough version of the example above would be:

from comdb2 import cdb2
import contextlib
try:
    with contextlib.closing(cdb2.Handle('mattdb')) as hndl:
        for row in hndl.execute("select 1, 'a' union all select 2, 'b'"):
            print(row)
except cdb2.Error as exc:
    print("Comdb2 exception encountered: %s" % exc)

In this example, contextlib.closing is used to guarantee that Handle.close is called at the end of the with block, and an exception handler been added for exceptions of type Error.

Controlling the Type Used For Result Rows

As you can see, rows are returned as a list of column values in positional order. If you’d prefer to get the columns back as some other type, you can set Handle.row_factory to one of the factories provided by comdb2.factories - for example:

from comdb2 import cdb2
from comdb2 import factories
hndl = cdb2.Handle('mattdb')
hndl.row_factory = factories.dict_row_factory
for row in hndl.execute("select 1 as 'x', 2 as 'y' union all select 3, 4"):
    print(row)

This program will return each row as a dict rather than a list:

{'y': 2, 'x': 1}
{'y': 4, 'x': 3}

Parameter Binding

In real applications you’ll often need to pass parameters into a SQL query. This is done using parameter binding, either by name or by position.

By Name

In the query, placeholders are specified using @name or :name, and a mapping of names to values is passed to Handle.execute along with the query. For example:

>>> query = "select 25 between @a and @b"
>>> print(list(hndl.execute(query, {'a': 20, 'b': 42})))
[[1]]
>>> params = {'a': 20, 'b': 23}
>>> print(list(hndl.execute(query, params)))
[[0]]

In this example, we run the query with two different sets of parameters, producing different results. First, we execute the query with @a bound to 20 and @b bound to 42. In this case, because 20 <= 25 <= 42, the expression evaluates to true, and a single row with a single column set to 1 is returned.

When we run the same query with @b bound to 23, a row with a column set to 0 is returned instead, because 20 <= 25 <= 23 is false.

Note

In both of these examples we make use of the list constructor to turn the iterable returned by Handle.execute into a list of result rows.

By Position

You can bind parameters positionally rather than by name, by using ? for each placeholder and providing a list or tuple of parameter values. For example:

>>> query = "select 25 between ? and ?"
>>> print(list(hndl.execute(query, [20, 42])))
[[1]]

Here, we execute the query with the first ? bound to 20 and the second ? bound to 42, so a row with a single column set to 1 is returned like in the previous example.

Types

For all Comdb2 types, the same Python type is used for binding a parameter value as is returned for a SQL query result column of that type. In brief, SQL types are mapped to Python types according to the following table:

SQL type	Python type
NULL	None
integer	int
real	float
blob	bytes
text	str
datetime	datetime.datetime
datetimeus	DatetimeUs

See Comdb2 to Python Type Mappings for a thorough explanation of these type mappings and their implications.

Note

This module uses byte strings to represent BLOB columns, and Unicode strings to represent TEXT columns. This is a very common source of problems for new users. Make sure to carefully read String and Blob Types on that page.

API Documentation

Handles

The user interacts with the database using Handle objects.

class comdb2.cdb2.Handle(database_name, tier='default', flags=0, tz='UTC', host=None)

Represents a connection to a database.

By default, the connection will be made to the cluster configured as the machine-wide default for the given database. This is almost always what you want. If you need to connect to a database that’s running on your local machine rather than a cluster, you can pass “local” as the tier. It’s also permitted to specify “dev”, “alpha”, “beta”, or “prod” as the tier, which will connect you directly to the tier you specify (firewall permitting).

Alternately, you can pass a machine name as the host argument, to connect directly to an instance of the given database on that host, rather than on a cluster or the local machine.

By default, the connection will use UTC as its timezone. This differs from cdb2api’s default behavior, where the timezone used by the query differs depending on the machine that it is run from. If for some reason you need to have that machine-specific default timezone instead, you can pass None for the tz argument. Any other valid timezone name may also be used instead of ‘UTC’.

Note that Python does not guarantee that object finalizers will be called when the interpreter exits, so to ensure that the handle is cleanly released you should call the close method when you’re done with it. You can use contextlib.closing to guarantee the handle is released when a block completes.

Parameters:

• database_name (str) – The name of the database to connect to.

• tier (str) – The cluster to connect to.

• host (str) – Alternately, a single remote host to connect to.

• flags (int) – A flags value passed directly through to cdb2_open.

• tz (str) – The timezone to be used by the new connection, or None to use a machine-specific default.

__iter__()

Iterate over all remaining rows in the current result set.

By default each row is returned as a list, where the elements in the list correspond to the result row’s columns in positional order, but this can be changed with the row_factory property.

Example

>>> hndl.execute("select 1, 2 UNION ALL select 3, 4")
>>> for row in hndl:
...     print(row)
[1, 2]
[3, 4]

close(ack_current_event=True)

Gracefully close the Comdb2 connection.

Once a Handle has been closed, no further operations may be performed on it.

If the handle was used to consume events from a Lua consumer, then ack_current_event tells the database what to do with the last event that was delivered. By default it will be marked as consumed and won’t be redelivered, but if ack_current_event=False then the event will be redelivered to another consumer for processing.

If a socket pool is running on the machine and the connection was in a clean state, this will turn over the connection to the socket pool. This cannot be done if the connection is in a transaction, or in the middle of retrieving a result set. Other restrictions may apply as well.

You can ensure that this gets called at the end of a block using something like:

>>> with contextlib.closing(Handle('mattdb')) as hndl:
>>>     for row in hndl.execute("select 1"):
>>>         print(row)
[1]

column_names()

Returns the names of the columns of the current result set.

Returns:

A list of unicode strings, one per column in the result set.

column_types()

Returns the type codes of the columns of the current result set.

Returns:

A list of integers, one per column in the result set. Each generally corresponds to one of the types in the TYPE global object exposed by this module.

Return type:

List[int]

execute(sql, parameters=None, *, column_types=None)

Execute a database operation (query or command).

The sql string may have placeholders for parameters to be passed. This should always be the preferred method of parameterizing the SQL query, as it prevents SQL injection vulnerabilities and is faster. Placeholders for named parameters must be in one of Comdb2’s native formats, either @param_name or :param_name. Alternatively, you can use ? for each placeholder to bind parameters positionally instead of by name.

If column_types is provided and non-empty, it must be a sequence of members of the ColumnType enumeration. The database will coerce the data in the Nth column of the result set to the Nth given column type. An error will be raised if the number of elements in column_types doesn’t match the number of columns in the result set, or if one of the elements is not a supported column type, or if coercion fails. If column_types is empty or not provided, no coercion is performed.

Parameters:

• sql (str) – The SQL string to execute.

• parameters (Mapping[str, Any] | Sequence[Any]) – If the SQL statement has @param_name or :param_name style placeholders, you must pass a mapping from parameter name to value. If the SQL statement has ? style placeholders, you must instead pass an ordered sequence of parameter values.

• column_types (Sequence[int]) – An optional sequence of types (values of the ColumnType enumeration) which the columns of the result set will be coerced to.

Returns:

This method returns the Handle that it was called on, which can be used as an iterator over the result set returned by the query. Iterating over it will yield one list per row in the result set, where the elements in the list correspond to the result columns within the row, in positional order.

The row_factory property can be used to return rows as a different type, instead.

Return type:

Handle

Example

>>> for row in hndl.execute("select 1, 2 UNION ALL select @x, @y",
...                         {"x": 2, "y": 4}):
...     print(row)
[1, 2]
[2, 4]

>>> for row in hndl.execute("select 1, 2 UNION ALL select ?, ?", [2, 4]):
...     print(row)
[1, 2]
[2, 4]

get_effects()

Return counts of rows affected by executed queries.

Within a transaction, these counts are a running total from the start of the transaction up through the last executed SQL statement. Outside of a transaction, these counts represent the rows affected by only the last executed SQL statement.

Warning

Calling this method consumes all remaining rows in the current result set.

Note

The results within a transaction are not necessarily reliable unless the VERIFYRETRY setting is turned off. All of the caveats of the cdb2_get_effects call apply.

Returns:

An count of rows that have been affected, selected, updated, deleted, or inserted.

Return type:

Effects

property row_factory

Factory used when constructing result rows.

By default, or when set to None, each row is returned as a list of column values. If you’d prefer to receive rows as a dict or as a collections.namedtuple, you can set this property to one of the factories provided by the comdb2.factories module.

Example

>>> from comdb2.factories import dict_row_factory
>>> hndl.row_factory = dict_row_factory
>>> for row in hndl.execute("SELECT 1 as 'foo', 2 as 'bar'"):
...     print(row)
{'foo': 1, 'bar': 2}

Added in version 0.9.

Exceptions

exception comdb2.cdb2.Error(error_code, error_message)

Exception type raised for all failed operations.

error_code

The error code from the failed cdb2api call.

Type:

int

error_message

The string returned by cdb2api’s cdb2_errstr after the failed call.

Type:

str

DatetimeUs

A class is provided for differentiating Comdb2’s DATETIMEUS type from its DATETIME type.

class comdb2.cdb2.DatetimeUs(year, month, day[, hour[, minute[, second[, microsecond[, tzinfo]]]]])

Provides a distinct representation for Comdb2’s DATETIMEUS type.

Historically, Comdb2 provided a DATETIME type with millisecond precision. Comdb2 R6 added a DATETIMEUS type, which instead has microsecond precision.

This module represents each Comdb2 type with a distinct Python type. For backwards compatibility with older Comdb2 databases, datetime.datetime is mapped to the DATETIME type, and this class to the DATETIMEUS type. Because this is a subclass of datetime.datetime, you don’t need to do anything special when reading a DATETIMEUS type out of the database. You can use isinstance if you need to check whether you’ve been given a datetime.datetime (meaning the column was of the DATETIME type) or a DatetimeUs (meaning the column was of the DATETIMEUS type), but all the same methods will work on either.

When binding a parameter of type DATETIMEUS, you must pass an instance of this class, as a datetime.datetime would instead be bound as a DATETIME. Instances of this class can be created using this constructor, or the fromdatetime alternate constructor, or any of the other alternate constructors inherited from datetime.datetime.

classmethod fromdatetime(datetime)

Return a DatetimeUs copied from a given datetime.datetime

Effects

class comdb2.cdb2.Effects(num_affected, num_selected, num_updated, num_deleted, num_inserted)

Type used to represent the count of rows affected by a SQL query.

An object of this type is returned by Handle.get_effects.

num_affected

The total number of rows that were affected.

num_deleted

The number of rows that were deleted.

num_inserted

The number of rows that were inserted.

num_selected

The number of rows that were selected.

num_updated

The number of rows that were updated.

Enumerations

Several mappings are provided to expose enumerations from cdb2api.h to Python.

class comdb2.cdb2.ColumnType(*values)

This enum represents all known Comdb2 column types.

Each value in the list returned by Handle.column_types will generally be the value corresponding to one of the members of this enumeration, though that’s not always guaranteed because new types can be added to the Comdb2 server at any time.

A sequence of consisting of members of this enum can be passed as the column_types parameter of Handle.execute. The database will coerce the returned data to the given column types, or return an error if it cannot.

comdb2.cdb2.TYPE = {'BLOB': 4, 'CSTRING': 3, 'DATETIME': 6, 'DATETIMEUS': 9, 'INTEGER': 1, 'INTERVALDS': 8, 'INTERVALDSUS': 10, 'INTERVALYM': 7, 'REAL': 2}

This dict maps all known Comdb2 types to their enumeration value.

It predates the ColumnType enum and is retained only for backwards compatibility. The ColumnType enum should be preferred for new usage.

class comdb2.cdb2.ConnectionFlags(*values)

This enum represents connection flags.

These values can be passed to the Handle constructor, either individually or as a bitwise OR of multiple flags.

comdb2.cdb2.HANDLE_FLAGS = {'DIRECT_CPU': 4, 'RANDOM': 8, 'RANDOMROOM': 16, 'READ_INTRANS_RESULTS': 2, 'ROOM': 32}

This dict maps connection flags to their enumeration value.

It predates the ConnectionFlags enum and is retained only for backwards compatibility. The ConnectionFlags enum should be preferred for new usage.

comdb2.cdb2.ERROR_CODE = {'ACCESS': -106, 'ASYNCERR': -9, 'BADCOLUMN': -7, 'BADREQ': -17, 'BADSTATE': -8, 'CONNECT_ERROR': -1, 'CONSTRAINTS': -103, 'CONV_FAIL': 113, 'DBCREATE_FAILED': -18, 'DEADLOCK': 203, 'DUPLICATE': 299, 'FKEY_VIOLATION': 3, 'INTERNAL': -5, 'INVALID_ID': -12, 'IO_ERROR': -4, 'MALLOC': 115, 'NOMASTER': -101, 'NONKLESS': 114, 'NOSTATEMENT': -6, 'NOTCONNECTED': -2, 'NOTSUPPORTED': 116, 'NULL_CONSTRAINT': 4, 'PREPARE_ERROR': -3, 'READONLY': -21, 'RECORD_OUT_OF_RANGE': -13, 'REJECTED': -15, 'STOPPED': -16, 'THREADPOOL_INTERNAL': -20, 'TRAN_IO_ERROR': -105, 'TRAN_MODE_UNSUPPORTED': -107, 'TZNAME_FAIL': 401, 'UNKNOWN': 300, 'UNTAGGED_DATABASE': -102, 'VERIFY_ERROR': 2}

This dict maps all known Comdb2 error names to their respective values.

The value returned in Error.error_code will generally be the value corresponding to one of the keys in this dict, though that’s not always guaranteed because new error codes can be added to the Comdb2 server at any time.