-
-
Notifications
You must be signed in to change notification settings - Fork 1.5k
UsageRecipes
mike bayer edited this page Jun 28, 2023
·
21 revisions
Theatrum Chemicum *
A variety of techniques to use with SQLAlchemy.
- Examples Included in the SQLAlchemy Distribution
- Schema Techniques
- 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
- Versioning
- 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
- 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.