Understanding Database Transactions and Commits

The connection maintains the link to the database. The cursor is the working tool created from that connection. Treating them as the same object makes it harder to understand how database operations work.

Programming

The Path from Python to a Database

A database operation in Python involves more than writing an SQL command. Python first needs a connection to the database. From that connection, you create a cursor. The cursor then sends SQL commands to the database and retrieves results. This connection-cursor relationship is the foundation for database work in Python.

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

Key point

Opening the Database Connection

The first step is to establish a database connection with sqlite3.connect(filename). The filename identifies the database file being connected to. A connection creates the link between the Python program and the database. The same general idea applies when the link is to a local database file or to a remote database server.

python

import sqlite3

conn = sqlite3.connect("school.db")

import sqlite3

conn = sqlite3.connect("school.db")

In this example, conn refers to the connection created by sqlite3.connect(). The connection is not the cursor and is not itself an SQL command. It is the link through which later database work takes place.

What objects and links are created when sqlite3.connect(filename) opens a database file?

Creating and Using a Cursor

After creating the connection, create a cursor with conn.cursor(). The cursor acts as the tool for executing SQL commands on the database. You call the cursor's execute() method to send an SQL command through the connection to the database. When a command produces results, the cursor is also involved in retrieving those results.

python

cursor = conn.cursor()
cursor.execute("SELECT name FROM students")

cursor = conn.cursor()
cursor.execute("SELECT name FROM students")

The first line creates a cursor from conn. The second line uses that cursor to execute an SQL command. The cursor does not replace the connection: it operates through the connection that created it.

What is the sequence from calling cursor.execute() to the database processing the SQL command and returning a result?

From Execution to Commit

Executing an SQL command and committing a transaction are related but distinct steps. The cursor executes the command, while the connection manages the link and the transaction that contains the database work. A commit confirms the changes in the transaction so that the executed changes become permanent in the database.

What changes before and after a commit, and when do executed database changes become permanent?

Worked example

A Complete Database Operation

Connect to a database, create the tool for SQL commands, execute a command, commit the changes, and close the connection.

1
Connect
sqlite3.connect("school.db") creates a connection to the database file.
2
Create the cursor
conn.cursor() creates the cursor that will execute SQL commands.
3
Execute
cursor.execute(...) sends an SQL command through the cursor.
4
Commit
conn.commit() confirms the transaction's changes so they become permanent.
5
Close
conn.close() closes the connection when the database work is finished.

Result

The program has followed the connection, cursor, execution, commit, and cleanup sequence.

python

import sqlite3

conn = sqlite3.connect("school.db")
cursor = conn.cursor()
cursor.execute("CREATE TABLE students (name TEXT)")
conn.commit()
conn.close()

import sqlite3

conn = sqlite3.connect("school.db")
cursor = conn.cursor()
cursor.execute("CREATE TABLE students (name TEXT)")
conn.commit()
conn.close()

SQL as the Command Language

SQL is a standardized language for database commands. Python provides the connection and cursor objects, but the command passed to execute() is written in SQL. SQL keywords are conventionally written in uppercase, while names defined by the user are written in lowercase.

python

cursor.execute("CREATE TABLE students (name TEXT)")

cursor.execute("CREATE TABLE students (name TEXT)")

Part of the command	Example	Role
SQL keyword	CREATE TABLE	A standardized database instruction
User-defined name	students	A name chosen for the database object
Column name	name	A user-defined name inside the table

The example separates SQL keywords from names chosen by the programmer.

Mistakes That Break the Workflow

Common mistake
Trying to execute SQL directly on the connection instead of using a cursor.
What it looks like
```
conn.execute("CREATE TABLE students (name TEXT)")
```
Why it fails
The cursor is the tool identified for executing SQL commands.
Do this instead
Create a cursor with conn.cursor() and call cursor.execute(...).
Common mistake
Treating the cursor as the database connection.
What it looks like
The cursor is described as the link to the database.
Why it fails
The connection maintains the database link, while the cursor sends commands and retrieves results.
Do this instead
Keep the roles separate: conn represents the connection and cursor represents the SQL operation tool.
Common mistake
Assuming execute() alone makes changes permanent.
What it looks like
```
cursor.execute("CREATE TABLE students (name TEXT)")
```
Why it fails
Execution sends the SQL command, but committing confirms the transaction's changes.
Do this instead
Use conn.commit() after the database changes that should become permanent.
Common mistake
Leaving the connection open after finishing.
What it looks like
The program performs database work and never closes conn.
Why it fails
An open connection can keep resources in use and can contribute to locking the database file.
Do this instead
Call conn.close() when the database work is done.
Common mistake
Confusing SQL keywords with user-defined names.
What it looks like
Writing a command without distinguishing standardized SQL terms from names chosen by the programmer.
Why it fails
SQL keywords and user-defined names have different roles in a command.
Do this instead
Use uppercase for SQL keywords and lowercase for user-defined names.

Practice the Sequence

EASY

Put these database actions in the correct order: create a cursor, close the connection, connect to the database, execute an SQL command, and commit the changes.

Hints

The cursor must come from an existing connection.
The SQL command is sent through the cursor.
Closing belongs after the database work is finished.

Your attempt (not saved)

What do you think happens?

A program has called cursor.execute() for a database change but has not called conn.commit(). Has the change been confirmed as permanent by the transaction?

Yes, execute() and commit() are the same operation
No, the change still needs to be committed
No, because a cursor cannot execute SQL

Reveal answer

Answer:

No, the change still needs to be committed

The cursor executes the SQL command, while the connection commits the transaction's changes so they become permanent.

The Complete Mental Model

1
sqlite3.connect(filename) creates the connection between Python and a database file or server.
2
conn.cursor() creates a cursor from the connection.
3
The cursor's execute() method sends SQL commands to the database and helps retrieve results.
4
The connection and cursor have different roles: the connection maintains the link, while the cursor performs SQL operations through it.
5
A commit confirms transaction changes as permanent, and the connection should be closed when the work is complete.

Key Takeaways

A database connection is the link between Python and the database.
A cursor is created from the connection and is used to execute SQL commands.
SQL provides standardized commands, with keywords conventionally written in uppercase and user-defined names in lowercase.
Executing a command and committing its transaction are separate steps.
Close the connection after database work to free resources and avoid locking the database file.

Related concepts

Working with SQL Tables

More concepts starting with U

The Path from Python to a Database

Opening the Database Connection

Creating and Using a Cursor

From Execution to Commit

SQL as the Command Language

Mistakes That Break the Workflow

Practice the Sequence

The Complete Mental Model

Key Takeaways

Related Topics

Related concepts