<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: yongchang zhang</title>
    <description>The latest articles on DEV Community by yongchang zhang (@yongchang_zhang_d603bd1c1).</description>
    <link>https://dev.to/yongchang_zhang_d603bd1c1</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4250780%2F13142999-1922-4358-b335-1547f7ff1e3d.png</url>
      <title>DEV Community: yongchang zhang</title>
      <link>https://dev.to/yongchang_zhang_d603bd1c1</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/yongchang_zhang_d603bd1c1"/>
    <language>en</language>
    <item>
      <title>Python SQLite: a tiny notes database with safe queries and rollback</title>
      <dc:creator>yongchang zhang</dc:creator>
      <pubDate>Wed, 18 Nov 2026 01:46:59 +0000</pubDate>
      <link>https://dev.to/yongchang_zhang_d603bd1c1/python-sqlite-a-tiny-notes-database-with-safe-queries-and-rollback-5anp</link>
      <guid>https://dev.to/yongchang_zhang_d603bd1c1/python-sqlite-a-tiny-notes-database-with-safe-queries-and-rollback-5anp</guid>
      <description>&lt;p&gt;A useful first database exercise should teach more than how to insert a row. It should also show what happens when a value contains an apostrophe, when a write fails, and when the connection closes.&lt;/p&gt;

&lt;p&gt;Here is a small, self-contained notes example. It runs in memory, so it does not create a database file or alter an existing one.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="n"&gt;sqlite3&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;contextlib&lt;/span&gt; &lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="n"&gt;closing&lt;/span&gt;

&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="nf"&gt;closing&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;sqlite3&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;connect&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;":memory:"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"""
        CREATE TABLE notes (
            id INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            done INTEGER NOT NULL DEFAULT 0 CHECK (done IN (0, 1))
        )
    """&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

    &lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;executemany&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
            &lt;span class="s2"&gt;"INSERT INTO notes (title) VALUES (?)"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
            &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"Read about SELECT"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"Review O'Reilly example"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
            &lt;span class="s2"&gt;"UPDATE notes SET done = ? WHERE id = ?"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="p"&gt;)&lt;/span&gt;

    &lt;span class="n"&gt;pending&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="s2"&gt;"SELECT id, title FROM notes WHERE done = ? ORDER BY id"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;fetchall&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;pending&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

    &lt;span class="k"&gt;try&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
            &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
                &lt;span class="s2"&gt;"INSERT INTO notes (title) VALUES (?)"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
                &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"Temporary note"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
            &lt;span class="p"&gt;)&lt;/span&gt;
            &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
                &lt;span class="s2"&gt;"INSERT INTO notes (title, done) VALUES (?, ?)"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
                &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"Invalid note"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
            &lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;except&lt;/span&gt; &lt;span class="n"&gt;sqlite3&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nc"&gt;IntegrityError&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="k"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"Rolled back both inserts"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

    &lt;span class="k"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"SELECT COUNT(*) FROM notes"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;fetchone&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The example was executed before publication and produced:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;[(2, "Review O'Reilly example")]
Rolled back both inserts
2
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  What to notice
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Values are separate from SQL.&lt;/strong&gt; The question marks bind values instead of building SQL with string concatenation. The apostrophe in the second title is ordinary data. The comma in &lt;code&gt;(0,)&lt;/code&gt; makes it a one-item tuple. Placeholders are for values, not arbitrary table or column names.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;A failed batch should not leave half the work behind.&lt;/strong&gt; The second transaction inserts a temporary note, then violates the &lt;code&gt;done&lt;/code&gt; constraint. The exception leaves the &lt;code&gt;with db&lt;/code&gt; block, triggering rollback. The final count is still two. Catching the exception outside that block matters.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Transaction handling and connection cleanup are different jobs.&lt;/strong&gt; Here, &lt;code&gt;with db&lt;/code&gt; manages commit/rollback; &lt;code&gt;closing(...)&lt;/code&gt; closes the connection. This example uses sqlite3's default legacy transaction mode; changing connection transaction settings can change those semantics.&lt;/p&gt;

&lt;h2&gt;
  
  
  Try it yourself
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;Change the invalid &lt;code&gt;done&lt;/code&gt; value from &lt;code&gt;2&lt;/code&gt; to &lt;code&gt;1&lt;/code&gt;. What count do you expect?&lt;/li&gt;
&lt;li&gt;Add a query that returns completed notes only.&lt;/li&gt;
&lt;li&gt;Add a &lt;code&gt;DELETE ... WHERE id = ?&lt;/code&gt; operation inside a transaction, then inspect the remaining rows.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Keep the in-memory database while experimenting. If you later switch to a file, plan how the table is created and how repeated runs should behave rather than blindly replaying inserts.&lt;/p&gt;

&lt;p&gt;Reference: &lt;a href="https://docs.python.org/3/library/sqlite3.html" rel="noopener noreferrer"&gt;Python's sqlite3 documentation&lt;/a&gt;.&lt;/p&gt;

&lt;p&gt;Disclosure: I run &lt;a href="https://www.howcodex.com/" rel="noopener noreferrer"&gt;Howcodex&lt;/a&gt;, an English-language IT tutorial site. Its &lt;a href="https://www.howcodex.com/python_sqlite/index.htm" rel="noopener noreferrer"&gt;Python SQLite tutorial collection&lt;/a&gt; is related reading. This post is a standalone example, prepared with AI assistance; the code and output above were checked by execution.&lt;/p&gt;

</description>
      <category>python</category>
    </item>
  </channel>
</rss>
