Building a Simple SQLite Library Manager in Python

Managing data efficiently is a key part of any project, and SQLite makes this task simple and lightweight. In this tutorial, we’ll build a small Python application to manage a library database, allowing you to add and retrieve books with minimal effort.

By the end of this article, you’ll know how to:

• Create a SQLite database and table.
• Insert records while preventing duplicates.
• Retrieve data based on specific criteria.

---

1. Creating the Database and Table

Let’s start by creating our SQLite database file and defining the books table. Each book will have fields for its title, author, ISBN, publication date, and genre.

import sqlite3
import os

def create_library_database():
    """Creates the library database if it doesn't already exist."""
    db_name = "library.db"

    if not os.path.exists(db_name):
        print(f"Creating database: {db_name}")
        conn = sqlite3.connect(db_name)
        cursor = conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS books (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT,
                author TEXT,
                isbn TEXT UNIQUE,
                published_date DATE,
                genre TEXT
            )
        ''')
        conn.commit()
        conn.close()
    else:
        print(f"Database already exists: {db_name}")

Run this function to initialize the database:

create_library_database()

This will create a library.db file in your project directory, containing a books table with the specified fields.

---

1. Inserting Books into the Database

To insert books, we want to ensure that duplicate entries (based on the isbn field) are avoided. Instead of manually checking for duplicates, we’ll use SQLite’s INSERT OR IGNORE statement.

Here’s the function to add books:

def insert_book(book):
    """
    Inserts a book into the database. If a book with the same ISBN already exists,
    the insertion is ignored.
    """
    conn = sqlite3.connect("library.db")
    cursor = conn.cursor()

    try:
        # Insert the book. Ignore the insertion if the ISBN already exists.
        cursor.execute('''
            INSERT OR IGNORE INTO books (title, author, isbn, published_date, genre)
            VALUES (?, ?, ?, ?, ?)
        ''', (book["title"], book["author"], book["isbn"], book["published_date"], book["genre"]))
        conn.commit()

        if cursor.rowcount == 0:
            print(f"The book with ISBN '{book['isbn']}' already exists in the database.")
        else:
            print(f"Book inserted: {book['title']} by {book['author']}")

    except sqlite3.Error as e:
        print(f"Database error: {e}")
    finally:
        conn.close()

This function uses the INSERT OR IGNORE SQL statement to ensure duplicate entries are skipped efficiently.

---

 3. Adding Some Books

Let’s test the insert_book function by adding some books to our library.

books = [
    {
        "title": "To Kill a Mockingbird",
        "author": "Harper Lee",
        "isbn": "9780061120084",
        "published_date": "1960-07-11",
        "genre": "Fiction"
    },
    {
        "title": "1984",
        "author": "George Orwell",
        "isbn": "9780451524935",
        "published_date": "1949-06-08",
        "genre": "Dystopian"
    },
    {
        "title": "Pride and Prejudice",
        "author": "Jane Austen",
        "isbn": "9781503290563",
        "published_date": "1813-01-28",
        "genre": "Romance"
    }
]

for book in books:
    insert_book(book)

---

When you run the above code, the books will be added to the database. If you run it again, you’ll see messages like:

The book with ISBN '9780061120084' already exists in the database.
The book with ISBN '9780451524935' already exists in the database.
The book with ISBN '9781503290563' already exists in the database.

---

4. Retrieving Books

You can easily retrieve data by querying the database. For example, to fetch all books in the library:

def fetch_all_books():
    conn = sqlite3.connect("library.db")
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM books")
    rows = cursor.fetchall()
    conn.close()
    return rows

books = fetch_all_books()
for book in books:
    print(book)

---

Conclusion

With just a few lines of Python, you now have a functional library manager that can insert books while preventing duplicates and retrieve records effortlessly. SQLite’s INSERT OR IGNORE is a powerful feature that simplifies handling constraints, making your code more concise and efficient.

Feel free to expand this project with features like:

• Searching for books by title or author.
• Updating book information.
• Deleting books.

What will you build next? 🚀