SQLite from Python

Beginner Data Science Praktikum

Created by Pavel · 21.03.2026 at 01:05 UTC · 1 completed

You need a place to park scraped rows, experiment IDs, or homework grades without provisioning a server—so you get SQLite, a full relational engine in a single file on disk. Python's sqlite3 module follows DB-API: connect, cursor, execute, and—easy to forget—commit() before you close, or your inserts vanish like they never happened.

The security subplot is short but critical: never build SQL with f-strings from user input; ? placeholders keep data separate from code so injection and quoting bugs do not become your plot twist. SQLite is forgiving about types compared to PostgreSQL, which helps prototypes move fast but also means discipline in CREATE TABLE still pays off when you reread the schema six months later.

Typical upstreams include scraped tables (HTML pages as messy data sources) and API payloads (requests, JSON APIs, and robust fetching). Tutorial: [1].

Sources

[1]https://docs.python.org/3/library/sqlite3.html Return to text

University approvals: 0

Tasks

Question 1

Why should you bind user-provided values with cursor.execute("SELECT * FROM t WHERE id = ?", (user_id,)) instead of f-strings?

Hint

Think about malicious input containing quotes.

F-strings are faster and therefore less safe

Parameter binding avoids SQL injection and handles quoting correctly

SQLite rejects all placeholders

Placeholders work only for INSERT, not SELECT

Question 2

You opened sqlite3.connect('app.db') and ran INSERT/UPDATE/DELETE statements. What must you call so changes survive after closing the connection?

Hint

Transactions end with an explicit decision.

cursor.flush()

connection.commit()

connection.rollback()

gc.collect()

Question 3

Implement memory_row_total() -> int using sqlite3.connect(':memory:'): create table t(x INTEGER), insert integers 10 and 32, then SELECT SUM(x) and return the integer result.

Submit the function; expression mode calls memory_row_total().

Hint

fetchone()[0] gives the scalar SUM.

import sqlite3


def memory_row_total() -> int:
    # TODO: create table, insert rows, and return SUM(x).
    pass

Starter code is prefilled; replace TODO blocks with your solution.

Runtime output (stdout/stderr)

2 test cases will be used for grading

Run checks runtime behavior only. Final correctness is evaluated when you submit.

Card Info

Topic: Data Science Praktikum
Difficulty: Beginner
Completed: 1 users

Creator

Pavel