<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
Firebird
—
SQLAlchemy 0.8 Documentation
</title>
<!-- begin iterate through SQLA + sphinx environment css_files -->
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../_static/docs.css" type="text/css" />
<link rel="stylesheet" href="../_static/changelog.css" type="text/css" />
<link rel="stylesheet" href="../_static/sphinx_paramlinks.css" type="text/css" />
<!-- end iterate through SQLA + sphinx environment css_files -->
<!-- begin layout.mako headers -->
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '0.8.7',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<!-- begin iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<!-- end iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/detectmobile.js"></script>
<script type="text/javascript" src="../_static/init.js"></script>
<link rel="index" title="Index" href="../genindex.html" />
<link rel="search" title="Search" href="../search.html" />
<link rel="copyright" title="Copyright" href="../copyright.html" />
<link rel="top" title="SQLAlchemy 0.8 Documentation" href="../index.html" />
<link rel="up" title="Dialects" href="index.html" />
<link rel="next" title="Informix" href="informix.html" />
<link rel="prev" title="Drizzle" href="drizzle.html" />
<!-- end layout.mako headers -->
</head>
<body>
<div id="docs-container">
<div id="docs-top-navigation-container" class="body-background">
<div id="docs-header">
<div id="docs-version-header">
Release: <span class="version-num">0.8.7</span> | Release Date: July 22, 2014
</div>
<h1>SQLAlchemy 0.8 Documentation</h1>
</div>
</div>
<div id="docs-body-container">
<div id="fixed-sidebar" class="withsidebar">
<div id="docs-sidebar-popout">
<h3><a href="../index.html">SQLAlchemy 0.8 Documentation</a></h3>
<p id="sidebar-paginate">
<a href="index.html" title="Dialects">Up</a> |
<a href="drizzle.html" title="Drizzle">Prev</a> |
<a href="informix.html" title="Informix">Next</a>
</p>
<p id="sidebar-topnav">
<a href="../index.html">Contents</a> |
<a href="../genindex.html">Index</a>
</p>
<div id="sidebar-search">
<form class="search" action="../search.html" method="get">
<input type="text" name="q" size="12" /> <input type="submit" value="Search" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div id="docs-sidebar">
<h3><a href="#">
Firebird
</a></h3>
<ul>
<li><a class="reference internal" href="#">Firebird</a><ul>
<li><a class="reference internal" href="#dialect-firebird">Support for the Firebird database.</a></li>
<li><a class="reference internal" href="#firebird-dialects">Firebird Dialects</a></li>
<li><a class="reference internal" href="#locking-behavior">Locking Behavior</a></li>
<li><a class="reference internal" href="#returning-support">RETURNING support</a></li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.firebird.kinterbasdb">kinterbasdb</a><ul>
<li><a class="reference internal" href="#dialect-firebird-kinterbasdb-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-firebird-kinterbasdb-connect">Connecting</a></li>
<li><a class="reference internal" href="#arguments">Arguments</a></li>
</ul>
</li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.firebird.fdb">fdb</a><ul>
<li><a class="reference internal" href="#dialect-firebird-fdb-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-firebird-fdb-connect">Connecting</a></li>
<li><a class="reference internal" href="#status">Status</a></li>
<li><a class="reference internal" href="#id1">Arguments</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="module-sqlalchemy.dialects.firebird.base">
<span id="firebird"></span><span id="firebird-toplevel"></span><h1>Firebird<a class="headerlink" href="#module-sqlalchemy.dialects.firebird.base" title="Permalink to this headline">¶</a></h1>
<div class="section" id="dialect-firebird">
<p>Support for the Firebird database.</p>
<h2>DBAPI Support<a class="headerlink" href="#dialect-firebird" title="Permalink to this headline">¶</a></h2>
<p>The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.<ul class="simple">
<li><a class="reference external" href="#module-sqlalchemy.dialects.firebird.kinterbasdb">kinterbasdb</a></li>
<li><a class="reference external" href="#module-sqlalchemy.dialects.firebird.fdb">fdb</a></li>
</ul>
</p>
</div>
<div class="section" id="firebird-dialects">
<h2>Firebird Dialects<a class="headerlink" href="#firebird-dialects" title="Permalink to this headline">¶</a></h2>
<p>Firebird offers two distinct <a class="reference external" href="http://mc-computing.com/Databases/Firebird/SQL_Dialect.html">dialects</a> (not to be confused with a
SQLAlchemy <tt class="docutils literal"><span class="pre">Dialect</span></tt>):</p>
<dl class="docutils">
<dt>dialect 1</dt>
<dd>This is the old syntax and behaviour, inherited from Interbase pre-6.0.</dd>
<dt>dialect 3</dt>
<dd>This is the newer and supported syntax, introduced in Interbase 6.0.</dd>
</dl>
<p>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.</p>
</div>
<div class="section" id="locking-behavior">
<h2>Locking Behavior<a class="headerlink" href="#locking-behavior" title="Permalink to this headline">¶</a></h2>
<p>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.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">result</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"select * from table"</span><span class="p">)</span>
<span class="n">row</span> <span class="o">=</span> <span class="n">result</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
<span class="k">return</span></pre></div>
</div>
<p>Where above, the <tt class="docutils literal"><span class="pre">ResultProxy</span></tt> 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 <tt class="docutils literal"><span class="pre">first()</span></tt> on the
<tt class="docutils literal"><span class="pre">ResultProxy</span></tt> which will fetch the first row and immediately close
all remaining cursor/connection resources.</p>
</div>
<div class="section" id="returning-support">
<h2>RETURNING support<a class="headerlink" href="#returning-support" title="Permalink to this headline">¶</a></h2>
<p>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 <tt class="docutils literal"><span class="pre">returning()</span></tt> method, such as:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># INSERT..RETURNING</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">table</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span><span class="n">returning</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col1</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col2</span><span class="p">)</span><span class="o">.</span>\
<span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'foo'</span><span class="p">)</span>
<span class="k">print</span> <span class="n">result</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="c"># UPDATE..RETURNING</span>
<span class="n">raises</span> <span class="o">=</span> <span class="n">empl</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span><span class="n">returning</span><span class="p">(</span><span class="n">empl</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">empl</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">salary</span><span class="p">)</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">empl</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">sales</span><span class="o">></span><span class="mi">100</span><span class="p">)</span><span class="o">.</span>\
<span class="n">values</span><span class="p">(</span><span class="nb">dict</span><span class="p">(</span><span class="n">salary</span><span class="o">=</span><span class="n">empl</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">salary</span> <span class="o">*</span> <span class="mf">1.1</span><span class="p">))</span>
<span class="k">print</span> <span class="n">raises</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.firebird.kinterbasdb">
<span id="kinterbasdb"></span><h2>kinterbasdb<a class="headerlink" href="#module-sqlalchemy.dialects.firebird.kinterbasdb" title="Permalink to this headline">¶</a></h2>
<p>Support for the Firebird database via the kinterbasdb driver.</p>
<div class="section" id="dialect-firebird-kinterbasdb-url">
<h3>DBAPI<a class="headerlink" href="#dialect-firebird-kinterbasdb-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for kinterbasdb is available at:
<a class="reference external" href="http://firebirdsql.org/index.php?op=devel&sub=python">http://firebirdsql.org/index.php?op=devel&sub=python</a></p>
</div>
<div class="section" id="dialect-firebird-kinterbasdb-connect">
<h3>Connecting<a class="headerlink" href="#dialect-firebird-kinterbasdb-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>firebird+kinterbasdb://user:password@host:port/path/to/db[?key=value&key=value...]</pre>
</div>
</p>
</div>
<div class="section" id="arguments">
<h3>Arguments<a class="headerlink" href="#arguments" title="Permalink to this headline">¶</a></h3>
<p>The Kinterbasdb backend accepts the <tt class="docutils literal"><span class="pre">enable_rowcount</span></tt> and <tt class="docutils literal"><span class="pre">retaining</span></tt>
arguments accepted by the <a class="reference internal" href="#module-sqlalchemy.dialects.firebird.fdb" title="sqlalchemy.dialects.firebird.fdb"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.dialects.firebird.fdb</span></tt></a> dialect. In addition, it
also accepts the following:</p>
<ul class="simple">
<li><tt class="docutils literal"><span class="pre">type_conv</span></tt> - 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.</li>
<li><tt class="docutils literal"><span class="pre">concurrency_level</span></tt> - set the backend policy with regards to threading
issues: by default SQLAlchemy uses policy 1. See the linked documents
below for further information.</li>
</ul>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference external" href="http://sourceforge.net/projects/kinterbasdb">http://sourceforge.net/projects/kinterbasdb</a></p>
<p><a class="reference external" href="http://kinterbasdb.sourceforge.net/dist_docs/usage.html#adv_param_conv_dynamic_type_translation">http://kinterbasdb.sourceforge.net/dist_docs/usage.html#adv_param_conv_dynamic_type_translation</a></p>
<p class="last"><a class="reference external" href="http://kinterbasdb.sourceforge.net/dist_docs/usage.html#special_issue_concurrency">http://kinterbasdb.sourceforge.net/dist_docs/usage.html#special_issue_concurrency</a></p>
</div>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.firebird.fdb">
<span id="fdb"></span><h2>fdb<a class="headerlink" href="#module-sqlalchemy.dialects.firebird.fdb" title="Permalink to this headline">¶</a></h2>
<p>Support for the Firebird database via the fdb driver.<p>fdb is a kinterbasdb compatible DBAPI for Firebird.</p>
<div class="versionadded">
<p><span>New in version 0.8: </span>- Support for the fdb Firebird driver.</p>
</div>
</p>
<div class="section" id="dialect-firebird-fdb-url">
<h3>DBAPI<a class="headerlink" href="#dialect-firebird-fdb-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for fdb is available at:
<a class="reference external" href="http://pypi.python.org/pypi/fdb/">http://pypi.python.org/pypi/fdb/</a></p>
</div>
<div class="section" id="dialect-firebird-fdb-connect">
<h3>Connecting<a class="headerlink" href="#dialect-firebird-fdb-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>firebird+fdb://user:password@host:port/path/to/db[?key=value&key=value...]</pre>
</div>
</p>
</div>
<div class="section" id="status">
<h3>Status<a class="headerlink" href="#status" title="Permalink to this headline">¶</a></h3>
<p>The fdb dialect is new and not yet tested (can’t get fdb to build).</p>
</div>
<div class="section" id="id1">
<h3>Arguments<a class="headerlink" href="#id1" title="Permalink to this headline">¶</a></h3>
<p>The <tt class="docutils literal"><span class="pre">fdb</span></tt> dialect is based on the <a class="reference internal" href="#module-sqlalchemy.dialects.firebird.kinterbasdb" title="sqlalchemy.dialects.firebird.kinterbasdb"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.dialects.firebird.kinterbasdb</span></tt></a>
dialect, however does not accept every argument that Kinterbasdb does.</p>
<ul>
<li><p class="first"><tt class="docutils literal"><span class="pre">enable_rowcount</span></tt> - 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 <tt class="docutils literal"><span class="pre">enable_rowcount</span></tt> option with
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options" title="sqlalchemy.engine.Connection.execution_options"><tt class="xref py py-meth docutils literal"><span class="pre">Connection.execution_options()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">conn</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span><span class="o">.</span><span class="n">execution_options</span><span class="p">(</span><span class="n">enable_rowcount</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">r</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span>
<span class="k">print</span> <span class="n">r</span><span class="o">.</span><span class="n">rowcount</span></pre></div>
</div>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">retaining</span></tt> - True by default. Leaving this on True will pass the
<tt class="docutils literal"><span class="pre">retaining=True</span></tt> keyword argument to the <tt class="docutils literal"><span class="pre">.commit()</span></tt> and <tt class="docutils literal"><span class="pre">.rollback()</span></tt>
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.</p>
<div class="versionadded">
<p><span>New in version 0.8.2: </span>- <tt class="docutils literal"><span class="pre">retaining</span></tt> keyword argument specifying
transaction retaining behavior. This flag will <strong>default to False</strong>
in 0.9.</p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference external" href="http://pythonhosted.org/fdb/usage-guide.html#retaining-transactions">http://pythonhosted.org/fdb/usage-guide.html#retaining-transactions</a> - information
on the “retaining” flag.</p>
</div>
</li>
</ul>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links">
Previous:
<a href="drizzle.html" title="previous chapter">Drizzle</a>
Next:
<a href="informix.html" title="next chapter">Informix</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2014, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.2b1.
</div>
</div>
</div>
</body>
</html>
