Creating Tables and Defining Schemas

A connection does not represent a table or a single SQL command. It maintains the link between Python and the database so that database operations can be performed.

Programming

From Python to a Database

Creating a table begins before the table itself exists. Python first needs a connection to the database. With sqlite3.connect(filename), Python establishes a link to a local database file or remote server. That connection is the foundation for database operations: it gives Python a path through which database work can take place.

What happens when sqlite3.connect(filename) links Python to a database?

Key point

A connection does not represent a table or a single SQL command. It maintains the link between Python and the database so that database operations can be performed.

Connection and Cursor Roles

After creating a connection, create a cursor from it with conn.cursor(). The cursor is the tool used to execute SQL commands on the database. The connection maintains the database link, while the cursor sends commands through that link and retrieves results. These objects have different roles, but they operate together.

How does a cursor operate through a database connection to execute SQL commands?

Part	Role
Connection	Maintains the link to the database
Cursor	Sends SQL commands and retrieves results
SQL	Expresses the database command

The connection, cursor, and SQL command contribute different parts of one database operation.

A Table as a Schema

A table schema describes the structure that a table is intended to have. In a table definition, the table name identifies the table, columns identify the fields it contains, data types describe the kinds of data associated with those columns, and constraints express rules applied to the table definition. The SQL command that defines this structure is sent through the cursor's execute() method.

How do a table name, columns, data types, and constraints fit together in a schema?

Worked example

Tracing a Table Definition

Suppose a Python program is connected to a database and uses a cursor to send a SQL command defining a table named learners. What does each part contribute?

1
Connection
The connection maintains the link between Python and the database.
2
Cursor
The cursor is created from the connection and acts as the tool for executing the SQL command.
3
Table name
The name learners identifies the table being defined.
4
Columns
The columns identify the fields that belong in the table.
5
Data types and constraints
These parts describe the kinds of data and the rules included in the table definition.

Result

The table schema is expressed in SQL, and the cursor sends that SQL command through the connection to the database.

Sending the SQL Command

The operation follows a clear sequence. First, sqlite3.connect(filename) establishes the connection. Next, conn.cursor() creates a cursor from that connection. Then the cursor's execute() method runs the SQL command. The SQL text describes the database operation, while Python's sqlite3 API provides the connection, cursor, and execution mechanism.

What is the sequence from writing SQL to sending it through the cursor and changing the database?

Mistakes in the Operation Chain

Common mistake
Treating the connection as if it were the cursor
What it looks like
A learner creates a connection but does not create a cursor before trying to execute SQL.
Why it fails
The connection maintains the database link, while the cursor is the tool used to execute SQL commands.
Do this instead
Create a cursor from the connection with conn.cursor(), then use the cursor's execute() method.
Common mistake
Forgetting which object executes the SQL
What it looks like
A learner assumes that sqlite3.connect(filename) itself sends the table-definition command.
Why it fails
sqlite3.connect(filename) establishes the connection. The cursor's execute() method runs the SQL command.
Do this instead
Trace the operation as connection first, cursor second, execute() third.
Common mistake
Mixing up SQL and the Python API
What it looks like
A learner treats the SQL command and conn.cursor() as if they were the same language construct.
Why it fails
SQL expresses the database command, while Python's sqlite3 API provides the connection, cursor, and execution mechanism.
Do this instead
Identify whether each part is SQL text or a Python sqlite3 operation.
Common mistake
Leaving the connection open after finishing
What it looks like
A learner completes database work and never closes the connection.
Why it fails
The connection should be closed when the work is complete to free resources and avoid locking the database file.
Do this instead
Close the connection after database operations are finished.

Trace Before You Execute

EASY

A program needs to define a table in a SQLite database. Arrange these actions in the correct order: use the cursor's execute() method, create a cursor with conn.cursor(), establish a connection with sqlite3.connect(filename), and close the connection when the work is complete.

Hints

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

Your attempt (not saved)

Worked example

Checking the Sequence

Which object maintains the link to the database, and which object sends the SQL command?

1
Identify the connection
The connection maintains the link to the database file or server.
2
Identify the cursor
The cursor is created from the connection and acts as the tool to execute SQL commands.
3
Identify the execution step
The cursor's execute() method runs the SQL command and can retrieve results through the cursor.

Result

The connection provides the link, and the cursor uses that link to send and retrieve database operations.

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 connection maintains the database link, while the cursor executes SQL commands and retrieves results.
4
A table schema describes a table name, columns, data types, and constraints.
5
SQL expresses standardized database commands, and Python's sqlite3 API provides the connection and cursor used to send them.
6
Close the connection when database work is complete to free resources and avoid locking the database file.

At a glance

A database connection links Python to a database file or server.
A cursor is created from the connection and executes SQL commands.
A table schema organizes a table name, columns, data types, and constraints.
SQL describes the database command, while Python's sqlite3 API manages the connection and cursor.
Closing the connection after database work frees resources and helps avoid locking the database file.

Related concepts

Inserting and Querying Table Data

More concepts starting with C