Firebird

Firebird

Support for the Firebird database.

DBAPI Support

The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.

Note

The Firebird dialect within SQLAlchemy is not currently supported. Thedialect is not tested within continuous integration and is likely to havemany issues and caveats not currently handled.

Firebird Dialects

Firebird offers two distinct dialects (not to be confused with aSQLAlchemy Dialect):

dialect 1
This is the old syntax and behaviour, inherited from Interbase pre-6.0.
dialect 3
This is the newer and supported syntax, introduced in Interbase 6.0.

The SQLAlchemy Firebird dialect detects these versions andadjusts its representation of SQL accordingly. However,support for dialect 1 is not well tested and probably hasincompatibilities.

Locking Behavior

Firebird locks tables aggressively. For this reason, a DROP TABLE mayhang until other transactions are released. SQLAlchemy does its bestto release transactions as quickly as possible. The most common causeof hanging transactions is a non-fully consumed result set, i.e.:

result = engine.execute("select * from table")
row = result.fetchone()
return

Where above, the ResultProxy has not been fully consumed. Theconnection will be returned to the pool and the transactional staterolled back once the Python garbage collector reclaims the objectswhich hold onto the connection, which often occurs asynchronously.The above use case can be alleviated by calling first() on theResultProxy which will fetch the first row and immediately closeall remaining cursor/connection resources.

RETURNING support

Firebird 2.0 supports returning a result set from inserts, and 2.1extends that to deletes and updates. This is generically exposed bythe SQLAlchemy returning() method, such as:

# INSERT..RETURNING
result = table.insert().returning(table.c.col1, table.c.col2).\
               values(name='foo')
print result.fetchall()
 
# UPDATE..RETURNING
raises = empl.update().returning(empl.c.id, empl.c.salary).\
              where(empl.c.sales>100).\
              values(dict(salary=empl.c.salary * 1.1))
print raises.fetchall()

fdb

Support for the Firebird database via the fdb driver.

fdb is a kinterbasdb compatible DBAPI for Firebird.

Changed in version 0.9: - The fdb dialect is now the default dialectunder the firebird:// URL space, as fdb is now the officialPython driver for Firebird.

DBAPI

Documentation and download information (if applicable) for fdb is available at:http://pypi.python.org/pypi/fdb/

Connecting

Connect String:

firebird+fdb://user:password@host:port/path/to/db[?key=value&key=value...]

Arguments

The fdb dialect is based on thesqlalchemy.dialects.firebird.kinterbasdb dialect, however does notaccept every argument that Kinterbasdb does.

enable_rowcount - True by default, setting this to False disablesthe usage of “cursor.rowcount” with theKinterbasdb dialect, which SQLAlchemy ordinarily calls upon automaticallyafter any UPDATE or DELETE statement. When disabled, SQLAlchemy’sResultProxy will return -1 for result.rowcount. The rationale here isthat Kinterbasdb requires a second round trip to the database when.rowcount is called - since SQLA’s resultproxy automatically closesthe cursor after a non-result-returning statement, rowcount must becalled, if at all, before the result object is returned. Additionally,cursor.rowcount may not return correct results with older versionsof Firebird, and setting this flag to False will also cause theSQLAlchemy ORM to ignore its usage. The behavior can also be controlled on aper-execution basis using the enable_rowcount option withConnection.execution_options():

conn = engine.connect().execution_options(enable_rowcount=True)
r = conn.execute(stmt)
print r.rowcount

retaining - False by default. Setting this to True will pass theretaining=True keyword argument to the .commit() and .rollback()methods of the DBAPI connection, which can improve performance in somesituations, but apparently with significant caveats.Please read the fdb and/or kinterbasdb DBAPI documentation in order tounderstand the implications of this flag.

Changed in version 0.9.0: - the retaining flag defaults to False.In 0.8 it defaulted to True.

kinterbasdb

Support for the Firebird database via the kinterbasdb driver.

DBAPI

Documentation and download information (if applicable) for kinterbasdb is available at:http://firebirdsql.org/index.php?op=devel&sub=python

Connecting

Connect String:

firebird+kinterbasdb://user:password@host:port/path/to/db[?key=value&key=value...]

Arguments

The Kinterbasdb backend accepts the enable_rowcount and retainingarguments accepted by the sqlalchemy.dialects.firebird.fdb dialect.In addition, it also accepts the following:

type_conv - select the kind of mapping done on the types: by defaultSQLAlchemy uses 200 with Unicode, datetime and decimal support. Seethe linked documents below for further information.
concurrency_level - set the backend policy with regards to threadingissues: by default SQLAlchemy uses policy 1. See the linked documentsbelow for further information.