Python Working with Databases Exercises

Python provides seamless integration with relational databases, allowing you to store, retrieve, and manipulate structured data efficiently. One of the most accessible tools is SQLite, a lightweight, serverless database engine included in Python’s standard library via the `sqlite3` module.

Using `sqlite3`, you can create databases, define tables, insert data, and run SQL queries — all using simple Python commands. This is especially useful for prototyping apps, developing locally, or managing application state without needing a full database server.

Consider the following Python code:

import sqlite3

conn = sqlite3.connect("users.db")
cursor = conn.cursor()

cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")
cursor.execute("INSERT INTO users (name) VALUES (?)", ("Alice",))
conn.commit()

cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

conn.close()

What does this code demonstrate about working with databases in Python?

It shows how to connect to a database, create a table, insert data, and retrieve records using SQLite.

It connects to a MySQL server and runs non-SQL queries.

It shows that Python cannot interact with databases without installing external packages.

It demonstrates how to query JSON files instead of SQL databases.

This code performs several common database operations using the built-in sqlite3 module:

Connects to (or creates) an SQLite database file users.db.
Creates a table named users if it doesn’t already exist.
Inserts data using parameterized queries to prevent SQL injection.
Fetches data using a SELECT query.
Commits changes and closes the connection to release resources.

This demonstrates how Python can fully manage relational data workflows using SQL, directly from your script — without requiring external database servers or complex setup.

Understanding database interactions is crucial for building data-driven applications, APIs, and full-stack web apps.

Which module is commonly used in Python for interacting with SQLite databases?

psycopg2

mysql.connector

pymongo

sqlite3

Python’s standard library includes the sqlite3 module to interact with SQLite databases without requiring external installations. mysql.connector is used for MySQL, psycopg2 for PostgreSQL, and pymongo for MongoDB.

What does the following Python code do?

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

Creates a SQLite database named example.db

Connects to an existing SQLite database or creates one if it doesn't exist

Creates a new table inside an existing database

Starts a server-based MySQL database session

The sqlite3.connect() function either connects to an existing SQLite database file or creates a new one if it doesn’t exist. This database is stored in a local file named example.db. It doesn’t create a table (Option 3), nor create SQLite or connect MySQL (Options 1 and 4).

What is the purpose of the `cursor()` method in Python's database connection object?

To close the database connection

To execute SQL commands and queries

To encrypt database communication

To commit changes to the database

The cursor() method creates a cursor object which allows you to execute SQL statements like SELECT, INSERT, UPDATE, and more. It acts as a control structure for interacting with the database. Committing changes is done using conn.commit() (not cursor()), and encryption is not handled at this level.

Which method is used to save changes made to a database after executing an `INSERT`, `UPDATE`, or `DELETE`?

cursor.save()

conn.save_changes()

cursor.execute()

conn.commit()

The conn.commit() method is used to persist changes made during a transaction (such as inserts, updates, and deletes). Without calling commit(), changes remain in memory and won’t be saved to the database file. cursor.execute() only runs queries — it doesn’t save changes.

What is the correct way to close a database connection in Python?

conn.disconnect()

conn.terminate()

conn.close()

cursor.close()

To properly close a connection to the database, use conn.close(). This releases any resources held by the connection. While cursor.close() closes the cursor, the connection itself remains open. The methods disconnect() and terminate() do not exist in Python’s DB-API standard for SQLite.

Which of the following code snippets correctly creates a table named `users` with columns `id`, `name`, and `email` in an SQLite database?

cursor.execute("CREATE TABLE users (id INTEGER, name TEXT, email TEXT)")

cursor.execute("INSERT INTO users VALUES (id, name, email)")

cursor.execute("CREATE DATABASE users (id INT, name TEXT, email TEXT)")

cursor.execute("TABLE users ADD (id INTEGER, name TEXT, email TEXT)")

To create a table in SQLite, you use the CREATE TABLE SQL statement. Option 1 is correct because it creates a table with the specified columns and types. Option 2 is for inserting data, not creating a table. Option 3 uses CREATE DATABASE, which is not valid in SQLite (databases are just files). Option 4 is an invalid SQL syntax.

After executing a `SELECT` query using `cursor.execute()`, which method should you use to retrieve all the resulting rows at once?

cursor.fetchall()

cursor.read_all()

cursor.select_all()

cursor.get_rows()

The cursor.fetchall() method retrieves all rows of a query result as a list of tuples. It's commonly used after SELECT statements. The other methods listed are not part of the Python DB-API specification and will raise AttributeError.

What will be the content of the database after running the following Python code?

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()

cur.execute("CREATE TABLE books (title TEXT)")
cur.execute("INSERT INTO books (title) VALUES ('Python 101')")
cur.execute("INSERT INTO books (title) VALUES ('Python 102')")
conn.rollback()

cur.execute("SELECT * FROM books")
print(cur.fetchall())

[('Python 101',), ('Python 102',)]

Raises an error due to rollback on in-memory database

None

[]

Since no conn.commit() is called after the inserts, and conn.rollback() is executed before the SELECT, the insert operations are undone. This results in an empty table. Rollback cancels uncommitted changes in the current transaction.

You want to prevent SQL injection when inserting user data into a database. Which is the correct and safe way to insert a user’s name into a `users` table?

Assume the variable `name = "Alice"`.

cursor.execute(f"INSERT INTO users (name) VALUES ('{name}')")

cursor.execute("INSERT INTO users (name) VALUES (%s)", (name,))

cursor.execute("INSERT INTO users (name) VALUES (?)", (name,))

cursor.execute("INSERT name INTO users VALUES (?)", name)

Using parameterized queries like cursor.execute("... VALUES (?)", (value,)) is the correct way to prevent SQL injection in SQLite. Option 1 is unsafe because it directly interpolates the string. Option 2 uses %s, which is the placeholder syntax for some databases (like PostgreSQL or MySQL), but not for SQLite. Option 4 has incorrect SQL syntax.

Which of the following statements about transactions in SQLite is TRUE?

By default, SQLite runs in autocommit mode, where every statement is a separate transaction.

You must always manually start a transaction with BEGIN TRANSACTION before executing any queries.

Calling conn.commit() rolls back the current transaction.

SQLite does not support transactions.

SQLite operates in autocommit mode by default, meaning every individual statement is automatically committed unless a transaction is explicitly started. Option 2 is false because SQLite starts transactions automatically when needed. Option 3 is false; commit() saves changes, while rollback() undoes them. Option 4 is false because SQLite fully supports transactions.

You want to safely execute multiple insertions into a SQLite database inside a transaction. Which of the following code snippets correctly uses a transaction to insert multiple rows?

conn = sqlite3.connect("mydb.db")
cursor = conn.cursor()

cursor.execute("BEGIN TRANSACTION")
cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
cursor.execute("INSERT INTO users (name) VALUES ('Bob')")
conn.commit()

conn = sqlite3.connect("mydb.db")
cursor = conn.cursor()

try:
    cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
    cursor.execute("INSERT INTO users (name) VALUES ('Bob')")
finally:
    conn.commit()

conn = sqlite3.connect("mydb.db")
cursor = conn.cursor()

cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
cursor.execute("INSERT INTO users (name) VALUES ('Bob')")
conn.commit()

conn = sqlite3.connect("mydb.db")
cursor = conn.cursor()

with conn:
    cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
    cursor.execute("INSERT INTO users (name) VALUES ('Bob')")

Option 4 uses the context manager with conn: which automatically wraps the block in a transaction and commits on success or rolls back on failure, making it the safest and cleanest approach. Option 1 manually starts a transaction but may cause issues if exceptions occur before commit. Option 3 does not explicitly start a transaction but SQLite autocommits by default (each statement). Option 2 commits even if exceptions occur, which may save partial data.

You want to fetch rows from a table where a certain column matches a parameter, but you accidentally write this code:

name = "Alice"
cursor.execute("SELECT * FROM users WHERE name = '%s'" % name)
rows = cursor.fetchall()

What is the major issue with this approach?

It can cause a syntax error.

It risks SQL injection attacks.

It uses parameterized queries correctly.

It will never return any results.

Using string interpolation to inject variables directly into SQL queries risks SQL injection, where malicious input could alter the query. The correct approach is to use parameterized queries, e.g., cursor.execute("SELECT * FROM users WHERE name = ?", (name,)) to safely pass parameters.

What will be the result of executing the following code snippet?

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()

cur.execute("CREATE TABLE numbers (num INTEGER UNIQUE)")
cur.execute("INSERT INTO numbers (num) VALUES (1)")
conn.commit()

try:
    cur.execute("INSERT INTO numbers (num) VALUES (1)")
    conn.commit()
except sqlite3.IntegrityError as e:
    print("Error:", e)

Raises an IntegrityError because num is marked as UNIQUE and 1 is already inserted.

Inserts the second row with value 1 successfully.

Ignores the error and silently fails.

Duplicates the row but logs a warning.

Since num is marked UNIQUE, attempting to insert a duplicate value causes an IntegrityError. The exception is caught and printed. The second insert does not succeed, ensuring data integrity.

You want to fetch a large number of rows from a SQLite database but avoid loading all rows into memory at once. Which method should you use?

cursor.fetchall()

cursor.fetchone()

cursor.fetchmany(size)

cursor.read()

cursor.fetchmany(size) fetches a limited number of rows at a time, allowing you to process large query results in manageable chunks. fetchall() loads all rows at once, which may be memory-intensive. fetchone() fetches only one row per call. read() is not a method of cursor objects.

What is the purpose of using parameterized queries (also known as prepared statements) in database operations?

To automatically create database indexes on queried columns.

To encrypt database queries for security.

To allow embedding Python code inside SQL statements.

To improve performance by caching query plans and prevent SQL injection attacks.

Parameterized queries improve performance by letting the database cache the query execution plan and prevent SQL injection by safely separating code and data. They do not encrypt queries, embed Python code in SQL, or create indexes automatically.

You want to insert multiple records into a table `employees` safely using executemany, but you notice one record causes an error. What happens by default, and how can you ensure all valid records get inserted even if some fail?

Which snippet implements the correct logic?

records = [(1, 'John'), (2, 'Jane'), (3, None)]  # None in a NOT NULL column

try:
    cursor.executemany("INSERT INTO employees (id, name) VALUES (?, ?)", records)
    conn.commit()
except Exception:
    conn.rollback()

for record in records:
    try:
        cursor.execute("INSERT INTO employees (id, name) VALUES (?, ?)", record)
        conn.commit()
    except Exception:
        conn.rollback()

cursor.executemany("INSERT INTO employees (id, name) VALUES (?, ?)", records)
conn.commit()

with conn:
    cursor.executemany("INSERT INTO employees (id, name) VALUES (?, ?)", records)

Option 2 inserts records one by one inside try-except blocks and commits individually, so failure of one record doesn’t affect others. Option 1 and 3 run all inserts in one batch; if one fails, the entire transaction rolls back, losing all inserts. Option 4 commits/rolls back as one transaction, so partial inserts won’t happen.

You execute the following code:

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()

cur.execute("CREATE TABLE inventory (item TEXT PRIMARY KEY, qty INTEGER)")
cur.execute("INSERT INTO inventory (item, qty) VALUES ('apple', 10)")
conn.commit()

try:
    cur.execute("UPDATE inventory SET qty = qty - 5 WHERE item = ?", ('apple',))
    cur.execute("INSERT INTO inventory (item, qty) VALUES ('apple', 20)")
    conn.commit()
except sqlite3.IntegrityError:
    conn.rollback()

cur.execute("SELECT * FROM inventory")
print(cur.fetchall())

What will be printed?

[('apple', 5)]

[('apple', 10)]

[]

Raises an exception

The UPDATE statement reduces qty from 10 to 5, but the subsequent INSERT violates the PRIMARY KEY constraint (duplicate 'apple'). The exception triggers a rollback, reverting both statements. So, the table remains unchanged with the original value (apple, 10).

What will be the output of the following code snippet?

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()

cur.execute("CREATE TABLE numbers (num INTEGER)")
cur.execute("INSERT INTO numbers (num) VALUES (1)")
cur.execute("INSERT INTO numbers (num) VALUES (2)")
conn.commit()

cur.execute("SELECT num FROM numbers")
rows = cur.fetchmany(1)
print(rows)

rows = cur.fetchmany(1)
print(rows)

[(1,)]
[(2,)]

[(1, 2)]
[]

[(1,)]
[]

[]
[]

fetchmany(1) fetches one row at a time. The first call returns the first row (1,), the second call fetches the next row (2,). This allows incremental processing of query results.

You want to create a context manager for SQLite connections to automatically commit on success and rollback on exceptions. Which class implementation below correctly achieves this?

class DBConnection:
    def __init__(self, db):
        self.conn = sqlite3.connect(db)
    def __enter__(self):
        return self.conn.cursor()
    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type is None:
            self.conn.commit()
        else:
            self.conn.rollback()
        self.conn.close()

class DBConnection:
    def __init__(self, db):
        self.conn = sqlite3.connect(db)
    def __enter__(self):
        return self.conn
    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type is None:
            self.conn.commit()
        else:
            self.conn.rollback()
        self.conn.close()

class DBConnection:
    def __init__(self, db):
        self.conn = sqlite3.connect(db)
    def __enter__(self):
        return self.conn.cursor()
    def __exit__(self, exc_type, exc_val, exc_tb):
        self.conn.commit()
        self.conn.close()

class DBConnection:
    def __init__(self, db):
        self.conn = sqlite3.connect(db)
    def __enter__(self):
        return self.conn
    def __exit__(self, exc_type, exc_val, exc_tb):
        self.conn.commit()
        self.conn.close()

Option 2 returns the connection object itself in __enter__ so users can get cursors as needed and correctly commits or rolls back based on exception presence in __exit__, then closes the connection. Option 1 returns a cursor, which might limit flexibility. Options 3 and 4 commit unconditionally even on exceptions, which is unsafe.