FastAPI

Chapter 10. Data Layer

If I’m not mistaken, I think Data was the comic relief on the show.

Brent Spiner, Star Trek: The Next Generation

Preview

This chapter finally creates a persistent home for our site’s data, at last connecting the three layers. It uses the relational database SQLite and introduces Python’s database API, aptly named DB-API. Chapter 14 goes into much more detail on databases, including the SQLAlchemy package and nonrelational databases.

DB-API

For over 20 years, Python has included a basic definition for a relational database interface called DB-API: PEP 249. Anyone who writes a Python driver for a relational database is expected to at least include support for DB-API, although other features may be included.

These are the main DB-API functions:

• Create a connection conn to the database with connect().

• Create a cursor curs with conn.cursor().

• Execute a SQL string stmt with curs.execute(stmt).

The execute...() functions run a SQL statement stmt string with optional parameters, listed here:

• execute(stmt) if there are no parameters

• execute(stmt, params), with parameters params in a single sequence (list or tuple) or dict

• executemany(stmt, params_seq), with multiple parameter groups in the sequence params_seq

There are five ways of specifying parameters, and not all are supported by all database drivers. If we have a statement stmt that begins with "select * from creature where", and we want to specify string parameters for the creature’s name ...