1528 lines
171 KiB
HTML
1528 lines
171 KiB
HTML
|
||
<!DOCTYPE html>
|
||
|
||
<html xmlns="http://www.w3.org/1999/xhtml" lang="zh_CN">
|
||
<head>
|
||
<meta charset="utf-8" /><meta name="generator" content="Docutils 0.17.1: http://docutils.sourceforge.net/" />
|
||
|
||
<title>sqlite3 --- SQLite 数据库 DB-API 2.0 接口模块 — Python 3.8.20 文档</title><meta name="viewport" content="width=device-width, initial-scale=1.0">
|
||
|
||
<link rel="stylesheet" href="../_static/pydoctheme.css" type="text/css" />
|
||
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
|
||
|
||
<script id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></script>
|
||
<script src="../_static/jquery.js"></script>
|
||
<script src="../_static/underscore.js"></script>
|
||
<script src="../_static/doctools.js"></script>
|
||
<script src="../_static/language_data.js"></script>
|
||
<script src="../_static/translations.js"></script>
|
||
|
||
<script src="../_static/sidebar.js"></script>
|
||
|
||
<link rel="search" type="application/opensearchdescription+xml"
|
||
title="在 Python 3.8.20 文档 中搜索"
|
||
href="../_static/opensearch.xml"/>
|
||
<link rel="author" title="关于这些文档" href="../about.html" />
|
||
<link rel="index" title="索引" href="../genindex.html" />
|
||
<link rel="search" title="搜索" href="../search.html" />
|
||
<link rel="copyright" title="版权所有" href="../copyright.html" />
|
||
<link rel="next" title="数据压缩和存档" href="archiving.html" />
|
||
<link rel="prev" title="dbm --- Unix "数据库" 接口" href="dbm.html" />
|
||
<link rel="canonical" href="https://docs.python.org/3/library/sqlite3.html" />
|
||
|
||
|
||
|
||
|
||
|
||
<style>
|
||
@media only screen {
|
||
table.full-width-table {
|
||
width: 100%;
|
||
}
|
||
}
|
||
</style>
|
||
<link rel="shortcut icon" type="image/png" href="../_static/py.svg" />
|
||
<script type="text/javascript" src="../_static/copybutton.js"></script>
|
||
<script type="text/javascript" src="../_static/menu.js"></script>
|
||
|
||
</head>
|
||
<body>
|
||
<div class="mobile-nav">
|
||
<input type="checkbox" id="menuToggler" class="toggler__input" aria-controls="navigation"
|
||
aria-pressed="false" aria-expanded="false" role="button" aria-label="Menu" />
|
||
<label for="menuToggler" class="toggler__label">
|
||
<span></span>
|
||
</label>
|
||
<nav class="nav-content" role="navigation">
|
||
<a href="https://www.python.org/" class="nav-logo">
|
||
<img src="../_static/py.svg" alt="Logo"/>
|
||
</a>
|
||
<div class="version_switcher_placeholder"></div>
|
||
<form role="search" class="search" action="../search.html" method="get">
|
||
<svg xmlns="http://www.w3.org/2000/svg" width="20" height="20" viewBox="0 0 24 24" class="search-icon">
|
||
<path fill-rule="nonzero"
|
||
d="M15.5 14h-.79l-.28-.27a6.5 6.5 0 001.48-5.34c-.47-2.78-2.79-5-5.59-5.34a6.505 6.505 0 00-7.27 7.27c.34 2.8 2.56 5.12 5.34 5.59a6.5 6.5 0 005.34-1.48l.27.28v.79l4.25 4.25c.41.41 1.08.41 1.49 0 .41-.41.41-1.08 0-1.49L15.5 14zm-6 0C7.01 14 5 11.99 5 9.5S7.01 5 9.5 5 14 7.01 14 9.5 11.99 14 9.5 14z" fill="#444"></path>
|
||
</svg>
|
||
<input type="text" name="q" aria-label="快速搜索"/>
|
||
<input type="submit" value="转向"/>
|
||
</form>
|
||
</nav>
|
||
<div class="menu-wrapper">
|
||
<nav class="menu" role="navigation" aria-label="main navigation">
|
||
<div class="language_switcher_placeholder"></div>
|
||
<h3><a href="../contents.html">目录</a></h3>
|
||
<ul>
|
||
<li><a class="reference internal" href="#"><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> --- SQLite 数据库 DB-API 2.0 接口模块</a><ul>
|
||
<li><a class="reference internal" href="#module-functions-and-constants">模块函数和常量</a></li>
|
||
<li><a class="reference internal" href="#connection-objects">连接对象（Connection）</a></li>
|
||
<li><a class="reference internal" href="#cursor-objects">Cursor 对象</a></li>
|
||
<li><a class="reference internal" href="#row-objects">行对象</a></li>
|
||
<li><a class="reference internal" href="#exceptions">异常</a></li>
|
||
<li><a class="reference internal" href="#sqlite-and-python-types">SQLite 与 Python 类型</a><ul>
|
||
<li><a class="reference internal" href="#introduction">概述</a></li>
|
||
<li><a class="reference internal" href="#using-adapters-to-store-additional-python-types-in-sqlite-databases">使用适配器将额外的 Python 类型保存在 SQLite 数据库中。</a><ul>
|
||
<li><a class="reference internal" href="#letting-your-object-adapt-itself">让对象自行适配</a></li>
|
||
<li><a class="reference internal" href="#registering-an-adapter-callable">注册可调用的适配器</a></li>
|
||
</ul>
|
||
</li>
|
||
<li><a class="reference internal" href="#converting-sqlite-values-to-custom-python-types">将SQLite 值转换为自定义Python 类型</a></li>
|
||
<li><a class="reference internal" href="#default-adapters-and-converters">默认适配器和转换器</a></li>
|
||
</ul>
|
||
</li>
|
||
<li><a class="reference internal" href="#controlling-transactions">控制事务</a></li>
|
||
<li><a class="reference internal" href="#using-sqlite3-efficiently">有效使用 <code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a><ul>
|
||
<li><a class="reference internal" href="#using-shortcut-methods">使用快捷方式</a></li>
|
||
<li><a class="reference internal" href="#accessing-columns-by-name-instead-of-by-index">通过名称而不是索引访问索引</a></li>
|
||
<li><a class="reference internal" href="#using-the-connection-as-a-context-manager">使用连接作为上下文管理器</a></li>
|
||
</ul>
|
||
</li>
|
||
</ul>
|
||
</li>
|
||
</ul>
|
||
|
||
<h4>上一个主题</h4>
|
||
<p class="topless"><a href="dbm.html"
|
||
title="上一章"><code class="xref py py-mod docutils literal notranslate"><span class="pre">dbm</span></code> --- Unix "数据库" 接口</a></p>
|
||
<h4>下一个主题</h4>
|
||
<p class="topless"><a href="archiving.html"
|
||
title="下一章">数据压缩和存档</a></p>
|
||
<div role="note" aria-label="source link">
|
||
<h3>本页</h3>
|
||
<ul class="this-page-menu">
|
||
<li><a href="../bugs.html">报告 Bug</a></li>
|
||
<li>
|
||
<a href="https://github.com/python/cpython/blob/3.8/Doc/library/sqlite3.rst"
|
||
rel="nofollow">显示源代码
|
||
</a>
|
||
</li>
|
||
</ul>
|
||
</div>
|
||
</nav>
|
||
</div>
|
||
</div>
|
||
|
||
<div id="outdated-warning" style="padding: .5em; text-align: center; background-color: #FFBABA; color: #6A0E0E;">
|
||
这个文档所针对的是一个已不再受支持的 Python 旧版本。
|
||
你应当升级版本，并阅读
|
||
<a href="/3/library/sqlite3.html"> Python 当前稳定版本的文档</a>.
|
||
</div>
|
||
|
||
<div class="related" role="navigation" aria-label="related navigation">
|
||
<h3>导航</h3>
|
||
<ul>
|
||
<li class="right" style="margin-right: 10px">
|
||
<a href="../genindex.html" title="总目录"
|
||
accesskey="I">索引</a></li>
|
||
<li class="right" >
|
||
<a href="../py-modindex.html" title="Python 模块索引"
|
||
>模块</a> |</li>
|
||
<li class="right" >
|
||
<a href="archiving.html" title="数据压缩和存档"
|
||
accesskey="N">下一页</a> |</li>
|
||
<li class="right" >
|
||
<a href="dbm.html" title="dbm --- Unix "数据库" 接口"
|
||
accesskey="P">上一页</a> |</li>
|
||
|
||
<li><img src="../_static/py.svg" alt="python logo" style="vertical-align: middle; margin-top: -1px"/></li>
|
||
<li><a href="https://www.python.org/">Python</a> »</li>
|
||
<li class="switchers">
|
||
<div class="language_switcher_placeholder"></div>
|
||
<div class="version_switcher_placeholder"></div>
|
||
</li>
|
||
<li>
|
||
|
||
</li>
|
||
<li id="cpython-language-and-version">
|
||
<a href="../index.html">3.8.20 Documentation</a> »
|
||
</li>
|
||
|
||
<li class="nav-item nav-item-1"><a href="index.html" >Python 标准库</a> »</li>
|
||
<li class="nav-item nav-item-2"><a href="persistence.html" accesskey="U">数据持久化</a> »</li>
|
||
<li class="right">
|
||
|
||
|
||
<div class="inline-search" role="search">
|
||
<form class="inline-search" action="../search.html" method="get">
|
||
<input placeholder="快速搜索" aria-label="快速搜索" type="text" name="q" />
|
||
<input type="submit" value="转向" />
|
||
<input type="hidden" name="check_keywords" value="yes" />
|
||
<input type="hidden" name="area" value="default" />
|
||
</form>
|
||
</div>
|
||
|
|
||
</li>
|
||
|
||
</ul>
|
||
</div>
|
||
|
||
<div class="document">
|
||
<div class="documentwrapper">
|
||
<div class="bodywrapper">
|
||
<div class="body" role="main">
|
||
|
||
<section id="module-sqlite3">
|
||
<span id="sqlite3-db-api-2-0-interface-for-sqlite-databases"></span><h1><a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> --- SQLite 数据库 DB-API 2.0 接口模块<a class="headerlink" href="#module-sqlite3" title="永久链接至标题">¶</a></h1>
|
||
<p><strong>源代码：</strong> <a class="reference external" href="https://github.com/python/cpython/tree/3.8/Lib/sqlite3/">Lib/sqlite3/</a></p>
|
||
<hr class="docutils" />
|
||
<p>SQLite 是一个C语言库，它可以提供一种轻量级的基于磁盘的数据库，这种数据库不需要独立的服务器进程，也允许需要使用一种非标准的 SQL 查询语言来访问它。一些应用程序可以使用 SQLite 作为内部数据存储。可以用它来创建一个应用程序原型，然后再迁移到更大的数据库，比如 PostgreSQL 或 Oracle。</p>
|
||
<p>sqlite3 模块由 Gerhard Häring 编写。它提供了符合 DB-API 2.0 规范的接口，这个规范是 <span class="target" id="index-6"></span><a class="pep reference external" href="https://www.python.org/dev/peps/pep-0249"><strong>PEP 249</strong></a>。</p>
|
||
<p>要使用这个模块，必须先创建一个 <a class="reference internal" href="#sqlite3.Connection" title="sqlite3.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> 对象，它代表数据库。下面例子中，数据将存储在 <code class="file docutils literal notranslate"><span class="pre">example.db</span></code> 文件中：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s1">'example.db'</span><span class="p">)</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>你也可以使用 <code class="docutils literal notranslate"><span class="pre">:memory:</span></code> 来创建一个内存中的数据库</p>
|
||
<p>当有了 <a class="reference internal" href="#sqlite3.Connection" title="sqlite3.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> 对象后，你可以创建一个 <a class="reference internal" href="#sqlite3.Cursor" title="sqlite3.Cursor"><code class="xref py py-class docutils literal notranslate"><span class="pre">Cursor</span></code></a> 游标对象，然后调用它的 <a class="reference internal" href="#sqlite3.Cursor.execute" title="sqlite3.Cursor.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> 方法来执行 SQL 语句：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
|
||
<span class="c1"># Create table</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'''CREATE TABLE stocks</span>
|
||
<span class="s1"> (date text, trans text, symbol text, qty real, price real)'''</span><span class="p">)</span>
|
||
|
||
<span class="c1"># Insert a row of data</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)"</span><span class="p">)</span>
|
||
|
||
<span class="c1"># Save (commit) the changes</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
|
||
|
||
<span class="c1"># We can also close the connection if we are done with it.</span>
|
||
<span class="c1"># Just be sure any changes have been committed or they will be lost.</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>这些数据被持久化保存了，而且可以在之后的会话中使用它们：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s1">'example.db'</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>通常你的 SQL 操作需要使用一些 Python 变量的值。你不应该使用 Python 的字符串操作来创建你的查询语句，因为那样做不安全；它会使你的程序容易受到 SQL 注入攻击（在 <a class="reference external" href="https://xkcd.com/327/">https://xkcd.com/327/</a> 上有一个搞笑的例子，看看有什么后果）</p>
|
||
<p>推荐另外一种方法：使用 DB-API 的参数替换。在你的 SQL 语句中，使用 <code class="docutils literal notranslate"><span class="pre">?</span></code> 占位符来代替值，然后把对应的值组成的元组做为 <a class="reference internal" href="#sqlite3.Cursor.execute" title="sqlite3.Cursor.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> 方法的第二个参数。（其他数据库可能会使用不同的占位符，比如 <code class="docutils literal notranslate"><span class="pre">%s</span></code> 或者 <code class="docutils literal notranslate"><span class="pre">:1</span></code>）例如：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="c1"># Never do this -- insecure!</span>
|
||
<span class="n">symbol</span> <span class="o">=</span> <span class="s1">'RHAT'</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"SELECT * FROM stocks WHERE symbol = '</span><span class="si">%s</span><span class="s2">'"</span> <span class="o">%</span> <span class="n">symbol</span><span class="p">)</span>
|
||
|
||
<span class="c1"># Do this instead</span>
|
||
<span class="n">t</span> <span class="o">=</span> <span class="p">(</span><span class="s1">'RHAT'</span><span class="p">,)</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'SELECT * FROM stocks WHERE symbol=?'</span><span class="p">,</span> <span class="n">t</span><span class="p">)</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">())</span>
|
||
|
||
<span class="c1"># Larger example that inserts many records at a time</span>
|
||
<span class="n">purchases</span> <span class="o">=</span> <span class="p">[(</span><span class="s1">'2006-03-28'</span><span class="p">,</span> <span class="s1">'BUY'</span><span class="p">,</span> <span class="s1">'IBM'</span><span class="p">,</span> <span class="mi">1000</span><span class="p">,</span> <span class="mf">45.00</span><span class="p">),</span>
|
||
<span class="p">(</span><span class="s1">'2006-04-05'</span><span class="p">,</span> <span class="s1">'BUY'</span><span class="p">,</span> <span class="s1">'MSFT'</span><span class="p">,</span> <span class="mi">1000</span><span class="p">,</span> <span class="mf">72.00</span><span class="p">),</span>
|
||
<span class="p">(</span><span class="s1">'2006-04-06'</span><span class="p">,</span> <span class="s1">'SELL'</span><span class="p">,</span> <span class="s1">'IBM'</span><span class="p">,</span> <span class="mi">500</span><span class="p">,</span> <span class="mf">53.00</span><span class="p">),</span>
|
||
<span class="p">]</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">executemany</span><span class="p">(</span><span class="s1">'INSERT INTO stocks VALUES (?,?,?,?,?)'</span><span class="p">,</span> <span class="n">purchases</span><span class="p">)</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>要在执行 SELECT 语句后获取数据，你可以把游标作为 <a class="reference internal" href="../glossary.html#term-iterator"><span class="xref std std-term">iterator</span></a>，然后调用它的 <a class="reference internal" href="#sqlite3.Cursor.fetchone" title="sqlite3.Cursor.fetchone"><code class="xref py py-meth docutils literal notranslate"><span class="pre">fetchone()</span></code></a> 方法来获取一条匹配的行，也可以调用 <a class="reference internal" href="#sqlite3.Cursor.fetchall" title="sqlite3.Cursor.fetchall"><code class="xref py py-meth docutils literal notranslate"><span class="pre">fetchall()</span></code></a> 来得到包含多个匹配行的列表。</p>
|
||
<p>下面是一个使用迭代器形式的例子：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'SELECT * FROM stocks ORDER BY price'</span><span class="p">):</span>
|
||
<span class="go"> print(row)</span>
|
||
|
||
<span class="go">('2006-01-05', 'BUY', 'RHAT', 100, 35.14)</span>
|
||
<span class="go">('2006-03-28', 'BUY', 'IBM', 1000, 45.0)</span>
|
||
<span class="go">('2006-04-06', 'SELL', 'IBM', 500, 53.0)</span>
|
||
<span class="go">('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)</span>
|
||
</pre></div>
|
||
</div>
|
||
<div class="admonition seealso">
|
||
<p class="admonition-title">参见</p>
|
||
<dl class="simple">
|
||
<dt><a class="reference external" href="https://www.sqlite.org">https://www.sqlite.org</a></dt><dd><p>SQLite的主页；它的文档详细描述了它所支持的 SQL 方言的语法和可用的数据类型。</p>
|
||
</dd>
|
||
<dt><a class="reference external" href="https://www.w3schools.com/sql/">https://www.w3schools.com/sql/</a></dt><dd><p>学习 SQL 语法的教程、参考和例子。</p>
|
||
</dd>
|
||
<dt><span class="target" id="index-7"></span><a class="pep reference external" href="https://www.python.org/dev/peps/pep-0249"><strong>PEP 249</strong></a> - DB-API 2.0 规范</dt><dd><p>PEP 由 Marc-André Lemburg 撰写。</p>
|
||
</dd>
|
||
</dl>
|
||
</div>
|
||
<section id="module-functions-and-constants">
|
||
<span id="sqlite3-module-contents"></span><h2>模块函数和常量<a class="headerlink" href="#module-functions-and-constants" title="永久链接至标题">¶</a></h2>
|
||
<dl class="data">
|
||
<dt id="sqlite3.version">
|
||
<code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">version</code><a class="headerlink" href="#sqlite3.version" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这个模块的版本号，是一个字符串。不是 SQLite 库的版本号。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="data">
|
||
<dt id="sqlite3.version_info">
|
||
<code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">version_info</code><a class="headerlink" href="#sqlite3.version_info" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这个模块的版本号，是一个由整数组成的元组。不是 SQLite 库的版本号。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="data">
|
||
<dt id="sqlite3.sqlite_version">
|
||
<code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">sqlite_version</code><a class="headerlink" href="#sqlite3.sqlite_version" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>使用中的 SQLite 库的版本号，是一个字符串。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="data">
|
||
<dt id="sqlite3.sqlite_version_info">
|
||
<code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">sqlite_version_info</code><a class="headerlink" href="#sqlite3.sqlite_version_info" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>使用中的 SQLite 库的版本号，是一个整数组成的元组。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="data">
|
||
<dt id="sqlite3.PARSE_DECLTYPES">
|
||
<code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">PARSE_DECLTYPES</code><a class="headerlink" href="#sqlite3.PARSE_DECLTYPES" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这个常量可以作为 <a class="reference internal" href="#sqlite3.connect" title="sqlite3.connect"><code class="xref py py-func docutils literal notranslate"><span class="pre">connect()</span></code></a> 函数的 <em>detect_types</em> 参数。</p>
|
||
<p>设置这个参数后，<a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块将解析它返回的每一列申明的类型。它会申明的类型的第一个单词，比如“integer primary key”，它会解析出“integer”，再比如“number(10)”，它会解析出“number”。然后，它会在转换器字典里查找那个类型注册的转换器函数，并调用它。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="data">
|
||
<dt id="sqlite3.PARSE_COLNAMES">
|
||
<code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">PARSE_COLNAMES</code><a class="headerlink" href="#sqlite3.PARSE_COLNAMES" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这个常量可以作为 <a class="reference internal" href="#sqlite3.connect" title="sqlite3.connect"><code class="xref py py-func docutils literal notranslate"><span class="pre">connect()</span></code></a> 函数的 <em>detect_types</em> 参数。</p>
|
||
<p>设置此参数可使得 SQLite 接口解析它所返回的每一列的列名。 它将在其中查找形式为 [mytype] 的字符串，然后将 'mytype' 确定为列的类型。 它将尝试在转换器字典中查找 'mytype' 条目，然后用找到的转换器函数来返回值。 在 <a class="reference internal" href="#sqlite3.Cursor.description" title="sqlite3.Cursor.description"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Cursor.description</span></code></a> 中找到的列名并不包括类型，举例来说，如果你在你的 SQL 中使用了像 <code class="docutils literal notranslate"><span class="pre">'as</span> <span class="pre">"Expiration</span> <span class="pre">date</span> <span class="pre">[datetime]"'</span></code> 这样的写法，那么我们将解析出在第一个 then we will parse out everything until the first <code class="docutils literal notranslate"><span class="pre">'['</span></code> 之前的所有内容并去除前导空格作为列名：即列名将为 "Expiration date"。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="function">
|
||
<dt id="sqlite3.connect">
|
||
<code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">connect</code><span class="sig-paren">(</span><em class="sig-param">database</em><span class="optional">[</span>, <em class="sig-param">timeout</em>, <em class="sig-param">detect_types</em>, <em class="sig-param">isolation_level</em>, <em class="sig-param">check_same_thread</em>, <em class="sig-param">factory</em>, <em class="sig-param">cached_statements</em>, <em class="sig-param">uri</em><span class="optional">]</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.connect" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>连接 SQLite 数据库 <em>database</em>。默认返回 <a class="reference internal" href="#sqlite3.Connection" title="sqlite3.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> 对象，除非使用了自定义的 <em>factory</em> 参数。</p>
|
||
<p><em>database</em> 是准备打开的数据库文件的路径（绝对路径或相对于当前目录的相对路径），它是 <a class="reference internal" href="../glossary.html#term-path-like-object"><span class="xref std std-term">path-like object</span></a>。你也可以用 <code class="docutils literal notranslate"><span class="pre">":memory:"</span></code> 在内存中打开一个数据库。</p>
|
||
<p>当一个数据库被多个连接访问的时候，如果其中一个进程修改这个数据库，在这个事务提交之前，这个 SQLite 数据库将会被一直锁定。<em>timeout</em> 参数指定了这个连接等待锁释放的超时时间，超时之后会引发一个异常。这个超时时间默认是 5.0（5秒）。</p>
|
||
<p><em>isolation_level</em> 参数，请查看 <a class="reference internal" href="#sqlite3.Connection" title="sqlite3.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> 对象的 <a class="reference internal" href="#sqlite3.Connection.isolation_level" title="sqlite3.Connection.isolation_level"><code class="xref py py-attr docutils literal notranslate"><span class="pre">isolation_level</span></code></a> 属性。</p>
|
||
<p>SQLite 原生只支持5种类型：TEXT，INTEGER，REAL，BLOB 和 NULL。如果你想用其它类型，你必须自己添加相应的支持。使用 <em>detect_types</em> 参数和模块级别的 <a class="reference internal" href="#sqlite3.register_converter" title="sqlite3.register_converter"><code class="xref py py-func docutils literal notranslate"><span class="pre">register_converter()</span></code></a> 函数注册**转换器** 可以简单的实现。</p>
|
||
<p><em>detect_types</em> 默认为 0 (即关闭，不进行类型检测)，你可以将其设为任意的 <a class="reference internal" href="#sqlite3.PARSE_DECLTYPES" title="sqlite3.PARSE_DECLTYPES"><code class="xref py py-const docutils literal notranslate"><span class="pre">PARSE_DECLTYPES</span></code></a> 和 <a class="reference internal" href="#sqlite3.PARSE_COLNAMES" title="sqlite3.PARSE_COLNAMES"><code class="xref py py-const docutils literal notranslate"><span class="pre">PARSE_COLNAMES</span></code></a> 组合来启用类型检测。 由于 SQLite 的行为，生成的字段类型 (例如 <code class="docutils literal notranslate"><span class="pre">max(data)</span></code>) 不能被检测，即使在设置了 <em>detect_types</em> 形参时也是如此。 在此情况下，返回的类型为 <a class="reference internal" href="stdtypes.html#str" title="str"><code class="xref py py-class docutils literal notranslate"><span class="pre">str</span></code></a>。</p>
|
||
<p>默认情况下，<em>check_same_thread</em> 为 <a class="reference internal" href="constants.html#True" title="True"><code class="xref py py-const docutils literal notranslate"><span class="pre">True</span></code></a>，只有当前的线程可以使用该连接。 如果设置为 <a class="reference internal" href="constants.html#False" title="False"><code class="xref py py-const docutils literal notranslate"><span class="pre">False</span></code></a>，则多个线程可以共享返回的连接。 当多个线程使用同一个连接的时候，用户应该把写操作进行序列化，以避免数据损坏。</p>
|
||
<p>默认情况下，当调用 connect 方法的时候，<a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块使用了它的 <a class="reference internal" href="#sqlite3.Connection" title="sqlite3.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> 类。当然，你也可以创建 <a class="reference internal" href="#sqlite3.Connection" title="sqlite3.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> 类的子类，然后创建提供了 <em>factory</em> 参数的 <a class="reference internal" href="#sqlite3.connect" title="sqlite3.connect"><code class="xref py py-func docutils literal notranslate"><span class="pre">connect()</span></code></a> 方法。</p>
|
||
<p>详情请查阅当前手册的 <a class="reference internal" href="#sqlite3-types"><span class="std std-ref">SQLite 与 Python 类型</span></a> 部分。</p>
|
||
<p><a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块在内部使用语句缓存来避免 SQL 解析开销。 如果要显式设置当前连接可以缓存的语句数，可以设置 <em>cached_statements</em> 参数。 当前实现的默认值是缓存100条语句。</p>
|
||
<p>如果 <em>uri</em> 为真，则 <em>database</em> 被解释为 URI。 它允许您指定选项。 例如，以只读模式打开数据库：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="n">db</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s1">'file:path/to/database?mode=ro'</span><span class="p">,</span> <span class="n">uri</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>有关此功能的更多信息，包括已知选项的列表，可以在 ` SQLite URI 文档 <<a class="reference external" href="https://www.sqlite.org/uri.html">https://www.sqlite.org/uri.html</a>>`_ 中找到。</p>
|
||
<p class="audit-hook">引发一个 <a class="reference internal" href="sys.html#auditing"><span class="std std-ref">审计事件</span></a> <code class="docutils literal notranslate"><span class="pre">sqlite3.connect</span></code> 并附带参数 <code class="docutils literal notranslate"><span class="pre">database</span></code>。</p>
|
||
<div class="versionchanged">
|
||
<p><span class="versionmodified changed">在 3.4 版更改: </span>增加了 <em>uri</em> 参数。</p>
|
||
</div>
|
||
<div class="versionchanged">
|
||
<p><span class="versionmodified changed">在 3.7 版更改: </span><em>database</em> 现在可以是一个 <a class="reference internal" href="../glossary.html#term-path-like-object"><span class="xref std std-term">path-like object</span></a> 对象了，而不仅仅是字符串。</p>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="function">
|
||
<dt id="sqlite3.register_converter">
|
||
<code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">register_converter</code><span class="sig-paren">(</span><em class="sig-param">typename</em>, <em class="sig-param">callable</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.register_converter" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>注册一个回调对象 <em>callable</em>, 用来转换数据库中的字节串为自定的 Python 类型。所有类型为 <em>typename</em> 的数据库的值在转换时，都会调用这个回调对象。通过指定 <a class="reference internal" href="#sqlite3.connect" title="sqlite3.connect"><code class="xref py py-func docutils literal notranslate"><span class="pre">connect()</span></code></a> 函数的 <em>detect-types</em> 参数来设置类型检测的方式。注意，<em>typename</em> 与查询语句中的类型名进行匹配时不区分大小写。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="function">
|
||
<dt id="sqlite3.register_adapter">
|
||
<code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">register_adapter</code><span class="sig-paren">(</span><em class="sig-param">type</em>, <em class="sig-param">callable</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.register_adapter" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>注册一个回调对象 <em>callable</em>，用来转换自定义Python类型为一个 SQLite 支持的类型。 这个回调对象 <em>callable</em> 仅接受一个 Python 值作为参数，而且必须返回以下某个类型的值：int，float，str 或 bytes。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="function">
|
||
<dt id="sqlite3.complete_statement">
|
||
<code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">complete_statement</code><span class="sig-paren">(</span><em class="sig-param">sql</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.complete_statement" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>如果字符串 <em>sql</em> 包含一个或多个完整的 SQL 语句（以分号结束）则返回 <a class="reference internal" href="constants.html#True" title="True"><code class="xref py py-const docutils literal notranslate"><span class="pre">True</span></code></a>。它不会验证 SQL 语法是否正确，仅会验证字符串字面上是否完整，以及是否以分号结束。</p>
|
||
<p>它可以用来构建一个 SQLite shell，下面是一个例子：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="c1"># A minimal SQLite shell for experiments</span>
|
||
|
||
<span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">isolation_level</span> <span class="o">=</span> <span class="kc">None</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
|
||
<span class="n">buffer</span> <span class="o">=</span> <span class="s2">""</span>
|
||
|
||
<span class="nb">print</span><span class="p">(</span><span class="s2">"Enter your SQL commands to execute in sqlite3."</span><span class="p">)</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="s2">"Enter a blank line to exit."</span><span class="p">)</span>
|
||
|
||
<span class="k">while</span> <span class="kc">True</span><span class="p">:</span>
|
||
<span class="n">line</span> <span class="o">=</span> <span class="nb">input</span><span class="p">()</span>
|
||
<span class="k">if</span> <span class="n">line</span> <span class="o">==</span> <span class="s2">""</span><span class="p">:</span>
|
||
<span class="k">break</span>
|
||
<span class="n">buffer</span> <span class="o">+=</span> <span class="n">line</span>
|
||
<span class="k">if</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">complete_statement</span><span class="p">(</span><span class="n">buffer</span><span class="p">):</span>
|
||
<span class="k">try</span><span class="p">:</span>
|
||
<span class="n">buffer</span> <span class="o">=</span> <span class="n">buffer</span><span class="o">.</span><span class="n">strip</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">buffer</span><span class="p">)</span>
|
||
|
||
<span class="k">if</span> <span class="n">buffer</span><span class="o">.</span><span class="n">lstrip</span><span class="p">()</span><span class="o">.</span><span class="n">upper</span><span class="p">()</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s2">"SELECT"</span><span class="p">):</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">cur</span><span class="o">.</span><span class="n">fetchall</span><span class="p">())</span>
|
||
<span class="k">except</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">Error</span> <span class="k">as</span> <span class="n">e</span><span class="p">:</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="s2">"An error occurred:"</span><span class="p">,</span> <span class="n">e</span><span class="o">.</span><span class="n">args</span><span class="p">[</span><span class="mi">0</span><span class="p">])</span>
|
||
<span class="n">buffer</span> <span class="o">=</span> <span class="s2">""</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="function">
|
||
<dt id="sqlite3.enable_callback_tracebacks">
|
||
<code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">enable_callback_tracebacks</code><span class="sig-paren">(</span><em class="sig-param">flag</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.enable_callback_tracebacks" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>默认情况下，您不会获得任何用户定义函数中的回溯消息，比如聚合，转换器，授权器回调等。如果要调试它们，可以设置 <em>flag</em> 参数为 <code class="docutils literal notranslate"><span class="pre">True</span></code> 并调用此函数。 之后，回调中的回溯信息将会输出到 <code class="docutils literal notranslate"><span class="pre">sys.stderr</span></code>。 再次使用 <a class="reference internal" href="constants.html#False" title="False"><code class="xref py py-const docutils literal notranslate"><span class="pre">False</span></code></a> 来禁用该功能。</p>
|
||
</dd></dl>
|
||
|
||
</section>
|
||
<section id="connection-objects">
|
||
<span id="sqlite3-connection-objects"></span><h2>连接对象（Connection）<a class="headerlink" href="#connection-objects" title="永久链接至标题">¶</a></h2>
|
||
<dl class="class">
|
||
<dt id="sqlite3.Connection">
|
||
<em class="property">class </em><code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">Connection</code><a class="headerlink" href="#sqlite3.Connection" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>SQLite 数据库连接对象有如下的属性和方法：</p>
|
||
<dl class="attribute">
|
||
<dt id="sqlite3.Connection.isolation_level">
|
||
<code class="sig-name descname">isolation_level</code><a class="headerlink" href="#sqlite3.Connection.isolation_level" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>获取或设置当前默认的隔离级别。 表示自动提交模式的 <a class="reference internal" href="constants.html#None" title="None"><code class="xref py py-const docutils literal notranslate"><span class="pre">None</span></code></a> 以及 "DEFERRED", "IMMEDIATE" 或 "EXCLUSIVE" 其中之一。 详细描述请参阅 <a class="reference internal" href="#sqlite3-controlling-transactions"><span class="std std-ref">控制事务</span></a>。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="attribute">
|
||
<dt id="sqlite3.Connection.in_transaction">
|
||
<code class="sig-name descname">in_transaction</code><a class="headerlink" href="#sqlite3.Connection.in_transaction" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>如果是在活动事务中（还没有提交改变），返回 <a class="reference internal" href="constants.html#True" title="True"><code class="xref py py-const docutils literal notranslate"><span class="pre">True</span></code></a>，否则，返回 <a class="reference internal" href="constants.html#False" title="False"><code class="xref py py-const docutils literal notranslate"><span class="pre">False</span></code></a>。它是一个只读属性。</p>
|
||
<div class="versionadded">
|
||
<p><span class="versionmodified added">3.2 新版功能.</span></p>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.cursor">
|
||
<code class="sig-name descname">cursor</code><span class="sig-paren">(</span><em class="sig-param">factory=Cursor</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.cursor" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这个方法接受一个可选参数 <em>factory</em>，如果要指定这个参数，它必须是一个可调用对象，而且必须返回 <a class="reference internal" href="#sqlite3.Cursor" title="sqlite3.Cursor"><code class="xref py py-class docutils literal notranslate"><span class="pre">Cursor</span></code></a> 类的一个实例或者子类。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.commit">
|
||
<code class="sig-name descname">commit</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.commit" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这个方法提交当前事务。如果没有调用这个方法，那么从上一次提交 <code class="docutils literal notranslate"><span class="pre">commit()</span></code> 以来所有的变化在其他数据库连接上都是不可见的。如果你往数据库里写了数据，但是又查询不到，请检查是否忘记了调用这个方法。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.rollback">
|
||
<code class="sig-name descname">rollback</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.rollback" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这个方法回滚从上一次调用 <a class="reference internal" href="#sqlite3.Connection.commit" title="sqlite3.Connection.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">commit()</span></code></a> 以来所有数据库的改变。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.close">
|
||
<code class="sig-name descname">close</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.close" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>关闭数据库连接。注意，它不会自动调用 <a class="reference internal" href="#sqlite3.Connection.commit" title="sqlite3.Connection.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">commit()</span></code></a> 方法。如果在关闭数据库连接之前没有调用 <a class="reference internal" href="#sqlite3.Connection.commit" title="sqlite3.Connection.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">commit()</span></code></a>，那么你的修改将会丢失！</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.execute">
|
||
<code class="sig-name descname">execute</code><span class="sig-paren">(</span><em class="sig-param">sql</em><span class="optional">[</span>, <em class="sig-param">parameters</em><span class="optional">]</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.execute" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这是一个非标准的快捷方法，它会调用 <a class="reference internal" href="#sqlite3.Connection.cursor" title="sqlite3.Connection.cursor"><code class="xref py py-meth docutils literal notranslate"><span class="pre">cursor()</span></code></a> 方法来创建一个游标对象，并使用给定的 <em>parameters</em> 参数来调用游标对象的 <a class="reference internal" href="#sqlite3.Cursor.execute" title="sqlite3.Cursor.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> 方法，最后返回这个游标对象。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.executemany">
|
||
<code class="sig-name descname">executemany</code><span class="sig-paren">(</span><em class="sig-param">sql</em><span class="optional">[</span>, <em class="sig-param">parameters</em><span class="optional">]</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.executemany" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这是一个非标准的快捷方法，它会调用 <a class="reference internal" href="#sqlite3.Connection.cursor" title="sqlite3.Connection.cursor"><code class="xref py py-meth docutils literal notranslate"><span class="pre">cursor()</span></code></a> 方法来创建一个游标对象，并使用给定的 <em>parameters</em> 参数来调用游标对象的 <a class="reference internal" href="#sqlite3.Cursor.executemany" title="sqlite3.Cursor.executemany"><code class="xref py py-meth docutils literal notranslate"><span class="pre">executemany()</span></code></a> 方法，最后返回这个游标对象。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.executescript">
|
||
<code class="sig-name descname">executescript</code><span class="sig-paren">(</span><em class="sig-param">sql_script</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.executescript" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这是一个非标准的快捷方法，它会调用 <a class="reference internal" href="#sqlite3.Connection.cursor" title="sqlite3.Connection.cursor"><code class="xref py py-meth docutils literal notranslate"><span class="pre">cursor()</span></code></a> 方法来创建一个游标对象，并使用给定的 <em>sql_script</em> 参数来调用游标对象的 <a class="reference internal" href="#sqlite3.Cursor.executescript" title="sqlite3.Cursor.executescript"><code class="xref py py-meth docutils literal notranslate"><span class="pre">executescript()</span></code></a> 方法，最后返回这个游标对象。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.create_function">
|
||
<code class="sig-name descname">create_function</code><span class="sig-paren">(</span><em class="sig-param">name</em>, <em class="sig-param">num_params</em>, <em class="sig-param">func</em>, <em class="sig-param">*</em>, <em class="sig-param">deterministic=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.create_function" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>创建一个可以在 SQL 语句中使用的用户自定义函数，函数名为 <em>name</em>。 <em>num_params</em> 为该函数所接受的形参个数（如果 <em>num_params</em> 为 -1，则该函数可接受任意数量的参数）， <em>func</em> 是一个 Python 可调用对象，它将作为 SQL 函数被调用。 如果 <em>deterministic</em> 为真值，则所创建的函数将被标记为 <a class="reference external" href="https://sqlite.org/deterministic.html">deterministic</a>，这允许 SQLite 执行额外的优化。 此旗标在 SQLite 3.8.3 或更高版本中受到支持，如果在旧版本中使用将引发 <a class="reference internal" href="#sqlite3.NotSupportedError" title="sqlite3.NotSupportedError"><code class="xref py py-exc docutils literal notranslate"><span class="pre">NotSupportedError</span></code></a>。</p>
|
||
<p>此函数可返回任何 SQLite 所支持的类型: bytes, str, int, float 和 <code class="docutils literal notranslate"><span class="pre">None</span></code>。</p>
|
||
<div class="versionchanged">
|
||
<p><span class="versionmodified changed">在 3.8 版更改: </span>增加了 <em>deterministic</em> 形参。</p>
|
||
</div>
|
||
<p>示例:</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
<span class="kn">import</span> <span class="nn">hashlib</span>
|
||
|
||
<span class="k">def</span> <span class="nf">md5sum</span><span class="p">(</span><span class="n">t</span><span class="p">):</span>
|
||
<span class="k">return</span> <span class="n">hashlib</span><span class="o">.</span><span class="n">md5</span><span class="p">(</span><span class="n">t</span><span class="p">)</span><span class="o">.</span><span class="n">hexdigest</span><span class="p">()</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">create_function</span><span class="p">(</span><span class="s2">"md5"</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="n">md5sum</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select md5(?)"</span><span class="p">,</span> <span class="p">(</span><span class="sa">b</span><span class="s2">"foo"</span><span class="p">,))</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()[</span><span class="mi">0</span><span class="p">])</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.create_aggregate">
|
||
<code class="sig-name descname">create_aggregate</code><span class="sig-paren">(</span><em class="sig-param">name</em>, <em class="sig-param">num_params</em>, <em class="sig-param">aggregate_class</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.create_aggregate" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>创建一个自定义的聚合函数。</p>
|
||
<p>参数中 <em>aggregate_class</em> 类必须实现两个方法：<code class="docutils literal notranslate"><span class="pre">step</span></code> 和 <code class="docutils literal notranslate"><span class="pre">finalize</span></code>。<code class="docutils literal notranslate"><span class="pre">step</span></code> 方法接受 <em>num_params</em> 个参数（如果 <em>num_params</em> 为 -1，那么这个函数可以接受任意数量的参数）；<code class="docutils literal notranslate"><span class="pre">finalize</span></code> 方法返回最终的聚合结果。</p>
|
||
<p><code class="docutils literal notranslate"><span class="pre">finalize</span></code> 方法可以返回任何 SQLite 支持的类型：bytes，str，int，float 和 <code class="docutils literal notranslate"><span class="pre">None</span></code>。</p>
|
||
<p>示例:</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="k">class</span> <span class="nc">MySum</span><span class="p">:</span>
|
||
<span class="k">def</span> <span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
|
||
<span class="bp">self</span><span class="o">.</span><span class="n">count</span> <span class="o">=</span> <span class="mi">0</span>
|
||
|
||
<span class="k">def</span> <span class="nf">step</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span>
|
||
<span class="bp">self</span><span class="o">.</span><span class="n">count</span> <span class="o">+=</span> <span class="n">value</span>
|
||
|
||
<span class="k">def</span> <span class="nf">finalize</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
|
||
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">count</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">create_aggregate</span><span class="p">(</span><span class="s2">"mysum"</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="n">MySum</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"create table test(i)"</span><span class="p">)</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"insert into test(i) values (1)"</span><span class="p">)</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"insert into test(i) values (2)"</span><span class="p">)</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select mysum(i) from test"</span><span class="p">)</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()[</span><span class="mi">0</span><span class="p">])</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.create_collation">
|
||
<code class="sig-name descname">create_collation</code><span class="sig-paren">(</span><em class="sig-param">name</em>, <em class="sig-param">callable</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.create_collation" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>使用 <em>name</em> 和 <em>callable</em> 创建排序规则。这个 <em>callable</em> 接受两个字符串对象，如果第一个小于第二个则返回 -1， 如果两个相等则返回 0，如果第一个大于第二个则返回 1。注意，这是用来控制排序的（SQL 中的 ORDER BY），所以它不会影响其它的 SQL 操作。</p>
|
||
<p>注意，这个 <em>callable</em> 可调用对象会把它的参数作为 Python 字节串，通常会以 UTF-8 编码格式对它进行编码。</p>
|
||
<p>以下示例显示了使用“错误方式”进行排序的自定义排序规则：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="k">def</span> <span class="nf">collate_reverse</span><span class="p">(</span><span class="n">string1</span><span class="p">,</span> <span class="n">string2</span><span class="p">):</span>
|
||
<span class="k">if</span> <span class="n">string1</span> <span class="o">==</span> <span class="n">string2</span><span class="p">:</span>
|
||
<span class="k">return</span> <span class="mi">0</span>
|
||
<span class="k">elif</span> <span class="n">string1</span> <span class="o"><</span> <span class="n">string2</span><span class="p">:</span>
|
||
<span class="k">return</span> <span class="mi">1</span>
|
||
<span class="k">else</span><span class="p">:</span>
|
||
<span class="k">return</span> <span class="o">-</span><span class="mi">1</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">create_collation</span><span class="p">(</span><span class="s2">"reverse"</span><span class="p">,</span> <span class="n">collate_reverse</span><span class="p">)</span>
|
||
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"create table test(x)"</span><span class="p">)</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">executemany</span><span class="p">(</span><span class="s2">"insert into test(x) values (?)"</span><span class="p">,</span> <span class="p">[(</span><span class="s2">"a"</span><span class="p">,),</span> <span class="p">(</span><span class="s2">"b"</span><span class="p">,)])</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select x from test order by x collate reverse"</span><span class="p">)</span>
|
||
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">cur</span><span class="p">:</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">row</span><span class="p">)</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>要移除一个排序规则，需要调用 <code class="docutils literal notranslate"><span class="pre">create_collation</span></code> 并设置 callable 参数为 <code class="docutils literal notranslate"><span class="pre">None</span></code>。</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="n">con</span><span class="o">.</span><span class="n">create_collation</span><span class="p">(</span><span class="s2">"reverse"</span><span class="p">,</span> <span class="kc">None</span><span class="p">)</span>
|
||
</pre></div>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.interrupt">
|
||
<code class="sig-name descname">interrupt</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.interrupt" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>可以从不同的线程调用这个方法来终止所有查询操作，这些查询操作可能正在连接上执行。此方法调用之后， 查询将会终止，而且查询的调用者会获得一个异常。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.set_authorizer">
|
||
<code class="sig-name descname">set_authorizer</code><span class="sig-paren">(</span><em class="sig-param">authorizer_callback</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.set_authorizer" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>此方法注册一个授权回调对象。每次在访问数据库中某个表的某一列的时候，这个回调对象将会被调用。如果要允许访问，则返回 <code class="xref py py-const docutils literal notranslate"><span class="pre">SQLITE_OK</span></code>，如果要终止整个 SQL 语句，则返回 <code class="xref py py-const docutils literal notranslate"><span class="pre">SQLITE_DENY</span></code>，如果这一列需要当做 NULL 值处理，则返回 <code class="xref py py-const docutils literal notranslate"><span class="pre">SQLITE_IGNORE</span></code>。这些常量可以在 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块中找到。</p>
|
||
<p>回调的第一个参数表示要授权的操作类型。 第二个和第三个参数将是参数或 <a class="reference internal" href="constants.html#None" title="None"><code class="xref py py-const docutils literal notranslate"><span class="pre">None</span></code></a>，具体取决于第一个参数的值。 第 4 个参数是数据库的名称（“main”，“temp”等），如果需要的话。 第 5 个参数是负责访问尝试的最内层触发器或视图的名称，或者如果此访问尝试直接来自输入 SQL 代码，则为 <a class="reference internal" href="constants.html#None" title="None"><code class="xref py py-const docutils literal notranslate"><span class="pre">None</span></code></a>。</p>
|
||
<p>请参阅 SQLite 文档，了解第一个参数的可能值以及第二个和第三个参数的含义，具体取决于第一个参数。 所有必需的常量都可以在 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块中找到。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.set_progress_handler">
|
||
<code class="sig-name descname">set_progress_handler</code><span class="sig-paren">(</span><em class="sig-param">handler</em>, <em class="sig-param">n</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.set_progress_handler" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>此例程注册回调。 对SQLite虚拟机的每个多指令调用回调。 如果要在长时间运行的操作期间从SQLite调用（例如更新用户界面），这非常有用。</p>
|
||
<p>如果要清除以前安装的任何进度处理程序，调用该方法时请将 <em>handler</em> 参数设置为 <a class="reference internal" href="constants.html#None" title="None"><code class="xref py py-const docutils literal notranslate"><span class="pre">None</span></code></a>。</p>
|
||
<p>从处理函数返回非零值将终止当前正在执行的查询并导致它引发 <a class="reference internal" href="#sqlite3.OperationalError" title="sqlite3.OperationalError"><code class="xref py py-exc docutils literal notranslate"><span class="pre">OperationalError</span></code></a> 异常。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.set_trace_callback">
|
||
<code class="sig-name descname">set_trace_callback</code><span class="sig-paren">(</span><em class="sig-param">trace_callback</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.set_trace_callback" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>为每个 SQLite 后端实际执行的 SQL 语句注册要调用的 <em>trace_callback</em>。</p>
|
||
<p>传递给回调的唯一参数是正在执行的语句（作为字符串）。 回调的返回值将被忽略。 请注意，后端不仅运行传递给 <a class="reference internal" href="#sqlite3.Cursor.execute" title="sqlite3.Cursor.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Cursor.execute()</span></code></a> 方法的语句。 其他来源包括 Python 模块的事务管理和当前数据库中定义的触发器的执行。</p>
|
||
<p>将传入的 <em>trace_callback</em> 设为 <a class="reference internal" href="constants.html#None" title="None"><code class="xref py py-const docutils literal notranslate"><span class="pre">None</span></code></a> 将禁用跟踪回调。</p>
|
||
<div class="versionadded">
|
||
<p><span class="versionmodified added">3.3 新版功能.</span></p>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.enable_load_extension">
|
||
<code class="sig-name descname">enable_load_extension</code><span class="sig-paren">(</span><em class="sig-param">enabled</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.enable_load_extension" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>此例程允许/禁止SQLite引擎从共享库加载SQLite扩展。 SQLite扩展可以定义新功能，聚合或全新的虚拟表实现。 一个众所周知的扩展是与SQLite一起分发的全文搜索扩展。</p>
|
||
<p>默认情况下禁用可加载扩展。 见 <a class="footnote-reference brackets" href="#f1" id="id1">1</a>.</p>
|
||
<div class="versionadded">
|
||
<p><span class="versionmodified added">3.2 新版功能.</span></p>
|
||
</div>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
|
||
<span class="c1"># enable extension loading</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">enable_load_extension</span><span class="p">(</span><span class="kc">True</span><span class="p">)</span>
|
||
|
||
<span class="c1"># Load the fulltext search extension</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select load_extension('./fts3.so')"</span><span class="p">)</span>
|
||
|
||
<span class="c1"># alternatively you can load the extension using an API call:</span>
|
||
<span class="c1"># con.load_extension("./fts3.so")</span>
|
||
|
||
<span class="c1"># disable extension loading again</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">enable_load_extension</span><span class="p">(</span><span class="kc">False</span><span class="p">)</span>
|
||
|
||
<span class="c1"># example from SQLite wiki</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"create virtual table recipe using fts3(name, ingredients)"</span><span class="p">)</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">executescript</span><span class="p">(</span><span class="s2">"""</span>
|
||
<span class="s2"> insert into recipe (name, ingredients) values ('broccoli stew', 'broccoli peppers cheese tomatoes');</span>
|
||
<span class="s2"> insert into recipe (name, ingredients) values ('pumpkin stew', 'pumpkin onions garlic celery');</span>
|
||
<span class="s2"> insert into recipe (name, ingredients) values ('broccoli pie', 'broccoli cheese onions flour');</span>
|
||
<span class="s2"> insert into recipe (name, ingredients) values ('pumpkin pie', 'pumpkin sugar flour butter');</span>
|
||
<span class="s2"> """</span><span class="p">)</span>
|
||
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">con</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select rowid, name, ingredients from recipe where name match 'pie'"</span><span class="p">):</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">row</span><span class="p">)</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.load_extension">
|
||
<code class="sig-name descname">load_extension</code><span class="sig-paren">(</span><em class="sig-param">path</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.load_extension" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>此例程从共享库加载SQLite扩展。 在使用此例程之前，必须使用 <a class="reference internal" href="#sqlite3.Connection.enable_load_extension" title="sqlite3.Connection.enable_load_extension"><code class="xref py py-meth docutils literal notranslate"><span class="pre">enable_load_extension()</span></code></a> 启用扩展加载。</p>
|
||
<p>默认情况下禁用可加载扩展。 见 <a class="footnote-reference brackets" href="#f1" id="id2">1</a>.</p>
|
||
<div class="versionadded">
|
||
<p><span class="versionmodified added">3.2 新版功能.</span></p>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="attribute">
|
||
<dt id="sqlite3.Connection.row_factory">
|
||
<code class="sig-name descname">row_factory</code><a class="headerlink" href="#sqlite3.Connection.row_factory" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>您可以将此属性更改为可接受游标和原始行作为元组的可调用对象，并将返回实际结果行。 这样，您可以实现更高级的返回结果的方法，例如返回一个可以按名称访问列的对象。</p>
|
||
<p>示例:</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="k">def</span> <span class="nf">dict_factory</span><span class="p">(</span><span class="n">cursor</span><span class="p">,</span> <span class="n">row</span><span class="p">):</span>
|
||
<span class="n">d</span> <span class="o">=</span> <span class="p">{}</span>
|
||
<span class="k">for</span> <span class="n">idx</span><span class="p">,</span> <span class="n">col</span> <span class="ow">in</span> <span class="nb">enumerate</span><span class="p">(</span><span class="n">cursor</span><span class="o">.</span><span class="n">description</span><span class="p">):</span>
|
||
<span class="n">d</span><span class="p">[</span><span class="n">col</span><span class="p">[</span><span class="mi">0</span><span class="p">]]</span> <span class="o">=</span> <span class="n">row</span><span class="p">[</span><span class="n">idx</span><span class="p">]</span>
|
||
<span class="k">return</span> <span class="n">d</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">row_factory</span> <span class="o">=</span> <span class="n">dict_factory</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select 1 as a"</span><span class="p">)</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()[</span><span class="s2">"a"</span><span class="p">])</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>如果返回一个元组是不够的，并且你想要对列进行基于名称的访问，你应该考虑将 <a class="reference internal" href="#sqlite3.Connection.row_factory" title="sqlite3.Connection.row_factory"><code class="xref py py-attr docutils literal notranslate"><span class="pre">row_factory</span></code></a> 设置为高度优化的 <a class="reference internal" href="#sqlite3.Row" title="sqlite3.Row"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlite3.Row</span></code></a> 类型。 <a class="reference internal" href="#sqlite3.Row" title="sqlite3.Row"><code class="xref py py-class docutils literal notranslate"><span class="pre">Row</span></code></a> 提供基于索引和不区分大小写的基于名称的访问，几乎没有内存开销。 它可能比您自己的基于字典的自定义方法甚至基于 db_row 的解决方案更好。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="attribute">
|
||
<dt id="sqlite3.Connection.text_factory">
|
||
<code class="sig-name descname">text_factory</code><a class="headerlink" href="#sqlite3.Connection.text_factory" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>使用此属性可以控制为 <code class="docutils literal notranslate"><span class="pre">TEXT</span></code> 数据类型返回的对象。 默认情况下，此属性设置为 <a class="reference internal" href="stdtypes.html#str" title="str"><code class="xref py py-class docutils literal notranslate"><span class="pre">str</span></code></a> 和 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块将返回 <code class="docutils literal notranslate"><span class="pre">TEXT</span></code> 的 Unicode 对象。 如果要返回字节串，可以将其设置为 <a class="reference internal" href="stdtypes.html#bytes" title="bytes"><code class="xref py py-class docutils literal notranslate"><span class="pre">bytes</span></code></a>。</p>
|
||
<p>您还可以将其设置为接受单个 bytestring 参数的任何其他可调用对象，并返回结果对象。</p>
|
||
<p>请参阅以下示例代码以进行说明：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
|
||
<span class="n">AUSTRIA</span> <span class="o">=</span> <span class="s2">"</span><span class="se">\xd6</span><span class="s2">sterreich"</span>
|
||
|
||
<span class="c1"># by default, rows are returned as Unicode</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select ?"</span><span class="p">,</span> <span class="p">(</span><span class="n">AUSTRIA</span><span class="p">,))</span>
|
||
<span class="n">row</span> <span class="o">=</span> <span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
|
||
<span class="k">assert</span> <span class="n">row</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span> <span class="o">==</span> <span class="n">AUSTRIA</span>
|
||
|
||
<span class="c1"># but we can make sqlite3 always return bytestrings ...</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">text_factory</span> <span class="o">=</span> <span class="nb">bytes</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select ?"</span><span class="p">,</span> <span class="p">(</span><span class="n">AUSTRIA</span><span class="p">,))</span>
|
||
<span class="n">row</span> <span class="o">=</span> <span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
|
||
<span class="k">assert</span> <span class="nb">type</span><span class="p">(</span><span class="n">row</span><span class="p">[</span><span class="mi">0</span><span class="p">])</span> <span class="ow">is</span> <span class="nb">bytes</span>
|
||
<span class="c1"># the bytestrings will be encoded in UTF-8, unless you stored garbage in the</span>
|
||
<span class="c1"># database ...</span>
|
||
<span class="k">assert</span> <span class="n">row</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span> <span class="o">==</span> <span class="n">AUSTRIA</span><span class="o">.</span><span class="n">encode</span><span class="p">(</span><span class="s2">"utf-8"</span><span class="p">)</span>
|
||
|
||
<span class="c1"># we can also implement a custom text_factory ...</span>
|
||
<span class="c1"># here we implement one that appends "foo" to all strings</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">text_factory</span> <span class="o">=</span> <span class="k">lambda</span> <span class="n">x</span><span class="p">:</span> <span class="n">x</span><span class="o">.</span><span class="n">decode</span><span class="p">(</span><span class="s2">"utf-8"</span><span class="p">)</span> <span class="o">+</span> <span class="s2">"foo"</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select ?"</span><span class="p">,</span> <span class="p">(</span><span class="s2">"bar"</span><span class="p">,))</span>
|
||
<span class="n">row</span> <span class="o">=</span> <span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
|
||
<span class="k">assert</span> <span class="n">row</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span> <span class="o">==</span> <span class="s2">"barfoo"</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="attribute">
|
||
<dt id="sqlite3.Connection.total_changes">
|
||
<code class="sig-name descname">total_changes</code><a class="headerlink" href="#sqlite3.Connection.total_changes" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>返回自打开数据库连接以来已修改，插入或删除的数据库行的总数。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.iterdump">
|
||
<code class="sig-name descname">iterdump</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.iterdump" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>返回以SQL文本格式转储数据库的迭代器。 保存内存数据库以便以后恢复时很有用。 此函数提供与 <strong class="program">sqlite3</strong> shell 中的 <kbd class="kbd docutils literal notranslate">.dump</kbd> 命令相同的功能。</p>
|
||
<p>示例:</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="c1"># Convert file existing_db.db to SQL dump file dump.sql</span>
|
||
<span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s1">'existing_db.db'</span><span class="p">)</span>
|
||
<span class="k">with</span> <span class="nb">open</span><span class="p">(</span><span class="s1">'dump.sql'</span><span class="p">,</span> <span class="s1">'w'</span><span class="p">)</span> <span class="k">as</span> <span class="n">f</span><span class="p">:</span>
|
||
<span class="k">for</span> <span class="n">line</span> <span class="ow">in</span> <span class="n">con</span><span class="o">.</span><span class="n">iterdump</span><span class="p">():</span>
|
||
<span class="n">f</span><span class="o">.</span><span class="n">write</span><span class="p">(</span><span class="s1">'</span><span class="si">%s</span><span class="se">\n</span><span class="s1">'</span> <span class="o">%</span> <span class="n">line</span><span class="p">)</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Connection.backup">
|
||
<code class="sig-name descname">backup</code><span class="sig-paren">(</span><em class="sig-param">target</em>, <em class="sig-param">*</em>, <em class="sig-param">pages=-1</em>, <em class="sig-param">progress=None</em>, <em class="sig-param">name="main"</em>, <em class="sig-param">sleep=0.250</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.backup" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>即使在 SQLite 数据库被其他客户端访问时，或者同时由同一连接访问，该方法也会对其进行备份。 该副本将写入强制参数 <em>target</em>，该参数必须是另一个 <a class="reference internal" href="#sqlite3.Connection" title="sqlite3.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> 实例。</p>
|
||
<p>默认情况下，或者当 <em>pages</em> 为 <code class="docutils literal notranslate"><span class="pre">0</span></code> 或负整数时，整个数据库将在一个步骤中复制；否则该方法一次循环复制 <em>pages</em> 规定数量的页面。</p>
|
||
<p>如果指定了 <em>progress</em>，则它必须为 <code class="docutils literal notranslate"><span class="pre">None</span></code> 或一个将在每次迭代时附带三个整数参数执行的可调用对象，这三个参数分别是前一次迭代的状态 <em>status</em>，将要拷贝的剩余页数 <em>remaining</em> 以及总页数 <em>total</em>。</p>
|
||
<p><em>name</em> 参数指定将被拷贝的数据库名称：它必须是一个字符串，其内容为表示主数据库的默认值 <code class="docutils literal notranslate"><span class="pre">"main"</span></code>，表示临时数据库的 <code class="docutils literal notranslate"><span class="pre">"temp"</span></code> 或是在 <code class="docutils literal notranslate"><span class="pre">ATTACH</span> <span class="pre">DATABASE</span></code> 语句的 <code class="docutils literal notranslate"><span class="pre">AS</span></code> 关键字之后指定表示附加数据库的名称。</p>
|
||
<p><em>sleep</em> 参数指定在备份剩余页的连续尝试之间要休眠的秒数，可以指定为一个整数或一个浮点数值。</p>
|
||
<p>示例一，将现有数据库复制到另一个数据库中：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="k">def</span> <span class="nf">progress</span><span class="p">(</span><span class="n">status</span><span class="p">,</span> <span class="n">remaining</span><span class="p">,</span> <span class="n">total</span><span class="p">):</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="sa">f</span><span class="s1">'Copied </span><span class="si">{</span><span class="n">total</span><span class="o">-</span><span class="n">remaining</span><span class="si">}</span><span class="s1"> of </span><span class="si">{</span><span class="n">total</span><span class="si">}</span><span class="s1"> pages...'</span><span class="p">)</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s1">'existing_db.db'</span><span class="p">)</span>
|
||
<span class="n">bck</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s1">'backup.db'</span><span class="p">)</span>
|
||
<span class="k">with</span> <span class="n">bck</span><span class="p">:</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">backup</span><span class="p">(</span><span class="n">bck</span><span class="p">,</span> <span class="n">pages</span><span class="o">=</span><span class="mi">1</span><span class="p">,</span> <span class="n">progress</span><span class="o">=</span><span class="n">progress</span><span class="p">)</span>
|
||
<span class="n">bck</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>示例二，将现有数据库复制到临时副本中：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="n">source</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s1">'existing_db.db'</span><span class="p">)</span>
|
||
<span class="n">dest</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s1">':memory:'</span><span class="p">)</span>
|
||
<span class="n">source</span><span class="o">.</span><span class="n">backup</span><span class="p">(</span><span class="n">dest</span><span class="p">)</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>可用性：SQLite 3.6.11 或以上版本</p>
|
||
<div class="versionadded">
|
||
<p><span class="versionmodified added">3.7 新版功能.</span></p>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
</dd></dl>
|
||
|
||
</section>
|
||
<section id="cursor-objects">
|
||
<span id="sqlite3-cursor-objects"></span><h2>Cursor 对象<a class="headerlink" href="#cursor-objects" title="永久链接至标题">¶</a></h2>
|
||
<dl class="class">
|
||
<dt id="sqlite3.Cursor">
|
||
<em class="property">class </em><code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">Cursor</code><a class="headerlink" href="#sqlite3.Cursor" title="永久链接至目标">¶</a></dt>
|
||
<dd><p><a class="reference internal" href="#sqlite3.Cursor" title="sqlite3.Cursor"><code class="xref py py-class docutils literal notranslate"><span class="pre">Cursor</span></code></a> 游标实例具有以下属性和方法。</p>
|
||
<span class="target" id="index-2"></span><span class="target" id="index-3"></span><dl class="method">
|
||
<dt id="sqlite3.Cursor.execute">
|
||
<code class="sig-name descname">execute</code><span class="sig-paren">(</span><em class="sig-param">sql</em><span class="optional">[</span>, <em class="sig-param">parameters</em><span class="optional">]</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Cursor.execute" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>执行SQL语句。 可以是参数化 SQL 语句（即，在 SQL 语句中使用占位符）。<a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块支持两种占位符：问号（qmark风格）和命名占位符（命名风格）。</p>
|
||
<p>以下是两种风格的示例：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"create table people (name_last, age)"</span><span class="p">)</span>
|
||
|
||
<span class="n">who</span> <span class="o">=</span> <span class="s2">"Yeltsin"</span>
|
||
<span class="n">age</span> <span class="o">=</span> <span class="mi">72</span>
|
||
|
||
<span class="c1"># This is the qmark style:</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"insert into people values (?, ?)"</span><span class="p">,</span> <span class="p">(</span><span class="n">who</span><span class="p">,</span> <span class="n">age</span><span class="p">))</span>
|
||
|
||
<span class="c1"># And this is the named style:</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select * from people where name_last=:who and age=:age"</span><span class="p">,</span> <span class="p">{</span><span class="s2">"who"</span><span class="p">:</span> <span class="n">who</span><span class="p">,</span> <span class="s2">"age"</span><span class="p">:</span> <span class="n">age</span><span class="p">})</span>
|
||
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">())</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
<p><a class="reference internal" href="#sqlite3.Cursor.execute" title="sqlite3.Cursor.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> 将只执行一条单独的 SQL 语句。 如果你尝试用它执行超过一条语句，将会引发 <a class="reference internal" href="#sqlite3.Warning" title="sqlite3.Warning"><code class="xref py py-exc docutils literal notranslate"><span class="pre">Warning</span></code></a>。 如果你想要用一次调用执行多条 SQL 语句请使用 <a class="reference internal" href="#sqlite3.Cursor.executescript" title="sqlite3.Cursor.executescript"><code class="xref py py-meth docutils literal notranslate"><span class="pre">executescript()</span></code></a>。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Cursor.executemany">
|
||
<code class="sig-name descname">executemany</code><span class="sig-paren">(</span><em class="sig-param">sql</em>, <em class="sig-param">seq_of_parameters</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Cursor.executemany" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>基于在序列 <em>seq_of_parameters</em> 中找到的所有形参序列或映射执行一条 SQL 命令。 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块还允许使用 <a class="reference internal" href="../glossary.html#term-iterator"><span class="xref std std-term">iterator</span></a> 代替序列来产生形参。</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="k">class</span> <span class="nc">IterChars</span><span class="p">:</span>
|
||
<span class="k">def</span> <span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
|
||
<span class="bp">self</span><span class="o">.</span><span class="n">count</span> <span class="o">=</span> <span class="nb">ord</span><span class="p">(</span><span class="s1">'a'</span><span class="p">)</span>
|
||
|
||
<span class="k">def</span> <span class="fm">__iter__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
|
||
<span class="k">return</span> <span class="bp">self</span>
|
||
|
||
<span class="k">def</span> <span class="fm">__next__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
|
||
<span class="k">if</span> <span class="bp">self</span><span class="o">.</span><span class="n">count</span> <span class="o">></span> <span class="nb">ord</span><span class="p">(</span><span class="s1">'z'</span><span class="p">):</span>
|
||
<span class="k">raise</span> <span class="ne">StopIteration</span>
|
||
<span class="bp">self</span><span class="o">.</span><span class="n">count</span> <span class="o">+=</span> <span class="mi">1</span>
|
||
<span class="k">return</span> <span class="p">(</span><span class="nb">chr</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">count</span> <span class="o">-</span> <span class="mi">1</span><span class="p">),)</span> <span class="c1"># this is a 1-tuple</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"create table characters(c)"</span><span class="p">)</span>
|
||
|
||
<span class="n">theIter</span> <span class="o">=</span> <span class="n">IterChars</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">executemany</span><span class="p">(</span><span class="s2">"insert into characters(c) values (?)"</span><span class="p">,</span> <span class="n">theIter</span><span class="p">)</span>
|
||
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select c from characters"</span><span class="p">)</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">cur</span><span class="o">.</span><span class="n">fetchall</span><span class="p">())</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>这是一个使用生成器 <a class="reference internal" href="../glossary.html#term-generator"><span class="xref std std-term">generator</span></a> 的简短示例：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
<span class="kn">import</span> <span class="nn">string</span>
|
||
|
||
<span class="k">def</span> <span class="nf">char_generator</span><span class="p">():</span>
|
||
<span class="k">for</span> <span class="n">c</span> <span class="ow">in</span> <span class="n">string</span><span class="o">.</span><span class="n">ascii_lowercase</span><span class="p">:</span>
|
||
<span class="k">yield</span> <span class="p">(</span><span class="n">c</span><span class="p">,)</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"create table characters(c)"</span><span class="p">)</span>
|
||
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">executemany</span><span class="p">(</span><span class="s2">"insert into characters(c) values (?)"</span><span class="p">,</span> <span class="n">char_generator</span><span class="p">())</span>
|
||
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select c from characters"</span><span class="p">)</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">cur</span><span class="o">.</span><span class="n">fetchall</span><span class="p">())</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Cursor.executescript">
|
||
<code class="sig-name descname">executescript</code><span class="sig-paren">(</span><em class="sig-param">sql_script</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Cursor.executescript" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这是一个非标准的便捷方法，可用于一次执行多条 SQL 语句。 它会首先执行一条 <code class="docutils literal notranslate"><span class="pre">COMMIT</span></code> 语句，再执行以形参方式获取的 SQL 脚本。</p>
|
||
<p><em>sql_script</em> 可以是一个 <a class="reference internal" href="stdtypes.html#str" title="str"><code class="xref py py-class docutils literal notranslate"><span class="pre">str</span></code></a> 类的实例。</p>
|
||
<p>示例:</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">executescript</span><span class="p">(</span><span class="s2">"""</span>
|
||
<span class="s2"> create table person(</span>
|
||
<span class="s2"> firstname,</span>
|
||
<span class="s2"> lastname,</span>
|
||
<span class="s2"> age</span>
|
||
<span class="s2"> );</span>
|
||
|
||
<span class="s2"> create table book(</span>
|
||
<span class="s2"> title,</span>
|
||
<span class="s2"> author,</span>
|
||
<span class="s2"> published</span>
|
||
<span class="s2"> );</span>
|
||
|
||
<span class="s2"> insert into book(title, author, published)</span>
|
||
<span class="s2"> values (</span>
|
||
<span class="s2"> 'Dirk Gently''s Holistic Detective Agency',</span>
|
||
<span class="s2"> 'Douglas Adams',</span>
|
||
<span class="s2"> 1987</span>
|
||
<span class="s2"> );</span>
|
||
<span class="s2"> """</span><span class="p">)</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Cursor.fetchone">
|
||
<code class="sig-name descname">fetchone</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Cursor.fetchone" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>获取一个查询结果集的下一行，返回一个单独序列，或是在没有更多可用数据时返回 <a class="reference internal" href="constants.html#None" title="None"><code class="xref py py-const docutils literal notranslate"><span class="pre">None</span></code></a>。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Cursor.fetchmany">
|
||
<code class="sig-name descname">fetchmany</code><span class="sig-paren">(</span><em class="sig-param">size=cursor.arraysize</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Cursor.fetchmany" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>获取下一个多行查询结果集，返回一个列表。 当没有更多可用行时将返回一个空列表。</p>
|
||
<p>每次调用获取的行数由 <em>size</em> 形参指定。 如果没有给出该形参，则由 cursor 的 arraysize 决定要获取的行数。 此方法将基于 size 形参值尝试获取指定数量的行。 如果获取不到指定的行数，则可能返回较少的行。</p>
|
||
<p>请注意 <em>size</em> 形参会涉及到性能方面的考虑。为了获得优化的性能，通常最好是使用 arraysize 属性。 如果使用 <em>size</em> 形参，则最好在从一个 <a class="reference internal" href="#sqlite3.Cursor.fetchmany" title="sqlite3.Cursor.fetchmany"><code class="xref py py-meth docutils literal notranslate"><span class="pre">fetchmany()</span></code></a> 调用到下一个调用之间保持相同的值。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Cursor.fetchall">
|
||
<code class="sig-name descname">fetchall</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Cursor.fetchall" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>获取一个查询结果的所有（剩余）行，返回一个列表。 请注意 cursor 的 arraysize 属性会影响此操作的执行效率。 当没有可用行时将返回一个空列表。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="method">
|
||
<dt id="sqlite3.Cursor.close">
|
||
<code class="sig-name descname">close</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Cursor.close" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>立即关闭 cursor（而不是在当 <code class="docutils literal notranslate"><span class="pre">__del__</span></code> 被调用的时候）。</p>
|
||
<p>从这一时刻起该 cursor 将不再可用，如果再尝试用该 cursor 执行任何操作将引发 <a class="reference internal" href="#sqlite3.ProgrammingError" title="sqlite3.ProgrammingError"><code class="xref py py-exc docutils literal notranslate"><span class="pre">ProgrammingError</span></code></a> 异常。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="attribute">
|
||
<dt id="sqlite3.Cursor.rowcount">
|
||
<code class="sig-name descname">rowcount</code><a class="headerlink" href="#sqlite3.Cursor.rowcount" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>虽然 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块的 <a class="reference internal" href="#sqlite3.Cursor" title="sqlite3.Cursor"><code class="xref py py-class docutils literal notranslate"><span class="pre">Cursor</span></code></a> 类实现了此属性，但数据库引擎本身对于确定 "受影响行"/"已选择行" 的支持并不完善。</p>
|
||
<p>对于 <a class="reference internal" href="#sqlite3.Cursor.executemany" title="sqlite3.Cursor.executemany"><code class="xref py py-meth docutils literal notranslate"><span class="pre">executemany()</span></code></a> 语句，修改行数会被汇总至 <a class="reference internal" href="#sqlite3.Cursor.rowcount" title="sqlite3.Cursor.rowcount"><code class="xref py py-attr docutils literal notranslate"><span class="pre">rowcount</span></code></a>。</p>
|
||
<p>根据 Python DB API 规格描述的要求，<a class="reference internal" href="#sqlite3.Cursor.rowcount" title="sqlite3.Cursor.rowcount"><code class="xref py py-attr docutils literal notranslate"><span class="pre">rowcount</span></code></a> 属性 "当未在 cursor 上执行 <code class="docutils literal notranslate"><span class="pre">executeXX()</span></code> 或者上一次操作的 rowcount 不是由接口确定时为 -1"。 这包括 <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> 语句，因为我们无法确定一次查询将产生的行计数，而要等获取了所有行时才会知道。</p>
|
||
<p>在 SQLite 的 3.6.5 版之前，如果你执行 <code class="docutils literal notranslate"><span class="pre">DELETE</span> <span class="pre">FROM</span> <span class="pre">table</span></code> 时不附带任何条件，则 <a class="reference internal" href="#sqlite3.Cursor.rowcount" title="sqlite3.Cursor.rowcount"><code class="xref py py-attr docutils literal notranslate"><span class="pre">rowcount</span></code></a> 将被设为 0。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="attribute">
|
||
<dt id="sqlite3.Cursor.lastrowid">
|
||
<code class="sig-name descname">lastrowid</code><a class="headerlink" href="#sqlite3.Cursor.lastrowid" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这个只读属性会提供最近修改行的 rowid。 它只在你使用 <a class="reference internal" href="#sqlite3.Cursor.execute" title="sqlite3.Cursor.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> 方法执行 <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> 或 <code class="docutils literal notranslate"><span class="pre">REPLACE</span></code> 语句时会被设置。 对于 <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> 或 <code class="docutils literal notranslate"><span class="pre">REPLACE</span></code> 以外的操作或者当 <a class="reference internal" href="#sqlite3.Cursor.executemany" title="sqlite3.Cursor.executemany"><code class="xref py py-meth docutils literal notranslate"><span class="pre">executemany()</span></code></a> 被调用时，<a class="reference internal" href="#sqlite3.Cursor.lastrowid" title="sqlite3.Cursor.lastrowid"><code class="xref py py-attr docutils literal notranslate"><span class="pre">lastrowid</span></code></a> 会被设为 <a class="reference internal" href="constants.html#None" title="None"><code class="xref py py-const docutils literal notranslate"><span class="pre">None</span></code></a>。</p>
|
||
<p>如果 <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> 或 <code class="docutils literal notranslate"><span class="pre">REPLACE</span></code> 语句操作失败则将返回上一次成功操作的 rowid。</p>
|
||
<div class="versionchanged">
|
||
<p><span class="versionmodified changed">在 3.6 版更改: </span>增加了 <code class="docutils literal notranslate"><span class="pre">REPLACE</span></code> 语句的支持。</p>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<dl class="attribute">
|
||
<dt id="sqlite3.Cursor.arraysize">
|
||
<code class="sig-name descname">arraysize</code><a class="headerlink" href="#sqlite3.Cursor.arraysize" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>用于控制 <a class="reference internal" href="#sqlite3.Cursor.fetchmany" title="sqlite3.Cursor.fetchmany"><code class="xref py py-meth docutils literal notranslate"><span class="pre">fetchmany()</span></code></a> 返回行数的可读取/写入属性。 该属性的默认值为 1，表示每次调用将获取单独一行。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="attribute">
|
||
<dt id="sqlite3.Cursor.description">
|
||
<code class="sig-name descname">description</code><a class="headerlink" href="#sqlite3.Cursor.description" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这个只读属性将提供上一次查询的列名称。 为了与 Python DB API 保持兼容，它会为每个列返回一个 7 元组，每个元组的最后六个条目均为 <a class="reference internal" href="constants.html#None" title="None"><code class="xref py py-const docutils literal notranslate"><span class="pre">None</span></code></a>。</p>
|
||
<p>对于没有任何匹配行的 <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> 语句同样会设置该属性。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="attribute">
|
||
<dt id="sqlite3.Cursor.connection">
|
||
<code class="sig-name descname">connection</code><a class="headerlink" href="#sqlite3.Cursor.connection" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>这个只读属性将提供 <a class="reference internal" href="#sqlite3.Cursor" title="sqlite3.Cursor"><code class="xref py py-class docutils literal notranslate"><span class="pre">Cursor</span></code></a> 对象所使用的 SQLite 数据库 <a class="reference internal" href="#sqlite3.Connection" title="sqlite3.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>。 通过调用 <a class="reference internal" href="#sqlite3.Connection.cursor" title="sqlite3.Connection.cursor"><code class="xref py py-meth docutils literal notranslate"><span class="pre">con.cursor()</span></code></a> 创建的 <a class="reference internal" href="#sqlite3.Cursor" title="sqlite3.Cursor"><code class="xref py py-class docutils literal notranslate"><span class="pre">Cursor</span></code></a> 对象所包含的 <a class="reference internal" href="#sqlite3.Cursor.connection" title="sqlite3.Cursor.connection"><code class="xref py py-attr docutils literal notranslate"><span class="pre">connection</span></code></a> 属性将指向 <em>con</em>:</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="gp">>>> </span><span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">connection</span> <span class="o">==</span> <span class="n">con</span>
|
||
<span class="go">True</span>
|
||
</pre></div>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
</dd></dl>
|
||
|
||
</section>
|
||
<section id="row-objects">
|
||
<span id="sqlite3-row-objects"></span><h2>行对象<a class="headerlink" href="#row-objects" title="永久链接至标题">¶</a></h2>
|
||
<dl class="class">
|
||
<dt id="sqlite3.Row">
|
||
<em class="property">class </em><code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">Row</code><a class="headerlink" href="#sqlite3.Row" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>一个 <a class="reference internal" href="#sqlite3.Row" title="sqlite3.Row"><code class="xref py py-class docutils literal notranslate"><span class="pre">Row</span></code></a> 实例，该实例将作为用于 <a class="reference internal" href="#sqlite3.Connection" title="sqlite3.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> 对象的高度优化的 <a class="reference internal" href="#sqlite3.Connection.row_factory" title="sqlite3.Connection.row_factory"><code class="xref py py-attr docutils literal notranslate"><span class="pre">row_factory</span></code></a>。 它的大部分行为都会模仿元组的特性。</p>
|
||
<p>它支持使用列名称的映射访问以及索引、迭代、文本表示、相等检测和 <a class="reference internal" href="functions.html#len" title="len"><code class="xref py py-func docutils literal notranslate"><span class="pre">len()</span></code></a> 等操作。</p>
|
||
<p>如果两个 <a class="reference internal" href="#sqlite3.Row" title="sqlite3.Row"><code class="xref py py-class docutils literal notranslate"><span class="pre">Row</span></code></a> 对象具有完全相同的列并且其成员均相等，则它们的比较结果为相等。</p>
|
||
<dl class="method">
|
||
<dt id="sqlite3.Row.keys">
|
||
<code class="sig-name descname">keys</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Row.keys" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>此方法会在一次查询之后立即返回一个列名称的列表，它是 <a class="reference internal" href="#sqlite3.Cursor.description" title="sqlite3.Cursor.description"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Cursor.description</span></code></a> 中每个元组的第一个成员。</p>
|
||
</dd></dl>
|
||
|
||
<div class="versionchanged">
|
||
<p><span class="versionmodified changed">在 3.5 版更改: </span>添加了对切片操作的支持。</p>
|
||
</div>
|
||
</dd></dl>
|
||
|
||
<p>让我们假设我们如上面的例子所示初始化一个表:</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'''create table stocks</span>
|
||
<span class="s1">(date text, trans text, symbol text,</span>
|
||
<span class="s1"> qty real, price real)'''</span><span class="p">)</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"""insert into stocks</span>
|
||
<span class="s2"> values ('2006-01-05','BUY','RHAT',100,35.14)"""</span><span class="p">)</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>现在我们将 <a class="reference internal" href="#sqlite3.Row" title="sqlite3.Row"><code class="xref py py-class docutils literal notranslate"><span class="pre">Row</span></code></a> 插入:</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">con</span><span class="o">.</span><span class="n">row_factory</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">Row</span>
|
||
<span class="gp">>>> </span><span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'select * from stocks'</span><span class="p">)</span>
|
||
<span class="go"><sqlite3.Cursor object at 0x7f4e7dd8fa80></span>
|
||
<span class="gp">>>> </span><span class="n">r</span> <span class="o">=</span> <span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
|
||
<span class="gp">>>> </span><span class="nb">type</span><span class="p">(</span><span class="n">r</span><span class="p">)</span>
|
||
<span class="go"><class 'sqlite3.Row'></span>
|
||
<span class="gp">>>> </span><span class="nb">tuple</span><span class="p">(</span><span class="n">r</span><span class="p">)</span>
|
||
<span class="go">('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)</span>
|
||
<span class="gp">>>> </span><span class="nb">len</span><span class="p">(</span><span class="n">r</span><span class="p">)</span>
|
||
<span class="go">5</span>
|
||
<span class="gp">>>> </span><span class="n">r</span><span class="p">[</span><span class="mi">2</span><span class="p">]</span>
|
||
<span class="go">'RHAT'</span>
|
||
<span class="gp">>>> </span><span class="n">r</span><span class="o">.</span><span class="n">keys</span><span class="p">()</span>
|
||
<span class="go">['date', 'trans', 'symbol', 'qty', 'price']</span>
|
||
<span class="gp">>>> </span><span class="n">r</span><span class="p">[</span><span class="s1">'qty'</span><span class="p">]</span>
|
||
<span class="go">100.0</span>
|
||
<span class="gp">>>> </span><span class="k">for</span> <span class="n">member</span> <span class="ow">in</span> <span class="n">r</span><span class="p">:</span>
|
||
<span class="gp">... </span> <span class="nb">print</span><span class="p">(</span><span class="n">member</span><span class="p">)</span>
|
||
<span class="gp">...</span>
|
||
<span class="go">2006-01-05</span>
|
||
<span class="go">BUY</span>
|
||
<span class="go">RHAT</span>
|
||
<span class="go">100.0</span>
|
||
<span class="go">35.14</span>
|
||
</pre></div>
|
||
</div>
|
||
</section>
|
||
<section id="exceptions">
|
||
<span id="sqlite3-exceptions"></span><h2>异常<a class="headerlink" href="#exceptions" title="永久链接至标题">¶</a></h2>
|
||
<dl class="exception">
|
||
<dt id="sqlite3.Warning">
|
||
<em class="property">exception </em><code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">Warning</code><a class="headerlink" href="#sqlite3.Warning" title="永久链接至目标">¶</a></dt>
|
||
<dd><p><a class="reference internal" href="exceptions.html#Exception" title="Exception"><code class="xref py py-exc docutils literal notranslate"><span class="pre">Exception</span></code></a> 的一个子类。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="exception">
|
||
<dt id="sqlite3.Error">
|
||
<em class="property">exception </em><code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">Error</code><a class="headerlink" href="#sqlite3.Error" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>此模块中其他异常的基类。 它是 <a class="reference internal" href="exceptions.html#Exception" title="Exception"><code class="xref py py-exc docutils literal notranslate"><span class="pre">Exception</span></code></a> 的一个子类。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="exception">
|
||
<dt id="sqlite3.DatabaseError">
|
||
<em class="property">exception </em><code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">DatabaseError</code><a class="headerlink" href="#sqlite3.DatabaseError" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>针对数据库相关错误引发的异常。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="exception">
|
||
<dt id="sqlite3.IntegrityError">
|
||
<em class="property">exception </em><code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">IntegrityError</code><a class="headerlink" href="#sqlite3.IntegrityError" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>当数据库的关系一致性受到影响时引发的异常。 例如外键检查失败等。 它是 <a class="reference internal" href="#sqlite3.DatabaseError" title="sqlite3.DatabaseError"><code class="xref py py-exc docutils literal notranslate"><span class="pre">DatabaseError</span></code></a> 的子类。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="exception">
|
||
<dt id="sqlite3.ProgrammingError">
|
||
<em class="property">exception </em><code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">ProgrammingError</code><a class="headerlink" href="#sqlite3.ProgrammingError" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>编程错误引发的异常，例如表未找到或已存在，SQL 语句存在语法错误，指定的形参数量错误等。 它是 <a class="reference internal" href="#sqlite3.DatabaseError" title="sqlite3.DatabaseError"><code class="xref py py-exc docutils literal notranslate"><span class="pre">DatabaseError</span></code></a> 的子类。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="exception">
|
||
<dt id="sqlite3.OperationalError">
|
||
<em class="property">exception </em><code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">OperationalError</code><a class="headerlink" href="#sqlite3.OperationalError" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>与数据库操作相关而不一定能受程序员掌控的错误引发的异常，例如发生非预期的连接中断，数据源名称未找到，事务无法被执行等。 它是 <a class="reference internal" href="#sqlite3.DatabaseError" title="sqlite3.DatabaseError"><code class="xref py py-exc docutils literal notranslate"><span class="pre">DatabaseError</span></code></a> 的子类。</p>
|
||
</dd></dl>
|
||
|
||
<dl class="exception">
|
||
<dt id="sqlite3.NotSupportedError">
|
||
<em class="property">exception </em><code class="sig-prename descclassname">sqlite3.</code><code class="sig-name descname">NotSupportedError</code><a class="headerlink" href="#sqlite3.NotSupportedError" title="永久链接至目标">¶</a></dt>
|
||
<dd><p>在使用了某个数据库不支持的方法或数据库 API 时引发的异常，例如在一个不支持事务或禁用了事务的连接上调用 <a class="reference internal" href="#sqlite3.Connection.rollback" title="sqlite3.Connection.rollback"><code class="xref py py-meth docutils literal notranslate"><span class="pre">rollback()</span></code></a> 方法等。 它是 <a class="reference internal" href="#sqlite3.DatabaseError" title="sqlite3.DatabaseError"><code class="xref py py-exc docutils literal notranslate"><span class="pre">DatabaseError</span></code></a> 的子类。</p>
|
||
</dd></dl>
|
||
|
||
</section>
|
||
<section id="sqlite-and-python-types">
|
||
<span id="sqlite3-types"></span><h2>SQLite 与 Python 类型<a class="headerlink" href="#sqlite-and-python-types" title="永久链接至标题">¶</a></h2>
|
||
<section id="introduction">
|
||
<h3>概述<a class="headerlink" href="#introduction" title="永久链接至标题">¶</a></h3>
|
||
<p>SQLite 原生支持如下的类型： <code class="docutils literal notranslate"><span class="pre">NULL</span></code>，<code class="docutils literal notranslate"><span class="pre">INTEGER</span></code>，<code class="docutils literal notranslate"><span class="pre">REAL</span></code>，<code class="docutils literal notranslate"><span class="pre">TEXT</span></code>，<code class="docutils literal notranslate"><span class="pre">BLOB</span></code>。</p>
|
||
<p>因此可以将以下Python类型发送到SQLite而不会出现任何问题：</p>
|
||
<table class="docutils align-default">
|
||
<colgroup>
|
||
<col style="width: 70%" />
|
||
<col style="width: 30%" />
|
||
</colgroup>
|
||
<thead>
|
||
<tr class="row-odd"><th class="head"><p>Python 类型</p></th>
|
||
<th class="head"><p>SQLite 类型</p></th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr class="row-even"><td><p><a class="reference internal" href="constants.html#None" title="None"><code class="xref py py-const docutils literal notranslate"><span class="pre">None</span></code></a></p></td>
|
||
<td><p><code class="docutils literal notranslate"><span class="pre">NULL</span></code></p></td>
|
||
</tr>
|
||
<tr class="row-odd"><td><p><a class="reference internal" href="functions.html#int" title="int"><code class="xref py py-class docutils literal notranslate"><span class="pre">int</span></code></a></p></td>
|
||
<td><p><code class="docutils literal notranslate"><span class="pre">INTEGER</span></code></p></td>
|
||
</tr>
|
||
<tr class="row-even"><td><p><a class="reference internal" href="functions.html#float" title="float"><code class="xref py py-class docutils literal notranslate"><span class="pre">float</span></code></a></p></td>
|
||
<td><p><code class="docutils literal notranslate"><span class="pre">REAL</span></code></p></td>
|
||
</tr>
|
||
<tr class="row-odd"><td><p><a class="reference internal" href="stdtypes.html#str" title="str"><code class="xref py py-class docutils literal notranslate"><span class="pre">str</span></code></a></p></td>
|
||
<td><p><code class="docutils literal notranslate"><span class="pre">TEXT</span></code></p></td>
|
||
</tr>
|
||
<tr class="row-even"><td><p><a class="reference internal" href="stdtypes.html#bytes" title="bytes"><code class="xref py py-class docutils literal notranslate"><span class="pre">bytes</span></code></a></p></td>
|
||
<td><p><code class="docutils literal notranslate"><span class="pre">BLOB</span></code></p></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<p>这是SQLite类型默认转换为Python类型的方式：</p>
|
||
<table class="docutils align-default">
|
||
<colgroup>
|
||
<col style="width: 22%" />
|
||
<col style="width: 78%" />
|
||
</colgroup>
|
||
<thead>
|
||
<tr class="row-odd"><th class="head"><p>SQLite 类型</p></th>
|
||
<th class="head"><p>Python 类型</p></th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">NULL</span></code></p></td>
|
||
<td><p><a class="reference internal" href="constants.html#None" title="None"><code class="xref py py-const docutils literal notranslate"><span class="pre">None</span></code></a></p></td>
|
||
</tr>
|
||
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">INTEGER</span></code></p></td>
|
||
<td><p><a class="reference internal" href="functions.html#int" title="int"><code class="xref py py-class docutils literal notranslate"><span class="pre">int</span></code></a></p></td>
|
||
</tr>
|
||
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">REAL</span></code></p></td>
|
||
<td><p><a class="reference internal" href="functions.html#float" title="float"><code class="xref py py-class docutils literal notranslate"><span class="pre">float</span></code></a></p></td>
|
||
</tr>
|
||
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">TEXT</span></code></p></td>
|
||
<td><p>取决于 <a class="reference internal" href="#sqlite3.Connection.text_factory" title="sqlite3.Connection.text_factory"><code class="xref py py-attr docutils literal notranslate"><span class="pre">text_factory</span></code></a> , 默认为 <a class="reference internal" href="stdtypes.html#str" title="str"><code class="xref py py-class docutils literal notranslate"><span class="pre">str</span></code></a></p></td>
|
||
</tr>
|
||
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">BLOB</span></code></p></td>
|
||
<td><p><a class="reference internal" href="stdtypes.html#bytes" title="bytes"><code class="xref py py-class docutils literal notranslate"><span class="pre">bytes</span></code></a></p></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<p><a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块的类型系统可通过两种方式来扩展：你可以通过对象适配将额外的 Python 类型保存在 SQLite 数据库中，你也可以让 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块通过转换器将 SQLite 类型转换为不同的 Python 类型。</p>
|
||
</section>
|
||
<section id="using-adapters-to-store-additional-python-types-in-sqlite-databases">
|
||
<h3>使用适配器将额外的 Python 类型保存在 SQLite 数据库中。<a class="headerlink" href="#using-adapters-to-store-additional-python-types-in-sqlite-databases" title="永久链接至标题">¶</a></h3>
|
||
<p>如上文所述，SQLite 只包含对有限类型集的原生支持。 要让 SQLite 能使用其他 Python 类型，你必须将它们 <strong>适配</strong> 至 sqlite3 模块所支持的 SQLite 类型中的一种：NoneType, int, float, str, bytes。</p>
|
||
<p>有两种方式能让 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块将某个定制的 Python 类型适配为受支持的类型。</p>
|
||
<section id="letting-your-object-adapt-itself">
|
||
<h4>让对象自行适配<a class="headerlink" href="#letting-your-object-adapt-itself" title="永久链接至标题">¶</a></h4>
|
||
<p>如果类是你自己编写的，这将是一个很好的方式。 假设你有这样一个类:</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Point</span><span class="p">:</span>
|
||
<span class="k">def</span> <span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">):</span>
|
||
<span class="bp">self</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">y</span> <span class="o">=</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>现在你想将这种点对象保存在一个 SQLite 列中。 首先你必须选择一种受支持的类型用来表示点对象。 让我们就用 str 并使用一个分号来分隔坐标值。 然后你需要给你的类加一个方法 <code class="docutils literal notranslate"><span class="pre">__conform__(self,</span> <span class="pre">protocol)</span></code>，它必须返回转换后的值。 形参 <em>protocol</em> 将为 <code class="xref py py-class docutils literal notranslate"><span class="pre">PrepareProtocol</span></code>。</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="k">class</span> <span class="nc">Point</span><span class="p">:</span>
|
||
<span class="k">def</span> <span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">):</span>
|
||
<span class="bp">self</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">y</span> <span class="o">=</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span>
|
||
|
||
<span class="k">def</span> <span class="nf">__conform__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">protocol</span><span class="p">):</span>
|
||
<span class="k">if</span> <span class="n">protocol</span> <span class="ow">is</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">PrepareProtocol</span><span class="p">:</span>
|
||
<span class="k">return</span> <span class="s2">"</span><span class="si">%f</span><span class="s2">;</span><span class="si">%f</span><span class="s2">"</span> <span class="o">%</span> <span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">y</span><span class="p">)</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
|
||
<span class="n">p</span> <span class="o">=</span> <span class="n">Point</span><span class="p">(</span><span class="mf">4.0</span><span class="p">,</span> <span class="o">-</span><span class="mf">3.2</span><span class="p">)</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select ?"</span><span class="p">,</span> <span class="p">(</span><span class="n">p</span><span class="p">,))</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()[</span><span class="mi">0</span><span class="p">])</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
</section>
|
||
<section id="registering-an-adapter-callable">
|
||
<h4>注册可调用的适配器<a class="headerlink" href="#registering-an-adapter-callable" title="永久链接至标题">¶</a></h4>
|
||
<p>另一种可能的做法是创建一个将该类型转换为字符串表示的函数并使用 <a class="reference internal" href="#sqlite3.register_adapter" title="sqlite3.register_adapter"><code class="xref py py-meth docutils literal notranslate"><span class="pre">register_adapter()</span></code></a> 注册该函数。</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="k">class</span> <span class="nc">Point</span><span class="p">:</span>
|
||
<span class="k">def</span> <span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">):</span>
|
||
<span class="bp">self</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">y</span> <span class="o">=</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span>
|
||
|
||
<span class="k">def</span> <span class="nf">adapt_point</span><span class="p">(</span><span class="n">point</span><span class="p">):</span>
|
||
<span class="k">return</span> <span class="s2">"</span><span class="si">%f</span><span class="s2">;</span><span class="si">%f</span><span class="s2">"</span> <span class="o">%</span> <span class="p">(</span><span class="n">point</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="n">point</span><span class="o">.</span><span class="n">y</span><span class="p">)</span>
|
||
|
||
<span class="n">sqlite3</span><span class="o">.</span><span class="n">register_adapter</span><span class="p">(</span><span class="n">Point</span><span class="p">,</span> <span class="n">adapt_point</span><span class="p">)</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
|
||
<span class="n">p</span> <span class="o">=</span> <span class="n">Point</span><span class="p">(</span><span class="mf">4.0</span><span class="p">,</span> <span class="o">-</span><span class="mf">3.2</span><span class="p">)</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select ?"</span><span class="p">,</span> <span class="p">(</span><span class="n">p</span><span class="p">,))</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()[</span><span class="mi">0</span><span class="p">])</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
<p><a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块有两个适配器可用于 Python 的内置 <a class="reference internal" href="datetime.html#datetime.date" title="datetime.date"><code class="xref py py-class docutils literal notranslate"><span class="pre">datetime.date</span></code></a> 和 <a class="reference internal" href="datetime.html#datetime.datetime" title="datetime.datetime"><code class="xref py py-class docutils literal notranslate"><span class="pre">datetime.datetime</span></code></a> 类型。 现在假设我们想要存储 <a class="reference internal" href="datetime.html#datetime.datetime" title="datetime.datetime"><code class="xref py py-class docutils literal notranslate"><span class="pre">datetime.datetime</span></code></a> 对象，但不是表示为 ISO 格式，而是表示为 Unix 时间戳。</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
<span class="kn">import</span> <span class="nn">datetime</span>
|
||
<span class="kn">import</span> <span class="nn">time</span>
|
||
|
||
<span class="k">def</span> <span class="nf">adapt_datetime</span><span class="p">(</span><span class="n">ts</span><span class="p">):</span>
|
||
<span class="k">return</span> <span class="n">time</span><span class="o">.</span><span class="n">mktime</span><span class="p">(</span><span class="n">ts</span><span class="o">.</span><span class="n">timetuple</span><span class="p">())</span>
|
||
|
||
<span class="n">sqlite3</span><span class="o">.</span><span class="n">register_adapter</span><span class="p">(</span><span class="n">datetime</span><span class="o">.</span><span class="n">datetime</span><span class="p">,</span> <span class="n">adapt_datetime</span><span class="p">)</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
|
||
<span class="n">now</span> <span class="o">=</span> <span class="n">datetime</span><span class="o">.</span><span class="n">datetime</span><span class="o">.</span><span class="n">now</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select ?"</span><span class="p">,</span> <span class="p">(</span><span class="n">now</span><span class="p">,))</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()[</span><span class="mi">0</span><span class="p">])</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
</section>
|
||
</section>
|
||
<section id="converting-sqlite-values-to-custom-python-types">
|
||
<h3>将SQLite 值转换为自定义Python 类型<a class="headerlink" href="#converting-sqlite-values-to-custom-python-types" title="永久链接至标题">¶</a></h3>
|
||
<p>编写适配器让你可以将定制的 Python 类型发送给 SQLite。 但要令它真正有用，我们需要实现从 Python 到 SQLite 再回到 Python 的双向转换。</p>
|
||
<p>输入转换器。</p>
|
||
<p>让我们回到 <code class="xref py py-class docutils literal notranslate"><span class="pre">Point</span></code> 类。 我们以字符串形式在 SQLite 中存储了 x 和 y 坐标值。</p>
|
||
<p>首先，我们将定义一个转换器函数，它接受这样的字符串作为形参并根据该参数构造一个 <code class="xref py py-class docutils literal notranslate"><span class="pre">Point</span></code> 对象。</p>
|
||
<div class="admonition note">
|
||
<p class="admonition-title">注解</p>
|
||
<p>转换器函数在调用时 <strong>总是</strong> 会附带一个 <a class="reference internal" href="stdtypes.html#bytes" title="bytes"><code class="xref py py-class docutils literal notranslate"><span class="pre">bytes</span></code></a> 对象，无论你将何种数据类型的值发给 SQLite。</p>
|
||
</div>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">convert_point</span><span class="p">(</span><span class="n">s</span><span class="p">):</span>
|
||
<span class="n">x</span><span class="p">,</span> <span class="n">y</span> <span class="o">=</span> <span class="nb">map</span><span class="p">(</span><span class="nb">float</span><span class="p">,</span> <span class="n">s</span><span class="o">.</span><span class="n">split</span><span class="p">(</span><span class="sa">b</span><span class="s2">";"</span><span class="p">))</span>
|
||
<span class="k">return</span> <span class="n">Point</span><span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">)</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>现在你需要让 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块知道你从数据库中选取的其实是一个点对象。 有两种方式都可以做到这件事：</p>
|
||
<ul class="simple">
|
||
<li><p>隐式的声明类型</p></li>
|
||
<li><p>显式的通过列名</p></li>
|
||
</ul>
|
||
<p>这两种方式会在 <a class="reference internal" href="#sqlite3-module-contents"><span class="std std-ref">模块函数和常量</span></a> 一节中描述，相应条目为 <a class="reference internal" href="#sqlite3.PARSE_DECLTYPES" title="sqlite3.PARSE_DECLTYPES"><code class="xref py py-const docutils literal notranslate"><span class="pre">PARSE_DECLTYPES</span></code></a> 和 <a class="reference internal" href="#sqlite3.PARSE_COLNAMES" title="sqlite3.PARSE_COLNAMES"><code class="xref py py-const docutils literal notranslate"><span class="pre">PARSE_COLNAMES</span></code></a> 常量。</p>
|
||
<p>下面的示例说明了这两种方法。</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="k">class</span> <span class="nc">Point</span><span class="p">:</span>
|
||
<span class="k">def</span> <span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">):</span>
|
||
<span class="bp">self</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">y</span> <span class="o">=</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span>
|
||
|
||
<span class="k">def</span> <span class="fm">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
|
||
<span class="k">return</span> <span class="s2">"(</span><span class="si">%f</span><span class="s2">;</span><span class="si">%f</span><span class="s2">)"</span> <span class="o">%</span> <span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">y</span><span class="p">)</span>
|
||
|
||
<span class="k">def</span> <span class="nf">adapt_point</span><span class="p">(</span><span class="n">point</span><span class="p">):</span>
|
||
<span class="k">return</span> <span class="p">(</span><span class="s2">"</span><span class="si">%f</span><span class="s2">;</span><span class="si">%f</span><span class="s2">"</span> <span class="o">%</span> <span class="p">(</span><span class="n">point</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="n">point</span><span class="o">.</span><span class="n">y</span><span class="p">))</span><span class="o">.</span><span class="n">encode</span><span class="p">(</span><span class="s1">'ascii'</span><span class="p">)</span>
|
||
|
||
<span class="k">def</span> <span class="nf">convert_point</span><span class="p">(</span><span class="n">s</span><span class="p">):</span>
|
||
<span class="n">x</span><span class="p">,</span> <span class="n">y</span> <span class="o">=</span> <span class="nb">list</span><span class="p">(</span><span class="nb">map</span><span class="p">(</span><span class="nb">float</span><span class="p">,</span> <span class="n">s</span><span class="o">.</span><span class="n">split</span><span class="p">(</span><span class="sa">b</span><span class="s2">";"</span><span class="p">)))</span>
|
||
<span class="k">return</span> <span class="n">Point</span><span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">)</span>
|
||
|
||
<span class="c1"># Register the adapter</span>
|
||
<span class="n">sqlite3</span><span class="o">.</span><span class="n">register_adapter</span><span class="p">(</span><span class="n">Point</span><span class="p">,</span> <span class="n">adapt_point</span><span class="p">)</span>
|
||
|
||
<span class="c1"># Register the converter</span>
|
||
<span class="n">sqlite3</span><span class="o">.</span><span class="n">register_converter</span><span class="p">(</span><span class="s2">"point"</span><span class="p">,</span> <span class="n">convert_point</span><span class="p">)</span>
|
||
|
||
<span class="n">p</span> <span class="o">=</span> <span class="n">Point</span><span class="p">(</span><span class="mf">4.0</span><span class="p">,</span> <span class="o">-</span><span class="mf">3.2</span><span class="p">)</span>
|
||
|
||
<span class="c1">#########################</span>
|
||
<span class="c1"># 1) Using declared types</span>
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">,</span> <span class="n">detect_types</span><span class="o">=</span><span class="n">sqlite3</span><span class="o">.</span><span class="n">PARSE_DECLTYPES</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"create table test(p point)"</span><span class="p">)</span>
|
||
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"insert into test(p) values (?)"</span><span class="p">,</span> <span class="p">(</span><span class="n">p</span><span class="p">,))</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select p from test"</span><span class="p">)</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="s2">"with declared types:"</span><span class="p">,</span> <span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()[</span><span class="mi">0</span><span class="p">])</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
|
||
<span class="c1">#######################</span>
|
||
<span class="c1"># 1) Using column names</span>
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">,</span> <span class="n">detect_types</span><span class="o">=</span><span class="n">sqlite3</span><span class="o">.</span><span class="n">PARSE_COLNAMES</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"create table test(p)"</span><span class="p">)</span>
|
||
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"insert into test(p) values (?)"</span><span class="p">,</span> <span class="p">(</span><span class="n">p</span><span class="p">,))</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'select p as "p [point]" from test'</span><span class="p">)</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="s2">"with column names:"</span><span class="p">,</span> <span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()[</span><span class="mi">0</span><span class="p">])</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
</section>
|
||
<section id="default-adapters-and-converters">
|
||
<h3>默认适配器和转换器<a class="headerlink" href="#default-adapters-and-converters" title="永久链接至标题">¶</a></h3>
|
||
<p>对于 datetime 模块中的 date 和 datetime 类型已提供了默认的适配器。 它们将会以 ISO 日期/ISO 时间戳的形式发给 SQLite。</p>
|
||
<p>默认转换器使用的注册名称是针对 <a class="reference internal" href="datetime.html#datetime.date" title="datetime.date"><code class="xref py py-class docutils literal notranslate"><span class="pre">datetime.date</span></code></a> 的 "date" 和针对 <a class="reference internal" href="datetime.html#datetime.datetime" title="datetime.datetime"><code class="xref py py-class docutils literal notranslate"><span class="pre">datetime.datetime</span></code></a> 的 "timestamp"。</p>
|
||
<p>通过这种方式，你可以在大多数情况下使用 Python 的 date/timestamp 对象而无须任何额外处理。 适配器的格式还与实验性的 SQLite date/time 函数兼容。</p>
|
||
<p>下面的示例演示了这一点。</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
<span class="kn">import</span> <span class="nn">datetime</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">,</span> <span class="n">detect_types</span><span class="o">=</span><span class="n">sqlite3</span><span class="o">.</span><span class="n">PARSE_DECLTYPES</span><span class="o">|</span><span class="n">sqlite3</span><span class="o">.</span><span class="n">PARSE_COLNAMES</span><span class="p">)</span>
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"create table test(d date, ts timestamp)"</span><span class="p">)</span>
|
||
|
||
<span class="n">today</span> <span class="o">=</span> <span class="n">datetime</span><span class="o">.</span><span class="n">date</span><span class="o">.</span><span class="n">today</span><span class="p">()</span>
|
||
<span class="n">now</span> <span class="o">=</span> <span class="n">datetime</span><span class="o">.</span><span class="n">datetime</span><span class="o">.</span><span class="n">now</span><span class="p">()</span>
|
||
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"insert into test(d, ts) values (?, ?)"</span><span class="p">,</span> <span class="p">(</span><span class="n">today</span><span class="p">,</span> <span class="n">now</span><span class="p">))</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select d, ts from test"</span><span class="p">)</span>
|
||
<span class="n">row</span> <span class="o">=</span> <span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">today</span><span class="p">,</span> <span class="s2">"=>"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="mi">0</span><span class="p">],</span> <span class="nb">type</span><span class="p">(</span><span class="n">row</span><span class="p">[</span><span class="mi">0</span><span class="p">]))</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">now</span><span class="p">,</span> <span class="s2">"=>"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="mi">1</span><span class="p">],</span> <span class="nb">type</span><span class="p">(</span><span class="n">row</span><span class="p">[</span><span class="mi">1</span><span class="p">]))</span>
|
||
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'select current_date as "d [date]", current_timestamp as "ts [timestamp]"'</span><span class="p">)</span>
|
||
<span class="n">row</span> <span class="o">=</span> <span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="s2">"current_date"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="mi">0</span><span class="p">],</span> <span class="nb">type</span><span class="p">(</span><span class="n">row</span><span class="p">[</span><span class="mi">0</span><span class="p">]))</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="s2">"current_timestamp"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="mi">1</span><span class="p">],</span> <span class="nb">type</span><span class="p">(</span><span class="n">row</span><span class="p">[</span><span class="mi">1</span><span class="p">]))</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>如果存储在 SQLite 中的时间戳的小数位多于 6 个数字，则时间戳转换器会将该值截断至微秒精度。</p>
|
||
</section>
|
||
</section>
|
||
<section id="controlling-transactions">
|
||
<span id="sqlite3-controlling-transactions"></span><h2>控制事务<a class="headerlink" href="#controlling-transactions" title="永久链接至标题">¶</a></h2>
|
||
<p>底层的 <code class="docutils literal notranslate"><span class="pre">sqlite3</span></code> 库默认会以 <code class="docutils literal notranslate"><span class="pre">autocommit</span></code> 模式运行，但 Python 的 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块默认则不使用此模式。</p>
|
||
<p><code class="docutils literal notranslate"><span class="pre">autocommit</span></code> 模式意味着修改数据库的操作会立即生效。 <code class="docutils literal notranslate"><span class="pre">BEGIN</span></code> 或 <code class="docutils literal notranslate"><span class="pre">SAVEPOINT</span></code> 语句会禁用 <code class="docutils literal notranslate"><span class="pre">autocommit</span></code> 模式，而用于结束外层事务的 <code class="docutils literal notranslate"><span class="pre">COMMIT</span></code>, <code class="docutils literal notranslate"><span class="pre">ROLLBACK</span></code> 或 <code class="docutils literal notranslate"><span class="pre">RELEASE</span></code> 则会恢复 <code class="docutils literal notranslate"><span class="pre">autocommit</span></code> 模式。</p>
|
||
<p>Python 的 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块默认会在数据修改语言 (DML) 类语句 (即 <code class="docutils literal notranslate"><span class="pre">INSERT</span></code>/<code class="docutils literal notranslate"><span class="pre">UPDATE</span></code>/<code class="docutils literal notranslate"><span class="pre">DELETE</span></code>/<code class="docutils literal notranslate"><span class="pre">REPLACE</span></code>) 之前隐式地执行一条 <code class="docutils literal notranslate"><span class="pre">BEGIN</span></code> 语句。</p>
|
||
<p>你可以控制 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 隐式执行的 <code class="docutils literal notranslate"><span class="pre">BEGIN</span></code> 语句的种类，具体做法是通过将 <em>isolation_level</em> 形参传给 <a class="reference internal" href="#sqlite3.connect" title="sqlite3.connect"><code class="xref py py-func docutils literal notranslate"><span class="pre">connect()</span></code></a> 调用，或者通过指定连接的 <code class="xref py py-attr docutils literal notranslate"><span class="pre">isolation_level</span></code> 属性。 如果你没有指定 <em>isolation_level</em>，将使用基本的 <code class="docutils literal notranslate"><span class="pre">BEGIN</span></code>，它等价于指定 <code class="docutils literal notranslate"><span class="pre">DEFERRED</span></code>。 其他可能的值为 <code class="docutils literal notranslate"><span class="pre">IMMEDIATE</span></code> 和 <code class="docutils literal notranslate"><span class="pre">EXCLUSIVE</span></code>。</p>
|
||
<p>你可以禁用 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块的隐式事务管理，具体做法是将 <code class="xref py py-attr docutils literal notranslate"><span class="pre">isolation_level</span></code> 设为 <code class="docutils literal notranslate"><span class="pre">None</span></code>。 这将使得下层的 <code class="docutils literal notranslate"><span class="pre">sqlite3</span></code> 库采用 <code class="docutils literal notranslate"><span class="pre">autocommit</span></code> 模式。 随后你可以通过在代码中显式地使用 <code class="docutils literal notranslate"><span class="pre">BEGIN</span></code>, <code class="docutils literal notranslate"><span class="pre">ROLLBACK</span></code>, <code class="docutils literal notranslate"><span class="pre">SAVEPOINT</span></code> 和 <code class="docutils literal notranslate"><span class="pre">RELEASE</span></code> 语句来完全控制事务状态。</p>
|
||
<div class="versionchanged">
|
||
<p><span class="versionmodified changed">在 3.6 版更改: </span>以前 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 会在 DDL 语句之前隐式地提交未完成事务。 现在则不会再这样做。</p>
|
||
</div>
|
||
</section>
|
||
<section id="using-sqlite3-efficiently">
|
||
<h2>有效使用 <a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a><a class="headerlink" href="#using-sqlite3-efficiently" title="永久链接至标题">¶</a></h2>
|
||
<section id="using-shortcut-methods">
|
||
<h3>使用快捷方式<a class="headerlink" href="#using-shortcut-methods" title="永久链接至标题">¶</a></h3>
|
||
<p>使用 <a class="reference internal" href="#sqlite3.Connection" title="sqlite3.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> 对象的非标准 <code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code>, <code class="xref py py-meth docutils literal notranslate"><span class="pre">executemany()</span></code> 和 <code class="xref py py-meth docutils literal notranslate"><span class="pre">executescript()</span></code> 方法，可以更简洁地编写代码，因为不必显式创建（通常是多余的） <a class="reference internal" href="#sqlite3.Cursor" title="sqlite3.Cursor"><code class="xref py py-class docutils literal notranslate"><span class="pre">Cursor</span></code></a> 对象。相反， <a class="reference internal" href="#sqlite3.Cursor" title="sqlite3.Cursor"><code class="xref py py-class docutils literal notranslate"><span class="pre">Cursor</span></code></a> 对象是隐式创建的，这些快捷方法返回游标对象。这样，只需对 <a class="reference internal" href="#sqlite3.Connection" title="sqlite3.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> 对象调用一次，就能直接执行 <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> 语句并遍历对象。</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="n">persons</span> <span class="o">=</span> <span class="p">[</span>
|
||
<span class="p">(</span><span class="s2">"Hugo"</span><span class="p">,</span> <span class="s2">"Boss"</span><span class="p">),</span>
|
||
<span class="p">(</span><span class="s2">"Calvin"</span><span class="p">,</span> <span class="s2">"Klein"</span><span class="p">)</span>
|
||
<span class="p">]</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
|
||
<span class="c1"># Create the table</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"create table person(firstname, lastname)"</span><span class="p">)</span>
|
||
|
||
<span class="c1"># Fill the table</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">executemany</span><span class="p">(</span><span class="s2">"insert into person(firstname, lastname) values (?, ?)"</span><span class="p">,</span> <span class="n">persons</span><span class="p">)</span>
|
||
|
||
<span class="c1"># Print the table contents</span>
|
||
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">con</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select firstname, lastname from person"</span><span class="p">):</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="n">row</span><span class="p">)</span>
|
||
|
||
<span class="nb">print</span><span class="p">(</span><span class="s2">"I just deleted"</span><span class="p">,</span> <span class="n">con</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"delete from person"</span><span class="p">)</span><span class="o">.</span><span class="n">rowcount</span><span class="p">,</span> <span class="s2">"rows"</span><span class="p">)</span>
|
||
|
||
<span class="c1"># close is not a shortcut method and it's not called automatically,</span>
|
||
<span class="c1"># so the connection object should be closed manually</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
</section>
|
||
<section id="accessing-columns-by-name-instead-of-by-index">
|
||
<h3>通过名称而不是索引访问索引<a class="headerlink" href="#accessing-columns-by-name-instead-of-by-index" title="永久链接至标题">¶</a></h3>
|
||
<p><a class="reference internal" href="#module-sqlite3" title="sqlite3: A DB-API 2.0 implementation using SQLite 3.x."><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a> 模块的一个有用功能是内置的 <a class="reference internal" href="#sqlite3.Row" title="sqlite3.Row"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlite3.Row</span></code></a> 类，该类旨在用作行工厂。</p>
|
||
<p>该类的行装饰器可以用索引（如元组）和不区分大小写的名称访问：</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">row_factory</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">Row</span>
|
||
|
||
<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
|
||
<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select 'John' as name, 42 as age"</span><span class="p">)</span>
|
||
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">cur</span><span class="p">:</span>
|
||
<span class="k">assert</span> <span class="n">row</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span> <span class="o">==</span> <span class="n">row</span><span class="p">[</span><span class="s2">"name"</span><span class="p">]</span>
|
||
<span class="k">assert</span> <span class="n">row</span><span class="p">[</span><span class="s2">"name"</span><span class="p">]</span> <span class="o">==</span> <span class="n">row</span><span class="p">[</span><span class="s2">"nAmE"</span><span class="p">]</span>
|
||
<span class="k">assert</span> <span class="n">row</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span> <span class="o">==</span> <span class="n">row</span><span class="p">[</span><span class="s2">"age"</span><span class="p">]</span>
|
||
<span class="k">assert</span> <span class="n">row</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span> <span class="o">==</span> <span class="n">row</span><span class="p">[</span><span class="s2">"AgE"</span><span class="p">]</span>
|
||
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
</section>
|
||
<section id="using-the-connection-as-a-context-manager">
|
||
<h3>使用连接作为上下文管理器<a class="headerlink" href="#using-the-connection-as-a-context-manager" title="永久链接至标题">¶</a></h3>
|
||
<p>连接对象可以用来作为上下文管理器，它可以自动提交或者回滚事务。如果出现异常，事务会被回滚；否则，事务会被提交。</p>
|
||
<div class="highlight-python3 notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlite3</span>
|
||
|
||
<span class="n">con</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s2">":memory:"</span><span class="p">)</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"create table person (id integer primary key, firstname varchar unique)"</span><span class="p">)</span>
|
||
|
||
<span class="c1"># Successful, con.commit() is called automatically afterwards</span>
|
||
<span class="k">with</span> <span class="n">con</span><span class="p">:</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"insert into person(firstname) values (?)"</span><span class="p">,</span> <span class="p">(</span><span class="s2">"Joe"</span><span class="p">,))</span>
|
||
|
||
<span class="c1"># con.rollback() is called after the with block finishes with an exception, the</span>
|
||
<span class="c1"># exception is still raised and must be caught</span>
|
||
<span class="k">try</span><span class="p">:</span>
|
||
<span class="k">with</span> <span class="n">con</span><span class="p">:</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"insert into person(firstname) values (?)"</span><span class="p">,</span> <span class="p">(</span><span class="s2">"Joe"</span><span class="p">,))</span>
|
||
<span class="k">except</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">IntegrityError</span><span class="p">:</span>
|
||
<span class="nb">print</span><span class="p">(</span><span class="s2">"couldn't add Joe twice"</span><span class="p">)</span>
|
||
|
||
<span class="c1"># Connection object used as context manager only commits or rollbacks transactions,</span>
|
||
<span class="c1"># so the connection object should be closed manually</span>
|
||
<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
<p class="rubric">备注</p>
|
||
<dl class="footnote brackets">
|
||
<dt class="label" id="f1"><span class="brackets">1</span><span class="fn-backref">(<a href="#id1">1</a>,<a href="#id2">2</a>)</span></dt>
|
||
<dd><p>sqlite3 模块的构建默认没有附带可加载扩展支持，因为某些平台（特别是 Mac OS X）上的 SQLite 库在编译时未使用此特性。 要获得可加载扩展支持，你必须传入 <code class="docutils literal notranslate"><span class="pre">--enable-loadable-sqlite-extensions</span></code> 来进行配置。</p>
|
||
</dd>
|
||
</dl>
|
||
</section>
|
||
</section>
|
||
</section>
|
||
|
||
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<div class="sphinxsidebar" role="navigation" aria-label="main navigation">
|
||
<div class="sphinxsidebarwrapper">
|
||
<h3><a href="../contents.html">目录</a></h3>
|
||
<ul>
|
||
<li><a class="reference internal" href="#"><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> --- SQLite 数据库 DB-API 2.0 接口模块</a><ul>
|
||
<li><a class="reference internal" href="#module-functions-and-constants">模块函数和常量</a></li>
|
||
<li><a class="reference internal" href="#connection-objects">连接对象（Connection）</a></li>
|
||
<li><a class="reference internal" href="#cursor-objects">Cursor 对象</a></li>
|
||
<li><a class="reference internal" href="#row-objects">行对象</a></li>
|
||
<li><a class="reference internal" href="#exceptions">异常</a></li>
|
||
<li><a class="reference internal" href="#sqlite-and-python-types">SQLite 与 Python 类型</a><ul>
|
||
<li><a class="reference internal" href="#introduction">概述</a></li>
|
||
<li><a class="reference internal" href="#using-adapters-to-store-additional-python-types-in-sqlite-databases">使用适配器将额外的 Python 类型保存在 SQLite 数据库中。</a><ul>
|
||
<li><a class="reference internal" href="#letting-your-object-adapt-itself">让对象自行适配</a></li>
|
||
<li><a class="reference internal" href="#registering-an-adapter-callable">注册可调用的适配器</a></li>
|
||
</ul>
|
||
</li>
|
||
<li><a class="reference internal" href="#converting-sqlite-values-to-custom-python-types">将SQLite 值转换为自定义Python 类型</a></li>
|
||
<li><a class="reference internal" href="#default-adapters-and-converters">默认适配器和转换器</a></li>
|
||
</ul>
|
||
</li>
|
||
<li><a class="reference internal" href="#controlling-transactions">控制事务</a></li>
|
||
<li><a class="reference internal" href="#using-sqlite3-efficiently">有效使用 <code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code></a><ul>
|
||
<li><a class="reference internal" href="#using-shortcut-methods">使用快捷方式</a></li>
|
||
<li><a class="reference internal" href="#accessing-columns-by-name-instead-of-by-index">通过名称而不是索引访问索引</a></li>
|
||
<li><a class="reference internal" href="#using-the-connection-as-a-context-manager">使用连接作为上下文管理器</a></li>
|
||
</ul>
|
||
</li>
|
||
</ul>
|
||
</li>
|
||
</ul>
|
||
|
||
<h4>上一个主题</h4>
|
||
<p class="topless"><a href="dbm.html"
|
||
title="上一章"><code class="xref py py-mod docutils literal notranslate"><span class="pre">dbm</span></code> --- Unix "数据库" 接口</a></p>
|
||
<h4>下一个主题</h4>
|
||
<p class="topless"><a href="archiving.html"
|
||
title="下一章">数据压缩和存档</a></p>
|
||
<div role="note" aria-label="source link">
|
||
<h3>本页</h3>
|
||
<ul class="this-page-menu">
|
||
<li><a href="../bugs.html">报告 Bug</a></li>
|
||
<li>
|
||
<a href="https://github.com/python/cpython/blob/3.8/Doc/library/sqlite3.rst"
|
||
rel="nofollow">显示源代码
|
||
</a>
|
||
</li>
|
||
</ul>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
<div class="clearer"></div>
|
||
</div>
|
||
<div class="related" role="navigation" aria-label="related navigation">
|
||
<h3>导航</h3>
|
||
<ul>
|
||
<li class="right" style="margin-right: 10px">
|
||
<a href="../genindex.html" title="总目录"
|
||
>索引</a></li>
|
||
<li class="right" >
|
||
<a href="../py-modindex.html" title="Python 模块索引"
|
||
>模块</a> |</li>
|
||
<li class="right" >
|
||
<a href="archiving.html" title="数据压缩和存档"
|
||
>下一页</a> |</li>
|
||
<li class="right" >
|
||
<a href="dbm.html" title="dbm --- Unix "数据库" 接口"
|
||
>上一页</a> |</li>
|
||
|
||
<li><img src="../_static/py.svg" alt="python logo" style="vertical-align: middle; margin-top: -1px"/></li>
|
||
<li><a href="https://www.python.org/">Python</a> »</li>
|
||
<li class="switchers">
|
||
<div class="language_switcher_placeholder"></div>
|
||
<div class="version_switcher_placeholder"></div>
|
||
</li>
|
||
<li>
|
||
|
||
</li>
|
||
<li id="cpython-language-and-version">
|
||
<a href="../index.html">3.8.20 Documentation</a> »
|
||
</li>
|
||
|
||
<li class="nav-item nav-item-1"><a href="index.html" >Python 标准库</a> »</li>
|
||
<li class="nav-item nav-item-2"><a href="persistence.html" >数据持久化</a> »</li>
|
||
<li class="right">
|
||
|
||
|
||
<div class="inline-search" role="search">
|
||
<form class="inline-search" action="../search.html" method="get">
|
||
<input placeholder="快速搜索" aria-label="快速搜索" type="text" name="q" />
|
||
<input type="submit" value="转向" />
|
||
<input type="hidden" name="check_keywords" value="yes" />
|
||
<input type="hidden" name="area" value="default" />
|
||
</form>
|
||
</div>
|
||
|
|
||
</li>
|
||
|
||
</ul>
|
||
</div>
|
||
<div class="footer">
|
||
© <a href="../copyright.html">版权所有</a> 2001-2024, Python Software Foundation.
|
||
<br />
|
||
This page is licensed under the Python Software Foundation License Version 2.
|
||
<br />
|
||
Examples, recipes, and other code in the documentation are additionally licensed under the Zero Clause BSD License.
|
||
<br />
|
||
|
||
<br />
|
||
|
||
The Python Software Foundation is a non-profit corporation.
|
||
<a href="https://www.python.org/psf/donations/">Please donate.</a>
|
||
<br />
|
||
<br />
|
||
|
||
最后更新于 12月 09, 2024.
|
||
<a href="https://docs.python.org/3/bugs.html">Found a bug</a>?
|
||
<br />
|
||
|
||
Created using <a href="https://www.sphinx-doc.org/">Sphinx</a> 2.4.4.
|
||
</div>
|
||
|
||
</body>
|
||
</html> |