23 Saving data
This chapter explains how to store and retrieve data reliably using databases from Python, comparing long‑standing relational systems with newer NoSQL options. It shows how Python’s ecosystem provides consistent, practical tools for connecting to and working with different back ends, focusing on when each approach fits best. The emphasis is on clear, minimal patterns you can reuse: connect, structure data, write and read records safely, and evolve your schema as projects grow.
On the relational side, the chapter starts with the DB‑API, which gives Python a common interface across databases and is illustrated with sqlite3 for lightweight, file‑based storage. It covers creating connections and cursors, executing parameterized queries to avoid injection issues, fetching results, and committing transactions. It then notes small portability differences you’ll hit with servers like PostgreSQL and MySQL (connection details and parameter styles). To reduce raw SQL and smooth over vendor differences, it introduces ORMs, focusing on SQLAlchemy: defining tables and schemas, creating sessions, inserting, selecting, updating, and mapping tables to classes for object‑style access. Finally, it shows how to manage schema evolution with Alembic migrations, using versioned upgrade and downgrade scripts to track and roll back changes safely.
For NoSQL, the chapter surveys models that trade strict normalization for flexibility and speed. It demonstrates Redis as an in‑memory key:value store well suited for caching, counters, and lists, including setting and incrementing values, working with lists, and using expirations, while noting persistence considerations and snapshot options. It then explores MongoDB’s document model with PyMongo, showing how databases and collections are created on demand and how to perform CRUD operations, address documents by their ObjectId, and replace or drop data as needed. The guidance throughout stresses choosing the right tool—relational databases for structured, relational workloads and NoSQL stores for cases favoring flexible schemas, rapid lookups, or large‑scale document storage.
Summary
- Python has a Database API (DB-API) that provides a generally consistent interface for clients of several relational databases.
- SQLite is a file based relational database that is part of the Python standard library.
- PostgreSQL and MySQL are to popular database servers commonly used with Python.
- Using an Object Relational Mapper (ORM) can make database code even more standard across databases.
- Using an ORM also lets you access relational databases through Python code and objects rather than SQL queries.
- Tools such as Alembic work with ORMs to use code to make reversible changes to a relational database schema.
- Key:value stores such as Redis provide quick in-memory data access without SQL (NoSQL).
- MongoDB provides scalability and is based on documents, without the strict structure of relational databases.
FAQ
What is the Python DB-API (PEP 249) and why does it matter?
The DB-API defines a common interface for Python database drivers so your code looks similar across SQLite, PostgreSQL, MySQL, and others. You connect, create cursors, execute statements, fetch results, and manage transactions with the same concepts, improving portability and reducing the learning curve when switching drivers.How do I safely pass parameters to SQL queries with sqlite3?
Use parameterized queries to avoid SQL injection and escaping issues. With sqlite3 you can use positional placeholders and a tuple:cursor.execute("SELECT * FROM people WHERE name = ?", (name,)) or named placeholders and a dict: cursor.execute("... WHERE name = :name", {"name": name}). Don’t build SQL by string concatenation.When should I use SQLite versus a server database like PostgreSQL or MySQL?
Use SQLite for small apps, local tools, prototypes, and testing: it’s file-based, zero-dependency, and in the standard library. Use PostgreSQL/MySQL when you need concurrent access, a networked server, stronger performance under load, advanced features, or production-grade reliability and scaling.How do transactions and commits work in sqlite3?
sqlite3 does not autocommit by default. After INSERT/UPDATE/DELETE, callconn.commit() to persist changes; otherwise you can conn.rollback(). Always commit before conn.close() because closing a connection does not implicitly commit pending work.What are the basics of fetching results with a DB-API cursor?
You can usefetchone() for a single row, fetchmany(n) for multiple rows, or fetchall() for the remainder. Cursors are iterable, so you can loop over rows directly: for row in cursor.execute("SELECT ..."): print(row).Why use an ORM, and what problems can it solve?
An ORM (like SQLAlchemy) maps tables/columns to Python objects. Benefits include: less raw SQL to maintain, fewer portability issues across databases, and more Pythonic code (query-building, inserts/updates via objects). You can still drop down to SQL when needed for control or performance.How do I get started with SQLAlchemy to create tables and run basic queries?
- Create an engine:engine = create_engine("sqlite:///data.db")- Define metadata and tables (columns and types).
- Create a session with
sessionmaker(bind=engine).- Insert via
people.insert().values(...) or mapped classes; select via select(people) and optional .where(...); update with people.update().values(...).where(...); and commit with session.commit(). Declarative mapping lets you define classes whose attributes map to columns and use session.add()/query().What is Alembic and how do migrations work?
Alembic is SQLAlchemy’s migration tool. You initialize a migration environment, set thesqlalchemy.url, generate revision files, and write upgrade()/downgrade() functions describing schema changes. Run alembic upgrade head to apply and alembic downgrade -1 to roll back. Alembic tracks versions in an alembic_version table.What are Redis’s common use cases and core operations from Python?
Redis is an in-memory key:value store great for caching, counters, queues, and quick lookups. With redis-py: set/get keys (set/get), atomic counters (incr), lists (rpush/lpush, lrange), and expirations (setex, pttl). Remember values are in memory; configure persistence or snapshot with save/bgsave if needed.How do I work with MongoDB documents using PyMongo?
- Connect withMongoClient(...) and select a database and collection (created on first insert).- Insert documents (Python dicts) and get an
ObjectId back.- Find with
find_one() or queries (including by _id).- Update with operators like
$set via update_one(), replace with replace_one(), and delete with delete_one().- Drop collections explicitly when you want to remove them.
The Quick Python Book, Fourth Edition ebook for free
