Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 37 ยท ORM Mapping

Column Options, Computed and Deferred Columns

Go beyond plain columns in the ORM with Enum and JSON types, Python and server defaults, onupdate timestamps, column_property expressions, deferred columns and Computed values.

The previous lesson covered the basic shape of a model. Real models need more: a status column limited to a fixed set of values, a JSON blob for flexible data, a "last updated" timestamp that changes itself, a derived value such as a total. SQLAlchemy has a tool for each, and all of them build on mapped_column() and a few helpers from sqlalchemy.orm.

The examples here are one growing model, an Invoice. Every program repeats the model so you can run each on its own.

Enums and JSON

A Python enum.Enum annotation maps to an Enum column. SQLAlchemy stores the member name (not the value) by default and converts back to the member when you load a row. For a variable bag of settings, annotate with dict and give the column a JSON type.

Python Enum and JSON columns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The raw rows show the database only holds the name ('draft') and the JSON text. If you want the enum value stored instead, pass Enum(State, values_callable=lambda e: [m.value for m in e]). Prefer a short, stable set of names, because renaming a member changes stored data.

PostgreSQL has native ENUM and JSONB types with operators for querying inside the document. SQLite's JSON support is text-based, so the examples here only store and load whole JSON values. Server-specific JSON queries are shown read-only where they appear.

Python defaults, server defaults and timestamps

default= (Python) and server_default= (database) were introduced for Core. In the ORM, insert_default is an alias for the Python-side default that makes the intent explicit. A callable default is evaluated per row, which is what you want for timestamps and identifiers.

Python Timestamps that maintain themselves

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

After commit() the session expires the object, and reading note.revision loads the value the database assigned. We come back to expiration in chapter 6. For onupdate, remember it only fires for updates issued through SQLAlchemy.

column_property: expressions as attributes

column_property() maps a SQL expression to an attribute, so every query for the entity selects the value as an extra column. It is read-only: you cannot assign to it. Use it for cheap computed values that only depend on the row itself.

Python column_property

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Because the expression is part of the class, you can use it in where(), order_by() and aggregates, as the example does. The computed value is loaded with the row, so after you change qty it is stale until the object is refreshed.

Deferred columns

Big columns (a long article body, a binary file) slow queries that do not need them. Mark such a column deferred=True and it is left out of the SELECT until you read the attribute, at which point a second query fetches it. The opposite tool, undefer(), loads it up front when you know you want it.

Python deferred and undefer

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Deferred loading is one of several loading strategies. Chapter 5 covers them together, including how to avoid the "N+1" trap where each row triggers its own extra query.

Computed columns

Some databases can generate a column from others on their side. SQLAlchemy models this with Computed, which adds a GENERATED ALWAYS AS (...) clause to the DDL. The database maintains the value, and your code only reads it.

Python Computed (generated) column

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Generated columns need database support (SQLite 3.31+, PostgreSQL 12+, MySQL 5.7+). Unlike column_property, the value is part of the table, so it can be indexed.

Choosing the right tool

You want Use
A value from a fixed list Enum (or a CHECK constraint)
Flexible, schema-less data JSON
A timestamp set once, or on every update default= and onupdate= callables
A value the database should always supply server_default=
A read-only derived value in every query column_property()
A big column loaded only when needed deferred=True
A value the database maintains and can index Computed()

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does SQLAlchemy store in an Enum column by default?

    By default the enum member name is stored. Use values_callable to store the values instead.
  2. 2. What does deferred=True do?

    Deferred columns are loaded with a separate query on first access, or up front with undefer().
  3. 3. Which tool maps a SQL expression such as qty * unit_cents to a read-only attribute?

    column_property() adds the expression to every SELECT of the entity.
  4. 4. When does onupdate=utcnow run?

    onupdate is applied by SQLAlchemy when it builds an UPDATE for the object, not by the database.

Summary

  • Annotate with an enum.Enum or use JSON for fixed choices and flexible data.
  • Use callable default/onupdate for timestamps and IDs, and server_default for database-side values.
  • column_property() adds read-only SQL expressions, deferred=True postpones heavy columns, and Computed() makes the database generate a value.

Next: Working with Objects: Add, Query, Update, Delete