UsageRecipes

Theatrum Chemicum *

A variety of techniques to use with SQLAlchemy.

---

• Examples Included in the SQLAlchemy Distribution
• Schema Techniques
  • View objects, including query and DDL capability
  • Drop all foreign key constraints explicitly, then all tables. Foolproof.
• ORM Configuration
  • Create a data getter method, auto-map a column to it via a decorator
  • Multiple Primary Mappers per Class (a.k.a how to do entity_name using SQLAlchemy 0.5 and above)
  • eagerload-capable relationship to the "last"/"first" item etc
  • Load polymorphically with polymorphic identity stored elsewhere
  • How to map to PostgreSQL INHERITS partitions
  • How to use CASE for polymorphic_on to produce more flexible polymorphic loading schemes
• Validation / Types
  • Validate column-based attributes based on the type of column.
  • Set up attribute validation/coercion events with all values of a certain type, typically a customized type
  • A "case-insensitive-string" type illustrating filtered comparison operators
  • Mark mapped columns as "not updatable" or "not insertable"
• Encryption
  • Symmetric encryption using client side functions
  • Symmetric encryption using server side functions
  • Database-side password crypt() using hybrid properties
• Versioning
  • Separate Version Tables (SQLA distribution example)
  • Issue INSERT instead of UPDATE for changes to an entity
  • Store key/values vertically, versioning all changes
• ORM Querying
  • Apply filtering criteria to all Query objects, such as a "public" column, a timestamp range, or similar
  • Load related objects using a separate query, manually "knit" together results into collections on the parent object
  • Iterate through a very large set of rows a chunk at a time, in O(N) time, i.e. not using LIMIT/OFFSET
• Session techniques
  • How to efficiently delete "orphans" via a many-to-many relationship
  • A recipe for ensuring the memory/database uniqueness of an object based on an attribute value
  • Allow the ORM to automatically update relationship attributes when a foreign key attribute has changed
  • Handy ways to temporarily disable autoflush
  • Temporarily use a distinct session / make database calls within after_commit()
  • Build a @property that's cached, expires when the database changes
  • How to DELETE items in a collection before INSERT occurs, to maintain unique constraints
  • Share user-defined state between a Session and its Connections; modify SQL, such as adding SQL comments or changing table names, on a per-Session/context basis
  • Create in-memory "indexes" to find different kinds of objects in a Session
  • Add query-time information to loaded ORM instances, such as FOR UPDATE etc
• Graphing / Documentation
  • How to implement an EXPLAIN SQL element to analyze queries
  • Generate diagrams from your database and mappers with GraphViz
  • Dump any schema to OmniGraffle
  • Profiling queries
• SQL Constructs
  • Warn for SELECT statements where tables in the FROM clause aren't joined together
  • Use window functions (ROW_NUMBER() OVER) for all LIMIT / OFFSET
  • Add SQL comments to arbitrary elements of a SQL statement
  • SELECT...INTO
  • render a Postgresql VALUES expression (i.e. constant table)
  • render a specific index of a Postgresql ARRAY expression
  • create a Table-like object that is just like an existing Table, but different name
• From the Blog
  • Django-style Database Routers in SQLAlchemy - (January 2012) Illustrates how to build rule-based database selection into the Session object.
  • Value Agnostic Types, Part II - (October 2011) Integrating smart value objects at the type level using TypeDecorator.
  • Hybrids and Value Agnostic Types, Part I - (October 2011) Creating smart value objects that integrate extremely well with the ORM.
  • Magic, a "New" ORM (May 2011) Use declarative to create your own "mini-ORM", using whatever configurational patterns suit your needs.
  • Storing/Using Enumerations (January 2011) Combines ENUM or CHAR with a nice in-Python usage pattern, replacing the need for cumbersome lookup tables.
  • Polymorphic Associations (May 2007) An old article, explains the basics of an association from many different parents to a particular target.