Establish a Robust Database Connection in Python

Many applications interact with databases to store and retrieve data. A fundamental skill for any developer is the ability to establish a reliable connection to a database using Python. This challenge will guide you through the process of connecting to a SQLite database, a simple file-based database perfect for development and testing.

Problem Description

Your task is to write a Python script that connects to a SQLite database. You will need to:

• Create a connection object: Use the sqlite3 module (built into Python) to establish a connection to a database file. If the database file doesn't exist, it should be created.
• Handle potential errors: Implement basic error handling for the connection process.
• Verify the connection: Perform a simple operation (like executing a dummy query) to confirm that the connection is successful.
• Close the connection: Ensure the database connection is properly closed after use to prevent resource leaks.

Examples

Example 1:

# Successful connection and execution
import sqlite3

db_file = "my_test_database.db"

try:
    conn = sqlite3.connect(db_file)
    print(f"Successfully connected to database: {db_file}")

    cursor = conn.cursor()
    cursor.execute("SELECT 1") # A simple query to verify connection
    result = cursor.fetchone()
    if result and result[0] == 1:
        print("Connection verified: Dummy query executed successfully.")
    else:
        print("Connection verification failed.")

except sqlite3.Error as e:
    print(f"Database error occurred: {e}")
finally:
    if 'conn' in locals() and conn:
        conn.close()
        print("Database connection closed.")

# Expected Output (if my_test_database.db does not exist and creation is successful):
# Successfully connected to database: my_test_database.db
# Connection verified: Dummy query executed successfully.
# Database connection closed.

Explanation: The code attempts to connect to "my_test_database.db". If successful, it prints a confirmation, executes a simple SELECT 1 query, and prints another confirmation. Finally, it closes the connection.

Example 2:

# Handling a scenario where the connection might fail (e.g., invalid path, permissions)
import sqlite3

db_file = "/nonexistent_directory/my_test_database.db" # Assuming this path is invalid

try:
    conn = sqlite3.connect(db_file)
    print(f"Successfully connected to database: {db_file}")

    cursor = conn.cursor()
    cursor.execute("SELECT 1")
    result = cursor.fetchone()
    if result and result[0] == 1:
        print("Connection verified: Dummy query executed successfully.")
    else:
        print("Connection verification failed.")

except sqlite3.Error as e:
    print(f"Database error occurred: {e}")
finally:
    if 'conn' in locals() and conn:
        conn.close()
        print("Database connection closed.")

# Expected Output (if the path is invalid and connection fails):
# Database error occurred: unable to open database file

Explanation: This example demonstrates the error handling. If sqlite3.connect fails (e.g., due to an invalid path or insufficient permissions), the except block catches the sqlite3.Error and prints an informative message. The finally block ensures cleanup is attempted even if an error occurs.

Constraints

• You must use Python's built-in sqlite3 module.
• The script should be able to run without requiring any external libraries to be installed (beyond standard Python installation).
• The database file should be created in the same directory as the script if it does not already exist.

Notes

• The sqlite3 module provides a straightforward API for working with SQLite databases.
• Pay close attention to the try...except...finally block for robust error handling and resource management.
• A successful connection doesn't guarantee that subsequent operations will succeed, but it's the first crucial step. The SELECT 1 query is a common and lightweight way to test if the connection is active.