Python Working With Databases Exercises

1/20

Correct

When using the `sqlite3` module, what does the `connection` object represent in the context of the DB-API?

A temporary folder where the database stores its log files.

The specific row currently being edited by the user.

The bridge to the database file that manages transactions and provides cursors.

The visual interface used to display tables in the console.

The Connection object is your primary handle to the database. It handles the "Big Picture" tasks: opening/closing the file, committing changes, and rolling back if something goes wrong.

import sqlite3
conn = sqlite3.connect('my_data.db')

What will be the state of the database after the following code is executed?

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute("CREATE TABLE users (id INT, name TEXT)")
cur.execute("INSERT INTO users VALUES (1, 'Alice')")
# (No further code)

The table exists and Alice is permanently saved.

The changes are pending; the data is not finalized because conn.commit() was not called.

The code raises an AttributeError because tables must be created with a separate tool.

The database is automatically saved to a file called users.db.

In most relational databases, changes like INSERT or UPDATE are Atomic. They are "staged" but not final until you explicitly say conn.commit().

Note: In SQLite, CREATE TABLE is often auto-committed, but the INSERT definitely is not.

What is the advantage of using a `with` statement with a `sqlite3` connection as shown below?

import sqlite3
conn = sqlite3.connect("app.db")

with conn:
    conn.execute("INSERT INTO logs VALUES ('started')")
    # ... more code ...

It automatically closes the connection to the file when the block ends.

It automatically commits the transaction if the block succeeds, or rolls it back if an exception occurs.

It speeds up the SQL execution by using a separate thread for the connection.

It prevents other programs from reading the database file while the block is active.

In sqlite3, the connection object can be used as a Context Manager for transactions.

If the code inside the with conn: block runs without errors, Python calls commit() for you. If an error happens, it calls rollback(), ensuring your database remains in a consistent state. Note: It does NOT close the connection; you still need to do that manually or use contextlib.closing().

What will happen if the following code tries to insert a duplicate ID into a table where `id` is a `PRIMARY KEY`?

import sqlite3
try:
    conn = sqlite3.connect(":memory:")
    conn.execute("CREATE TABLE t(id INT PRIMARY KEY)")
    conn.execute("INSERT INTO t VALUES (1)")
    conn.execute("INSERT INTO t VALUES (1)")
except sqlite3.Error as e:
    print(f"Error: {e}")

It prints: Error: UNIQUE constraint failed: t.id

It ignores the second insert and finishes silently.

It prints: Error: DuplicateKeyException

The database crashes and deletes the table.

The sqlite3.Error (or more specifically sqlite3.IntegrityError) is raised whenever a database constraint is violated. Catching these errors is essential for providing user feedback instead of letting the script crash.

Quick Recap of Python Working With Databases Concepts

If you are not clear on the concepts of Working With Databases, you can quickly review them here before practicing the exercises. This recap highlights the essential points and logic to help you solve problems confidently.

Working with Databases — Definition, Mechanics, and Usage

In Python, the sqlite3 module is the standard gateway for data persistence. It implements the DB-API 2.0 specification. This standardization is critical: it means the methods you use for SQLite (execute, commit, fetchall) are virtually identical to those used for professional engines like PostgreSQL, MySQL, or Oracle.

Unlike flat files (JSON/CSV), a database allows for relational data, where tables are linked via keys, and indexed searching, which allows you to find one row among millions in milliseconds.

Why Use a Database — Key Benefits

While JSON and CSV are great for data exchange, databases are designed for long-term storage, complex relationships, and high-speed retrieval.

Benefit	Detailed Explanation
ACID Compliance	Guarantees that transactions are Atomic, Consistent, Isolated, and Durable, preventing data corruption during crashes.
Relational Structure	Enables linking tables via Foreign Keys (e.g., connecting an 'Order' to a specific 'User' ID) to avoid data duplication.
Search Efficiency	Uses B-Tree Indexes to find specific records out of millions in milliseconds, far faster than scanning a text file.
Parameterized Safety	The DB-API provides built-in protection against SQL Injection attacks by separating query logic from user data.

The Core Mechanics: Connection vs. Cursor

To interact with a database, you must manage two distinct objects. Confusing them can lead to "Database is locked" errors or memory leaks.

Object	Role & Responsibilities
Connection (conn)	Represents the actual file on disk. It handles Transactions (Commit/Rollback), configuration (Row Factories), and closes the "pipe" to the data.
Cursor (cur)	Acts as the active "pointer" or execution engine. It sends SQL commands to the engine and holds the result set in a buffer for you to fetch.

Secure Data Entry: Parameterized Queries

The most dangerous security flaw in database programming is SQL Injection. Never use f-strings or .format() to insert variables into a query. Always use the ? placeholder provided by the sqlite3 module.

import sqlite3

# SECURE WAY: The '?' tells the engine to treat input strictly as DATA.
user_id = 105
user_name = "Alice'; DROP TABLE users; --" # Malicious input attempt

# The database engine escapes this safely automatically
cursor.execute("INSERT INTO users (id, name) VALUES (?, ?)", (user_id, user_name))

By passing a tuple as the second argument, you ensure that even if a user enters malicious SQL code, it is stored as a literal string rather than being executed by the database.

Advanced Logic: Row Factories

By default, sqlite3 returns query results as tuples (e.g., ('Alice', 95)). In a table with many columns, referencing row[12] is error-prone. By setting a Row Factory, you can access columns by their names, making your code significantly more readable.

import sqlite3

conn = sqlite3.connect('academy.db')
# This configuration allows name-based access
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute("SELECT name, grade, email FROM students WHERE id = 1")
row = cursor.fetchone()

# Now you can treat the row like a read-only dictionary
print(f"Student: {row['name']} | Email: {row['email']}")

Transaction Management (Atomic Operations)

A Transaction is a sequence of database operations that must be treated as a single unit. In Python, you can use the with statement on the connection object to automate safety.

import sqlite3

conn = sqlite3.connect('bank.db')

try:
    # The 'with' block handles the transaction
    with conn:
        # If any line fails, the entire block is ROLLED BACK automatically
        conn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        conn.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    print("Transfer successful and committed.")
except sqlite3.Error as e:
    # If an exception occurs, changes are NOT saved to the disk
    print(f"Transaction failed: {e}")
finally:
    conn.close()

Best Practices — Professional Standards

To build production-grade database applications, follow these industry standards for performance and reliability.

Index Frequently Queried Columns: If you search by email or username, use CREATE INDEX. Without an index, the database must perform a "Full Table Scan," which slows down exponentially as data grows.
Use IF NOT EXISTS: Always use CREATE TABLE IF NOT EXISTS. This makes your setup scripts "idempotent," meaning they can run multiple times without crashing your application.
Batch Your Operations: When inserting thousands of rows, use cursor.executemany(). This is significantly faster than calling execute() inside a Python for loop because it reduces communication overhead with the database engine.
Keep Transactions Short: Avoid holding a transaction open while performing slow tasks (like calling an external API). This can lock the database file and prevent other processes from writing data.

Summary: Key Points

Standardization: DB-API 2.0 makes Python database code portable across SQLite, MySQL, and PostgreSQL.
Security: The ? placeholder is mandatory to prevent SQL Injection.
Persistence: Changes like INSERT or DELETE are only permanent after a commit().
Memory: Iterate through cursors for large datasets instead of using fetchall() to keep RAM usage low.

About This Exercise: Working with Databases in Python

Variables and lists are great for temporary data, but for a real application, you need a memory that survives a restart. That’s the role of the database. At Solviyo, we view database interaction as a critical bridge for any backend developer. Whether you’re building a web app, a data pipeline, or a local tool, you need to know how to move data safely between Python objects and SQL tables. We’ve designed these Python exercises to help you master the sqlite3 module and the universal DB-API 2.0 standards, ensuring you can execute queries and manage transactions with precision.

We’re moving beyond simple "SELECT" statements. These exercises will push you to handle parameterized queries to prevent SQL injection, manage database connections using context managers, and understand the lifecycle of a cursor. You’ll tackle MCQs and coding practice that explain how to map Python data types to SQL columns and how to handle relational data effectively. By the end of this section, you'll be writing robust code that treats the database as a reliable partner in your application’s architecture.

What You Will Learn

This section is built to turn raw data into a structured, persistent asset. Through our structured Python exercises with answers, we’ll explore:

Connections and Cursors: Understanding the roles of the connection object and the cursor in managing the database flow.
CRUD Operations: Mastering the four pillars of database interaction: Create, Read, Update, and Delete.
Parameterized Queries: Learning the professional way to pass variables into SQL to keep your applications secure.
Transaction Management: Using commit() and rollback() to ensure your data stays consistent even when errors occur.
Fetching Results: Efficiently using fetchone(), fetchmany(), and fetchall() to retrieve data without overloading your RAM.

Why This Topic Matters

Why do we care about databases? Because data is the lifeblood of modern software. If you can’t store and retrieve information efficiently, your application is limited to a single session. Mastering SQL interaction within Python allows you to build systems that scale, from simple todo lists to complex e-commerce platforms. It’s the foundational skill required for full-stack development and data engineering.

From a professional perspective, writing clean database logic is about safety and performance. Understanding when to open a connection and how to properly close a cursor prevents resource leaks that can crash production servers. By mastering these exercises, you’re learning to build applications that are not just functional, but enterprise-ready. It’s a major step toward becoming a developer who can handle the full lifecycle of application data.

Start Practicing

Ready to make your data permanent? Every one of our Python exercises comes with detailed explanations and answers to help you bridge the gap between Python logic and SQL storage. We break down the mechanics of the DB-API so you can take these skills to any database engine, from SQLite to PostgreSQL. If you need a quick refresh on basic SQL syntax, check out our "Quick Recap" section before you dive in. Let’s see how you manage persistent data.

Need a Quick Refresher?

Jump back to the Python Cheat Sheet to review concepts before solving more challenges.

Python Working With Databases Exercises

When using the `sqlite3` module, what does the `connection` object represent in the context of the DB-API?

Which code snippet correctly creates a database that exists only in RAM (memory) and is deleted once the program finishes?

Why is it necessary to create a `cursor` object from the connection (e.g., `cur = conn.cursor()`)?

What will be the state of the database after the following code is executed?

Which method should you call on the cursor to retrieve the very first row of a `SELECT` query result?

You need to process 1,000,000 rows from a database. Which approach is the most memory-efficient in Python?

Why it matters:

What is the advantage of using a `with` statement with a `sqlite3` connection as shown below?

After executing an `UPDATE` or `DELETE` statement, how can you determine how many rows were actually changed by the database?

Which snippet correctly demonstrates how to process a large result set in chunks of 100 rows at a time?

What is the proper sequence for closing database resources in a production environment?

A user provides their username via a web form. Which of the following is the correct and secure way to query the database using that variable?

Parametrized Queries:

You have a list of 5,000 product tuples `[(pid, price), ...]`. What is the most efficient way to insert all of them into a table?

What will happen if the following code tries to insert a duplicate ID into a table where `id` is a `PRIMARY KEY`?

If you have a dictionary `user_data = {"uid": 10, "status": "active"}`, which syntax allows you to map the dictionary keys directly into your SQL statement?

SQLite does not have a native `DATETIME` storage type. When you insert a Python `datetime` object into an SQLite column declared as `TIMESTAMP`, how is it stored and what is the best practice for retrieval?

By default, `fetchone()` returns a tuple. If you want your query results to behave like a dictionary (allowing access via `row['column_name']`), which internal attribute of the connection should you modify?

You have a complex Python function `validate_checksum(data)`. How can you use this function directly inside a SQL statement to filter rows?

In a multi-threaded application, you need to insert a row into a "Parent" table and immediately get its auto-incremented ID to use in a "Child" table. What is the safest way to do this using the DB-API?

What is a primary functional difference between `cursor.execute()` and `cursor.executescript()`?

By default, `sqlite3` opens a transaction automatically before the first DML statement (`INSERT`, `UPDATE`, etc.). If you want to disable this behavior and handle transactions purely via SQL commands (`BEGIN`, `COMMIT`), how should you initialize the connection?

Quick Recap of Python Working With Databases Concepts

Working with Databases — Definition, Mechanics, and Usage

Why Use a Database — Key Benefits

The Core Mechanics: Connection vs. Cursor

Secure Data Entry: Parameterized Queries

Advanced Logic: Row Factories

Transaction Management (Atomic Operations)

Best Practices — Professional Standards

Summary: Key Points

About This Exercise: Working with Databases in Python

What You Will Learn

Why This Topic Matters

Start Practicing

Need a Quick Refresher?

Python Working With Databases Exercises

Python Working With Databases Practice Questions

When using the sqlite3 module, what does the connection object represent in the context of the DB-API?

Which code snippet correctly creates a database that exists only in RAM (memory) and is deleted once the program finishes?

Why is it necessary to create a cursor object from the connection (e.g., cur = conn.cursor())?

What will be the state of the database after the following code is executed?

Which method should you call on the cursor to retrieve the very first row of a SELECT query result?

You need to process 1,000,000 rows from a database. Which approach is the most memory-efficient in Python?

Why it matters:

What is the advantage of using a with statement with a sqlite3 connection as shown below?

After executing an UPDATE or DELETE statement, how can you determine how many rows were actually changed by the database?

Which snippet correctly demonstrates how to process a large result set in chunks of 100 rows at a time?

What is the proper sequence for closing database resources in a production environment?

A user provides their username via a web form. Which of the following is the correct and secure way to query the database using that variable?

Parametrized Queries:

You have a list of 5,000 product tuples [(pid, price), ...]. What is the most efficient way to insert all of them into a table?

What will happen if the following code tries to insert a duplicate ID into a table where id is a PRIMARY KEY?

If you have a dictionary user_data = {"uid": 10, "status": "active"}, which syntax allows you to map the dictionary keys directly into your SQL statement?

SQLite does not have a native DATETIME storage type. When you insert a Python datetime object into an SQLite column declared as TIMESTAMP, how is it stored and what is the best practice for retrieval?

By default, fetchone() returns a tuple. If you want your query results to behave like a dictionary (allowing access via row['column_name']), which internal attribute of the connection should you modify?

You have a complex Python function validate_checksum(data). How can you use this function directly inside a SQL statement to filter rows?

In a multi-threaded application, you need to insert a row into a "Parent" table and immediately get its auto-incremented ID to use in a "Child" table. What is the safest way to do this using the DB-API?

What is a primary functional difference between cursor.execute() and cursor.executescript()?

By default, sqlite3 opens a transaction automatically before the first DML statement (INSERT, UPDATE, etc.). If you want to disable this behavior and handle transactions purely via SQL commands (BEGIN, COMMIT), how should you initialize the connection?

Quick Recap of Python Working With Databases Concepts

Working with Databases — Definition, Mechanics, and Usage

Why Use a Database — Key Benefits

The Core Mechanics: Connection vs. Cursor

Secure Data Entry: Parameterized Queries

Advanced Logic: Row Factories

Transaction Management (Atomic Operations)

Best Practices — Professional Standards

Summary: Key Points

Test Your Python Working With Databases Knowledge

About This Exercise: Working with Databases in Python

What You Will Learn

Why This Topic Matters

Start Practicing

Need a Quick Refresher?

When using the `sqlite3` module, what does the `connection` object represent in the context of the DB-API?

Why is it necessary to create a `cursor` object from the connection (e.g., `cur = conn.cursor()`)?

Which method should you call on the cursor to retrieve the very first row of a `SELECT` query result?

What is the advantage of using a `with` statement with a `sqlite3` connection as shown below?

After executing an `UPDATE` or `DELETE` statement, how can you determine how many rows were actually changed by the database?

You have a list of 5,000 product tuples `[(pid, price), ...]`. What is the most efficient way to insert all of them into a table?

What will happen if the following code tries to insert a duplicate ID into a table where `id` is a `PRIMARY KEY`?

If you have a dictionary `user_data = {"uid": 10, "status": "active"}`, which syntax allows you to map the dictionary keys directly into your SQL statement?

SQLite does not have a native `DATETIME` storage type. When you insert a Python `datetime` object into an SQLite column declared as `TIMESTAMP`, how is it stored and what is the best practice for retrieval?

By default, `fetchone()` returns a tuple. If you want your query results to behave like a dictionary (allowing access via `row['column_name']`), which internal attribute of the connection should you modify?

You have a complex Python function `validate_checksum(data)`. How can you use this function directly inside a SQL statement to filter rows?

What is a primary functional difference between `cursor.execute()` and `cursor.executescript()`?

By default, `sqlite3` opens a transaction automatically before the first DML statement (`INSERT`, `UPDATE`, etc.). If you want to disable this behavior and handle transactions purely via SQL commands (`BEGIN`, `COMMIT`), how should you initialize the connection?