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
|
<!DOCTYPE html>
<html class="writer-html5" lang="en" data-content_root="./">
<head>
<meta charset="utf-8" /><meta name="viewport" content="width=device-width, initial-scale=1" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>DBAPI notes — APSW 3.46.0.1 documentation</title>
<link rel="stylesheet" type="text/css" href="_static/pygments.css?v=72bcf2f2" />
<link rel="stylesheet" type="text/css" href="_static/css/theme.css?v=19f00094" />
<link rel="stylesheet" type="text/css" href="_static/apsw.css?v=3c7e2631" />
<link rel="shortcut icon" href="_static/favicon.ico"/>
<!--[if lt IE 9]>
<script src="_static/js/html5shiv.min.js"></script>
<![endif]-->
<script src="_static/jquery.js?v=5d32c60e"></script>
<script src="_static/_sphinx_javascript_frameworks_compat.js?v=2cd50e6c"></script>
<script src="_static/documentation_options.js?v=d98f5d2b"></script>
<script src="_static/doctools.js?v=9a2dae69"></script>
<script src="_static/sphinx_highlight.js?v=dc90522c"></script>
<script src="_static/js/theme.js"></script>
<link rel="author" title="About these documents" href="about.html" />
<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="next" title="sqlite3 module differences" href="pysqlite.html" />
<link rel="prev" title="Execution and tracing" href="execution.html" />
</head>
<body class="wy-body-for-nav">
<div class="wy-grid-for-nav">
<nav data-toggle="wy-nav-shift" class="wy-nav-side">
<div class="wy-side-scroll">
<div class="wy-side-nav-search" >
<a href="index.html" class="icon icon-home">
APSW
<img src="_static/apswlogo.png" class="logo" alt="Logo"/>
</a>
<div class="version">
3.46.0.1
</div>
<div role="search">
<form id="rtd-search-form" class="wy-form" action="search.html" method="get">
<input type="text" name="q" placeholder="Search docs" aria-label="Search docs" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div><div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="Navigation menu">
<ul class="current">
<li class="toctree-l1"><a class="reference internal" href="about.html">About</a></li>
<li class="toctree-l1"><a class="reference internal" href="tips.html">Tips</a></li>
<li class="toctree-l1"><a class="reference internal" href="example.html">Example/Tour</a></li>
<li class="toctree-l1"><a class="reference internal" href="install.html">Installation and customization</a></li>
<li class="toctree-l1"><a class="reference internal" href="extensions.html">Extensions</a></li>
<li class="toctree-l1"><a class="reference internal" href="apsw.html">APSW Module</a></li>
<li class="toctree-l1"><a class="reference internal" href="connection.html">Connections to a database</a></li>
<li class="toctree-l1"><a class="reference internal" href="cursor.html">Cursors (executing SQL)</a></li>
<li class="toctree-l1"><a class="reference internal" href="blob.html">Blob Input/Output</a></li>
<li class="toctree-l1"><a class="reference internal" href="backup.html">Backup</a></li>
<li class="toctree-l1"><a class="reference internal" href="vtable.html">Virtual Tables</a></li>
<li class="toctree-l1"><a class="reference internal" href="vfs.html">Virtual File System (VFS)</a></li>
<li class="toctree-l1"><a class="reference internal" href="shell.html">Shell</a></li>
<li class="toctree-l1"><a class="reference internal" href="bestpractice.html">Best Practice</a></li>
<li class="toctree-l1"><a class="reference internal" href="ext.html">Various interesting and useful bits of functionality</a></li>
<li class="toctree-l1"><a class="reference internal" href="exceptions.html">Exceptions and Errors</a></li>
<li class="toctree-l1"><a class="reference internal" href="execution.html">Execution and tracing</a></li>
<li class="toctree-l1 current"><a class="current reference internal" href="#">DBAPI notes</a><ul>
<li class="toctree-l2"><a class="reference internal" href="#module-interface">Module Interface</a></li>
<li class="toctree-l2"><a class="reference internal" href="#connection-objects">Connection Objects</a></li>
<li class="toctree-l2"><a class="reference internal" href="#cursor-objects">Cursor Objects</a></li>
<li class="toctree-l2"><a class="reference internal" href="#type-objects">Type objects</a></li>
<li class="toctree-l2"><a class="reference internal" href="#optional-db-api-extensions">Optional DB API Extensions</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="pysqlite.html">sqlite3 module differences</a></li>
<li class="toctree-l1"><a class="reference internal" href="benchmarking.html">Benchmarking</a></li>
<li class="toctree-l1"><a class="reference internal" href="copyright.html">Copyright and License</a></li>
<li class="toctree-l1"><a class="reference internal" href="changes.html">Change History</a></li>
<li class="toctree-l1"><a class="reference internal" href="py-modindex.html">Module Index</a></li>
<li class="toctree-l1"><a class="reference internal" href="genindex.html">Index</a></li>
</ul>
</div>
</div>
</nav>
<section data-toggle="wy-nav-shift" class="wy-nav-content-wrap"><nav class="wy-nav-top" aria-label="Mobile navigation menu" >
<i data-toggle="wy-nav-top" class="fa fa-bars"></i>
<a href="index.html">APSW</a>
</nav>
<div class="wy-nav-content">
<div class="rst-content style-external-links">
<div role="navigation" aria-label="Page navigation">
<ul class="wy-breadcrumbs">
<li><a href="index.html" class="icon icon-home" aria-label="Home"></a></li>
<li class="breadcrumb-item active">DBAPI notes</li>
<li class="wy-breadcrumbs-aside">
<a href="_sources/dbapi.rst.txt" rel="nofollow"> View page source</a>
</li>
</ul><div class="rst-breadcrumbs-buttons" role="navigation" aria-label="Sequential page navigation">
<a href="execution.html" class="btn btn-neutral float-left" title="Execution and tracing" accesskey="p"><span class="fa fa-arrow-circle-left" aria-hidden="true"></span> Previous</a>
<a href="pysqlite.html" class="btn btn-neutral float-right" title="sqlite3 module differences" accesskey="n">Next <span class="fa fa-arrow-circle-right" aria-hidden="true"></span></a>
</div>
<hr/>
</div>
<div role="main" class="document" itemscope="itemscope" itemtype="http://schema.org/Article">
<div itemprop="articleBody">
<section id="dbapi-notes">
<span id="dbapinotes"></span><h1>DBAPI notes<a class="headerlink" href="#dbapi-notes" title="Link to this heading"></a></h1>
<p>DBAPI is defined in <span class="target" id="index-0"></span><a class="pep reference external" href="https://peps.python.org/pep-0249/"><strong>PEP 249</strong></a>. This section describes how APSW complies or differs from it.</p>
<section id="module-interface">
<h2>Module Interface<a class="headerlink" href="#module-interface" title="Link to this heading"></a></h2>
<p>There is no connect method. Use the <a class="reference internal" href="connection.html#apsw.Connection" title="apsw.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> constructor instead.</p>
<p>The Connection object and any cursors can be used in any thread. You
cannot use the cursor concurrently in multiple threads for example
calling <a class="reference internal" href="cursor.html#apsw.Cursor.execute" title="apsw.Cursor.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Cursor.execute()</span></code></a> at the same time. If you attempt to do
so then an <a class="reference internal" href="exceptions.html#apsw.ThreadingViolationError" title="apsw.ThreadingViolationError"><code class="xref py py-exc docutils literal notranslate"><span class="pre">exception</span></code></a> will be raised.
The Python Global Interpreter Lock (GIL) is released during all SQLite
API calls allowing for maximum concurrency.</p>
<p>Three different paramstyles are supported. Note that SQLite starts
parameter numbers from one not zero when using <em>qmark/numeric</em> style.</p>
<table class="docutils align-default">
<thead>
<tr class="row-odd"><th class="head"><p>style</p></th>
<th class="head"><p>use</p></th>
</tr>
</thead>
<tbody>
<tr class="row-even"><td><p>qmark</p></td>
<td><p><code class="code docutils literal notranslate"><span class="pre">...</span> <span class="pre">WHERE</span> <span class="pre">name=?</span></code></p></td>
</tr>
<tr class="row-odd"><td><p>numeric</p></td>
<td><p><code class="code docutils literal notranslate"><span class="pre">...</span> <span class="pre">WHERE</span> <span class="pre">name=?4</span></code></p></td>
</tr>
<tr class="row-even"><td><p>named</p></td>
<td><p><code class="code docutils literal notranslate"><span class="pre">...</span> <span class="pre">WHERE</span> <span class="pre">name=:name</span></code> or
<code class="code docutils literal notranslate"><span class="pre">...</span> <span class="pre">WHERE</span> <span class="pre">name=$name</span></code> or
<code class="code docutils literal notranslate"><span class="pre">...</span> <span class="pre">WHERE</span> <span class="pre">name=@name</span></code></p></td>
</tr>
</tbody>
</table>
<p>The DBAPI exceptions are not used. The <a class="reference internal" href="exceptions.html#exceptions"><span class="std std-ref">exceptions</span></a>
used correspond to specific SQLite error codes.</p>
</section>
<section id="connection-objects">
<h2>Connection Objects<a class="headerlink" href="#connection-objects" title="Link to this heading"></a></h2>
<p>There are no commit or rollback methods. You can issue queries as
<cite>BEGIN</cite>, <cite>COMMIT</cite>, and <cite>ROLLBACK</cite> to do transactions manually. You
should use <a class="reference internal" href="connection.html#apsw.Connection.__enter__" title="apsw.Connection.__enter__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">with</span> <span class="pre">Connection</span></code></a> to get
automatic transaction control, which includes <a class="reference external" href="https://sqlite.org/lang_savepoint.html">nested transactions</a>.</p>
<p>Several methods that are defined in DBAPI to be on the cursor are
instead on the Connection object, since this is where SQLite actually
stores the information. Doing operations in any other cursor attached
to the same Connection object does update their values, and this makes
you aware of that.</p>
</section>
<section id="cursor-objects">
<h2>Cursor Objects<a class="headerlink" href="#cursor-objects" title="Link to this heading"></a></h2>
<p>Use <a class="reference internal" href="cursor.html#apsw.Cursor.get_description" title="apsw.Cursor.get_description"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Cursor.get_description()</span></code></a> instead of description. This
information is only obtained on request.</p>
<p id="rowcount">There is no rowcount. Row counts don’t make sense in SQLite any way.
SQLite returns results one row at a time, not calculating the next
result row until you ask for it. Consequently getting a rowcount
would have to calculate all the result rows and would not reduce the
amount of effort needed.</p>
<p>callproc is not implemented as SQLite doesn’t support stored procedures.</p>
<p><a class="reference internal" href="cursor.html#apsw.Cursor.execute" title="apsw.Cursor.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> returns the Cursor object and you can use it
as an iterator to get the results (if any).</p>
<p><a class="reference internal" href="cursor.html#apsw.Cursor.executemany" title="apsw.Cursor.executemany"><code class="xref py py-meth docutils literal notranslate"><span class="pre">executemany()</span></code></a> returns the Cursor object and you can use
it as an iterator to get the results (if any).</p>
<p>fetchone is not available. Use the cursor as an iterator, or call
Python’s <a class="reference external" href="https://docs.python.org/3/library/functions.html?highlight=next#next">next function</a>
to get the next row.</p>
<p>fetchmany is not available. Use <a class="reference internal" href="cursor.html#apsw.Cursor.fetchall" title="apsw.Cursor.fetchall"><code class="xref py py-meth docutils literal notranslate"><span class="pre">fetchall()</span></code></a> to get all
remaining results.</p>
<p>nextset is not applicable or implemented.</p>
<p>arraysize is not available as fetchmany isn’t.</p>
<p>Neither setinputsizes or setoutputsize are applicable or implemented.</p>
</section>
<section id="type-objects">
<h2>Type objects<a class="headerlink" href="#type-objects" title="Link to this heading"></a></h2>
<p>None of the date or time methods are available since SQLite 3 does not
have a native date or time type. There are <a class="reference external" href="https://sqlite.org/lang_datefunc.html">functions</a> for
manipulating dates and time which are stored as strings or
<a class="reference external" href="https://en.wikipedia.org/wiki/Julian_day">Julian days</a> (floating
point number).</p>
<p>Use the standard bytes type for blobs.</p>
<p><a class="reference internal" href="ext.html"><span class="doc">Various interesting and useful bits of functionality</span></a> provides functionality to convert and adapt types to those
supported by SQLite.</p>
</section>
<section id="optional-db-api-extensions">
<h2>Optional DB API Extensions<a class="headerlink" href="#optional-db-api-extensions" title="Link to this heading"></a></h2>
<p>rownumber is not available.</p>
<p>Exception classes are not available as attributes of Connection but
instead are on the <a class="reference internal" href="apsw.html#module-apsw" title="apsw: Python access to SQLite database library"><code class="xref py py-mod docutils literal notranslate"><span class="pre">apsw</span></code></a> module. See <a class="reference internal" href="exceptions.html#exceptions"><span class="std std-ref">Exceptions and Errors</span></a> for
more details.</p>
<p>Use <a class="reference internal" href="cursor.html#apsw.Cursor.connection" title="apsw.Cursor.connection"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Cursor.connection</span></code></a> to get the associated Connection
object from a cursor.</p>
<p>scroll and messages are not available.</p>
<p>The Cursor object supports the iterator protocol and this is the only
way of getting information back.</p>
<p>To get the last inserted row id, call
<a class="reference internal" href="connection.html#apsw.Connection.last_insert_rowid" title="apsw.Connection.last_insert_rowid"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.last_insert_rowid()</span></code></a>. That stores the id from the last
insert on any Cursor associated with the the Connection. You can also
add <a class="reference external" href="https://sqlite.org/lang_corefunc.html">select last_insert_rowid()</a> to the end of your execute
statements:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">db</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"""BEGIN;</span>
<span class="s2"> INSERT ... ;</span>
<span class="s2"> INSERT ... ;</span>
<span class="s2"> SELECT last_insert_rowid();</span>
<span class="s2"> COMMIT"""</span><span class="p">):</span>
<span class="n">lastrowid</span><span class="o">=</span><span class="n">row</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span>
</pre></div>
</div>
<p>There is no errorhandler attribute.</p>
</section>
</section>
</div>
</div>
<footer><div class="rst-footer-buttons" role="navigation" aria-label="Footer">
<a href="execution.html" class="btn btn-neutral float-left" title="Execution and tracing" accesskey="p" rel="prev"><span class="fa fa-arrow-circle-left" aria-hidden="true"></span> Previous</a>
<a href="pysqlite.html" class="btn btn-neutral float-right" title="sqlite3 module differences" accesskey="n" rel="next">Next <span class="fa fa-arrow-circle-right" aria-hidden="true"></span></a>
</div>
<hr/>
<div role="contentinfo">
<p>© <a href="copyright.html">Copyright</a> 2004-2024, Roger Binns <rogerb@rogerbinns.com>.
<span class="lastupdated">Last updated on Jun 16, 2024.
</span></p>
</div>
Built with <a href="https://www.sphinx-doc.org/">Sphinx</a> using a
<a href="https://github.com/readthedocs/sphinx_rtd_theme">theme</a>
provided by <a href="https://readthedocs.org">Read the Docs</a>.
</footer>
</div>
</div>
</section>
</div>
<script>
jQuery(function () {
SphinxRtdTheme.Navigation.enable(true);
});
</script>
</body>
</html>
|
