Understanding Database Transactions and Commits

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.

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.

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.

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.

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.

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.

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.

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

Mistakes That Break the Workflow

• Trying to execute SQL directly on the connection instead of using a cursor.

  The cursor is the tool identified for executing SQL commands.

  Fix: Create a cursor with conn.cursor() and call cursor.execute(...).

• Treating the cursor as the database connection.

  The connection maintains the database link, while the cursor sends commands and retrieves results.

  Fix: Keep the roles separate: conn represents the connection and cursor represents the SQL operation tool.

• Assuming execute() alone makes changes permanent.

  Execution sends the SQL command, but committing confirms the transaction's changes.

  Fix: Use conn.commit() after the database changes that should become permanent.

• Leaving the connection open after finishing.

  An open connection can keep resources in use and can contribute to locking the database file.

  Fix: Call conn.close() when the database work is done.

• Confusing SQL keywords with user-defined names.

  SQL keywords and user-defined names have different roles in a command.

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

Practice the Sequence

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.