Team Ai
Apppublic

parthtamu/rag-code-assistant

sourceHugging Faceupdated 7mo agoView on Hugging Face
0likes
sqlite3.html2984 linesDownload Raw Back to docs
1<!DOCTYPE html>2 3<html lang="en" data-content_root="../">4  <head>5    <meta charset="utf-8" />6    <meta name="viewport" content="width=device-width, initial-scale=1.0" /><meta name="viewport" content="width=device-width, initial-scale=1" />7<meta property="og:title" content="sqlite3 — DB-API 2.0 interface for SQLite databases" />8<meta property="og:type" content="website" />9<meta property="og:url" content="https://docs.python.org/3/library/sqlite3.html" />10<meta property="og:site_name" content="Python documentation" />11<meta property="og:description" content="Source code: Lib/sqlite3/ SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard ..." />12<meta property="og:image:width" content="1146" />13<meta property="og:image:height" content="600" />14<meta property="og:image" content="https://docs.python.org/3.15/_images/social_previews/summary_library_sqlite3_de021cde.png" />15<meta property="og:image:alt" content="Source code: Lib/sqlite3/ SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard ..." />16<meta name="description" content="Source code: Lib/sqlite3/ SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard ..." />17<meta name="twitter:card" content="summary_large_image" />18<meta name="theme-color" content="#3776ab">19 20    <title>sqlite3 — DB-API 2.0 interface for SQLite databases &#8212; Python 3.15.0a6 documentation</title><meta name="viewport" content="width=device-width, initial-scale=1.0">21    22    <link rel="stylesheet" type="text/css" href="../_static/pygments.css?v=b86133f3" />23    <link rel="stylesheet" type="text/css" href="../_static/classic.css?v=234b1a7c" />24    <link rel="stylesheet" type="text/css" href="../_static/pydoctheme.css?v=89a2f22a" />25    <link rel="stylesheet" type="text/css" href="../_static/profiling-sampling-visualization.css?v=0c2600ae" />26    <link id="pygments_dark_css" media="(prefers-color-scheme: dark)" rel="stylesheet" type="text/css" href="../_static/pygments_dark.css?v=5349f25f" />27    28    <script src="../_static/documentation_options.js?v=6b7c9ff5"></script>29    <script src="../_static/doctools.js?v=9bcbadda"></script>30    <script src="../_static/sphinx_highlight.js?v=dc90522c"></script>31    <script src="../_static/profiling-sampling-visualization.js?v=9811ed04"></script>32    33    <script src="../_static/sidebar.js"></script>34    35    <link rel="search" type="application/opensearchdescription+xml"36          title="Search within Python 3.15.0a6 documentation"37          href="../_static/opensearch.xml"/>38    <link rel="author" title="About these documents" href="../about.html" />39    <link rel="index" title="Index" href="../genindex.html" />40    <link rel="search" title="Search" href="../search.html" />41    <link rel="copyright" title="Copyright" href="../copyright.html" />42    <link rel="next" title="Data Compression and Archiving" href="archiving.html" />43    <link rel="prev" title="dbm — Interfaces to Unix “databases”" href="dbm.html" />44    45      46      <script defer file-types="bz2,epub,zip" data-domain="docs.python.org" src="https://analytics.python.org/js/script.file-downloads.outbound-links.js"></script>47      48      <link rel="canonical" href="https://docs.python.org/3/library/sqlite3.html">49      50    51 52    53    <style>54      @media only screen {55        table.full-width-table {56            width: 100%;57        }58      }59    </style>60<link rel="stylesheet" href="../_static/pydoctheme_dark.css" media="(prefers-color-scheme: dark)" id="pydoctheme_dark_css">61    <link rel="shortcut icon" type="image/png" href="../_static/py.svg">62            <script type="text/javascript" src="../_static/copybutton.js"></script>63            <script type="text/javascript" src="../_static/menu.js"></script>64            <script type="text/javascript" src="../_static/search-focus.js"></script>65            <script type="text/javascript" src="../_static/themetoggle.js"></script> 66            <script type="text/javascript" src="../_static/rtd_switcher.js"></script>67            <meta name="readthedocs-addons-api-version" content="1">68 69  </head>70<body>71<div class="mobile-nav">72    <input type="checkbox" id="menuToggler" class="toggler__input" aria-controls="navigation"73           aria-pressed="false" aria-expanded="false" role="button" aria-label="Menu">74    <nav class="nav-content" role="navigation">75        <label for="menuToggler" class="toggler__label">76            <span></span>77        </label>78        <span class="nav-items-wrapper">79            <a href="https://www.python.org/" class="nav-logo">80                <img src="../_static/py.svg" alt="Python logo">81            </a>82            <span class="version_switcher_placeholder"></span>83            <form role="search" class="search" action="../search.html" method="get">84                <svg xmlns="http://www.w3.org/2000/svg" width="20" height="20" viewBox="0 0 24 24" class="search-icon">85                    <path fill-rule="nonzero" fill="currentColor" 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"></path>86                </svg>87                <input placeholder="Quick search" aria-label="Quick search" type="search" name="q">88                <input type="submit" value="Go">89            </form>90        </span>91    </nav>92    <div class="menu-wrapper">93        <nav class="menu" role="navigation" aria-label="main navigation">94            <div class="language_switcher_placeholder"></div>95            96<label class="theme-selector-label">97    Theme98    <select class="theme-selector" oninput="activateTheme(this.value)">99        <option value="auto" selected>Auto</option>100        <option value="light">Light</option>101        <option value="dark">Dark</option>102    </select>103</label>104  <div>105    <h3><a href="../contents.html">Table of Contents</a></h3>106    <ul>107<li><a class="reference internal" href="#"><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> — DB-API 2.0 interface for SQLite databases</a><ul>108<li><a class="reference internal" href="#tutorial">Tutorial</a></li>109<li><a class="reference internal" href="#reference">Reference</a><ul>110<li><a class="reference internal" href="#module-functions">Module functions</a></li>111<li><a class="reference internal" href="#module-constants">Module constants</a></li>112<li><a class="reference internal" href="#connection-objects">Connection objects</a></li>113<li><a class="reference internal" href="#cursor-objects">Cursor objects</a></li>114<li><a class="reference internal" href="#row-objects">Row objects</a></li>115<li><a class="reference internal" href="#blob-objects">Blob objects</a></li>116<li><a class="reference internal" href="#prepareprotocol-objects">PrepareProtocol objects</a></li>117<li><a class="reference internal" href="#exceptions">Exceptions</a></li>118<li><a class="reference internal" href="#sqlite-and-python-types">SQLite and Python types</a></li>119<li><a class="reference internal" href="#default-adapters-and-converters-deprecated">Default adapters and converters (deprecated)</a></li>120<li><a class="reference internal" href="#command-line-interface">Command-line interface</a></li>121</ul>122</li>123<li><a class="reference internal" href="#how-to-guides">How-to guides</a><ul>124<li><a class="reference internal" href="#how-to-use-placeholders-to-bind-values-in-sql-queries">How to use placeholders to bind values in SQL queries</a></li>125<li><a class="reference internal" href="#how-to-adapt-custom-python-types-to-sqlite-values">How to adapt custom Python types to SQLite values</a><ul>126<li><a class="reference internal" href="#how-to-write-adaptable-objects">How to write adaptable objects</a></li>127<li><a class="reference internal" href="#how-to-register-adapter-callables">How to register adapter callables</a></li>128</ul>129</li>130<li><a class="reference internal" href="#how-to-convert-sqlite-values-to-custom-python-types">How to convert SQLite values to custom Python types</a></li>131<li><a class="reference internal" href="#adapter-and-converter-recipes">Adapter and converter recipes</a></li>132<li><a class="reference internal" href="#how-to-use-connection-shortcut-methods">How to use connection shortcut methods</a></li>133<li><a class="reference internal" href="#how-to-use-the-connection-context-manager">How to use the connection context manager</a></li>134<li><a class="reference internal" href="#how-to-work-with-sqlite-uris">How to work with SQLite URIs</a></li>135<li><a class="reference internal" href="#how-to-create-and-use-row-factories">How to create and use row factories</a></li>136<li><a class="reference internal" href="#how-to-handle-non-utf-8-text-encodings">How to handle non-UTF-8 text encodings</a></li>137</ul>138</li>139<li><a class="reference internal" href="#explanation">Explanation</a><ul>140<li><a class="reference internal" href="#transaction-control">Transaction control</a><ul>141<li><a class="reference internal" href="#transaction-control-via-the-autocommit-attribute">Transaction control via the <code class="docutils literal notranslate"><span class="pre">autocommit</span></code> attribute</a></li>142<li><a class="reference internal" href="#transaction-control-via-the-isolation-level-attribute">Transaction control via the <code class="docutils literal notranslate"><span class="pre">isolation_level</span></code> attribute</a></li>143</ul>144</li>145</ul>146</li>147</ul>148</li>149</ul>150 151  </div>152  <div>153    <h4>Previous topic</h4>154    <p class="topless"><a href="dbm.html"155                          title="previous chapter"><code class="xref py py-mod docutils literal notranslate"><span class="pre">dbm</span></code> — Interfaces to Unix “databases”</a></p>156  </div>157  <div>158    <h4>Next topic</h4>159    <p class="topless"><a href="archiving.html"160                          title="next chapter">Data Compression and Archiving</a></p>161  </div>162  <script>163    document.addEventListener('DOMContentLoaded', () => {164        const title = document.querySelector('meta[property="og:title"]').content;165        const elements = document.querySelectorAll('.improvepage');166        const pageurl = window.location.href.split('?')[0];167        elements.forEach(element => {168            const url = new URL(element.href.split('?')[0].replace("-nojs", ""));169            url.searchParams.set('pagetitle', title);170            url.searchParams.set('pageurl', pageurl);171            url.searchParams.set('pagesource', "library/sqlite3.rst");172            element.href = url.toString();173        });174    });175  </script>176  <div role="note" aria-label="source link">177    <h3>This page</h3>178    <ul class="this-page-menu">179      <li><a href="../bugs.html">Report a bug</a></li>180      <li><a class="improvepage" href="../improve-page-nojs.html">Improve this page</a></li>181      <li>182        <a href="https://github.com/python/cpython/blob/main/Doc/library/sqlite3.rst?plain=1"183            rel="nofollow">Show source184        </a>185      </li>186      187    </ul>188  </div>189        </nav>190    </div>191</div>192 193  194    <div class="related" role="navigation" aria-label="Related">195      <h3>Navigation</h3>196      <ul>197        <li class="right" style="margin-right: 10px">198          <a href="../genindex.html" title="General Index"199             accesskey="I">index</a></li>200        <li class="right" >201          <a href="../py-modindex.html" title="Python Module Index"202             >modules</a> |</li>203        <li class="right" >204          <a href="archiving.html" title="Data Compression and Archiving"205             accesskey="N">next</a> |</li>206        <li class="right" >207          <a href="dbm.html" title="dbm — Interfaces to Unix “databases”"208             accesskey="P">previous</a> |</li>209 210          <li><img src="../_static/py.svg" alt="Python logo" style="vertical-align: middle; margin-top: -1px"></li>211          <li><a href="https://www.python.org/">Python</a> &#187;</li>212          <li class="switchers">213            <div class="language_switcher_placeholder"></div>214            <div class="version_switcher_placeholder"></div>215          </li>216          <li>217              218          </li>219    <li id="cpython-language-and-version">220      <a href="../index.html">3.15.0a6 Documentation</a> &#187;221    </li>222 223          <li class="nav-item nav-item-1"><a href="index.html" >The Python Standard Library</a> &#187;</li>224          <li class="nav-item nav-item-2"><a href="persistence.html" accesskey="U">Data Persistence</a> &#187;</li>225        <li class="nav-item nav-item-this"><a href=""><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> — DB-API 2.0 interface for SQLite databases</a></li>226                <li class="right">227                    228 229    <div class="inline-search" role="search">230        <form class="inline-search" action="../search.html" method="get">231          <input placeholder="Quick search" aria-label="Quick search" type="search" name="q" id="search-box">232          <input type="submit" value="Go">233        </form>234    </div>235                     |236                </li>237            <li class="right">238<label class="theme-selector-label">239    Theme240    <select class="theme-selector" oninput="activateTheme(this.value)">241        <option value="auto" selected>Auto</option>242        <option value="light">Light</option>243        <option value="dark">Dark</option>244    </select>245</label> |</li>246            247      </ul>248    </div>    249 250    <div class="document">251      <div class="documentwrapper">252        <div class="bodywrapper">253          <div class="body" role="main">254            255  <section id="module-sqlite3">256<span id="sqlite3-db-api-2-0-interface-for-sqlite-databases"></span><h1><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> — DB-API 2.0 interface for SQLite databases<a class="headerlink" href="#module-sqlite3" title="Link to this heading">¶</a></h1>257<p><strong>Source code:</strong> <a class="extlink-source reference external" href="https://github.com/python/cpython/tree/main/Lib/sqlite3/">Lib/sqlite3/</a></p>258<p id="sqlite3-intro">SQLite is a C library that provides a lightweight disk-based database that259doesn’t require a separate server process and allows accessing the database260using a nonstandard variant of the SQL query language. Some applications can use261SQLite for internal data storage.  It’s also possible to prototype an262application using SQLite and then port the code to a larger database such as263PostgreSQL or Oracle.</p>264<p>The <code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> module was written by Gerhard Häring.  It provides an SQL interface265compliant with the DB-API 2.0 specification described by <span class="target" id="index-0"></span><a class="pep reference external" href="https://peps.python.org/pep-0249/"><strong>PEP 249</strong></a>, and266requires the third-party <a class="reference external" href="https://sqlite.org/">SQLite</a> library.</p>267<p>This is an <a class="reference internal" href="../glossary.html#term-optional-module"><span class="xref std std-term">optional module</span></a>.268If it is missing from your copy of CPython,269look for documentation from your distributor (that is,270whoever provided Python to you).271If you are the distributor, see <a class="reference internal" href="../using/configure.html#optional-module-requirements"><span class="std std-ref">Requirements for optional modules</span></a>.</p>272<p>This document includes four main sections:</p>273<ul class="simple">274<li><p><a class="reference internal" href="#sqlite3-tutorial"><span class="std std-ref">Tutorial</span></a> teaches how to use the <code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> module.</p></li>275<li><p><a class="reference internal" href="#sqlite3-reference"><span class="std std-ref">Reference</span></a> describes the classes and functions this module276defines.</p></li>277<li><p><a class="reference internal" href="#sqlite3-howtos"><span class="std std-ref">How-to guides</span></a> details how to handle specific tasks.</p></li>278<li><p><a class="reference internal" href="#sqlite3-explanation"><span class="std std-ref">Explanation</span></a> provides in-depth background on279transaction control.</p></li>280</ul>281<div class="admonition seealso">282<p class="admonition-title">See also</p>283<dl class="simple">284<dt><a class="reference external" href="https://www.sqlite.org">https://www.sqlite.org</a></dt><dd><p>The SQLite web page; the documentation describes the syntax and the285available data types for the supported SQL dialect.</p>286</dd>287<dt><a class="reference external" href="https://www.w3schools.com/sql/">https://www.w3schools.com/sql/</a></dt><dd><p>Tutorial, reference and examples for learning SQL syntax.</p>288</dd>289<dt><span class="target" id="index-1"></span><a class="pep reference external" href="https://peps.python.org/pep-0249/"><strong>PEP 249</strong></a> - Database API Specification 2.0</dt><dd><p>PEP written by Marc-André Lemburg.</p>290</dd>291</dl>292</div>293<section id="tutorial">294<span id="sqlite3-tutorial"></span><h2>Tutorial<a class="headerlink" href="#tutorial" title="Link to this heading">¶</a></h2>295<p>In this tutorial, you will create a database of Monty Python movies296using basic <code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> functionality.297It assumes a fundamental understanding of database concepts,298including <a class="reference external" href="https://en.wikipedia.org/wiki/Cursor_(databases)">cursors</a> and <a class="reference external" href="https://en.wikipedia.org/wiki/Database_transaction">transactions</a>.</p>299<p>First, we need to create a new database and open300a database connection to allow <code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> to work with it.301Call <a class="reference internal" href="#sqlite3.connect" title="sqlite3.connect"><code class="xref py py-func docutils literal notranslate"><span class="pre">sqlite3.connect()</span></code></a> to create a connection to302the database <code class="file docutils literal notranslate"><span class="pre">tutorial.db</span></code> in the current working directory,303implicitly creating it if it does not exist:</p>304<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span><span class="w"> </span><span class="nn">sqlite3</span>305<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">&quot;tutorial.db&quot;</span><span class="p">)</span>306</pre></div>307</div>308<p>The returned <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> object <code class="docutils literal notranslate"><span class="pre">con</span></code>309represents the connection to the on-disk database.</p>310<p>In order to execute SQL statements and fetch results from SQL queries,311we will need to use a database cursor.312Call <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> to create the <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>313<div class="highlight-python 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>314</pre></div>315</div>316<p>Now that we’ve got a database connection and a cursor,317we can create a database table <code class="docutils literal notranslate"><span class="pre">movie</span></code> with columns for title,318release year, and review score.319For simplicity, we can just use column names in the table declaration –320thanks to the <a class="reference external" href="https://www.sqlite.org/flextypegood.html">flexible typing</a> feature of SQLite,321specifying the data types is optional.322Execute the <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> statement323by calling <a class="reference internal" href="#sqlite3.Cursor.execute" title="sqlite3.Cursor.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">cur.execute(...)</span></code></a>:</p>324<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;CREATE TABLE movie(title, year, score)&quot;</span><span class="p">)</span>325</pre></div>326</div>327<p>We can verify that the new table has been created by querying328the <code class="docutils literal notranslate"><span class="pre">sqlite_master</span></code> table built-in to SQLite,329which should now contain an entry for the <code class="docutils literal notranslate"><span class="pre">movie</span></code> table definition330(see <a class="reference external" href="https://www.sqlite.org/schematab.html">The Schema Table</a> for details).331Execute that query by calling <a class="reference internal" href="#sqlite3.Cursor.execute" title="sqlite3.Cursor.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">cur.execute(...)</span></code></a>,332assign the result to <code class="docutils literal notranslate"><span class="pre">res</span></code>,333and call <a class="reference internal" href="#sqlite3.Cursor.fetchone" title="sqlite3.Cursor.fetchone"><code class="xref py py-meth docutils literal notranslate"><span class="pre">res.fetchone()</span></code></a> to fetch the resulting row:</p>334<div class="highlight-pycon notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">res</span> <span class="o">=</span> <span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;SELECT name FROM sqlite_master&quot;</span><span class="p">)</span>335<span class="gp">&gt;&gt;&gt; </span><span class="n">res</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>336<span class="go">(&#39;movie&#39;,)</span>337</pre></div>338</div>339<p>We can see that the table has been created,340as the query returns a <a class="reference internal" href="stdtypes.html#tuple" title="tuple"><code class="xref py py-class docutils literal notranslate"><span class="pre">tuple</span></code></a> containing the table’s name.341If we query <code class="docutils literal notranslate"><span class="pre">sqlite_master</span></code> for a non-existent table <code class="docutils literal notranslate"><span class="pre">spam</span></code>,342<code class="xref py py-meth docutils literal notranslate"><span class="pre">res.fetchone()</span></code> will return <code class="docutils literal notranslate"><span class="pre">None</span></code>:</p>343<div class="highlight-pycon notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">res</span> <span class="o">=</span> <span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;SELECT name FROM sqlite_master WHERE name=&#39;spam&#39;&quot;</span><span class="p">)</span>344<span class="gp">&gt;&gt;&gt; </span><span class="n">res</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span> <span class="ow">is</span> <span class="kc">None</span>345<span class="go">True</span>346</pre></div>347</div>348<p>Now, add two rows of data supplied as SQL literals349by executing an <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> statement,350once again by calling <a class="reference internal" href="#sqlite3.Cursor.execute" title="sqlite3.Cursor.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">cur.execute(...)</span></code></a>:</p>351<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;&quot;&quot;</span>352<span class="s2">    INSERT INTO movie VALUES</span>353<span class="s2">        (&#39;Monty Python and the Holy Grail&#39;, 1975, 8.2),</span>354<span class="s2">        (&#39;And Now for Something Completely Different&#39;, 1971, 7.5)</span>355<span class="s2">&quot;&quot;&quot;</span><span class="p">)</span>356</pre></div>357</div>358<p>The <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> statement implicitly opens a transaction,359which needs to be committed before changes are saved in the database360(see <a class="reference internal" href="#sqlite3-controlling-transactions"><span class="std std-ref">Transaction control</span></a> for details).361Call <a class="reference internal" href="#sqlite3.Connection.commit" title="sqlite3.Connection.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">con.commit()</span></code></a> on the connection object362to commit the transaction:</p>363<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="n">con</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>364</pre></div>365</div>366<p>We can verify that the data was inserted correctly367by executing a <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> query.368Use the now-familiar <a class="reference internal" href="#sqlite3.Cursor.execute" title="sqlite3.Cursor.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">cur.execute(...)</span></code></a> to369assign the result to <code class="docutils literal notranslate"><span class="pre">res</span></code>,370and call <a class="reference internal" href="#sqlite3.Cursor.fetchall" title="sqlite3.Cursor.fetchall"><code class="xref py py-meth docutils literal notranslate"><span class="pre">res.fetchall()</span></code></a> to return all resulting rows:</p>371<div class="highlight-pycon notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">res</span> <span class="o">=</span> <span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;SELECT score FROM movie&quot;</span><span class="p">)</span>372<span class="gp">&gt;&gt;&gt; </span><span class="n">res</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>373<span class="go">[(8.2,), (7.5,)]</span>374</pre></div>375</div>376<p>The result is a <a class="reference internal" href="stdtypes.html#list" title="list"><code class="xref py py-class docutils literal notranslate"><span class="pre">list</span></code></a> of two <code class="xref py py-class docutils literal notranslate"><span class="pre">tuple</span></code>s, one per row,377each containing that row’s <code class="docutils literal notranslate"><span class="pre">score</span></code> value.</p>378<p>Now, insert three more rows by calling379<a class="reference internal" href="#sqlite3.Cursor.executemany" title="sqlite3.Cursor.executemany"><code class="xref py py-meth docutils literal notranslate"><span class="pre">cur.executemany(...)</span></code></a>:</p>380<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="n">data</span> <span class="o">=</span> <span class="p">[</span>381    <span class="p">(</span><span class="s2">&quot;Monty Python Live at the Hollywood Bowl&quot;</span><span class="p">,</span> <span class="mi">1982</span><span class="p">,</span> <span class="mf">7.9</span><span class="p">),</span>382    <span class="p">(</span><span class="s2">&quot;Monty Python&#39;s The Meaning of Life&quot;</span><span class="p">,</span> <span class="mi">1983</span><span class="p">,</span> <span class="mf">7.5</span><span class="p">),</span>383    <span class="p">(</span><span class="s2">&quot;Monty Python&#39;s Life of Brian&quot;</span><span class="p">,</span> <span class="mi">1979</span><span class="p">,</span> <span class="mf">8.0</span><span class="p">),</span>384<span class="p">]</span>385<span class="n">cur</span><span class="o">.</span><span class="n">executemany</span><span class="p">(</span><span class="s2">&quot;INSERT INTO movie VALUES(?, ?, ?)&quot;</span><span class="p">,</span> <span class="n">data</span><span class="p">)</span>386<span class="n">con</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>  <span class="c1"># Remember to commit the transaction after executing INSERT.</span>387</pre></div>388</div>389<p>Notice that <code class="docutils literal notranslate"><span class="pre">?</span></code> placeholders are used to bind <code class="docutils literal notranslate"><span class="pre">data</span></code> to the query.390Always use placeholders instead of <a class="reference internal" href="../tutorial/inputoutput.html#tut-formatting"><span class="std std-ref">string formatting</span></a>391to bind Python values to SQL statements,392to avoid <a class="reference external" href="https://en.wikipedia.org/wiki/SQL_injection">SQL injection attacks</a>393(see <a class="reference internal" href="#sqlite3-placeholders"><span class="std std-ref">How to use placeholders to bind values in SQL queries</span></a> for more details).</p>394<p>We can verify that the new rows were inserted395by executing a <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> query,396this time iterating over the results of the query:</p>397<div class="highlight-pycon notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </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="s2">&quot;SELECT year, title FROM movie ORDER BY year&quot;</span><span class="p">):</span>398<span class="gp">... </span>    <span class="nb">print</span><span class="p">(</span><span class="n">row</span><span class="p">)</span>399<span class="go">(1971, &#39;And Now for Something Completely Different&#39;)</span>400<span class="go">(1975, &#39;Monty Python and the Holy Grail&#39;)</span>401<span class="go">(1979, &quot;Monty Python&#39;s Life of Brian&quot;)</span>402<span class="go">(1982, &#39;Monty Python Live at the Hollywood Bowl&#39;)</span>403<span class="go">(1983, &quot;Monty Python&#39;s The Meaning of Life&quot;)</span>404</pre></div>405</div>406<p>Each row is a two-item <a class="reference internal" href="stdtypes.html#tuple" title="tuple"><code class="xref py py-class docutils literal notranslate"><span class="pre">tuple</span></code></a> of <code class="docutils literal notranslate"><span class="pre">(year,</span> <span class="pre">title)</span></code>,407matching the columns selected in the query.</p>408<p>Finally, verify that the database has been written to disk409by calling <a class="reference internal" href="#sqlite3.Connection.close" title="sqlite3.Connection.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">con.close()</span></code></a>410to close the existing connection, opening a new one,411creating a new cursor, then querying the database:</p>412<div class="highlight-pycon notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>413<span class="gp">&gt;&gt;&gt; </span><span class="n">new_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">&quot;tutorial.db&quot;</span><span class="p">)</span>414<span class="gp">&gt;&gt;&gt; </span><span class="n">new_cur</span> <span class="o">=</span> <span class="n">new_con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>415<span class="gp">&gt;&gt;&gt; </span><span class="n">res</span> <span class="o">=</span> <span class="n">new_cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;SELECT title, year FROM movie ORDER BY score DESC&quot;</span><span class="p">)</span>416<span class="gp">&gt;&gt;&gt; </span><span class="n">title</span><span class="p">,</span> <span class="n">year</span> <span class="o">=</span> <span class="n">res</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>417<span class="gp">&gt;&gt;&gt; </span><span class="nb">print</span><span class="p">(</span><span class="sa">f</span><span class="s1">&#39;The highest scoring Monty Python movie is </span><span class="si">{</span><span class="n">title</span><span class="si">!r}</span><span class="s1">, released in </span><span class="si">{</span><span class="n">year</span><span class="si">}</span><span class="s1">&#39;</span><span class="p">)</span>418<span class="go">The highest scoring Monty Python movie is &#39;Monty Python and the Holy Grail&#39;, released in 1975</span>419<span class="gp">&gt;&gt;&gt; </span><span class="n">new_con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>420</pre></div>421</div>422<p>You’ve now created an SQLite database using the <code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> module,423inserted data and retrieved values from it in multiple ways.</p>424<div class="admonition seealso">425<p class="admonition-title">See also</p>426<ul class="simple">427<li><p><a class="reference internal" href="#sqlite3-howtos"><span class="std std-ref">How-to guides</span></a> for further reading:</p>428<ul>429<li><p><a class="reference internal" href="#sqlite3-placeholders"><span class="std std-ref">How to use placeholders to bind values in SQL queries</span></a></p></li>430<li><p><a class="reference internal" href="#sqlite3-adapters"><span class="std std-ref">How to adapt custom Python types to SQLite values</span></a></p></li>431<li><p><a class="reference internal" href="#sqlite3-converters"><span class="std std-ref">How to convert SQLite values to custom Python types</span></a></p></li>432<li><p><a class="reference internal" href="#sqlite3-connection-context-manager"><span class="std std-ref">How to use the connection context manager</span></a></p></li>433<li><p><a class="reference internal" href="#sqlite3-howto-row-factory"><span class="std std-ref">How to create and use row factories</span></a></p></li>434</ul>435</li>436<li><p><a class="reference internal" href="#sqlite3-explanation"><span class="std std-ref">Explanation</span></a> for in-depth background on transaction control.</p></li>437</ul>438</div>439</section>440<section id="reference">441<span id="sqlite3-reference"></span><h2>Reference<a class="headerlink" href="#reference" title="Link to this heading">¶</a></h2>442<section id="module-functions">443<span id="sqlite3-module-functions"></span><span id="sqlite3-module-contents"></span><h3>Module functions<a class="headerlink" href="#module-functions" title="Link to this heading">¶</a></h3>444<dl class="py function">445<dt class="sig sig-object py" id="sqlite3.connect">446<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">connect</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">database</span></span></em>, <em class="sig-param"><span class="keyword-only-separator o"><abbr title="Keyword-only parameters separator (PEP 3102)"><span class="pre">*</span></abbr></span></em>, <em class="sig-param"><span class="n"><span class="pre">timeout</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">5.0</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">detect_types</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">0</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">isolation_level</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">'DEFERRED'</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">check_same_thread</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">True</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">factory</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">sqlite3.Connection</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">cached_statements</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">128</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">uri</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">False</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">autocommit</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">sqlite3.LEGACY_TRANSACTION_CONTROL</span></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.connect" title="Link to this definition">¶</a></dt>447<dd><p>Open a connection to an SQLite database.</p>448<dl class="field-list simple">449<dt class="field-odd">Parameters<span class="colon">:</span></dt>450<dd class="field-odd"><ul class="simple">451<li><p><strong>database</strong> (<a class="reference internal" href="../glossary.html#term-path-like-object"><span class="xref std std-term">path-like object</span></a>) – The path to the database file to be opened.452You can pass <code class="docutils literal notranslate"><span class="pre">&quot;:memory:&quot;</span></code> to create an <a class="reference external" href="https://sqlite.org/inmemorydb.html">SQLite database existing only453in memory</a>, and open a connection454to it.</p></li>455<li><p><strong>timeout</strong> (<a class="reference internal" href="functions.html#float" title="float"><em>float</em></a>) – How many seconds the connection should wait before raising456an <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> when a table is locked.457If another connection opens a transaction to modify a table,458that table will be locked until the transaction is committed.459Default five seconds.</p></li>460<li><p><strong>detect_types</strong> (<a class="reference internal" href="functions.html#int" title="int"><em>int</em></a>) – Control whether and how data types not461<a class="reference internal" href="#sqlite3-types"><span class="std std-ref">natively supported by SQLite</span></a>462are looked up to be converted to Python types,463using the converters registered with <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>.464Set it to any combination (using <code class="docutils literal notranslate"><span class="pre">|</span></code>, bitwise or) of465<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> and <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>466to enable this.467Column names take precedence over declared types if both flags are set.468By default (<code class="docutils literal notranslate"><span class="pre">0</span></code>), type detection is disabled.</p></li>469<li><p><strong>isolation_level</strong> (<a class="reference internal" href="stdtypes.html#str" title="str"><em>str</em></a><em> | </em><em>None</em>) – Control legacy transaction handling behaviour.470See <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">Connection.isolation_level</span></code></a> and471<a class="reference internal" href="#sqlite3-transaction-control-isolation-level"><span class="std std-ref">Transaction control via the isolation_level attribute</span></a> for more information.472Can be <code class="docutils literal notranslate"><span class="pre">&quot;DEFERRED&quot;</span></code> (default), <code class="docutils literal notranslate"><span class="pre">&quot;EXCLUSIVE&quot;</span></code> or <code class="docutils literal notranslate"><span class="pre">&quot;IMMEDIATE&quot;</span></code>;473or <code class="docutils literal notranslate"><span class="pre">None</span></code> to disable opening transactions implicitly.474Has no effect unless <a class="reference internal" href="#sqlite3.Connection.autocommit" title="sqlite3.Connection.autocommit"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Connection.autocommit</span></code></a> is set to475<a class="reference internal" href="#sqlite3.LEGACY_TRANSACTION_CONTROL" title="sqlite3.LEGACY_TRANSACTION_CONTROL"><code class="xref py py-const docutils literal notranslate"><span class="pre">LEGACY_TRANSACTION_CONTROL</span></code></a> (the default).</p></li>476<li><p><strong>check_same_thread</strong> (<a class="reference internal" href="functions.html#bool" title="bool"><em>bool</em></a>) – If <code class="docutils literal notranslate"><span class="pre">True</span></code> (default), <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> will be raised477if the database connection is used by a thread478other than the one that created it.479If <code class="docutils literal notranslate"><span class="pre">False</span></code>, the connection may be accessed in multiple threads;480write operations may need to be serialized by the user481to avoid data corruption.482See <a class="reference internal" href="#sqlite3.threadsafety" title="sqlite3.threadsafety"><code class="xref py py-attr docutils literal notranslate"><span class="pre">threadsafety</span></code></a> for more information.</p></li>483<li><p><strong>factory</strong> (<a class="reference internal" href="#sqlite3.Connection" title="sqlite3.Connection"><em>Connection</em></a>) – A custom subclass of <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> to create the connection with,484if not the default <code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code> class.</p></li>485<li><p><strong>cached_statements</strong> (<a class="reference internal" href="functions.html#int" title="int"><em>int</em></a>) – The number of statements that <code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code>486should internally cache for this connection, to avoid parsing overhead.487By default, 128 statements.</p></li>488<li><p><strong>uri</strong> (<a class="reference internal" href="functions.html#bool" title="bool"><em>bool</em></a>) – If set to <code class="docutils literal notranslate"><span class="pre">True</span></code>, <em>database</em> is interpreted as a489<abbr title="Uniform Resource Identifier">URI</abbr> with a file path490and an optional query string.491The scheme part <em>must</em> be <code class="docutils literal notranslate"><span class="pre">&quot;file:&quot;</span></code>,492and the path can be relative or absolute.493The query string allows passing parameters to SQLite,494enabling various <a class="reference internal" href="#sqlite3-uri-tricks"><span class="std std-ref">How to work with SQLite URIs</span></a>.</p></li>495<li><p><strong>autocommit</strong> (<a class="reference internal" href="functions.html#bool" title="bool"><em>bool</em></a>) – Control <span class="target" id="index-2"></span><a class="pep reference external" href="https://peps.python.org/pep-0249/"><strong>PEP 249</strong></a> transaction handling behaviour.496See <a class="reference internal" href="#sqlite3.Connection.autocommit" title="sqlite3.Connection.autocommit"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Connection.autocommit</span></code></a> and497<a class="reference internal" href="#sqlite3-transaction-control-autocommit"><span class="std std-ref">Transaction control via the autocommit attribute</span></a> for more information.498<em>autocommit</em> currently defaults to499<a class="reference internal" href="#sqlite3.LEGACY_TRANSACTION_CONTROL" title="sqlite3.LEGACY_TRANSACTION_CONTROL"><code class="xref py py-const docutils literal notranslate"><span class="pre">LEGACY_TRANSACTION_CONTROL</span></code></a>.500The default will change to <code class="docutils literal notranslate"><span class="pre">False</span></code> in a future Python release.</p></li>501</ul>502</dd>503<dt class="field-even">Return type<span class="colon">:</span></dt>504<dd class="field-even"><p><a class="reference internal" href="#sqlite3.Connection" title="sqlite3.Connection"><em>Connection</em></a></p>505</dd>506</dl>507<p class="audit-hook">Raises an <a class="reference internal" href="sys.html#auditing"><span class="std std-ref">auditing event</span></a> <code class="docutils literal notranslate"><span class="pre">sqlite3.connect</span></code> with argument <code class="docutils literal notranslate"><span class="pre">database</span></code>.</p>508<p class="audit-hook">Raises an <a class="reference internal" href="sys.html#auditing"><span class="std std-ref">auditing event</span></a> <code class="docutils literal notranslate"><span class="pre">sqlite3.connect/handle</span></code> with argument <code class="docutils literal notranslate"><span class="pre">connection_handle</span></code>.</p>509<div class="versionchanged">510<p><span class="versionmodified changed">Changed in version 3.4: </span>Added the <em>uri</em> parameter.</p>511</div>512<div class="versionchanged">513<p><span class="versionmodified changed">Changed in version 3.7: </span><em>database</em> can now also be a <a class="reference internal" href="../glossary.html#term-path-like-object"><span class="xref std std-term">path-like object</span></a>, not only a string.</p>514</div>515<div class="versionchanged">516<p><span class="versionmodified changed">Changed in version 3.10: </span>Added the <code class="docutils literal notranslate"><span class="pre">sqlite3.connect/handle</span></code> auditing event.</p>517</div>518<div class="versionchanged">519<p><span class="versionmodified changed">Changed in version 3.12: </span>Added the <em>autocommit</em> parameter.</p>520</div>521<div class="versionchanged">522<p><span class="versionmodified changed">Changed in version 3.15: </span>All parameters except <em>database</em> are now keyword-only.</p>523</div>524</dd></dl>525 526<dl class="py function">527<dt class="sig sig-object py" id="sqlite3.complete_statement">528<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">complete_statement</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">statement</span></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.complete_statement" title="Link to this definition">¶</a></dt>529<dd><p>Return <code class="docutils literal notranslate"><span class="pre">True</span></code> if the string <em>statement</em> appears to contain530one or more complete SQL statements.531No syntactic verification or parsing of any kind is performed,532other than checking that there are no unclosed string literals533and the statement is terminated by a semicolon.</p>534<p>For example:</p>535<div class="highlight-pycon notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="n">sqlite3</span><span class="o">.</span><span class="n">complete_statement</span><span class="p">(</span><span class="s2">&quot;SELECT foo FROM bar;&quot;</span><span class="p">)</span>536<span class="go">True</span>537<span class="gp">&gt;&gt;&gt; </span><span class="n">sqlite3</span><span class="o">.</span><span class="n">complete_statement</span><span class="p">(</span><span class="s2">&quot;SELECT foo&quot;</span><span class="p">)</span>538<span class="go">False</span>539</pre></div>540</div>541<p>This function may be useful during command-line input542to determine if the entered text seems to form a complete SQL statement,543or if additional input is needed before calling <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>544<p>See <code class="xref py py-func docutils literal notranslate"><span class="pre">runsource()</span></code> in <a class="extlink-source reference external" href="https://github.com/python/cpython/tree/main/Lib/sqlite3/__main__.py">Lib/sqlite3/__main__.py</a>545for real-world use.</p>546</dd></dl>547 548<dl class="py function">549<dt class="sig sig-object py" id="sqlite3.enable_callback_tracebacks">550<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">enable_callback_tracebacks</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">flag</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.enable_callback_tracebacks" title="Link to this definition">¶</a></dt>551<dd><p>Enable or disable callback tracebacks.552By default you will not get any tracebacks in user-defined functions,553aggregates, converters, authorizer callbacks etc. If you want to debug them,554you can call this function with <em>flag</em> set to <code class="docutils literal notranslate"><span class="pre">True</span></code>. Afterwards, you555will get tracebacks from callbacks on <a class="reference internal" href="sys.html#sys.stderr" title="sys.stderr"><code class="xref py py-data docutils literal notranslate"><span class="pre">sys.stderr</span></code></a>. Use <code class="docutils literal notranslate"><span class="pre">False</span></code>556to disable the feature again.</p>557<div class="admonition note">558<p class="admonition-title">Note</p>559<p>Errors in user-defined function callbacks are logged as unraisable exceptions.560Use an <a class="reference internal" href="sys.html#sys.unraisablehook" title="sys.unraisablehook"><code class="xref py py-func docutils literal notranslate"><span class="pre">unraisable</span> <span class="pre">hook</span> <span class="pre">handler</span></code></a> for561introspection of the failed callback.</p>562</div>563</dd></dl>564 565<dl class="py function">566<dt class="sig sig-object py" id="sqlite3.register_adapter">567<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">register_adapter</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">type</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">adapter</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.register_adapter" title="Link to this definition">¶</a></dt>568<dd><p>Register an <em>adapter</em> <a class="reference internal" href="../glossary.html#term-callable"><span class="xref std std-term">callable</span></a> to adapt the Python type <em>type</em>569into an SQLite type.570The adapter is called with a Python object of type <em>type</em> as its sole571argument, and must return a value of a572<a class="reference internal" href="#sqlite3-types"><span class="std std-ref">type that SQLite natively understands</span></a>.</p>573</dd></dl>574 575<dl class="py function">576<dt class="sig sig-object py" id="sqlite3.register_converter">577<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">register_converter</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">typename</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">converter</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.register_converter" title="Link to this definition">¶</a></dt>578<dd><p>Register the <em>converter</em> <a class="reference internal" href="../glossary.html#term-callable"><span class="xref std std-term">callable</span></a> to convert SQLite objects of type579<em>typename</em> into a Python object of a specific type.580The converter is invoked for all SQLite values of type <em>typename</em>;581it is passed a <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> object and should return an object of the582desired Python type.583Consult the parameter <em>detect_types</em> of584<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> for information regarding how type detection works.</p>585<p>Note: <em>typename</em> and the name of the type in your query are matched586case-insensitively.</p>587</dd></dl>588 589</section>590<section id="module-constants">591<span id="sqlite3-module-constants"></span><h3>Module constants<a class="headerlink" href="#module-constants" title="Link to this heading">¶</a></h3>592<dl class="py data">593<dt class="sig sig-object py" id="sqlite3.LEGACY_TRANSACTION_CONTROL">594<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">LEGACY_TRANSACTION_CONTROL</span></span><a class="headerlink" href="#sqlite3.LEGACY_TRANSACTION_CONTROL" title="Link to this definition">¶</a></dt>595<dd><p>Set <a class="reference internal" href="#sqlite3.Connection.autocommit" title="sqlite3.Connection.autocommit"><code class="xref py py-attr docutils literal notranslate"><span class="pre">autocommit</span></code></a> to this constant to select596old style (pre-Python 3.12) transaction control behaviour.597See <a class="reference internal" href="#sqlite3-transaction-control-isolation-level"><span class="std std-ref">Transaction control via the isolation_level attribute</span></a> for more information.</p>598</dd></dl>599 600<dl class="py data">601<dt class="sig sig-object py" id="sqlite3.PARSE_DECLTYPES">602<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">PARSE_DECLTYPES</span></span><a class="headerlink" href="#sqlite3.PARSE_DECLTYPES" title="Link to this definition">¶</a></dt>603<dd><p>Pass this flag value to the <em>detect_types</em> parameter of604<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> to look up a converter function using605the declared types for each column.606The types are declared when the database table is created.607<code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> will look up a converter function using the first word of the608declared type as the converter dictionary key.609For example:</p>610<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">CREATE</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">test</span><span class="p">(</span>611<span class="w">   </span><span class="n">i</span><span class="w"> </span><span class="nb">integer</span><span class="w"> </span><span class="k">primary</span><span class="w"> </span><span class="k">key</span><span class="p">,</span><span class="w">  </span><span class="o">!</span><span class="w"> </span><span class="n">will</span><span class="w"> </span><span class="n">look</span><span class="w"> </span><span class="n">up</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="n">converter</span><span class="w"> </span><span class="n">named</span><span class="w"> </span><span class="ss">&quot;integer&quot;</span>612<span class="w">   </span><span class="n">p</span><span class="w"> </span><span class="n">point</span><span class="p">,</span><span class="w">                </span><span class="o">!</span><span class="w"> </span><span class="n">will</span><span class="w"> </span><span class="n">look</span><span class="w"> </span><span class="n">up</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="n">converter</span><span class="w"> </span><span class="n">named</span><span class="w"> </span><span class="ss">&quot;point&quot;</span>613<span class="w">   </span><span class="n">n</span><span class="w"> </span><span class="nb">number</span><span class="p">(</span><span class="mi">10</span><span class="p">)</span><span class="w">            </span><span class="o">!</span><span class="w"> </span><span class="n">will</span><span class="w"> </span><span class="n">look</span><span class="w"> </span><span class="n">up</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="n">converter</span><span class="w"> </span><span class="n">named</span><span class="w"> </span><span class="ss">&quot;number&quot;</span>614<span class="w"> </span><span class="p">)</span>615</pre></div>616</div>617<p>This flag may be combined with <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> using the <code class="docutils literal notranslate"><span class="pre">|</span></code>618(bitwise or) operator.</p>619<div class="admonition note">620<p class="admonition-title">Note</p>621<p>Generated fields (for example <code class="docutils literal notranslate"><span class="pre">MAX(p)</span></code>) are returned as <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>.622Use <code class="xref py py-const docutils literal notranslate"><span class="pre">PARSE_COLNAMES</span></code> to enforce types for such queries.</p>623</div>624</dd></dl>625 626<dl class="py data">627<dt class="sig sig-object py" id="sqlite3.PARSE_COLNAMES">628<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">PARSE_COLNAMES</span></span><a class="headerlink" href="#sqlite3.PARSE_COLNAMES" title="Link to this definition">¶</a></dt>629<dd><p>Pass this flag value to the <em>detect_types</em> parameter of630<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> to look up a converter function by631using the type name, parsed from the query column name,632as the converter dictionary key.633The query column name must be wrapped in double quotes (<code class="docutils literal notranslate"><span class="pre">&quot;</span></code>)634and the type name must be wrapped in square brackets (<code class="docutils literal notranslate"><span class="pre">[]</span></code>).</p>635<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="k">MAX</span><span class="p">(</span><span class="n">p</span><span class="p">)</span><span class="w"> </span><span class="k">as</span><span class="w"> </span><span class="ss">&quot;p [point]&quot;</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">test</span><span class="p">;</span><span class="w">  </span><span class="o">!</span><span class="w"> </span><span class="n">will</span><span class="w"> </span><span class="n">look</span><span class="w"> </span><span class="n">up</span><span class="w"> </span><span class="n">converter</span><span class="w"> </span><span class="ss">&quot;point&quot;</span>636</pre></div>637</div>638<p>This flag may be combined with <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> using the <code class="docutils literal notranslate"><span class="pre">|</span></code>639(bitwise or) operator.</p>640</dd></dl>641 642<dl class="py data">643<dt class="sig sig-object py" id="sqlite3.SQLITE_OK">644<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_OK</span></span><a class="headerlink" href="#sqlite3.SQLITE_OK" title="Link to this definition">¶</a></dt>645<dt class="sig sig-object py" id="sqlite3.SQLITE_DENY">646<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DENY</span></span><a class="headerlink" href="#sqlite3.SQLITE_DENY" title="Link to this definition">¶</a></dt>647<dt class="sig sig-object py" id="sqlite3.SQLITE_IGNORE">648<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_IGNORE</span></span><a class="headerlink" href="#sqlite3.SQLITE_IGNORE" title="Link to this definition">¶</a></dt>649<dd><p>Flags that should be returned by the <em>authorizer_callback</em> <a class="reference internal" href="../glossary.html#term-callable"><span class="xref std std-term">callable</span></a>650passed to <a class="reference internal" href="#sqlite3.Connection.set_authorizer" title="sqlite3.Connection.set_authorizer"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.set_authorizer()</span></code></a>, to indicate whether:</p>651<ul class="simple">652<li><p>Access is allowed (<code class="xref py py-const docutils literal notranslate"><span class="pre">SQLITE_OK</span></code>),</p></li>653<li><p>The SQL statement should be aborted with an error (<code class="xref py py-const docutils literal notranslate"><span class="pre">SQLITE_DENY</span></code>)</p></li>654<li><p>The column should be treated as a <code class="docutils literal notranslate"><span class="pre">NULL</span></code> value (<code class="xref py py-const docutils literal notranslate"><span class="pre">SQLITE_IGNORE</span></code>)</p></li>655</ul>656</dd></dl>657 658<dl class="py data">659<dt class="sig sig-object py" id="sqlite3.apilevel">660<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">apilevel</span></span><a class="headerlink" href="#sqlite3.apilevel" title="Link to this definition">¶</a></dt>661<dd><p>String constant stating the supported DB-API level. Required by the DB-API.662Hard-coded to <code class="docutils literal notranslate"><span class="pre">&quot;2.0&quot;</span></code>.</p>663</dd></dl>664 665<dl class="py data">666<dt class="sig sig-object py" id="sqlite3.paramstyle">667<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">paramstyle</span></span><a class="headerlink" href="#sqlite3.paramstyle" title="Link to this definition">¶</a></dt>668<dd><p>String constant stating the type of parameter marker formatting expected by669the <code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> module. Required by the DB-API. Hard-coded to670<code class="docutils literal notranslate"><span class="pre">&quot;qmark&quot;</span></code>.</p>671<div class="admonition note">672<p class="admonition-title">Note</p>673<p>The <code class="docutils literal notranslate"><span class="pre">named</span></code> DB-API parameter style is also supported.</p>674</div>675</dd></dl>676 677<dl class="py data">678<dt class="sig sig-object py" id="sqlite3.sqlite_version">679<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">sqlite_version</span></span><a class="headerlink" href="#sqlite3.sqlite_version" title="Link to this definition">¶</a></dt>680<dd><p>Version number of the runtime SQLite library as a <a class="reference internal" href="stdtypes.html#str" title="str"><code class="xref py py-class docutils literal notranslate"><span class="pre">string</span></code></a>.</p>681</dd></dl>682 683<dl class="py data">684<dt class="sig sig-object py" id="sqlite3.sqlite_version_info">685<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">sqlite_version_info</span></span><a class="headerlink" href="#sqlite3.sqlite_version_info" title="Link to this definition">¶</a></dt>686<dd><p>Version number of the runtime SQLite library as a <a class="reference internal" href="stdtypes.html#tuple" title="tuple"><code class="xref py py-class docutils literal notranslate"><span class="pre">tuple</span></code></a> of687<a class="reference internal" href="functions.html#int" title="int"><code class="xref py py-class docutils literal notranslate"><span class="pre">integers</span></code></a>.</p>688</dd></dl>689 690<dl class="py data">691<dt class="sig sig-object py" id="sqlite3.SQLITE_KEYWORDS">692<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_KEYWORDS</span></span><a class="headerlink" href="#sqlite3.SQLITE_KEYWORDS" title="Link to this definition">¶</a></dt>693<dd><p>A <a class="reference internal" href="stdtypes.html#tuple" title="tuple"><code class="xref py py-class docutils literal notranslate"><span class="pre">tuple</span></code></a> containing all SQLite keywords.</p>694<p>This constant is only available if Python was compiled with SQLite6953.24.0 or greater.</p>696<div class="versionadded">697<p><span class="versionmodified added">Added in version 3.15.</span></p>698</div>699</dd></dl>700 701<dl class="py data">702<dt class="sig sig-object py" id="sqlite3.threadsafety">703<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">threadsafety</span></span><a class="headerlink" href="#sqlite3.threadsafety" title="Link to this definition">¶</a></dt>704<dd><p>Integer constant required by the DB-API 2.0, stating the level of thread705safety the <code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> module supports. This attribute is set based on706the default <a class="reference external" href="https://sqlite.org/threadsafe.html">threading mode</a> the707underlying SQLite library is compiled with. The SQLite threading modes are:</p>708<ol class="arabic simple">709<li><p><strong>Single-thread</strong>: In this mode, all mutexes are disabled and SQLite is710unsafe to use in more than a single thread at once.</p></li>711<li><p><strong>Multi-thread</strong>: In this mode, SQLite can be safely used by multiple712threads provided that no single database connection is used713simultaneously in two or more threads.</p></li>714<li><p><strong>Serialized</strong>: In serialized mode, SQLite can be safely used by715multiple threads with no restriction.</p></li>716</ol>717<p>The mappings from SQLite threading modes to DB-API 2.0 threadsafety levels718are as follows:</p>719<table class="docutils align-default">720<thead>721<tr class="row-odd"><th class="head"><p>SQLite threading722mode</p></th>723<th class="head"><p><span class="target" id="index-3"></span><a class="pep reference external" href="https://peps.python.org/pep-0249/#threadsafety"><strong>threadsafety</strong></a></p></th>724<th class="head"><p><a class="reference external" href="https://sqlite.org/compile.html#threadsafe">SQLITE_THREADSAFE</a></p></th>725<th class="head"><p>DB-API 2.0 meaning</p></th>726</tr>727</thead>728<tbody>729<tr class="row-even"><td><p>single-thread</p></td>730<td><p>0</p></td>731<td><p>0</p></td>732<td><p>Threads may not share the733module</p></td>734</tr>735<tr class="row-odd"><td><p>multi-thread</p></td>736<td><p>1</p></td>737<td><p>2</p></td>738<td><p>Threads may share the module,739but not connections</p></td>740</tr>741<tr class="row-even"><td><p>serialized</p></td>742<td><p>3</p></td>743<td><p>1</p></td>744<td><p>Threads may share the module,745connections and cursors</p></td>746</tr>747</tbody>748</table>749<div class="versionchanged">750<p><span class="versionmodified changed">Changed in version 3.11: </span>Set <em>threadsafety</em> dynamically instead of hard-coding it to <code class="docutils literal notranslate"><span class="pre">1</span></code>.</p>751</div>752</dd></dl>753 754<dl class="py data" id="sqlite3-dbconfig-constants">755<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_DEFENSIVE">756<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_DEFENSIVE</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_DEFENSIVE" title="Link to this definition">¶</a></dt>757<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_DQS_DDL">758<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_DQS_DDL</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_DQS_DDL" title="Link to this definition">¶</a></dt>759<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_DQS_DML">760<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_DQS_DML</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_DQS_DML" title="Link to this definition">¶</a></dt>761<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_ENABLE_FKEY">762<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_ENABLE_FKEY</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_ENABLE_FKEY" title="Link to this definition">¶</a></dt>763<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER">764<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER" title="Link to this definition">¶</a></dt>765<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION">766<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION" title="Link to this definition">¶</a></dt>767<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_ENABLE_QPSG">768<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_ENABLE_QPSG</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_ENABLE_QPSG" title="Link to this definition">¶</a></dt>769<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_ENABLE_TRIGGER">770<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_ENABLE_TRIGGER</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_ENABLE_TRIGGER" title="Link to this definition">¶</a></dt>771<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_ENABLE_VIEW">772<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_ENABLE_VIEW</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_ENABLE_VIEW" title="Link to this definition">¶</a></dt>773<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_LEGACY_ALTER_TABLE">774<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_LEGACY_ALTER_TABLE</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_LEGACY_ALTER_TABLE" title="Link to this definition">¶</a></dt>775<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_LEGACY_FILE_FORMAT">776<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_LEGACY_FILE_FORMAT</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_LEGACY_FILE_FORMAT" title="Link to this definition">¶</a></dt>777<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE">778<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE" title="Link to this definition">¶</a></dt>779<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_RESET_DATABASE">780<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_RESET_DATABASE</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_RESET_DATABASE" title="Link to this definition">¶</a></dt>781<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_TRIGGER_EQP">782<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_TRIGGER_EQP</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_TRIGGER_EQP" title="Link to this definition">¶</a></dt>783<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_TRUSTED_SCHEMA">784<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_TRUSTED_SCHEMA</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_TRUSTED_SCHEMA" title="Link to this definition">¶</a></dt>785<dt class="sig sig-object py" id="sqlite3.SQLITE_DBCONFIG_WRITABLE_SCHEMA">786<span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">SQLITE_DBCONFIG_WRITABLE_SCHEMA</span></span><a class="headerlink" href="#sqlite3.SQLITE_DBCONFIG_WRITABLE_SCHEMA" title="Link to this definition">¶</a></dt>787<dd><p>These constants are used for the <a class="reference internal" href="#sqlite3.Connection.setconfig" title="sqlite3.Connection.setconfig"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.setconfig()</span></code></a>788and <a class="reference internal" href="#sqlite3.Connection.getconfig" title="sqlite3.Connection.getconfig"><code class="xref py py-meth docutils literal notranslate"><span class="pre">getconfig()</span></code></a> methods.</p>789<p>The availability of these constants varies depending on the version of SQLite790Python was compiled with.</p>791<div class="versionadded">792<p><span class="versionmodified added">Added in version 3.12.</span></p>793</div>794<div class="admonition seealso">795<p class="admonition-title">See also</p>796<dl class="simple">797<dt><a class="reference external" href="https://www.sqlite.org/c3ref/c_dbconfig_defensive.html">https://www.sqlite.org/c3ref/c_dbconfig_defensive.html</a></dt><dd><p>SQLite docs: Database Connection Configuration Options</p>798</dd>799</dl>800</div>801</dd></dl>802 803<div class="deprecated-removed">804<p><span class="versionmodified removed">Deprecated since version 3.12, removed in version 3.14: </span>The <code class="xref py py-data docutils literal notranslate"><span class="pre">version</span></code> and <code class="xref py py-data docutils literal notranslate"><span class="pre">version_info</span></code> constants.</p>805</div>806</section>807<section id="connection-objects">808<span id="sqlite3-connection-objects"></span><h3>Connection objects<a class="headerlink" href="#connection-objects" title="Link to this heading">¶</a></h3>809<dl class="py class">810<dt class="sig sig-object py" id="sqlite3.Connection">811<em class="property"><span class="k"><span class="pre">class</span></span><span class="w"> </span></em><span class="sig-prename descclassname"><span class="pre">sqlite3.</span></span><span class="sig-name descname"><span class="pre">Connection</span></span><a class="headerlink" href="#sqlite3.Connection" title="Link to this definition">¶</a></dt>812<dd><p>Each open SQLite database is represented by a <code class="docutils literal notranslate"><span class="pre">Connection</span></code> object,813which is created using <a class="reference internal" href="#sqlite3.connect" title="sqlite3.connect"><code class="xref py py-func docutils literal notranslate"><span class="pre">sqlite3.connect()</span></code></a>.814Their main purpose is creating <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> objects,815and <a class="reference internal" href="#sqlite3-controlling-transactions"><span class="std std-ref">Transaction control</span></a>.</p>816<div class="admonition seealso">817<p class="admonition-title">See also</p>818<ul class="simple">819<li><p><a class="reference internal" href="#sqlite3-connection-shortcuts"><span class="std std-ref">How to use connection shortcut methods</span></a></p></li>820<li><p><a class="reference internal" href="#sqlite3-connection-context-manager"><span class="std std-ref">How to use the connection context manager</span></a></p></li>821</ul>822</div>823<div class="versionchanged">824<p><span class="versionmodified changed">Changed in version 3.13: </span>A <a class="reference internal" href="exceptions.html#ResourceWarning" title="ResourceWarning"><code class="xref py py-exc docutils literal notranslate"><span class="pre">ResourceWarning</span></code></a> is emitted if <a class="reference internal" href="#sqlite3.Connection.close" title="sqlite3.Connection.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">close()</span></code></a> is not called before825a <code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code> object is deleted.</p>826</div>827<p>An SQLite database connection has the following attributes and methods:</p>828<dl class="py method">829<dt class="sig sig-object py" id="sqlite3.Connection.cursor">830<span class="sig-name descname"><span class="pre">cursor</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">factory</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">Cursor</span></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.cursor" title="Link to this definition">¶</a></dt>831<dd><p>Create and return 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> object.832The cursor method accepts a single optional parameter <em>factory</em>. If833supplied, this must be a <a class="reference internal" href="../glossary.html#term-callable"><span class="xref std std-term">callable</span></a> returning834an instance of <code class="xref py py-class docutils literal notranslate"><span class="pre">Cursor</span></code> or its subclasses.</p>835</dd></dl>836 837<dl class="py method">838<dt class="sig sig-object py" id="sqlite3.Connection.blobopen">839<span class="sig-name descname"><span class="pre">blobopen</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">table</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">column</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">rowid</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em>, <em class="sig-param"><span class="keyword-only-separator o"><abbr title="Keyword-only parameters separator (PEP 3102)"><span class="pre">*</span></abbr></span></em>, <em class="sig-param"><span class="n"><span class="pre">readonly</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">False</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">name</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">'main'</span></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.blobopen" title="Link to this definition">¶</a></dt>840<dd><p>Open a <a class="reference internal" href="#sqlite3.Blob" title="sqlite3.Blob"><code class="xref py py-class docutils literal notranslate"><span class="pre">Blob</span></code></a> handle to an existing841<abbr title="Binary Large OBject">BLOB</abbr>.</p>842<dl class="field-list simple">843<dt class="field-odd">Parameters<span class="colon">:</span></dt>844<dd class="field-odd"><ul class="simple">845<li><p><strong>table</strong> (<a class="reference internal" href="stdtypes.html#str" title="str"><em>str</em></a>) – The name of the table where the blob is located.</p></li>846<li><p><strong>column</strong> (<a class="reference internal" href="stdtypes.html#str" title="str"><em>str</em></a>) – The name of the column where the blob is located.</p></li>847<li><p><strong>rowid</strong> (<a class="reference internal" href="functions.html#int" title="int"><em>int</em></a>) – The row id where the blob is located.</p></li>848<li><p><strong>readonly</strong> (<a class="reference internal" href="functions.html#bool" title="bool"><em>bool</em></a>) – Set to <code class="docutils literal notranslate"><span class="pre">True</span></code> if the blob should be opened without write849permissions.850Defaults to <code class="docutils literal notranslate"><span class="pre">False</span></code>.</p></li>851<li><p><strong>name</strong> (<a class="reference internal" href="stdtypes.html#str" title="str"><em>str</em></a>) – The name of the database where the blob is located.852Defaults to <code class="docutils literal notranslate"><span class="pre">&quot;main&quot;</span></code>.</p></li>853</ul>854</dd>855<dt class="field-even">Raises<span class="colon">:</span></dt>856<dd class="field-even"><p><a class="reference internal" href="#sqlite3.OperationalError" title="sqlite3.OperationalError"><strong>OperationalError</strong></a> – When trying to open a blob in a <code class="docutils literal notranslate"><span class="pre">WITHOUT</span> <span class="pre">ROWID</span></code> table.</p>857</dd>858<dt class="field-odd">Return type<span class="colon">:</span></dt>859<dd class="field-odd"><p><a class="reference internal" href="#sqlite3.Blob" title="sqlite3.Blob">Blob</a></p>860</dd>861</dl>862<div class="admonition note">863<p class="admonition-title">Note</p>864<p>The blob size cannot be changed using the <a class="reference internal" href="#sqlite3.Blob" title="sqlite3.Blob"><code class="xref py py-class docutils literal notranslate"><span class="pre">Blob</span></code></a> class.865Use the SQL function <code class="docutils literal notranslate"><span class="pre">zeroblob</span></code> to create a blob with a fixed size.</p>866</div>867<div class="versionadded">868<p><span class="versionmodified added">Added in version 3.11.</span></p>869</div>870</dd></dl>871 872<dl class="py method">873<dt class="sig sig-object py" id="sqlite3.Connection.commit">874<span class="sig-name descname"><span class="pre">commit</span></span><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.commit" title="Link to this definition">¶</a></dt>875<dd><p>Commit any pending transaction to the database.876If <a class="reference internal" href="#sqlite3.Connection.autocommit" title="sqlite3.Connection.autocommit"><code class="xref py py-attr docutils literal notranslate"><span class="pre">autocommit</span></code></a> is <code class="docutils literal notranslate"><span class="pre">True</span></code>, or there is no open transaction,877this method does nothing.878If <code class="xref py py-attr docutils literal notranslate"><span class="pre">autocommit</span></code> is <code class="docutils literal notranslate"><span class="pre">False</span></code>, a new transaction is implicitly879opened if a pending transaction was committed by this method.</p>880</dd></dl>881 882<dl class="py method">883<dt class="sig sig-object py" id="sqlite3.Connection.rollback">884<span class="sig-name descname"><span class="pre">rollback</span></span><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.rollback" title="Link to this definition">¶</a></dt>885<dd><p>Roll back to the start of any pending transaction.886If <a class="reference internal" href="#sqlite3.Connection.autocommit" title="sqlite3.Connection.autocommit"><code class="xref py py-attr docutils literal notranslate"><span class="pre">autocommit</span></code></a> is <code class="docutils literal notranslate"><span class="pre">True</span></code>, or there is no open transaction,887this method does nothing.888If <code class="xref py py-attr docutils literal notranslate"><span class="pre">autocommit</span></code> is <code class="docutils literal notranslate"><span class="pre">False</span></code>, a new transaction is implicitly889opened if a pending transaction was rolled back by this method.</p>890</dd></dl>891 892<dl class="py method">893<dt class="sig sig-object py" id="sqlite3.Connection.close">894<span class="sig-name descname"><span class="pre">close</span></span><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.close" title="Link to this definition">¶</a></dt>895<dd><p>Close the database connection.896If <a class="reference internal" href="#sqlite3.Connection.autocommit" title="sqlite3.Connection.autocommit"><code class="xref py py-attr docutils literal notranslate"><span class="pre">autocommit</span></code></a> is <code class="docutils literal notranslate"><span class="pre">False</span></code>,897any pending transaction is implicitly rolled back.898If <code class="xref py py-attr docutils literal notranslate"><span class="pre">autocommit</span></code> is <code class="docutils literal notranslate"><span class="pre">True</span></code> or <a class="reference internal" href="#sqlite3.LEGACY_TRANSACTION_CONTROL" title="sqlite3.LEGACY_TRANSACTION_CONTROL"><code class="xref py py-data docutils literal notranslate"><span class="pre">LEGACY_TRANSACTION_CONTROL</span></code></a>,899no implicit transaction control is executed.900Make sure to <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> before closing901to avoid losing pending changes.</p>902</dd></dl>903 904<dl class="py method">905<dt class="sig sig-object py" id="sqlite3.Connection.execute">906<span class="sig-name descname"><span class="pre">execute</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">sql</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">parameters</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">()</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.execute" title="Link to this definition">¶</a></dt>907<dd><p>Create a new <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> object and call908<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> on it with the given <em>sql</em> and <em>parameters</em>.909Return the new cursor object.</p>910</dd></dl>911 912<dl class="py method">913<dt class="sig sig-object py" id="sqlite3.Connection.executemany">914<span class="sig-name descname"><span class="pre">executemany</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">sql</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">parameters</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.executemany" title="Link to this definition">¶</a></dt>915<dd><p>Create a new <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> object and call916<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> on it with the given <em>sql</em> and <em>parameters</em>.917Return the new cursor object.</p>918</dd></dl>919 920<dl class="py method">921<dt class="sig sig-object py" id="sqlite3.Connection.executescript">922<span class="sig-name descname"><span class="pre">executescript</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">sql_script</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.executescript" title="Link to this definition">¶</a></dt>923<dd><p>Create a new <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> object and call924<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> on it with the given <em>sql_script</em>.925Return the new cursor object.</p>926</dd></dl>927 928<dl class="py method">929<dt class="sig sig-object py" id="sqlite3.Connection.create_function">930<span class="sig-name descname"><span class="pre">create_function</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">name</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">narg</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">func</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em>, <em class="sig-param"><span class="keyword-only-separator o"><abbr title="Keyword-only parameters separator (PEP 3102)"><span class="pre">*</span></abbr></span></em>, <em class="sig-param"><span class="n"><span class="pre">deterministic</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">False</span></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.create_function" title="Link to this definition">¶</a></dt>931<dd><p>Create or remove a user-defined SQL function.</p>932<dl class="field-list simple">933<dt class="field-odd">Parameters<span class="colon">:</span></dt>934<dd class="field-odd"><ul class="simple">935<li><p><strong>name</strong> (<a class="reference internal" href="stdtypes.html#str" title="str"><em>str</em></a>) – The name of the SQL function.</p></li>936<li><p><strong>narg</strong> (<a class="reference internal" href="functions.html#int" title="int"><em>int</em></a>) – The number of arguments the SQL function can accept.937If <code class="docutils literal notranslate"><span class="pre">-1</span></code>, it may take any number of arguments.</p></li>938<li><p><strong>func</strong> (<a class="reference internal" href="../glossary.html#term-callback"><span class="xref std std-term">callback</span></a> | None) – A <a class="reference internal" href="../glossary.html#term-callable"><span class="xref std std-term">callable</span></a> that is called when the SQL function is invoked.939The callable must return <a class="reference internal" href="#sqlite3-types"><span class="std std-ref">a type natively supported by SQLite</span></a>.940Set to <code class="docutils literal notranslate"><span class="pre">None</span></code> to remove an existing SQL function.</p></li>941<li><p><strong>deterministic</strong> (<a class="reference internal" href="functions.html#bool" title="bool"><em>bool</em></a>) – If <code class="docutils literal notranslate"><span class="pre">True</span></code>, the created SQL function is marked as942<a class="reference external" href="https://sqlite.org/deterministic.html">deterministic</a>,943which allows SQLite to perform additional optimizations.</p></li>944</ul>945</dd>946</dl>947<div class="versionchanged">948<p><span class="versionmodified changed">Changed in version 3.8: </span>Added the <em>deterministic</em> parameter.</p>949</div>950<div class="versionchanged">951<p><span class="versionmodified changed">Changed in version 3.15: </span>The first three parameters are now positional-only.</p>952</div>953<p>Example:</p>954<div class="highlight-pycon notranslate"><div class="highlight"><pre><span></span><span class="gp">&gt;&gt;&gt; </span><span class="kn">import</span><span class="w"> </span><span class="nn">hashlib</span>955<span class="gp">&gt;&gt;&gt; </span><span class="k">def</span><span class="w"> </span><span class="nf">md5sum</span><span class="p">(</span><span class="n">t</span><span class="p">):</span>956<span class="gp">... </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>957<span class="gp">&gt;&gt;&gt; </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">&quot;:memory:&quot;</span><span class="p">)</span>958<span class="gp">&gt;&gt;&gt; </span><span class="n">con</span><span class="o">.</span><span class="n">create_function</span><span class="p">(</span><span class="s2">&quot;md5&quot;</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="n">md5sum</span><span class="p">)</span>959<span class="gp">&gt;&gt;&gt; </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">&quot;SELECT md5(?)&quot;</span><span class="p">,</span> <span class="p">(</span><span class="sa">b</span><span class="s2">&quot;foo&quot;</span><span class="p">,)):</span>960<span class="gp">... </span>    <span class="nb">print</span><span class="p">(</span><span class="n">row</span><span class="p">)</span>961<span class="go">(&#39;acbd18db4cc2f85cedef654fccc4a4d8&#39;,)</span>962<span class="gp">&gt;&gt;&gt; </span><span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>963</pre></div>964</div>965</dd></dl>966 967<dl class="py method">968<dt class="sig sig-object py" id="sqlite3.Connection.create_aggregate">969<span class="sig-name descname"><span class="pre">create_aggregate</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">name</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">n_arg</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">aggregate_class</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.create_aggregate" title="Link to this definition">¶</a></dt>970<dd><p>Create or remove a user-defined SQL aggregate function.</p>971<dl class="field-list simple">972<dt class="field-odd">Parameters<span class="colon">:</span></dt>973<dd class="field-odd"><ul class="simple">974<li><p><strong>name</strong> (<a class="reference internal" href="stdtypes.html#str" title="str"><em>str</em></a>) – The name of the SQL aggregate function.</p></li>975<li><p><strong>n_arg</strong> (<a class="reference internal" href="functions.html#int" title="int"><em>int</em></a>) – The number of arguments the SQL aggregate function can accept.976If <code class="docutils literal notranslate"><span class="pre">-1</span></code>, it may take any number of arguments.</p></li>977<li><p><strong>aggregate_class</strong> (<a class="reference internal" href="../glossary.html#term-class"><span class="xref std std-term">class</span></a> | None) – <p>A class must implement the following methods:</p>978<ul>979<li><p><code class="docutils literal notranslate"><span class="pre">step()</span></code>: Add a row to the aggregate.</p></li>980<li><p><code class="docutils literal notranslate"><span class="pre">finalize()</span></code>: Return the final result of the aggregate as981<a class="reference internal" href="#sqlite3-types"><span class="std std-ref">a type natively supported by SQLite</span></a>.</p></li>982</ul>983<p>The number of arguments that the <code class="docutils literal notranslate"><span class="pre">step()</span></code> method must accept984is controlled by <em>n_arg</em>.</p>985<p>Set to <code class="docutils literal notranslate"><span class="pre">None</span></code> to remove an existing SQL aggregate function.</p>986</p></li>987</ul>988</dd>989</dl>990<div class="versionchanged">991<p><span class="versionmodified changed">Changed in version 3.15: </span>All three parameters are now positional-only.</p>992</div>993<p>Example:</p>994<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="k">class</span><span class="w"> </span><span class="nc">MySum</span><span class="p">:</span>995    <span class="k">def</span><span class="w"> </span><span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>996        <span class="bp">self</span><span class="o">.</span><span class="n">count</span> <span class="o">=</span> <span class="mi">0</span>997 998    <span class="k">def</span><span class="w"> </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>999        <span class="bp">self</span><span class="o">.</span><span class="n">count</span> <span class="o">+=</span> <span class="n">value</span>1000 1001    <span class="k">def</span><span class="w"> </span><span class="nf">finalize</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>1002        <span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">count</span>1003 1004<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">&quot;:memory:&quot;</span><span class="p">)</span>1005<span class="n">con</span><span class="o">.</span><span class="n">create_aggregate</span><span class="p">(</span><span class="s2">&quot;mysum&quot;</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="n">MySum</span><span class="p">)</span>1006<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;CREATE TABLE test(i)&quot;</span><span class="p">)</span>1007<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;INSERT INTO test(i) VALUES(1)&quot;</span><span class="p">)</span>1008<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;INSERT INTO test(i) VALUES(2)&quot;</span><span class="p">)</span>1009<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;SELECT mysum(i) FROM test&quot;</span><span class="p">)</span>1010<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>1011 1012<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>1013</pre></div>1014</div>1015</dd></dl>1016 1017<dl class="py method">1018<dt class="sig sig-object py" id="sqlite3.Connection.create_window_function">1019<span class="sig-name descname"><span class="pre">create_window_function</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">name</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">num_params</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">aggregate_class</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.create_window_function" title="Link to this definition">¶</a></dt>1020<dd><p>Create or remove a user-defined aggregate window function.</p>1021<dl class="field-list simple">1022<dt class="field-odd">Parameters<span class="colon">:</span></dt>1023<dd class="field-odd"><ul class="simple">1024<li><p><strong>name</strong> (<a class="reference internal" href="stdtypes.html#str" title="str"><em>str</em></a>) – The name of the SQL aggregate window function to create or remove.</p></li>1025<li><p><strong>num_params</strong> (<a class="reference internal" href="functions.html#int" title="int"><em>int</em></a>) – The number of arguments the SQL aggregate window function can accept.1026If <code class="docutils literal notranslate"><span class="pre">-1</span></code>, it may take any number of arguments.</p></li>1027<li><p><strong>aggregate_class</strong> (<a class="reference internal" href="../glossary.html#term-class"><span class="xref std std-term">class</span></a> | None) – <p>A class that must implement the following methods:</p>1028<ul>1029<li><p><code class="docutils literal notranslate"><span class="pre">step()</span></code>: Add a row to the current window.</p></li>1030<li><p><code class="docutils literal notranslate"><span class="pre">value()</span></code>: Return the current value of the aggregate.</p></li>1031<li><p><code class="docutils literal notranslate"><span class="pre">inverse()</span></code>: Remove a row from the current window.</p></li>1032<li><p><code class="docutils literal notranslate"><span class="pre">finalize()</span></code>: Return the final result of the aggregate as1033<a class="reference internal" href="#sqlite3-types"><span class="std std-ref">a type natively supported by SQLite</span></a>.</p></li>1034</ul>1035<p>The number of arguments that the <code class="docutils literal notranslate"><span class="pre">step()</span></code> and <code class="docutils literal notranslate"><span class="pre">value()</span></code> methods1036must accept is controlled by <em>num_params</em>.</p>1037<p>Set to <code class="docutils literal notranslate"><span class="pre">None</span></code> to remove an existing SQL aggregate window function.</p>1038</p></li>1039</ul>1040</dd>1041<dt class="field-even">Raises<span class="colon">:</span></dt>1042<dd class="field-even"><p><a class="reference internal" href="#sqlite3.NotSupportedError" title="sqlite3.NotSupportedError"><strong>NotSupportedError</strong></a> – If used with a version of SQLite older than 3.25.0,1043which does not support aggregate window functions.</p>1044</dd>1045</dl>1046<div class="versionadded">1047<p><span class="versionmodified added">Added in version 3.11.</span></p>1048</div>1049<p>Example:</p>1050<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="c1"># Example taken from https://www.sqlite.org/windowfunctions.html#udfwinfunc</span>1051<span class="k">class</span><span class="w"> </span><span class="nc">WindowSumInt</span><span class="p">:</span>1052    <span class="k">def</span><span class="w"> </span><span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>1053        <span class="bp">self</span><span class="o">.</span><span class="n">count</span> <span class="o">=</span> <span class="mi">0</span>1054 1055    <span class="k">def</span><span class="w"> </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>1056<span class="w">        </span><span class="sd">&quot;&quot;&quot;Add a row to the current window.&quot;&quot;&quot;</span>1057        <span class="bp">self</span><span class="o">.</span><span class="n">count</span> <span class="o">+=</span> <span class="n">value</span>1058 1059    <span class="k">def</span><span class="w"> </span><span class="nf">value</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>1060<span class="w">        </span><span class="sd">&quot;&quot;&quot;Return the current value of the aggregate.&quot;&quot;&quot;</span>1061        <span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">count</span>1062 1063    <span class="k">def</span><span class="w"> </span><span class="nf">inverse</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span>1064<span class="w">        </span><span class="sd">&quot;&quot;&quot;Remove a row from the current window.&quot;&quot;&quot;</span>1065        <span class="bp">self</span><span class="o">.</span><span class="n">count</span> <span class="o">-=</span> <span class="n">value</span>1066 1067    <span class="k">def</span><span class="w"> </span><span class="nf">finalize</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>1068<span class="w">        </span><span class="sd">&quot;&quot;&quot;Return the final value of the aggregate.</span>1069 1070<span class="sd">        Any clean-up actions should be placed here.</span>1071<span class="sd">        &quot;&quot;&quot;</span>1072        <span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">count</span>1073 1074 1075<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">&quot;:memory:&quot;</span><span class="p">)</span>1076<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;CREATE TABLE test(x, y)&quot;</span><span class="p">)</span>1077<span class="n">values</span> <span class="o">=</span> <span class="p">[</span>1078    <span class="p">(</span><span class="s2">&quot;a&quot;</span><span class="p">,</span> <span class="mi">4</span><span class="p">),</span>1079    <span class="p">(</span><span class="s2">&quot;b&quot;</span><span class="p">,</span> <span class="mi">5</span><span class="p">),</span>1080    <span class="p">(</span><span class="s2">&quot;c&quot;</span><span class="p">,</span> <span class="mi">3</span><span class="p">),</span>1081    <span class="p">(</span><span class="s2">&quot;d&quot;</span><span class="p">,</span> <span class="mi">8</span><span class="p">),</span>1082    <span class="p">(</span><span class="s2">&quot;e&quot;</span><span class="p">,</span> <span class="mi">1</span><span class="p">),</span>1083<span class="p">]</span>1084<span class="n">cur</span><span class="o">.</span><span class="n">executemany</span><span class="p">(</span><span class="s2">&quot;INSERT INTO test VALUES(?, ?)&quot;</span><span class="p">,</span> <span class="n">values</span><span class="p">)</span>1085<span class="n">con</span><span class="o">.</span><span class="n">create_window_function</span><span class="p">(</span><span class="s2">&quot;sumint&quot;</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="n">WindowSumInt</span><span class="p">)</span>1086<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;&quot;&quot;</span>1087<span class="s2">    SELECT x, sumint(y) OVER (</span>1088<span class="s2">        ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING</span>1089<span class="s2">    ) AS sum_y</span>1090<span class="s2">    FROM test ORDER BY x</span>1091<span class="s2">&quot;&quot;&quot;</span><span class="p">)</span>1092<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>1093<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>1094</pre></div>1095</div>1096</dd></dl>1097 1098<dl class="py method">1099<dt class="sig sig-object py" id="sqlite3.Connection.create_collation">1100<span class="sig-name descname"><span class="pre">create_collation</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">name</span></span></em>, <em class="sig-param"><span class="n"><span class="pre">callable</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.create_collation" title="Link to this definition">¶</a></dt>1101<dd><p>Create a collation named <em>name</em> using the collating function <em>callable</em>.1102<em>callable</em> is passed two <a class="reference internal" href="stdtypes.html#str" title="str"><code class="xref py py-class docutils literal notranslate"><span class="pre">string</span></code></a> arguments,1103and it should return an <a class="reference internal" href="functions.html#int" title="int"><code class="xref py py-class docutils literal notranslate"><span class="pre">integer</span></code></a>:</p>1104<ul class="simple">1105<li><p><code class="docutils literal notranslate"><span class="pre">1</span></code> if the first is ordered higher than the second</p></li>1106<li><p><code class="docutils literal notranslate"><span class="pre">-1</span></code> if the first is ordered lower than the second</p></li>1107<li><p><code class="docutils literal notranslate"><span class="pre">0</span></code> if they are ordered equal</p></li>1108</ul>1109<p>The following example shows a reverse sorting collation:</p>1110<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="k">def</span><span class="w"> </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>1111    <span class="k">if</span> <span class="n">string1</span> <span class="o">==</span> <span class="n">string2</span><span class="p">:</span>1112        <span class="k">return</span> <span class="mi">0</span>1113    <span class="k">elif</span> <span class="n">string1</span> <span class="o">&lt;</span> <span class="n">string2</span><span class="p">:</span>1114        <span class="k">return</span> <span class="mi">1</span>1115    <span class="k">else</span><span class="p">:</span>1116        <span class="k">return</span> <span class="o">-</span><span class="mi">1</span>1117 1118<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">&quot;:memory:&quot;</span><span class="p">)</span>1119<span class="n">con</span><span class="o">.</span><span class="n">create_collation</span><span class="p">(</span><span class="s2">&quot;reverse&quot;</span><span class="p">,</span> <span class="n">collate_reverse</span><span class="p">)</span>1120 1121<span class="n">cur</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;CREATE TABLE test(x)&quot;</span><span class="p">)</span>1122<span class="n">cur</span><span class="o">.</span><span class="n">executemany</span><span class="p">(</span><span class="s2">&quot;INSERT INTO test(x) VALUES(?)&quot;</span><span class="p">,</span> <span class="p">[(</span><span class="s2">&quot;a&quot;</span><span class="p">,),</span> <span class="p">(</span><span class="s2">&quot;b&quot;</span><span class="p">,)])</span>1123<span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;SELECT x FROM test ORDER BY x COLLATE reverse&quot;</span><span class="p">)</span>1124<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">cur</span><span class="p">:</span>1125    <span class="nb">print</span><span class="p">(</span><span class="n">row</span><span class="p">)</span>1126<span class="n">con</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>1127</pre></div>1128</div>1129<p>Remove a collation function by setting <em>callable</em> to <code class="docutils literal notranslate"><span class="pre">None</span></code>.</p>1130<div class="versionchanged">1131<p><span class="versionmodified changed">Changed in version 3.11: </span>The collation name can contain any Unicode character.  Earlier, only1132ASCII characters were allowed.</p>1133</div>1134</dd></dl>1135 1136<dl class="py method">1137<dt class="sig sig-object py" id="sqlite3.Connection.interrupt">1138<span class="sig-name descname"><span class="pre">interrupt</span></span><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.interrupt" title="Link to this definition">¶</a></dt>1139<dd><p>Call this method from a different thread to abort any queries that might1140be executing on the connection.1141Aborted queries will raise an <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>1142</dd></dl>1143 1144<dl class="py method">1145<dt class="sig sig-object py" id="sqlite3.Connection.set_authorizer">1146<span class="sig-name descname"><span class="pre">set_authorizer</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">authorizer_callback</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.set_authorizer" title="Link to this definition">¶</a></dt>1147<dd><p>Register <a class="reference internal" href="../glossary.html#term-callable"><span class="xref std std-term">callable</span></a> <em>authorizer_callback</em> to be invoked1148for each attempt to access a column of a table in the database.1149The callback should return one of <a class="reference internal" href="#sqlite3.SQLITE_OK" title="sqlite3.SQLITE_OK"><code class="xref py py-const docutils literal notranslate"><span class="pre">SQLITE_OK</span></code></a>,1150<a class="reference internal" href="#sqlite3.SQLITE_DENY" title="sqlite3.SQLITE_DENY"><code class="xref py py-const docutils literal notranslate"><span class="pre">SQLITE_DENY</span></code></a>, or <a class="reference internal" href="#sqlite3.SQLITE_IGNORE" title="sqlite3.SQLITE_IGNORE"><code class="xref py py-const docutils literal notranslate"><span class="pre">SQLITE_IGNORE</span></code></a>1151to signal how access to the column should be handled1152by the underlying SQLite library.</p>1153<p>The first argument to the callback signifies what kind of operation is to be1154authorized. The second and third argument will be arguments or <code class="docutils literal notranslate"><span class="pre">None</span></code>1155depending on the first argument. The 4th argument is the name of the database1156(“main”, “temp”, etc.) if applicable. The 5th argument is the name of the1157inner-most trigger or view that is responsible for the access attempt or1158<code class="docutils literal notranslate"><span class="pre">None</span></code> if this access attempt is directly from input SQL code.</p>1159<p>Please consult the SQLite documentation about the possible values for the first1160argument and the meaning of the second and third argument depending on the first1161one. All necessary constants are available in the <code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> module.</p>1162<p>Passing <code class="docutils literal notranslate"><span class="pre">None</span></code> as <em>authorizer_callback</em> will disable the authorizer.</p>1163<div class="versionchanged">1164<p><span class="versionmodified changed">Changed in version 3.11: </span>Added support for disabling the authorizer using <code class="docutils literal notranslate"><span class="pre">None</span></code>.</p>1165</div>1166<div class="versionchanged">1167<p><span class="versionmodified changed">Changed in version 3.15: </span>The only parameter is now positional-only.</p>1168</div>1169</dd></dl>1170 1171<dl class="py method">1172<dt class="sig sig-object py" id="sqlite3.Connection.set_progress_handler">1173<span class="sig-name descname"><span class="pre">set_progress_handler</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">progress_handler</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em>, <em class="sig-param"><span class="n"><span class="pre">n</span></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.set_progress_handler" title="Link to this definition">¶</a></dt>1174<dd><p>Register <a class="reference internal" href="../glossary.html#term-callable"><span class="xref std std-term">callable</span></a> <em>progress_handler</em> to be invoked for every <em>n</em>1175instructions of the SQLite virtual machine. This is useful if you want to1176get called from SQLite during long-running operations, for example to update1177a GUI.</p>1178<p>If you want to clear any previously installed progress handler, call the1179method with <code class="docutils literal notranslate"><span class="pre">None</span></code> for <em>progress_handler</em>.</p>1180<p>Returning a non-zero value from the handler function will terminate the1181currently executing query and cause it to raise 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>1182exception.</p>1183<div class="versionchanged">1184<p><span class="versionmodified changed">Changed in version 3.15: </span>The first parameter is now positional-only.</p>1185</div>1186</dd></dl>1187 1188<dl class="py method">1189<dt class="sig sig-object py" id="sqlite3.Connection.set_trace_callback">1190<span class="sig-name descname"><span class="pre">set_trace_callback</span></span><span class="sig-paren">(</span><em class="sig-param"><span class="n"><span class="pre">trace_callback</span></span></em>, <em class="sig-param"><span class="positional-only-separator o"><abbr title="Positional-only parameter separator (PEP 570)"><span class="pre">/</span></abbr></span></em><span class="sig-paren">)</span><a class="headerlink" href="#sqlite3.Connection.set_trace_callback" title="Link to this definition">¶</a></dt>1191<dd><p>Register <a class="reference internal" href="../glossary.html#term-callable"><span class="xref std std-term">callable</span></a> <em>trace_callback</em> to be invoked1192for each SQL statement that is actually executed by the SQLite backend.</p>1193<p>The only argument passed to the callback is the statement (as1194<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>) that is being executed. The return value of the callback is1195ignored. Note that the backend does not only run statements passed to the1196<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> methods.  Other sources include the1197<a class="reference internal" href="#sqlite3-controlling-transactions"><span class="std std-ref">transaction management</span></a> of the1198<code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlite3</span></code> module and the execution of triggers defined in the current1199database.</p>1200<p>Passing <code class="docutils literal notranslate"><span class="pre">None</span></code> as <em>trace_callback</em> will disable the trace callback.</p>

Showing the first 1,200 of 2984 lines. Download the file for the rest.