SQLite and Python

by Toby Ho

SQLite is a lightweight SQL relational database. Although it is not built for multi-user concurrent applications, it has become a staple data storage option for mobile, desktop, and even websites for client-side storage. Chances are you’ve encountered SQLite as the default development database option for web frameworks like Django. What you might not know is there’s a SQLite installation inside every Chrome, Firefox, Safari browser, every Android, iPhone, iOS device, as well as every Python installation, making it easily the most widely deployed database on the planet.

I’ve become quite fond of SQLite having worked with it in many different contexts: Python, Ruby, JavaScript, C, Go, Django, Rails, work, business, pleasure, before breakfast, with lunch, after drinks, on a hike, driving, in the shower, did I mention Python?

Which is why I want to share it with you. In the following text, I’ll show you all the building blocks you need to build a boring todo list app. The only thing exciting about this is you’ll be building it with SQLite and Python. Are you ready?

As SQLite comes with Python, all you have to do is start a Python shell and

>>> import sqlite3

You can see the version of SQLite by

>>> sqlite3.sqlite_version
'3.45.3'

My version of Python (3.12.4) comes with SQLite 3.45.3. Let’s go!

Now let’s create a database. A SQLite database is entirely contained in one file, commonly suffixed .sqlite or .db. Let’s create one now:

>>> db = sqlite3.connect("task.db")
>>> db
<sqlite3.Connection object at 0x107cae2f0>

Now we have a connection to the database which is stored in the file database.db. If that file doesn’t exist, it will be automatically created. Now let’s create a table:

>>> db.execute("""
... create table task (
...   id integer primary key,
...   name text,
...   complete integer
... );
... """)
<sqlite3.Cursor object at 0x107f228c0>

Now we have a task table containing 3 columns:

  • id – a standard integer primary key that auto-assigns values if you insert rows while omitting it
  • name – a text name field
  • complete – a boolean flag to mark if this task is completed. SQLite does not have a boolean type, so we use 1 to mean true and 0 to mean false

SQLite has a grand total of 5 different data types:

  • NULL
  • integer
  • real (a.k.a floating point numbers)
  • text (a.k.a varchar a.k.a string)
  • blob

A glaring omission is a date or datetime type. You are advised to either use a text representation of dates or an integer representation. SQLite provides some date functions that can work with either representation. To me, this radical simplicity truly makes SQLite the JSON of databases.

Anyway, now that we have a table, let’s insert some tasks:

>>> db.execute("""
... insert into task (name, complete) values
... ('Make dinner', 0),
... ('Water plants', 0),
... ('Math homework', 0);
... """)
<sqlite3.Cursor object at 0x107f22a40>

After making modifications to data, always

>>> db.commit()

in order to persist it to the database.

We’ve inserted 3 tasks, omitting the id value to allow it to be auto-assigned. Let’s see what we have:

>>> results = db.execute("""
... select * from task;
... """).fetchall()

The execute method returns a Cursor object. We in turn call the fetchall() method of the cursor which goes through the rows of the result set, collects them and returns them as a list of tuples. We can see what they are:

>>> for row in results:
...   print(row)
... 
(1, 'Make dinner', 0)
(2, 'Water plants', 0)
(3, 'Math homework', 0)

Okay, let’s say later you complete your math homework, and you want to mark it complete:

>>> db.execute("""
... update task
... set complete = 1
... where id = 3;
... """)
<sqlite3.Cursor object at 0x107f22ac0>
>>> db.commit()

At this point I should say something about query parameters and SQL hygiene. Let’s say you want to allow the id and complete values in the SQL statement to be substituted by variables. Maybe you want to write a function to mark a task:

def mark_task(task_id, complete):
    db.execute(
        "update task " +
        "set complete = " + str(complete) +
        "where id = " + str(task_id)
    )
    db.commit()

This piece of code works, but is susceptible to a security vulnerablity. If an attacker could construct data that goes into either of the parameters of this function, they can change the resulting SQL query at will.

Granted, the security concerns for a single-user database is very low compared to a multi-user distributed database. But as a matter of habit, it’s always good to practice good SQL hygiene, especially when it’s easy to do. So, instead of the above, do:

def mark_task(task_id, complete):
    db.execute("""
    update task
    set complete = ?
    where id = ?
    """, (complete, task_id))
    db.commit()

This way, the SQLite database driver will take care of escaping the passed in values to prevent task_id or complete from arbitrarily changing the resulting SQL statement. Alternatively, you can give names to the parameters like so:

def mark_task(task_id, complete):
    params = {
        "task_id": task_id,
        "complete": complete
    }
    db.execute("""
    update task
    set complete = :complete
    where id = :task_id
    """, params)
    db.commit()

At this point, I’ve shown you how to

  • create and connect to a SQLite database
  • create a table in that database
  • insert data into that table
  • query data back
  • update existing rows in the table

You should be able to build a simple todo list application using SQLite from scratch, whether it’s a simple command-line application, a desktop GUI application, or a web application. Have at it!

Resources

  • SQLite SQL language reference – covers the SQLite dialect of SQL from official site. While there are a bunch of SQLite learning material out there, I couldn’t find one that didn’t have ad banners. The documentation is a little dense, but does contain examples if you scroll down.
  • Python sqlite3 module API – covers built-in Python interface to Sqlite: the sqlite3 module. Lots of useful code examples. No ads.
  • If you’d like a reference implementation, I’ve created a very simple command-line version.

Toby Ho is a software developer who loves to learn and teach. Currently interested in compilers, programming languages, software architecture, assembly language programming, and debugging. His website is https://tobyho.com/

Pablo Rivera

,