Week 3: SQL in Python

Due: Wednesday, Sep 9th

This week is mostly documentation. The sqlite3 page is the reference you will come back to for HW2 and the labs, so the goal is to know what is on it rather than to memorize it. It is also your first look at the Python DB-API, which is the same interface you will use with PostgreSQL later in the semester.

Prompt

Paste the standard prompt, then paste this block.

Topic: Running SQL from Python with the sqlite3 module, and
modifying a database.

My learning objectives:
- Explain the roles of a connection and a cursor, and when
  changes are actually saved to the database.
- Execute a parameterized query, and explain why building SQL
  with string formatting is unsafe.
- Write INSERT, UPDATE, and DELETE statements, and CREATE TABLE
  statements with basic column constraints.

Show me where this lives in the documentation. Tell me what the
official docs call each thing and roughly where to find it, and
give me search terms, rather than explaining everything yourself.

Sources

• sqlite3 — DB-API 2.0 interface for SQLite databases in the Python standard library documentation. Work through the Tutorial, then read How to use placeholders to bind values in SQL queries. Skim the Connection and Cursor sections of the reference to see what methods exist.
• 1.5 Modifying data
• 1.6 Data types and table creation

Watch for this one

Assistants will sometimes show you an f-string inside execute(). The Python documentation is explicit that this is wrong, and labels its example "Never do this – insecure!" If your assistant does it, that is a good answer to the second report question.

Also check the placeholder style. The sqlite3 module uses ? for positional parameters and :name for named ones. Other database modules use different markers, so an assistant that has been thinking about a different library may hand you the wrong one.