Quick prototyping with sqlite3

Batteries included is a blog series about the Python Standard Library. Each day, I share insights, ideas and examples for different parts of the library. Blaugust is an annual blogging festival in August where the goal is to write a blog post every day of the month.

Many applications require some way of storing information and often that storage is a database. Setting up a full database integration with something like Postgres or MySQL can be a project of its own and might not be worth the work in the early prototyping stage.

Python has built-in bindings with database system sqlite3 that can operate on files on the disk or run completely in memory. I really like the flexibility of it when figuring out things before commiting to building a more permanent database system for the software.

To use it in Python, you need to have sqlite3 itself installed. If it’s not already installed in your system, you can follow the instructions behind the previous link to find out how to get it up and running on yours.

Quickstart

To use sqlite3 in memory, run

import sqlite3

# Initialize
connection = sqlite3.connect(':memory:')
cursor = connection.cursor()

# Create table and insert a row
cursor.execute('CREATE TABLE post(title, body, author)')
cursor.execute('INSERT INTO post VALUES ("Hello world", "My first blog post", "Juhis")')

# Commit insertion to persist it
connection.commit()

# Fetch

result = cursor.execute('SELECT * FROM post')
rows = result.fetchall()

print(rows)
# prints
# [('Hello world', 'My first blog post', 'Juhis')]

When using the in-memory database, it’s emptied once the script runs (or your REPL session is closed) so it’s not the solution for permanent options.

Sqlite3 works with file-based storage too. Instead of :memory: , give connect method a filename where you want to store your database, keeping everything else as-is.

connection = sqlite3.connect('my-database.db')

After running this script, you can find a binary file my-database.db in the folder where you ran the script. It will persist between sessions so you can keep on prototyping on it.

Fast prototyping

While you can use sqlite in production as well, in my opinion it really shines as quick-and-dirty prototyping database engine.

It’s quick to create and modify tables, insert and query data without spending time on finetuning datatypes and indexes and such. Usually, that’s the stuff you want to do when you know what you’re building. Unoptimized database is hardly the bottleneck when the main issue is trying to figure out what the end user needs and wants.

Knowing how to interact with sqlite databases will make you a more capable Python developer in various environments.