Handling Database Errors and Exceptions

A database connection is established using sqlite3.connect(filename), which creates a link to a local database file or remote server.

Programming

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.

Key point

When database work fails, it is important to know which operation was in progress: establishing the connection or executing SQL through the cursor. The source material establishes these operations and their relationship; it does not specify particular exception classes or a complete try-and-except pattern.

Connection and Cursor States

What contains what, and how does a cursor use a connection to operate on the database?

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

What happens to the program's control flow when connecting to the database or executing SQL raises an exception?

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

How does a SQL command move from Python code through the cursor and connection to the database, and how does the result return?

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.

Part	Role
Python program	Creates and coordinates the database objects.
Connection	Maintains the link to the database file or server.
Cursor	Acts as the tool for executing SQL commands and retrieving results.
SQL	Expresses the database command.

The distinct roles in a Python database operation

Key point

SQL keywords are written in uppercase, while user-defined names are written in lowercase in the source guidance. This naming style helps distinguish the language's commands from names supplied by the database designer.

A Complete Operation Trace

Worked example

Tracing a Database Request

A Python program needs to send a SQL command to a database and receive the results. Identify the order of the database objects and operations.

1
Establish the link
The program uses sqlite3.connect(filename) to create a connection to a local database file or remote server.
2
Create the command tool
The program calls conn.cursor() to create a cursor from the connection.
3
Send the command
The cursor uses its execute() method to run the SQL command.
4
Retrieve results
The cursor and connection work together so that the cursor can retrieve results from the database operation.
5
Finish responsibly
When database work is complete, the connection should be closed to free resources and avoid locking the database file.

Result

The operational chain is connection, cursor, execute, results, and close. If an exception occurs while connecting or executing, the normal chain does not continue as a successful operation.

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

Common mistake
Treating the connection and cursor as the same object.
What it looks like
Describing the cursor as the link to the database.
Why it fails
The connection maintains the link, while the cursor is the tool used to execute SQL commands and retrieve results.
Do this instead
Remember: the connection links to the database; the cursor operates through that connection.
Common mistake
Trying to create a cursor before establishing a connection.
What it looks like
Starting with conn.cursor() without first creating conn through sqlite3.connect(filename).
Why it fails
The cursor is created from a connection.
Do this instead
Establish the connection first, then call conn.cursor().
Common mistake
Sending SQL directly to the database without using the cursor.
What it looks like
```
Treating SQL as if it were executed by sqlite3.connect(filename).
```
Why it fails
The connection establishes the link; the cursor's execute() method runs SQL commands.
Do this instead
Use the cursor to execute SQL after the connection has been established.
Common mistake
Ignoring the distinction between SQL keywords and user-defined names.
What it looks like
Writing every database name as though it were an SQL keyword.
Why it fails
The source guidance distinguishes uppercase SQL keywords from lowercase user-defined names.
Do this instead
Use uppercase for SQL keywords and lowercase for user-defined names.
Common mistake
Leaving the connection open after database work is complete.
What it looks like
Finishing the database operation without closing the connection.
Why it fails
The connection should be closed to free resources and avoid locking the database file.
Do this instead
Close the connection when the database work is done.

Practice the Trace

EASY

A database task has reached the point where sqlite3.connect(filename) has successfully created a connection. Explain the next two database-operation stages and identify which object sends the SQL command.

Hints

The next object is created from the connection.
The SQL command is run with a method belonging to the cursor.

Your attempt (not saved)

MEDIUM

A program reports that its normal database sequence stopped. The last operation attempted was cursor.execute(). Explain whether the interruption occurred during connection establishment or during SQL execution, and state what should happen to the connection when database work is complete.