From zero to a project: a Python app that works with an SQLite database

Every program we wrote in the earlier articles forgets everything when it closes. You enter students’ names and marks, close the program, and the data is gone. Real programs need permanent storage, and the most organised way to get it is a database. In this article we build a small but complete project together: an app for managing a personal book library that stores the books in an SQLite database and lets you add, search, update and delete them. These operations are known by the abbreviation CRUD, and they are the basis of most apps you use every day, from to-do lists to online shops.

Why a database? And why SQLite?

You might ask: why not save the data in a text file? You can, for simple data, but as it grows you will need fast searching, changes without rewriting the whole file, and protection against duplicated or corrupted data. Databases were designed for all of that.

SQLite is a lightweight database kept entirely in one file on your computer. It needs no server or separate installation, and best of all it is built into Python through the sqlite3 module. It is not just a teaching toy: it is used in a huge number of apps and devices around the world, from phones to web browsers. And the SQL you learn with it carries over to larger databases such as MySQL and PostgreSQL.

Key ideas before we start

A relational database organises information in tables, like the spreadsheets you know, but with stricter rules. Each table represents one kind of thing: a table of books, a table of readers, a table of loans. Each row represents one item, each column one property of it, and every column has a fixed type: text, whole number or decimal. The strength of this organisation is that tables link to each other through keys; the loans table does not repeat the book’s title and author every time, it stores only the book’s number and points to its row in the books table. That way each fact is stored in one place, and if you correct an author’s name once, the correction appears everywhere. SQL is the language we use to talk to the database: we ask it to create a table, add a row, search, update or delete. It is a declarative language: you say “what” you want and leave the database to decide “how” to do it as fast as possible.

The project plan

Step 1: design the table

Before any code, think about the data. Each book has a title, an author, a publication year and a reading status. In a database we keep that in a table: each row is a book, and each column is a piece of information:

The primary key is an important idea: a number that never repeats, which we use to point to a particular book when updating or deleting it, even if titles are similar.

Step 2: connect and create the table

import sqlite3

conn = sqlite3.connect("library.db")
conn.execute("""
    CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        author TEXT,
        year INTEGER,
        status TEXT DEFAULT 'unread'
    )
""")
conn.commit()

The connect function opens the database file and creates it if it does not exist. The phrase IF NOT EXISTS makes the code safe to run more than once. And commit writes the changes to the file; without it they may be lost.

Step 3: the four operations

Adding

def add_book(title, author, year):
    conn.execute(
        "INSERT INTO books (title, author, year) VALUES (?, ?, ?)",
        (title, author, year),
    )
    conn.commit()

Notice the question marks. These are parameterised queries, and they are the most important safety rule in this article: never put user input directly into the text of a query, for example with f-strings. If you do, someone could type text into the title field that changes the meaning of the query and deletes your data – an attack known as “SQL injection”. The question marks make the sqlite3 library treat the input purely as data, whatever it contains.

Searching

def find_books(word):
    rows = conn.execute(
        "SELECT id, title, author, year, status FROM books "
        "WHERE title LIKE ? OR author LIKE ? ORDER BY title",
        (f"%{word}%", f"%{word}%"),
    )
    return rows.fetchall()

The word LIKE with percent signs finds any title or author containing the word anywhere. ORDER BY sorts the results.

Updating and deleting

def set_status(book_id, status):
    conn.execute("UPDATE books SET status = ? WHERE id = ?", (status, book_id))
    conn.commit()

def delete_book(book_id):
    conn.execute("DELETE FROM books WHERE id = ?", (book_id,))
    conn.commit()

Pay attention to the WHERE clause. A DELETE statement without it deletes every row in the table! That is why we always identify the book by its unique number, and it is wise for the program to ask the user to confirm before deleting.

In the following video Corey Schafer gives a thorough explanation of using SQLite from Python that complements what you have learned here:

Step 4: a simple interface

Let us tie everything together with a command menu in the terminal: the program shows numbered options (add, search, change status, delete, quit), reads the user’s choice in a loop and calls the right function. This is where what you learned in functions pays off: each operation is a separate function, and the main loop is short and clear. For more structure, you can gather all these functions into a Library class, as we explained in object-oriented programming, so the object keeps the database connection.

Step 5: turn the exercise into a real project

The difference between an exercise and a project worthy of your portfolio lies in the details:

• Checking input: what if the user enters a year as “two thousand”? Handle the error with a clear message instead of letting the program stop.
• Reports: add a command that shows the number of books in each status using GROUP BY and COUNT.
• Closing the connection: use with, or close the connection on exit.
• Exporting: add an option to export the list to a CSV file that opens in spreadsheet programs.
• Documentation: write a README explaining the idea of the project and how to run it, and publish it on GitHub.

How do you see what is inside the database?

During development you will often need to check with your own eyes what was actually saved. There are free, open-source programs that open an SQLite file and show its tables in a spreadsheet-like view, such as DB Browser for SQLite, and let you write and try SQL queries before putting them in your code. A useful habit: try every new query there first on a copy of the file, and once you are sure of its result, move it into your program. And before any experiment with deleting or bulk updates, keep a backup copy of the database file in another folder.

Common mistakes

• Forgetting commit: the insert seems to work, then the data vanishes after the program closes.
• Building the query by joining strings: a serious security hole, as we explained. Always use question marks.
• Forgetting the comma in a one-item tuple: (book_id,), not (book_id).
• Updating or deleting without WHERE: it may change every row at once.

Ideas for extending the project

Once the app works, try adding a second table for ratings and notes linked to the books table, and you will learn about relationships between tables. Or turn the interface into a graphical window with Tkinter, which is built into Python. Or make the app a small website with Flask. Each extension teaches you a new skill and adds to your portfolio.

This project is an advanced step in your Python journey. If you are at the start of it, go back to why start programming with Python. In the Python course at Badr Academy Education we build projects like this step by step with code review. For further resources visit the learning library, try the Find your path tool to see what comes next, or browse all the courses.