trac.db.utils – Trac DB utilities

Utilities for the Trac DB abstraction layer.

Classes

The following classes are not meant to be used directly. In particular, the ConnectionWrapper is what the DbContextManager context managers will return.

For example:

>>> with env.db_query as db:
...     for name, value in db.execute("SELECT name, value FROM system"):
...         print("row: [{name}, {value}]".format(name=name, value=value))
...
row: [database_version, 29]

Here db is a ConnectionWrapper.

class trac.db.util.ConnectionWrapper(cnx, log=None, readonly=False)

Bases: object

Generic wrapper around connection objects.

Since 0.12:This wrapper no longer makes cursors produced by the connection iterable using IterableCursor. Since 1.0:added a ‘readonly’ flag preventing the forwarding of commit and rollback

All of the following methods but execute, executemany and check_select need to be implemented by the backend-specific subclass.

In addition, the standard methods from PEP 0249 Connection Objects are also available.

cast(self, column, type)

Local SQL dialect for type casting.

Parameters:

• column – name of the column
• type – generic type (int, int64, text)

concat(self, *args):

Local SQL dialect for string concatenation.

Parameters:args – values to be concatenated specified as multiple parameters.

like(self):

Local SQL dialect for a case-insensitive LIKE clause.

like_escape(self, text):

Local SQL dialect for searching for litteral text in a LIKE clause.

quote(self, identifier):

Local SQL dialect for quoting an identifier.

get_last_id(self, cursor, table, column='id'):

Local SQL dialect for retrieving the last value of an auto-increment column, immediately following an INSERT clause.

Parameters:

• cursor – the cursor in which the INSERT was executed
• table – the name of the table in which the insertion happened
• column – the name of the auto-increment column

Some backends, like PostgreSQL, support that feature natively indirectly via sequences.

update_sequence(self, cursor, table, column='id'):

Local SQL dialect for resetting a sequence.

Same parameters as for get_last_id.

This can be used whenever rows were created with an explicit value for the auto-increment column, as it could happen during a database upgrade and the recreation of a table. See #8575 for details.

check_select(query)

Verify if the query is compatible according to the readonly nature of the wrapped Connection.

Returns:True if this is a SELECT Raise:ValueError if this is not a SELECT and the wrapped Connection is read-only.

execute(query, params=None)

Execute an SQL query

The optional params is a tuple containing the parameter values expected by the query.

If the query is a SELECT, return all the rows (“fetchall”). When more control is needed, use cursor().

executemany(query, params=None)

Execute an SQL query, on a sequence of tuples (“executemany”).

The optional params is a sequence of tuples containing the parameter values expected by the query.

If the query is a SELECT, return all the rows (“fetchall”). When more control is needed, use cursor().

Also, all the ConnectionWrapper subclasses (SQLiteConnection, PostgreSQLConnection and MySQLConnection) have a reimplemented cursor() method which returns an IterableCursor.

class trac.db.util.IterableCursor(cursor, log=None)

Bases: object

Wrapper for DB-API cursor objects that makes the cursor iterable and escapes all “%”s used inside literal strings with parameterized queries.

Iteration will generate the rows of a SELECT query one by one.