In this lesson, you will learn how to build resilient database interactions by gracefully catching exceptions that occur when your SQL execution violates schema constraints or encounters connectivity issues.
When performing database operations, your code must anticipate that not every INSERT or UPDATE will succeed. You might encounter sqlite3.IntegrityError if you try to insert a duplicate primary key or violate a NOT NULL constraint. Alternatively, you may face sqlite3.OperationalError if the database file becomes locked or the connection is severed during a transaction.
To handle these reliably, you should wrap your database interactions in try-except blocks. By catching specific exceptions rather than a generic Exception, you can distinguish between a user error (like a duplicate entry) and a system failure (like a lost connection).
Robust Exception Handling Pattern
import sqlite3
def safe_insert_user(user_id, email):
conn = sqlite3.connect('app_data.db')
cursor = conn.cursor()
try:
cursor.execute("INSERT INTO users (id, email) VALUES (?, ?)", (user_id, email))
conn.commit()
except sqlite3.IntegrityError:
print(f"Error: User with ID {user_id} already exists.")
conn.rollback() # Roll back to maintain atomicity
except sqlite3.OperationalError as e:
print(f"Database connection issue: {e}")
finally:
conn.close()
When an error occurs during a transaction, it is crucial to call conn.rollback(). This reverts the database to its state before the transaction began, ensuring that your data remains consistent and avoiding "partial state" bugs where only part of your logic persists.
As an advanced practitioner, you should also consider wrapping these calls in custom exception handlers. By abstracting the database-specific exceptions into your own domain-specific errors, you keep your application logic clean and decoupled from the underlying storage technology. Always ensure that conn.close() is in a finally block to prevent connection leaks, even if an error crashes your logic.
Key Takeaway: Use specific exception handling and explicit transaction rollbacks to preserve data integrity and prevent the application from crashing when database constraints or connection failures occur.