SQLite is an open source relational database included with the Python standard library as of Python 2.5. The pysqlite database driver is also included with the standard library so that no further external dependencies are required to access a SQLite database from within Python applications.
sqlite3 - embedded relational database is an extensive tutorial showing many of the common create, read, update and delete operations a developer would want to do with SQLite.
A simple step-by-step SQLite tutorial walks through creating databases as well as inserting, updating, querying and deleting data.
SQLite: The art of keep it simple uses C code examples from SQLite's codebase to show how its design has been kept consistent and tight throughout 15+ years of active development. There's also a great design document on the SQLite site that covers many of these principles.
My list of SQLite resources is a nice roundup of useful tools to use with SQLite and tutorials for learning more about the database.
Using SQLite with Flask explains how Flask code can directly query a SQLite database without an ORM.
SQLite Browser is an open source graphical user interface for working with SQLite.
The official sqlite3 module in the Python stdlib docs contains a bunch of scenarios with code for how to use the database from a Python application.
Using the SQLite JSON1 and FTS5 Extensions with Python shows how to compile SQLite 3.9.0+ with json1 and fts5 (full-text search) support to use these new features.
SQLite with a fine-toothed comb digs into the internals of SQLite and shows some bugs found (and since fixed) while the author was researching the SQLite source code.