search
HomeBackend DevelopmentPython TutorialBuilding a Simple SQLite Library Manager in Python

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}")

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

The above is the detailed content of Building a Simple SQLite Library Manager in Python. For more information, please follow other related articles on the PHP Chinese website!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you create multi-dimensional arrays using NumPy?How do you create multi-dimensional arrays using NumPy?Apr 29, 2025 am 12:27 AM

Create multi-dimensional arrays with NumPy can be achieved through the following steps: 1) Use the numpy.array() function to create an array, such as np.array([[1,2,3],[4,5,6]]) to create a 2D array; 2) Use np.zeros(), np.ones(), np.random.random() and other functions to create an array filled with specific values; 3) Understand the shape and size properties of the array to ensure that the length of the sub-array is consistent and avoid errors; 4) Use the np.reshape() function to change the shape of the array; 5) Pay attention to memory usage to ensure that the code is clear and efficient.

Is Tuple Comprehension possible in Python? If yes, how and if not why?Is Tuple Comprehension possible in Python? If yes, how and if not why?Apr 28, 2025 pm 04:34 PM

Article discusses impossibility of tuple comprehension in Python due to syntax ambiguity. Alternatives like using tuple() with generator expressions are suggested for creating tuples efficiently.(159 characters)

What are Modules and Packages in Python?What are Modules and Packages in Python?Apr 28, 2025 pm 04:33 PM

The article explains modules and packages in Python, their differences, and usage. Modules are single files, while packages are directories with an __init__.py file, organizing related modules hierarchically.

What is docstring in Python?What is docstring in Python?Apr 28, 2025 pm 04:30 PM

Article discusses docstrings in Python, their usage, and benefits. Main issue: importance of docstrings for code documentation and accessibility.

What is a lambda function?What is a lambda function?Apr 28, 2025 pm 04:28 PM

Article discusses lambda functions, their differences from regular functions, and their utility in programming scenarios. Not all languages support them.

What is a break, continue and pass in Python?What is a break, continue and pass in Python?Apr 28, 2025 pm 04:26 PM

Article discusses break, continue, and pass in Python, explaining their roles in controlling loop execution and program flow.

What is a pass in Python?What is a pass in Python?Apr 28, 2025 pm 04:25 PM

The article discusses the 'pass' statement in Python, a null operation used as a placeholder in code structures like functions and classes, allowing for future implementation without syntax errors.

Can we Pass a function as an argument in Python?Can we Pass a function as an argument in Python?Apr 28, 2025 pm 04:23 PM

Article discusses passing functions as arguments in Python, highlighting benefits like modularity and use cases such as sorting and decorators.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software