Inserting and Retrieving Data with SQL
A database connection is established using sqlite3.connect(filename), which creates a link to a local database file or remote server.
From Python to Stored Data
Working with a database in Python begins with two cooperating objects: a connection and a cursor. The connection maintains the link to the database file or server. The cursor is the tool used to send SQL commands through that connection and retrieve results. Once this relationship is clear, inserting and retrieving data becomes a sequence of understandable steps rather than a collection of unrelated commands.
The connection provides the link; the cursor performs database work through that link.
The Connection-Cursor Relationship
The connection and cursor have different responsibilities. Calling sqlite3.connect(filename) establishes a database connection. That connection represents the link to a local database file or remote server. Calling conn.cursor() creates a cursor from the connection. The cursor then acts as the working tool for executing SQL commands on the database.
Opening the Database Path
A database connection is established using sqlite3.connect(filename). It creates a link to a local database file or remote server.
In this pattern, sqlite3.connect(filename) is the connection-establishing operation, and conn is the name used for the resulting connection in this example. The connection is the foundation for later database operations because the cursor is created from it.
Sending SQL Through the Cursor
After the connection exists, create a cursor with conn.cursor(). The cursor is the object used to execute SQL commands. Its execute() method runs an SQL command on the database. This makes the cursor the active interface for both changing database data, such as with an INSERT command, and reading database data, such as with a SELECT command.
The string passed to execute() represents an SQL command. SQL is a standardized language for database commands, so INSERT and SELECT are standardized instructions rather than names invented by a particular Python program. SQL keywords are written in uppercase in this convention, while user-defined names are written in lowercase.
Following an Insert and Retrieval
Tracing Two Database Operations
Follow the roles of the connection and cursor when a Python program inserts data and then retrieves data with SQL.
Establish the link: The program uses sqlite3.connect(filename), creating a connection to the database file or remote server.
Create the working tool: The program uses conn.cursor(), creating a cursor from the established connection.
Send the insert command: The cursor's execute() method sends an INSERT command through the connection so the database can receive the instruction to change its stored data.
Send the retrieval command: The cursor's execute() method sends a SELECT command through the connection so the program can retrieve database results.
Finish the operation: When database work is complete, the connection should be closed to free resources and avoid locking the database file.
The connection remains the link to the database throughout the operation, while the cursor is used to execute both the INSERT and SELECT commands and retrieve results.
Mistakes in the Operation Chain
Trying to create a cursor without first establishing a connection
The cursor is created from a connection with conn.cursor() and works through that connection.
Fix:
Establish the connection with sqlite3.connect(filename), then create the cursor from conn.Treating the connection as the object that directly executes SQL commands
The source distinguishes the connection's link-maintaining role from the cursor's command-executing role.
Fix:
Use the cursor's execute() method to run SQL commands.Confusing SQL keywords with user-defined names
The stated SQL writing convention uses uppercase for SQL keywords and lowercase for user-defined names.
Fix:
Keep SQL keywords uppercase and user-defined names lowercase.Leaving the database connection open after database work
An open connection can continue using resources and may lock the database file.
Fix:
Close the connection when the database work is complete.
A Reliable Working Sequence
- Establish the database connection with sqlite3.connect(filename).
- Create a cursor from the connection with conn.cursor().
- Use the cursor's execute() method to send SQL commands.
- Use the cursor to retrieve results from retrieval commands.
- Close the connection when the database work is complete.
Check Your Model
A teammate says, “The cursor is the database connection.” Explain why that description is inaccurate. In your answer, name the responsibility of the connection, the responsibility of the cursor, and the method used to execute SQL commands.
Hints
- Start with the difference between maintaining a link and performing an operation.
- Recall which object is created with conn.cursor().
- Recall the method used to run SQL commands.
What do you think happens?
A program has created a connection but has not yet created a cursor. Which step is still needed before the program can use the cursor's execute() method?
Reveal answer
Answer: Create a cursor with conn.cursor().
The cursor is created from the connection, and the cursor's execute() method is the tool used to run SQL commands.
Key Takeaways
- sqlite3.connect(filename) establishes a link to a database file or remote server.
- conn.cursor() creates a cursor from an established connection.
- The cursor's execute() method sends SQL commands to the database.
- The connection maintains the link, while the cursor sends commands and retrieves results.
- SQL provides standardized database commands, and the connection should be closed when work is complete.
Key Takeaways
- A connection establishes the link between Python and a database file or server.
- A cursor is created from the connection and is used to execute SQL commands.
- INSERT commands change stored database data, while SELECT commands retrieve data.
- SQL is a standardized language for database commands, with keywords conventionally written in uppercase.
- Close the connection after database work to free resources and avoid locking the database file.