Theory
The shell was practice; Python is the product
Everything so far happened in the sqlite3 shell: you typed, the shell answered. Fine for you, useless for the exam cell: they want ResultDesk to be a program: a menu, buttons, reports that generate themselves.
Programs cannot type into shells. Your Python code must open college.db itself, run SQL itself, and read the answers itself.
Python ships the bridge in its standard library: the sqlite3 module. No installation, one import, three objects.
Theory
Phone line, receptionist, request slip
Talking to a database from a program has three layers:
- connect() dials the office: one open phone line to college.db (the Connection object).
- cursor() brings a receptionist onto that line (the Cursor object): the one who actually processes requests.
- execute() hands the receptionist one request slip: your SQL, written on a Python string.
Line, receptionist, slip. Every database program you ever write starts with these three.
Theory
The canonical skeleton
Five lines, worth memorising as a unit:
1. import sqlite3
2. conn = sqlite3.connect('college.db'): opens the file, returns a Connection.
3. cur = conn.cursor(): returns a Cursor, the SQL runner.
4. cur.execute('SELECT * FROM marks'): runs one SQL statement, passed as a string.
5. conn.close(): hangs up, releasing the file.
Every lesson in the rest of this unit only adds flesh to this skeleton: fetching results, committing changes.
Practical
ResultDesk's first database program
import sqlite3
# open (or create!) the database file
conn = sqlite3.connect('college.db')
# the worker that runs SQL
cur = conn.cursor()
# any SQL from Unit 1 travels as a plain string:
cur.execute("CREATE TABLE IF NOT EXISTS marks (roll INTEGER, subject TEXT, score INTEGER)")
cur.execute("SELECT roll, score FROM marks WHERE subject = 'DBMS'")
# (reading the rows: next lesson's fetchone/fetchall)
conn.close() # always hang up
This example runs in Gri-Learn on the web, where you can edit it and see the output.
Think first
The mystery of the empty database
A student's script says sqlite3.connect('collage.db') (spelling slip) and then SELECTs from marks. The real file college.db sits right there, full of data. Before tapping: what error appears, and what new thing now exists in the folder?
Show the answer
The error is no such table: marks, and the folder now contains a brand-new, EMPTY file called collage.db.
connect() does not check spelling: a missing file is silently created. So typos do not fail loudly at connect time; they fail confusingly at query time, on a database that has nothing in it. When you see 'no such table' on a table you are sure exists, check the filename and the working directory first.
Quiz
To reuse the shell skills from Unit 2, a student writes cur.execute('.dump marks') in Python. What happens?
- Syntax error near '.': dot-commands are shell features, not SQL, and execute() only accepts SQL
- It works: execute accepts anything the shell accepts
- It dumps the table to the screen
- It silently creates a file called marks.sql
Show the answer
Syntax error near '.': dot-commands are shell features, not SQL, and execute() only accepts SQL
execute() speaks exactly ONE language: SQL. Dot-commands (.dump, .schema, .mode) belong to the sqlite3 command-line shell, a different program entirely: fed to execute() they are gibberish and raise an error. This shell-vs-module boundary is the most common conceptual slip of the whole unit. (Programmatic dumping exists, via the connection's iterdump(), worth one line in an exam answer.)
Watch out
Three skeleton slips
Cursor skipped: rows come via the cursor in this syllabus's canonical pattern: learn conn → cursor → execute as one chain.
One statement per execute(): stacking 'CREATE ...; INSERT ...' in one call fails; executescript() exists for scripts.
close() is not save: closing without commit() throws away uncommitted changes: the exact trap the transactions lesson promised, defused properly next lesson.
Theory
Everything you know just became ammunition
Notice what execute() takes: any SQL string. Every SELECT, JOIN, GROUP BY, CASE and trigger from Unit 1 now runs from Python, unchanged, quotes around it. The subject's two halves have met: SQL is the language, Python is the speaker. Next lesson the receptionist starts handing answers back: fetchone and fetchall.
Summary
Key takeaways
- import sqlite3: standard library, nothing to install.
- conn = sqlite3.connect('file.db') opens the database and CREATES it if the name is new (typo trap).
- cur = conn.cursor() gives the worker; cur.execute('SQL') runs exactly one statement.
- conn.close() releases the file; closing does not save pending changes.
- Dot-commands never work inside execute(): SQL only.
- The five-line skeleton (import, connect, cursor, execute, close) underlies every database program.
- Memory hook: phone line, receptionist, request slip.