Chapter 23: Saving data

Introduction

The chapter introduction explains why cleaned data may need a database and identifies relational and NoSQL databases as useful storage options.

• Database storage and retrieval: Explain why storing cleaned data may call for a database: the data needs to be retrievable later, and significant amounts of data should be accessible without undue hassle.
  Why it matters: The reason for using a database starts with the need to retrieve stored data later.
• Relational and NoSQL options: Describe relational and NoSQL databases as two potentially useful options, giving examples named in the introduction without claiming it explains how they differ.
  Why it matters: The introduction leaves room for either family of databases, depending on the use case.

Key terms: database, store, retrieve, relational databases, NoSQL databases, PostgreSQL, MySQL, SQL Server, MongoDB

23.1 Relational databases

This section positions relational databases as an established way to store data and explains why sqlite3 can serve as an introduction to Python database use.

• Relational database role: Describe why relational databases are a common option for storing and manipulating data.
  Why it matters: This explains why the chapter considers relational databases as a storage option.
• Consistent Python database handling: Explain why sqlite3 is used to introduce Python database work without covering every relational database separately.
  Why it matters: It clarifies what the sqlite3 example can illustrate and why it does not cover every database-specific detail.

Key terms: relational databases, sqlite3

23.1.1 The Python database API

This subsection explains why Python code for accessing different SQL databases can look similar.

• Database API portability: Explain how PEP-249’s Database API makes Python SQL database code more portable across database implementations.
  Why it matters: The SQLite examples can help you recognize similar database-access code elsewhere.

Key terms: PEP-249, Database API, DB-API, SQLite, PostgreSQL, MySQL

23.2 SQLite: Using the sqlite3 database

This section explains when SQLite is useful and how to connect to a database, run parameterized SQL, read results, and save changes with Python’s sqlite3 module.

• SQLite as a local database: Explain why sqlite3 is useful for small applications and quick prototypes, and why it is less suited to large, high-traffic applications.
  Why it matters: These tradeoffs help you decide when SQLite is a practical choice.
• Connection and cursor setup: Describe how to get ready to run SQL against an sqlite3 database.
  Why it matters: The connection and cursor are the starting point for the database operations in this section.
• Safe SQL variable binding: Explain how to pass variable values to sqlite3 queries and why the section prefers binding them over constructing a query string.
  Why it matters: Binding values makes queries more secure and avoids manual escaping errors.
• Retrieving rows from queries: Describe the ways the section gives for reading rows returned by a query.
  Why it matters: These options let you consume query results in the form your code needs.
• Transaction commit and rollback: Explain why changes must be committed and what the delay before committing permits.
  Why it matters: Without an explicit commit, changes you intended to save may not be preserved.

Key terms: sqlite3, standard library, local file, connect, Connection object, Cursor object, :memory:, ?, execute, variable binding

23.3 Using MySQL, PostgreSQL, and other relational databases

This section explains why Python code for other relational databases resembles SQLite code but is not always portable.

• DB-API similarity and portability: Explain why accessing other SQL databases from Python is similar to using SQLite, and identify the connection and query-parameter differences that can prevent code from being completely portable.
  Why it matters: It helps you anticipate what must change when moving Python database code beyond SQLite.

Key terms: DB-API, database server, connection, parameters, queries

23.4 Making database handling easier with an object relational mapper

The section explains why raw SQL through DB-API can be cumbersome and introduces object relational mappers as a Python-oriented alternative.

• Raw SQL tradeoffs: Explain why writing raw SQL through DB-API can complicate moving between databases and maintaining Python code, while recognizing that raw SQL is sometimes worth using.
  Why it matters: These tradeoffs explain why a Python-oriented database approach can be appealing.
• Object relational mapper purpose: Describe what an object relational mapper does and why it offers an alternative to writing raw SQL in Python.
  Why it matters: The mapping idea is the basis for understanding why developers use an ORM.

Key terms: DB-API, raw SQL, SQL variant, object relational mapper, ORM, relational database, Python objects

23.4.1 SQLAlchemy

This subsection shows how to set up SQLAlchemy, work with a relational table through its API or a mapped class, and why an ORM can be useful.

• SQLAlchemy ORM benefits: Explain why the section uses SQLAlchemy instead of only raw SQL: it maps database tables and columns to Python objects while retaining access to SQL, and it reduces database-specific SQL differences.
  Why it matters: This explains what the extra ORM setup buys you compared with writing SQL directly.
• Engine table and session setup: Describe the setup used in the example: create an engine, define a table and its typed columns in MetaData, create a session with sessionmaker, and call create_all to create the table.
  Why it matters: These pieces establish the table and session used by the section's insert, query, and update examples.
• Table based record operations: Trace how the example inserts, selects, and updates records using the Table object, including how the session saves changes.
  Why it matters: It shows how to perform ordinary database work through SQLAlchemy's table interface rather than hand-written SQL.
• Mapped class record operations: Describe how mapping a table to a class changes the example's insert, update, and delete operations.
  Why it matters: This is the section's clearest example of treating database records as Python objects.

Key terms: SQLAlchemy, ORM, relational database, SQL, create_engine, MetaData, Table, Column, sessionmaker, Session

23.4.2 Using Alembic for database schema changes

This section explains why database migrations are useful and shows how Alembic tracks, applies, and reverses a SQLAlchemy database schema change.

• Purpose of database migrations: Explain why a migration tool is preferable to manually changing database tables and ORM code as a project develops.
  Why it matters: A migration history helps keep database changes manageable as the code changes.
• Alembic revision preparation: Describe how the example prepares Alembic and a revision script for a schema change.
  Why it matters: The revision script connects a specific schema change to Alembic’s ordered migration history.
• Applying and reversing revisions: Trace what happens when the example upgrades the database and then downgrades it by one version.
  Why it matters: Checking the tables before and after each command makes the effect of a migration visible.

Key terms: database migration tool, schema, migrations, Alembic, alembic.ini, revision script, down_revision, upgrade(), downgrade(), upgrade

23.5 NoSQL databases

This section introduces NoSQL databases as alternatives to relational tables and explains their varied data models and potential retrieval advantages.

• NoSQL data models: Distinguish the related-table structure of relational databases from NoSQL approaches that may store data as key-value pairs, indexed documents, or graphs.
  Why it matters: This distinction helps you recognize why different databases call for different ways of thinking about stored data.
• Normalization and retrieval tradeoff: Explain why the section says retrieving information from NoSQL databases can be faster and easier.
  Why it matters: It connects a difference in data organization to a possible practical benefit.

Key terms: NoSQL databases, key-value pairs, indexed documents, graphs, normalized, retrieving information

23.6 Key-value stores with Redis

Introduces Redis as a fast, networked key-value store and demonstrates basic Python operations, expiring cache entries, and persistence considerations.

• Redis as a networked dictionary: Explain what Redis stores and why its design suits caching and quick lookups.
  Why it matters: This gives you a way to recognize when Redis might fit a data-storage task.
• Python connection and basic keys: Describe how the section connects Python to Redis and uses keys for simple values and counters.
  Why it matters: These are the first operations needed to try Redis from Python.
• Redis list operations: Trace how values are added to and retrieved from a Redis list.
  Why it matters: Redis keys can hold lists as well as single values.
• Expiring keys for caching: Explain how key expiration supports a Redis cache and how the example checks it.
  Why it matters: Automatic expiration lets a simple cache discard old entries without separate cleanup code.
• Memory and persistence tradeoff: Explain the data-loss risk of Redis's in-memory storage and the persistence options described here.
  Why it matters: Speed does not remove the need to decide how much data loss an application can tolerate.

Key terms: Redis, key-value store, in-memory, caching, message broker, redis-py, Redis connection, keys(), incr, rpush

23.7 Documents in MongoDB

This section introduces MongoDB’s document model and shows how Python uses pymongo to connect to a server and work with documents in collections.

• MongoDB Documents and Fit: Explain how MongoDB stores documents rather than rows and columns, and describe the kinds of demands for which the section suggests considering it.
  Why it matters: The storage model and workload matter more than MongoDB’s popularity when choosing a database.
• Python Client and Collections: Describe what is needed to connect from Python and how databases, collections, and documents relate, including what happens when an unused collection is accessed.
  Why it matters: This explains both what the Python client connects to and why an accessed but empty collection may not show up yet.
• Document Operations and Matching: Trace how the example inserts, finds, changes, and deletes a document, and explain how it identifies documents for those operations.
  Why it matters: These operations show how dictionary-shaped Python data becomes stored data that can be located and changed.

Key terms: MongoDB, document-based database, BSON, documents, pymongo, MongoClient, database, collection, document, insert()

23.8 Creating a database

This section sets an open-ended challenge to choose a database for a previously discussed dataset, load the data, and retrieve matching records.

• Database creation challenge: Describe how to approach the challenge: choose a previously discussed dataset and a suitable database type, write code to load the data, then use two common or likely search criteria to retrieve single and multiple matching records.
  Why it matters: The challenge puts database selection, loading, and retrieval together for a dataset of your choice.

Key terms: dataset, type of database, search criteria, single and multiple matching records

23.9 Summary