Support for the Firebird database.
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. The dialect is not tested within continuous integration and is likely to have many issues and caveats not currently handled.
Firebird offers two distinct dialects (not to be confused with a
SQLAlchemy Dialect):
The SQLAlchemy Firebird dialect detects these versions and adjusts its representation of SQL accordingly. However, support for dialect 1 is not well tested and probably has incompatibilities.
Firebird locks tables aggressively. For this reason, a DROP TABLE may hang until other transactions are released. SQLAlchemy does its best to release transactions as quickly as possible. The most common cause of hanging transactions is a non-fully consumed result set, i.e.:
result = engine.execute("select * from table")
row = result.fetchone()
returnWhere above, the ResultProxy has not been fully consumed. The
connection will be returned to the pool and the transactional state
rolled back once the Python garbage collector reclaims the objects
which hold onto the connection, which often occurs asynchronously.
The above use case can be alleviated by calling first() on the
ResultProxy which will fetch the first row and immediately close
all remaining cursor/connection resources.
Firebird 2.0 supports returning a result set from inserts, and 2.1
extends that to deletes and updates. This is generically exposed by
the 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()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 dialect
under the firebird:// URL space, as fdb is now the official
Python driver for Firebird.
Documentation and download information (if applicable) for fdb is available at: http://pypi.python.org/pypi/fdb/
Connect String:
firebird+fdb://user:password@host:port/path/to/db[?key=value&key=value...]The fdb dialect is based on the
sqlalchemy.dialects.firebird.kinterbasdb dialect, however does not
accept every argument that Kinterbasdb does.
enable_rowcount - True by default, setting this to False disables
the usage of “cursor.rowcount” with the
Kinterbasdb dialect, which SQLAlchemy ordinarily calls upon automatically
after any UPDATE or DELETE statement. When disabled, SQLAlchemy’s
ResultProxy will return -1 for result.rowcount. The rationale here is
that Kinterbasdb requires a second round trip to the database when
.rowcount is called - since SQLA’s resultproxy automatically closes
the cursor after a non-result-returning statement, rowcount must be
called, if at all, before the result object is returned. Additionally,
cursor.rowcount may not return correct results with older versions
of Firebird, and setting this flag to False will also cause the
SQLAlchemy ORM to ignore its usage. The behavior can also be controlled on a
per-execution basis using the enable_rowcount option with
Connection.execution_options():
conn = engine.connect().execution_options(enable_rowcount=True)
r = conn.execute(stmt)
print r.rowcountretaining - False by default. Setting this to True will pass the
retaining=True keyword argument to the .commit() and .rollback()
methods of the DBAPI connection, which can improve performance in some
situations, but apparently with significant caveats.
Please read the fdb and/or kinterbasdb DBAPI documentation in order to
understand the implications of this flag.
Changed in version 0.9.0: - the retaining flag defaults to False.
In 0.8 it defaulted to True.
See also
http://pythonhosted.org/fdb/usage-guide.html#retaining-transactions - information on the “retaining” flag.
Support for the Firebird database via the kinterbasdb driver.
Documentation and download information (if applicable) for kinterbasdb is available at: http://firebirdsql.org/index.php?op=devel&sub=python
Connect String:
firebird+kinterbasdb://user:password@host:port/path/to/db[?key=value&key=value...]The Kinterbasdb backend accepts the enable_rowcount and retaining
arguments 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 default
SQLAlchemy uses 200 with Unicode, datetime and decimal support. See
the linked documents below for further information.concurrency_level - set the backend policy with regards to threading
issues: by default SQLAlchemy uses policy 1. See the linked documents
below for further information.