DEV Community

A0mineTV
A0mineTV

Posted on

1

Building a Simple SQLite Library Manager in Python

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}")
Enter fullscreen mode Exit fullscreen mode

Run this function to initialize the database:

create_library_database()
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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? 🚀

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay