Handling Database Errors and Exceptions

From Python to a Database

Database work in Python is organized around three cooperating parts: a connection, a cursor, and SQL. The connection establishes the link to a local database file or remote server. The cursor is created from that connection and acts as the tool for executing SQL commands. SQL expresses the database command itself.

Connection and Cursor States

The connection and cursor have different responsibilities. sqlite3.connect(filename) establishes the connection. Calling conn.cursor() creates a cursor from that connection. The connection maintains the link to the database, while the cursor is used to send SQL commands and retrieve results.

This relationship explains why the cursor is not the starting point. A cursor comes from a connection, so the connection must be established before the cursor can be created.

Where Control Can Leave the Normal Path

The important trace is the order of operations. The program first attempts to establish a connection. If the connection is available, it creates a cursor. The cursor then executes SQL commands. An exception raised during connection or execution sends control away from the normal sequence; the source material identifies these database operations but does not define the specific Python syntax for catching or responding to the exception.

SQL Through the Cursor

SQL is the standardized language used to express database commands. Python provides the program that creates the connection and cursor, but the command sent to the database is written in SQL. The cursor's execute() method is the point at which the SQL command is run.

A Complete Operation Trace

This trace separates two questions that are often mixed together. The first question is how the program reaches the database: connect, then create a cursor. The second is how the database receives a command: call execute() through the cursor. Keeping these stages separate makes it easier to identify whether a problem occurred during connection or during SQL execution.

Mistakes in the Operation Sequence

• Treating the connection and cursor as the same object.

  The connection maintains the link, while the cursor is the tool used to execute SQL commands and retrieve results.

  Fix: Remember: the connection links to the database; the cursor operates through that connection.

• Trying to create a cursor before establishing a connection.

  The cursor is created from a connection.

  Fix: Establish the connection first, then call conn.cursor().

• Sending SQL directly to the database without using the cursor.

  The connection establishes the link; the cursor's execute() method runs SQL commands.

  Fix: Use the cursor to execute SQL after the connection has been established.

• Ignoring the distinction between SQL keywords and user-defined names.

  The source guidance distinguishes uppercase SQL keywords from lowercase user-defined names.

  Fix: Use uppercase for SQL keywords and lowercase for user-defined names.

• Leaving the connection open after database work is complete.

  The connection should be closed to free resources and avoid locking the database file.

  Fix: Close the connection when the database work is done.

Practice the Trace

Key Takeaways

1. sqlite3.connect(filename) establishes a link to a local database file or remote server.
2. conn.cursor() creates a cursor from the connection.
3. The cursor's execute() method sends SQL commands to the database and supports retrieving results.
4. The connection maintains the database link, while the cursor operates through that link.
5. The connection should be closed after database work to free resources and avoid locking the database file.