Lesson 19 · Python
Databases: Python & SQLite in Practice
Drive the database from Python: the sqlite3 module's connections and cursors, fetching rows, the SQL injection attack and the ? placeholders that end it, transactions with commit and rollback, and a small data-access layer for a notes app.
Prerequisites: Databases: Schema Design
Learning objectives
By the end of this lesson, you will be able to:
- Open a SQLite database with sqlite3.connect, run statements through a cursor, and read results with fetchone, fetchall and row_factory=sqlite3.Row
- Explain how SQL injection works by tracing the query that formatted user input assembles - and why stripping or escaping input cannot be the fix
- Write parameterized queries with ? and named placeholders so values travel as data, and whitelist the identifiers that placeholders cannot bind
- Control transactions with commit and rollback, and demonstrate that uncommitted changes are invisible to other connections and lost on close
- Organize a data-access layer of functions that take a connection and never commit, with callers composing all-or-nothing units through the with-statement