Forem

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

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay