Python SQLite3 Database Manager

This challenge will test your ability to interact with SQLite databases using Python's built-in sqlite3 module. You will create a simple database to store and retrieve information, simulating a common task in application development. This is a foundational skill for any developer working with persistent data.

Problem Description

Your task is to create a Python script that manages a simple SQLite database for storing and retrieving information about books. The script should:

1. Create a database file: If it doesn't exist, create a new SQLite database file named library.db.
2. Create a table: Create a table named books within the database with the following columns:
   • id (INTEGER, PRIMARY KEY AUTOINCREMENT)
   • title (TEXT, NOT NULL)
   • author (TEXT, NOT NULL)
   • year (INTEGER)
3. Insert data: Implement a function to insert new book records into the books table.
4. Query data: Implement functions to retrieve book records based on different criteria:
   • Retrieve all books.
   • Retrieve books by a specific author.
   • Retrieve books published after a certain year.
5. Handle potential errors: Ensure your code gracefully handles common database operations errors, such as trying to insert a book with a missing title or author.
6. Close the connection: Ensure the database connection is properly closed when the script finishes or when an operation is completed.

Examples

Example 1: Creating the database and inserting data

Input:

import sqlite3

def create_database(db_name="library.db"):
    # ... (implementation to create db and table) ...
    pass

def insert_book(conn, title, author, year=None):
    # ... (implementation to insert a book) ...
    pass

db_file = "library.db"
conn = sqlite3.connect(db_file)
create_database(db_file)

insert_book(conn, "The Hitchhiker's Guide to the Galaxy", "Douglas Adams", 1979)
insert_book(conn, "Pride and Prejudice", "Jane Austen", 1813)
insert_book(conn, "1984", "George Orwell", 1949)

conn.commit()
conn.close()

Output: A file named library.db will be created in the same directory. Inside this file, a table named books will exist with the three records inserted.

Explanation: The script connects to library.db, creates the books table if it doesn't exist, and then inserts three book records. The conn.commit() saves these changes, and conn.close() releases the database connection.

Example 2: Querying all books

Input:

import sqlite3

def get_all_books(db_name="library.db"):
    # ... (implementation to connect, query all, and return results) ...
    pass

# Assuming library.db and books table are already populated from Example 1
books = get_all_books()
print(books)

Output:

[(1, "The Hitchhiker's Guide to the Galaxy", "Douglas Adams", 1979), (2, "Pride and Prejudice", "Jane Austen", 1813), (3, "1984", "George Orwell", 1949)]

Explanation: The get_all_books function connects to the database, executes a SELECT * FROM books query, fetches all rows, and returns them as a list of tuples.

Example 3: Querying books by author and year

Input:

import sqlite3

def get_books_by_author(conn, author_name):
    # ... (implementation to query books by author) ...
    pass

def get_books_published_after(conn, year):
    # ... (implementation to query books by year) ...
    pass

db_file = "library.db"
conn = sqlite3.connect(db_file)

# Assuming library.db and books table are already populated

author_books = get_books_by_author(conn, "Douglas Adams")
print(f"Books by Douglas Adams: {author_books}")

recent_books = get_books_published_after(conn, 1950)
print(f"Books published after 1950: {recent_books}")

conn.close()

Output:

Books by Douglas Adams: [(1, "The Hitchhiker's Guide to the Galaxy", "Douglas Adams", 1979)]
Books published after 1950: [(3, '1984', 'George Orwell', 1949)]

Explanation: get_books_by_author uses a WHERE author = ? clause to filter results. get_books_published_after uses a WHERE year > ? clause. The placeholder ? is used to prevent SQL injection.

Example 4: Handling errors (optional)

Input:

import sqlite3

def insert_book(conn, title, author, year=None):
    # ... (implementation, potentially with try-except block for constraints) ...
    pass

db_file = "library.db"
conn = sqlite3.connect(db_file)

try:
    insert_book(conn, "Missing Author", None, 2023) # Missing author, should raise error
except sqlite3.IntegrityError as e:
    print(f"Error inserting book: {e}")
    conn.rollback() # Rollback the transaction
finally:
    conn.close()

Output:

Error inserting book: NOT NULL constraint failed: books.author

Explanation: Attempting to insert a book with a NULL author violates the NOT NULL constraint. The sqlite3.IntegrityError is caught, an error message is printed, and conn.rollback() undoes the failed transaction.

Constraints

• The database file name must be library.db.
• The table name must be books.
• The title and author columns must not be NULL.
• The year column can be NULL.
• All database operations should be performed using the sqlite3 module in Python.
• Your solution should handle potential sqlite3.Error exceptions.

Notes

• Remember to use conn.commit() to save your changes to the database.
• Use parameterized queries (e.g., ?) to safely insert or query data and prevent SQL injection vulnerabilities.
• Consider using with sqlite3.connect(...) as conn: for automatic connection management and commit/rollback.
• The sqlite3 module automatically creates the database file if it doesn't exist when you first connect to it.
• Think about how to structure your functions to be reusable and clear.