Trac SQL Database API

Trac uses a very thin layer on top of the standard ​Python Database API 2.0 for interfacing with supported relational database systems such as ​SQLite or ​PostgreSQL.

Note that Trac prior to version 0.9 used the PySQLite APIs directly, and has no support for other database systems. This document describes the thin DB API abstraction layer introduced in version 0.9.

You can find the specifics of the database API in the trac.db package. This package provides:

• Simple pooling of database connections
• Iterable cursors
• Selection of DB modules based of connection URIs.

Accessing to the Database

Code accessing the database in Trac go through this layer simply by using the Environment method get_db_cnx():

from trac.env import Environment

env = Environment('/path/to/projenv')
db = env.get_db_cnx()
cursor = db.cursor()
# Execute some SQL statements

db.commit()

The get_db_cnx method looks at the value of the database configuration option in trac.ini, which should contain a database connection URI. The default value for this option tells Trac to use an SQLite database inside the environment directory:

[trac]
database = sqlite:db/trac.db

The connection URI syntax has been designed to be compatible with that provided by ​SQLObject (see also the Wiki page on SQLObject ​connections). The only supported URI schemes at this point are sqlite and postgres.

Rules for DB API Usage

Different DB API modules have different ways to pass parameters to the cursors' execute method, and different ways to access query results. To keep the database API as thin as possible, the Trac team has decided to use a relatively common subset in all database code.

Parameter passing

Always use the "format" parameter style, and always with %s (because that's the only type that pyPgSQL supports). Statement parameters always need to be passed into execute as an actual sequence (list or tuple).

So the following statements are okay:

cursor.execute("SELECT author,ipnr,comment FROM wiki WHERE name=%s", [thename])
cursor.execute("SELECT id FROM ticket WHERE time>=%s AND time<=%s", (start, stop))

The following uses are not okay:

cursor.execute("SELECT author,ipnr,comment FROM wiki WHERE name=?", thename)
cursor.execute("SELECT id FROM ticket WHERE time>=%i AND time<=%i", start, stop)

At any cost, try avoiding the use of ​string formatting to get values into the SQL statement. The database automatically escapes values you pass using execute() arguments, the same is not true if you use string formatting, opening your code up to ​SQL injection attacks.

On the other hand, you must use string formatting to dynamically specify names of tables or columns, i.e. anything that is not a value as such:

cursor.execute("SELECT time FROM %s WHERE name=%%s" % table, (thename,))

Retrieving results

For convenience, cursors returned by the database connection object are iterable after having executed an SQL query. Individual fields in result rows may only be accessed using integer indices:

cursor.execute("SELECT author,ipnr,comment FROM wiki WHERE name=%s", (thename,))
for row in cursor:
    print 'Author: %s (%s)' % (row[0], row[1])
    print row[2]

Accessing fields using the column names is not supported by all database modules, so it should not be used. Automatically unpacking rows into tuples of named variables often provides better readability:

cursor.execute("SELECT author,ipnr,comment FROM wiki WHERE name=%s", (thename,))
for author, ipnr, comment in cursor:
    print 'Author: %s (%s)' % (author, ipnr)
    print comment

Guidelines for SQL Statements

As you may know, support for SQL varies among different database systems. The requirements of Trac are relatively trivial, so we try to stick to a common subset that is supported by the majority of databases.

TODO Need content

---

See also: TracDev, TracDev/DatabaseSchema, TracDev/DatabaseUpgrades, TracDev/CodingStyle