Pyfied
Log in
Python SQLite · Exercises
Practical Python

Python SQLite Exercises

4 exercises that build on the Python SQLite lesson, from easy to hard. Each one is checked in your browser when you press Check. Stuck? Use the hint before peeking at the solution.

What this lesson covers: SQLite is a small database built into Python's sqlite3 module that stores data in tables you create, fill and query with SQL, without needing a separate server. By the end you can create an SQLite database in Python, insert and read rows with ? placeholders, filter, sort and group data in SQL, protect against SQL injection, and use transactions to commit or roll back changes. You will practise: Save scores in a table; Predict what survives the rollback; Fix the injection-prone lookup; Sales report as a list of dicts.

0 / 4 completed

1. Save scores in a table

Write code Easy

Write a function add_scores(conn, rows) that receives an open connection and a list of (player, points) tuples. It must create the table scores(player TEXT, points INTEGER) if it does not exist, insert every row with executemany and ? placeholders, commit, and return the number of rows in the table.

Your code 
import sqlite3

def add_scores(conn, rows):
    # create the table (IF NOT EXISTS), insert the rows with placeholders, commit
    # then return how many rows the table holds
    pass

conn = sqlite3.connect(":memory:")
print(add_scores(conn, [("Asha", 30), ("Ben", 20)]))





  
  

  
  
  



    
  
    

  

    
2. Predict what survives the rollback

    Predict the output
    Easy
    
  

  
Read the program and type exactly what it prints. Think about which changes were committed and which were rolled back.

  
    

  

    Read this code
    

      
      
    

  

  
import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE tasks (title TEXT, done INTEGER)")
conn.execute("INSERT INTO tasks VALUES ('write', 0)")
conn.commit()

conn.execute("INSERT INTO tasks VALUES ('test', 0)")
conn.execute("UPDATE tasks SET done = 1 WHERE title = 'write'")
conn.rollback()
print(conn.execute("SELECT * FROM tasks").fetchall())

with conn:
    conn.execute("INSERT INTO tasks VALUES ('ship', 1)")
print(conn.execute("SELECT COUNT(*) FROM tasks WHERE done = 1").fetchone())


  
  
  

  

    
      
      
      
      
    
    
  

  




  
  

  
  
  



    

  
    

  

    
3. Fix the injection-prone lookup

    Fix the bug
    Medium
    
  

  
find_grade(conn, name) should return the grade of the student called name, or None when there is no such student. It breaks on names with an apostrophe and can be tricked by input like x' OR '1'='1. Fix it so the value is passed as a parameter.

  
    

  

    Your code
    

      
      
      
      
    

  

  
import sqlite3

def find_grade(conn, name):
    sql = f"SELECT grade FROM students WHERE name = '{name}'"
    row = conn.execute(sql).fetchone()
    return row[0] if row else None

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE students (name TEXT, grade INTEGER)")
conn.executemany("INSERT INTO students VALUES (?, ?)", [("Asha", 91), ("Liam O'Brien", 78)])
print(find_grade(conn, "Asha"))
print(find_grade(conn, "Liam O'Brien"))


  
  
  
  

    
      
      
      
      
    
    
  

  




  
  

  
  
  



    
  
    

  

    
4. Sales report as a list of dicts

    Write code
    Hard
    
  

  
The table sales(region TEXT, amount REAL) already exists in conn. Write sales_report(conn, minimum) that returns a list of dicts with keys region, orders (number of sales) and total (sum of amounts), one per region, for regions whose total is greater than minimum, sorted by total from largest to smallest. Use GROUP BY and HAVING with a ? placeholder, and sqlite3.Row or dict(zip(...)) to build the dicts.

  
    

  

    Your code
    

      
      
      
      
    

  

  
import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE sales (region TEXT, amount REAL)")
conn.executemany("INSERT INTO sales VALUES (?, ?)", [
    ("north", 120.0), ("south", 80.0), ("north", 60.0),
    ("east", 45.5), ("south", 40.0), ("west", 10.0),
])

def sales_report(conn, minimum):
    # group by region, keep totals above minimum, biggest total first
    return []

print(sales_report(conn, 50))


  
  
  
  

    
      
      
      
      
    
    
  

  




  
  

  
  
  



    
  
  

    Next lessonPython Logging
    
More Python SQLite examples · All Python exercises