Offensive Security Researcher & Quantum Cryptography Analyst

SQLite in Python: A Complete Guide to the sqlite3 Module

Abdul Wahab Junaid
SQLite in Python

If you have ever needed a database for a small project, a prototype, or even a desktop application, and you did not want the hassle of installing and configuring a full database server, you have probably run into SQLite. What makes SQLite even more appealing for Python developers is that you do not need to install anything extra to use it. Python ships with a built-in module called sqlite3, and it has been part of the standard library since Python 2.5. In this article, I am going to walk you through everything you need to know to start using SQLite in your Python projects, from the absolute basics to some of the more advanced patterns I use in real applications.

What Is SQLite and Why Use It With Python?

SQLite is a lightweight, file-based, serverless relational database engine. Unlike MySQL or PostgreSQL, there is no separate server process running in the background. The entire database lives in a single file on disk (or even in memory), and your application talks to it directly through a library. This makes SQLite incredibly easy to set up, back up, and move around. You can literally copy a .db file to another computer and it will work exactly the same way.

I reach for SQLite whenever I am building:

Because Python includes the sqlite3 module out of the box, there is zero friction. No pip install, no external dependencies, nothing to configure. You just import sqlite3 and you are ready to go.

Getting Started: Connecting to a Database

The first step in working with SQLite in Python is establishing a connection. The sqlite3.connect() function does this for you, and it also creates the database file if it does not already exist.

import sqlite3

connection = sqlite3.connect("my_database.db")

If you only need a temporary database that disappears once your program ends, you can create an in-memory database instead:

connection = sqlite3.connect(":memory:")

I use in-memory databases a lot when writing unit tests. They are fast, they do not leave files lying around on disk, and every test run starts with a completely clean slate.

Once you have a connection, you need a cursor object to actually execute SQL statements. Think of the cursor as your tool for sending commands to the database and reading back results.

cursor = connection.cursor()

Creating Tables

Before you can store any data, you need to define a table structure. Here is a simple example where I create a table to store information about books:

cursor.execute("""
    CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        author TEXT NOT NULL,
        published_year INTEGER,
        price REAL
    )
""")
connection.commit()

A few things worth pointing out here. I always add IF NOT EXISTS so the script does not throw an error if I run it more than once. I also always call connection.commit() after making changes, because SQLite transactions are not written to disk permanently until you commit them. If you forget to commit, your changes might be lost, especially if the program exits unexpectedly.

Inserting Data

There are a couple of ways to insert data, but I strongly recommend using parameterized queries rather than string formatting. Here is why that matters, and how to do it properly.

cursor.execute(
    "INSERT INTO books (title, author, published_year, price) VALUES (?, ?, ?, ?)",
    ("Atomic Habits", "James Clear", 2018, 16.99)
)
connection.commit()

Notice the question marks in the SQL string. These are placeholders, and the actual values are passed as a tuple in the second argument. This is called a parameterized query, and it is the safest way to insert user-provided data into your database. I will talk more about why this matters when I get to the SQL injection section later in this series, but for now, just remember: never build SQL strings by concatenating raw user input.

If you need to insert multiple rows at once, executemany() is your friend:

books_to_add = [
    ("Deep Work", "Cal Newport", 2016, 14.99),
    ("The Pragmatic Programmer", "Andrew Hunt", 1999, 39.99),
    ("Clean Code", "Robert Martin", 2008, 32.50),
]

cursor.executemany(
    "INSERT INTO books (title, author, published_year, price) VALUES (?, ?, ?, ?)",
    books_to_add
)
connection.commit()

This is much faster than looping through and calling execute() individually, especially when you are inserting a large number of rows.

Reading Data

To fetch data, you execute a SELECT statement and then pull the results using one of the fetch methods.

cursor.execute("SELECT * FROM books WHERE published_year > ?", (2000,))
rows = cursor.fetchall()

for row in rows:
    print(row)

You have three main options for retrieving results:

By default, rows come back as plain tuples, which means you access values by index like row[0], row[1], and so on. Personally, I find this a bit clunky, especially in larger codebases. That is why I almost always set the row_factory to sqlite3.Row:

connection.row_factory = sqlite3.Row
cursor = connection.cursor()
cursor.execute("SELECT * FROM books")
for row in cursor.fetchall():
    print(row["title"], row["author"])

This lets you access columns by name, which makes your code far more readable and less prone to bugs when the table structure changes.

Updating and Deleting Records

Updating and deleting records follows the same parameterized pattern:

cursor.execute(
    "UPDATE books SET price = ? WHERE title = ?",
    (18.99, "Atomic Habits")
)
connection.commit()

cursor.execute("DELETE FROM books WHERE published_year < ?", (1990,))
connection.commit()

Always double check your WHERE clause before running an UPDATE or DELETE. I have seen plenty of developers accidentally wipe out an entire table because they forgot the condition. It is a good habit to first run the equivalent SELECT statement to see exactly which rows will be affected before committing to the change.

Using Context Managers for Cleaner Code

Python’s with statement works nicely with SQLite connections, though there is a subtlety worth knowing. When you use with connection:, it automatically commits the transaction if everything succeeds, or rolls it back if an exception occurs. It does not, however, automatically close the connection.

with sqlite3.connect("my_database.db") as connection:
    cursor = connection.cursor()
    cursor.execute("INSERT INTO books (title, author) VALUES (?, ?)", ("1984", "George Orwell"))

I still like to close the connection explicitly when I am done with it, either with connection.close() or by wrapping the whole thing in a try/finally block.

Handling Transactions Properly

By default, sqlite3 opens transactions automatically before any data-modifying statement (INSERT, UPDATE, DELETE), and it is your job to commit or roll them back. If something goes wrong partway through a batch of operations, you can roll everything back to keep your data consistent:

try:
    cursor.execute("INSERT INTO books (title, author) VALUES (?, ?)", ("Book A", "Author A"))
    cursor.execute("INSERT INTO books (title, author) VALUES (?, ?)", ("Book B", "Author B"))
    connection.commit()
except sqlite3.Error as e:
    print(f"An error occurred: {e}")
    connection.rollback()

This pattern is essential whenever you are performing multiple related operations that need to succeed or fail together, like transferring money between two accounts in a financial application.

Working With Different Data Types

SQLite uses what is called dynamic typing, or “type affinity,” which is different from most other database systems. It does not strictly enforce that a column only holds one type of data, though it does try to coerce values toward the declared type. Python’s sqlite3 module handles the translation between Python types and SQLite types automatically:

If you need to store more complex types like dates, dictionaries, or lists, you will need to convert them to one of these basic types first. A common approach for storing structured data is to serialize it with json.dumps() before insertion and parse it back with json.loads() when reading.

import json

data = {"genre": "self-help", "rating": 4.5}
cursor.execute("INSERT INTO metadata (info) VALUES (?)", (json.dumps(data),))

Error Handling

Real-world code needs to handle failures gracefully. The sqlite3 module raises a hierarchy of exceptions, and catching sqlite3.Error is a safe general-purpose choice, though you can catch more specific exceptions like sqlite3.IntegrityError for constraint violations:

try:
    cursor.execute("INSERT INTO books (id, title) VALUES (?, ?)", (1, "Duplicate ID Book"))
    connection.commit()
except sqlite3.IntegrityError:
    print("A book with that ID already exists.")
except sqlite3.Error as e:
    print(f"Database error: {e}")

Practical Example: A Small Contact Manager

Let me tie everything together with a slightly larger example, a simple contact manager script:

import sqlite3

def create_connection(db_file):
    return sqlite3.connect(db_file)

def create_table(connection):
    connection.execute("""
        CREATE TABLE IF NOT EXISTS contacts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            phone TEXT,
            email TEXT UNIQUE
        )
    """)
    connection.commit()

def add_contact(connection, name, phone, email):
    try:
        connection.execute(
            "INSERT INTO contacts (name, phone, email) VALUES (?, ?, ?)",
            (name, phone, email)
        )
        connection.commit()
        print(f"Added {name} successfully.")
    except sqlite3.IntegrityError:
        print(f"A contact with the email {email} already exists.")

def list_contacts(connection):
    connection.row_factory = sqlite3.Row
    cursor = connection.execute("SELECT * FROM contacts ORDER BY name")
    for row in cursor.fetchall():
        print(f"{row['id']}: {row['name']} - {row['phone']} - {row['email']}")

if __name__ == "__main__":
    conn = create_connection("contacts.db")
    create_table(conn)
    add_contact(conn, "Sarah Khan", "555-0192", "sarah@example.com")
    add_contact(conn, "James Lee", "555-0143", "james@example.com")
    list_contacts(conn)
    conn.close()

This small script demonstrates connecting, creating a table, inserting data safely, handling a uniqueness constraint, and reading data back with named columns. It is roughly the pattern I use as a starting point for most small SQLite-backed tools I build.

Best Practices I Follow

Over the years, I have picked up a handful of habits that save me from headaches later:

Always use parameterized queries. Never format SQL strings with f-strings or % formatting when user input is involved. This is the single most important rule for avoiding SQL injection.

Close your connections. Use try/finally or a context manager to make sure connections are closed properly, especially in long-running applications.

Enable foreign key constraints. SQLite does not enforce foreign keys by default. You need to explicitly turn this on with connection.execute("PRAGMA foreign_keys = ON") for each connection.

Use sqlite3.Row as your row factory. It makes your code far more readable than working with plain tuples.

Batch your inserts. When inserting many rows, use executemany() instead of looping over execute().

Handle concurrency carefully. SQLite allows multiple readers but only one writer at a time by default. If you are building something with heavier concurrent write needs, consider enabling WAL (Write-Ahead Logging) mode with PRAGMA journal_mode=WAL, or consider whether a client-server database might be a better fit for your use case.

Back up your database file regularly. Since SQLite databases are just files, backing them up is as simple as copying the file, but you should do this while no write operations are in progress, or use the built-in backup API (connection.backup()) for a safe, consistent copy.

Common Pitfalls to Avoid

A few mistakes I see (and have made myself) repeatedly:

Forgetting to call commit() after modifying data, then wondering why changes seem to disappear. Assuming SQLite enforces column types strictly, when in reality it uses type affinity and can be more permissive than you expect. Not handling the sqlite3.IntegrityError exception when working with unique or foreign key constraints, leading to unhandled crashes. Opening a new connection for every single query in a loop, which is unnecessarily slow. It is much better to open one connection and reuse it for the duration of your program or request.

Wrapping Up

The sqlite3 module makes it incredibly simple to add persistent storage to a Python project without any external dependencies or server setup. Once you understand connections, cursors, parameterized queries, and transactions, you have basically covered 90 percent of what you will use day to day. From here, the natural next steps are learning about SQLite’s built-in functions, understanding how to properly prevent SQL injection at a deeper level, working with date and time values, and understanding features like the AUTOINCREMENT keyword. I cover each of those topics in dedicated articles, so if you want to go deeper into any one of them, that is a great place to continue.

SQLite paired with Python is one of those combinations that just works, and once you get comfortable with the basic workflow shown here, you will find yourself reaching for it constantly for small tools, prototypes, and even some production applications.

Exit mobile version