1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
|
<!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.9 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/sphinx_paramlinks.css" type="text/css" />
<link rel="stylesheet" href="../_static/changelog.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.9.8',
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.9 Documentation" href="../index.html" />
<link rel="up" title="Dialects" href="index.html" />
<link rel="next" title="Microsoft SQL Server" href="mssql.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.9.8</span> | Release Date: October 13, 2014
</div>
<h1>SQLAlchemy 0.9 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.9 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="mssql.html" title="Microsoft SQL Server">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.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="#arguments">Arguments</a></li>
</ul>
</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="#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.fdb">fdb</a></li>
<li><a class="reference external" href="#module-sqlalchemy.dialects.firebird.kinterbasdb">kinterbasdb</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.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>
<div class="versionchanged">
<p><span>Changed in version 0.9: </span>- The fdb dialect is now the default dialect
under the <tt class="docutils literal"><span class="pre">firebird://</span></tt> URL space, as <tt class="docutils literal"><span class="pre">fdb</span></tt> is now the official
Python driver for Firebird.</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="arguments">
<h3>Arguments<a class="headerlink" href="#arguments" 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> - False by default. Setting this to 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 - in 0.8 it defaults to <tt class="docutils literal"><span class="pre">True</span></tt>
for backwards compatibility.</p>
</div>
<div class="versionchanged">
<p><span>Changed in version 0.9.0: </span>- the <tt class="docutils literal"><span class="pre">retaining</span></tt> flag defaults to <tt class="docutils literal"><span class="pre">False</span></tt>.
In 0.8 it defaulted to <tt class="docutils literal"><span class="pre">True</span></tt>.</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 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="id1">
<h3>Arguments<a class="headerlink" href="#id1" 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>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links">
Previous:
<a href="drizzle.html" title="previous chapter">Drizzle</a>
Next:
<a href="mssql.html" title="next chapter">Microsoft SQL Server</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>
|
