SQL statements create database structures, insert and update records, and retrieve selected rows. Python commonly sends these statements through a DB-API connection and cursor. section 23.1.1 section 23.2
import sqlite3
conn = sqlite3.connect("datafile.db")
A minimal sqlite3 connection opens a local database file without requiring a separate server. section 23.2
A typical Python workflow is:
- Create a connection with sqlite3.connect and obtain a cursor from it. section 23.2
- Run SQL with cursor.execute, then retrieve rows with fetchone, fetchmany, fetchall, or cursor iteration. section 23.2
- Use ? placeholders with a tuple, or named parameters beginning with : and a dictionary, instead of constructing SQL strings. section 23.2
- Commit changes explicitly and close the connection when finished. section 23.2
Create, query, and update records
cursor.execute("create table people (id integer primary key, name text, count integer)") cursor.execute("insert into people (name, count) values (?, ?)", ("Jill", 15)) conn.commit() result = cursor.execute("select * from people where name like :name", {"name": "bob"}) print(result.fetchall()) cursor.execute("update people set count=? where name=?", (20, "Jill")) conn.commit() section 23.2
