SQLite Database Management with Python

This challenge focuses on implementing fundamental SQLite database operations using Python's sqlite3 module. You'll be creating a database, defining a table, inserting data, querying data, and updating records – essential skills for interacting with relational databases. This exercise will solidify your understanding of database concepts and Python's sqlite3 library.

Problem Description

You are tasked with creating a simple database to manage a list of books. The database should store information about each book, including its title, author, publication year, and ISBN. You need to implement the following functionalities:

1. Create a Database: Create a SQLite database file named books.db.
2. Create a Table: Create a table named books within the database with the following columns:
   • title (TEXT, PRIMARY KEY) - The title of the book.
   • author (TEXT) - The author of the book.
   • publication_year (INTEGER) - The year the book was published.
   • isbn (TEXT, UNIQUE) - The ISBN of the book.
3. Insert Data: Insert three sample books into the books table.
4. Query Data: Retrieve all books from the books table and print their details.
5. Update Data: Update the publication year of a specific book (identified by its title) to a new year.
6. Delete Data: Delete a book from the table based on its ISBN.

Expected Behavior:

The program should execute without errors, create the database and table, insert the sample data, query and display all books, update the publication year of a specified book, and then delete a book based on its ISBN. The final state of the database should reflect these operations.

Examples

Example 1:

Input:  No direct input required. The program defines the data and operations.
Output:
Book 1: Title: The Lord of the Rings, Author: J.R.R. Tolkien, Publication Year: 1954, ISBN: 978-0618260222
Book 2: Title: Pride and Prejudice, Author: Jane Austen, Publication Year: 1813, ISBN: 978-0141439518
Book 3: Title: 1984, Author: George Orwell, Publication Year: 1949, ISBN: 978-0451524935

Updated Book: Title: The Lord of the Rings, Author: J.R.R. Tolkien, Publication Year: 1955, ISBN: 978-0618260222

Book 2: Title: Pride and Prejudice, Author: Jane Austen, Publication Year: 1813, ISBN: 978-0141439518
Book 3: Title: 1984, Author: George Orwell, Publication Year: 1949, ISBN: 978-0451524935

Explanation: The program creates the database, table, inserts data, prints all books, updates the publication year of "The Lord of the Rings" to 1955, and then deletes the book with ISBN "978-0451524935". The final output shows the remaining books.

Example 2: (Edge Case - Book Not Found for Update)

Input:  Attempt to update a book that doesn't exist.
Output:
"Book not found for update."

Explanation: If the title provided for updating doesn't exist in the database, the program should gracefully handle the error and print an appropriate message.

Constraints

• The database file (books.db) should be created in the same directory as the Python script.
• The ISBN must be unique for each book. Attempting to insert a book with a duplicate ISBN should result in an error handled gracefully.
• The publication year must be an integer.
• The code should be well-structured and readable.
• Error handling should be implemented to prevent the program from crashing due to invalid input or database errors.

Notes

• Remember to connect to the database using sqlite3.connect().
• Use a cursor object to execute SQL queries.
• Commit changes to the database after inserting, updating, or deleting data.
• Close the database connection when you are finished.
• Consider using parameterized queries to prevent SQL injection vulnerabilities (although less critical for this simple exercise, it's a good practice).
• Think about how to handle potential errors, such as attempting to update a book that doesn't exist. Provide informative error messages to the user.