The SQLAlchemy distribution includes a variety of code examples illustrating
a select set of patterns, some typical and some not so typical. All are
runnable and can be found in the /examples directory of the
distribution. Descriptions and source code for all can be found here.
Mapping Recipes
Adjacency List
An example of a dictionary-of-dictionaries structure mapped using
an adjacency list model.
E.g.:
node = TreeNode('rootnode')
node.append('node1')
node.append('node3')
session.add(node)
session.commit()
dump_tree(node)
Listing of files:
Associations
Examples illustrating the usage of the “association object” pattern,
where an intermediary class mediates the relationship between two
classes that are associated in a many-to-many pattern.
Listing of files:
dict_of_sets_with_default.py - An advanced association proxy example which
illustrates nesting of association proxies to produce multi-level Python
collections, in this case a dictionary with string keys and sets of integers
as values, which conceal the underlying mapped classes.
proxied_association.py - Same example as basic_association, adding in
usage of sqlalchemy.ext.associationproxy to make explicit references
to OrderItem optional.
basic_association.py - Illustrate a many-to-many relationship between an
“Order” and a collection of “Item” objects, associating a purchase price
with each via an association object called “OrderItem”
Directed Graphs
An example of persistence for a directed graph structure. The
graph is stored as a collection of edges, each referencing both a
“lower” and an “upper” node in a table of nodes. Basic persistence
and querying for lower- and upper- neighbors are illustrated:
n2 = Node(2)
n5 = Node(5)
n2.add_neighbor(n5)
print n2.higher_neighbors()
Listing of files:
Dynamic Relations as Dictionaries
Illustrates how to place a dictionary-like facade on top of a
“dynamic” relation, so that dictionary operations (assuming simple
string keys) can operate upon a large collection without loading the
full collection at once.
Listing of files:
Generic Associations
Illustrates various methods of associating multiple types of
parents with a particular child object.
The examples all use the declarative extension along with
declarative mixins. Each one presents the identical use
case at the end - two classes, Customer and Supplier, both
subclassing the HasAddresses mixin, which ensures that the
parent class is provided with an addresses collection
which contains Address objects.
The discriminator_on_association.py and generic_fk.py scripts
are modernized versions of recipes presented in the 2007 blog post
Polymorphic Associations with SQLAlchemy.
Listing of files:
generic_fk.py - Illustrates a so-called “generic foreign key”, in a similar fashion
to that of popular frameworks such as Django, ROR, etc. This
approach bypasses standard referential integrity
practices, in that the “foreign key” column is not actually
constrained to refer to any particular table; instead,
in-application logic is used to determine which table is referenced.
table_per_association.py - Illustrates a mixin which provides a generic association
via a individually generated association tables for each parent class.
The associated objects themselves are persisted in a single table
shared among all parents.
table_per_related.py - Illustrates a generic association which persists association
objects within individual tables, each one generated to persist
those objects on behalf of a particular parent class.
discriminator_on_association.py - Illustrates a mixin which provides a generic association
using a single target table and a single association table,
referred to by all parent tables. The association table
contains a “discriminator” column which determines what type of
parent object associates to each particular row in the association
table.
Large Collections
Large collection example.
Illustrates the options to use with
relationship() when the list of related
objects is very large, including:
Listing of files:
Materialized Paths
Illustrates the “materialized paths” pattern for hierarchical data using the
SQLAlchemy ORM.
Listing of files:
Nested Sets
Illustrates a rudimentary way to implement the “nested sets”
pattern for hierarchical data using the SQLAlchemy ORM.
Listing of files:
Relationship Join Conditions
Examples of various orm.relationship() configurations,
which make use of the primaryjoin argument to compose special types
of join conditions.
Listing of files:
threeway.py - Illustrate a “three way join” - where a primary table joins to a remote
table via an association table, but then the primary table also needs
to refer to some columns in the remote table directly.
cast.py - Illustrate a relationship() that joins two columns where those
columns are not of the same type, and a CAST must be used on the SQL
side in order to match them.
Space Invaders
A Space Invaders game using SQLite as the state machine.
Originally developed in 2012. Adapted to work in Python 3.
Runs in a textual console using ASCII art.
To run:
python -m examples.space_invaders.space_invaders
While it runs, watch the SQL output in the log:
tail -f space_invaders.log
enjoy!
Listing of files:
XML Persistence
Illustrates three strategies for persisting and querying XML
documents as represented by ElementTree in a relational
database. The techniques do not apply any mappings to the
ElementTree objects directly, so are compatible with the
native cElementTree as well as lxml, and can be adapted to
suit any kind of DOM representation system. Querying along
xpath-like strings is illustrated as well.
E.g.:
# parse an XML file and persist in the database
doc = ElementTree.parse("test.xml")
session.add(Document(file, doc))
session.commit()
# locate documents with a certain path/attribute structure
for document in find_document('/somefile/header/field2[@attr=foo]'):
# dump the XML
print document
Listing of files:
pickle_type.py - illustrates a quick and dirty way to persist an XML document expressed using
ElementTree and pickle.
adjacency_list.py - Illustrates an explicit way to persist an XML document expressed using
ElementTree.
optimized_al.py - Uses the same strategy as
adjacency_list.py, but associates each DOM row with its owning
document row, so that a full document of DOM nodes can be loaded
using O(1) queries - the construction of the “hierarchy” is performed
after the load in a non-recursive fashion and is more
efficient.
Versioning Objects
Versioning with a History Table
Illustrates an extension which creates version tables for entities and stores
records for each change. The given extensions generate an anonymous “history”
class which represents historical versions of the target object.
Compare to the Versioning using Temporal Rows examples which write updates
as new rows in the same table, without using a separate history table.
Usage is illustrated via a unit test module test_versioning.py, which can
be run via py.test:
# assume SQLAlchemy is installed where py.test is
cd examples/versioned_history
py.test test_versioning.py
A fragment of example usage, using declarative:
from history_meta import Versioned, versioned_session
Base = declarative_base()
class SomeClass(Versioned, Base):
__tablename__ = 'sometable'
id = Column(Integer, primary_key=True)
name = Column(String(50))
def __eq__(self, other):
assert type(other) is SomeClass and other.id == self.id
Session = sessionmaker(bind=engine)
versioned_session(Session)
sess = Session()
sc = SomeClass(name='sc1')
sess.add(sc)
sess.commit()
sc.name = 'sc1modified'
sess.commit()
assert sc.version == 2
SomeClassHistory = SomeClass.__history_mapper__.class_
assert sess.query(SomeClassHistory).\
filter(SomeClassHistory.version == 1).\
all() \
== [SomeClassHistory(version=1, name='sc1')]
The Versioned mixin is designed to work with declarative. To use
the extension with classical mappers, the _history_mapper function
can be applied:
from history_meta import _history_mapper
m = mapper(SomeClass, sometable)
_history_mapper(m)
SomeHistoryClass = SomeClass.__history_mapper__.class_
Listing of files:
Versioning using Temporal Rows
Several examples that illustrate the technique of intercepting changes
that would be first interpreted as an UPDATE on a row, and instead turning
it into an INSERT of a new row, leaving the previous row intact as
a historical version.
Compare to the Versioning with a History Table example which writes a
history row to a separate history table.
Listing of files:
versioned_rows_w_versionid.py - Illustrates a method to intercept changes on objects, turning
an UPDATE statement on a single row into an INSERT statement, so that a new
row is inserted with the new data, keeping the old row intact.
versioned_update_old_row.py - Illustrates the same UPDATE into INSERT technique of versioned_rows.py,
but also emits an UPDATE on the old row to affect a change in timestamp.
Also includes a QueryEvents.before_compile() hook to limit queries
to only the most recent version.
versioned_map.py - A variant of the versioned_rows example built around the
concept of a “vertical table” structure, like those illustrated in
Vertical Attribute Mapping examples.
versioned_rows.py - Illustrates a method to intercept changes on objects, turning
an UPDATE statement on a single row into an INSERT statement, so that a new
row is inserted with the new data, keeping the old row intact.
Vertical Attribute Mapping
Illustrates “vertical table” mappings.
A “vertical table” refers to a technique where individual attributes
of an object are stored as distinct rows in a table. The “vertical
table” technique is used to persist objects which can have a varied
set of attributes, at the expense of simple query control and brevity.
It is commonly found in content/document management systems in order
to represent user-created structures flexibly.
Two variants on the approach are given. In the second, each row
references a “datatype” which contains information about the type of
information stored in the attribute, such as integer, string, or date.
Example:
shrew = Animal(u'shrew')
shrew[u'cuteness'] = 5
shrew[u'weasel-like'] = False
shrew[u'poisonous'] = True
session.add(shrew)
session.flush()
q = (session.query(Animal).
filter(Animal.facts.any(
and_(AnimalFact.key == u'weasel-like',
AnimalFact.value == True))))
print 'weasel-like animals', q.all()
Listing of files:
Special APIs
Attribute Instrumentation
Examples illustrating modifications to SQLAlchemy’s attribute management
system.
Listing of files:
Horizontal Sharding
A basic example of using the SQLAlchemy Sharding API.
Sharding refers to horizontally scaling data across multiple
databases.
The basic components of a “sharded” mapping are:
multiple databases, each assigned a ‘shard id’
a function which can return a single shard id, given an instance
to be saved; this is called “shard_chooser”
a function which can return a list of shard ids which apply to a particular
instance identifier; this is called “id_chooser”.If it returns all shard ids,
all shards will be searched.
a function which can return a list of shard ids to try, given a particular
Query (“query_chooser”). If it returns all shard ids, all shards will be
queried and the results joined together.
In this example, four sqlite databases will store information about weather
data on a database-per-continent basis. We provide example shard_chooser,
id_chooser and query_chooser functions. The query_chooser illustrates
inspection of the SQL expression element in order to attempt to determine a
single shard being requested.
The construction of generic sharding routines is an ambitious approach
to the issue of organizing instances among multiple databases. For a
more plain-spoken alternative, the “distinct entity” approach
is a simple method of assigning objects to different tables (and potentially
database nodes) in an explicit way - described on the wiki at
EntityName.
Listing of files:
Extending the ORM
Dogpile Caching
Illustrates how to embed
dogpile.cache
functionality within the Query object, allowing full cache control
as well as the ability to pull “lazy loaded” attributes from long term cache.
In this demo, the following techniques are illustrated:
Using custom subclasses of Query
Basic technique of circumventing Query to pull from a
custom cache source instead of the database.
Rudimental caching with dogpile.cache, using “regions” which allow
global control over a fixed set of configurations.
Using custom MapperOption objects to configure options on
a Query, including the ability to invoke the options
deep within an object graph when lazy loads occur.
E.g.:
# query for Person objects, specifying cache
q = Session.query(Person).options(FromCache("default"))
# specify that each Person's "addresses" collection comes from
# cache too
q = q.options(RelationshipCache(Person.addresses, "default"))
# query
print q.all()
To run, both SQLAlchemy and dogpile.cache must be
installed or on the current PYTHONPATH. The demo will create a local
directory for datafiles, insert initial data, and run. Running the
demo a second time will utilize the cache files already present, and
exactly one SQL statement against two tables will be emitted - the
displayed result however will utilize dozens of lazyloads that all
pull from cache.
The demo scripts themselves, in order of complexity, are run as Python
modules so that relative imports work:
python -m examples.dogpile_caching.helloworld
python -m examples.dogpile_caching.relationship_caching
python -m examples.dogpile_caching.advanced
python -m examples.dogpile_caching.local_session_caching
Listing of files:
environment.py - Establish data / cache file paths, and configurations,
bootstrap fixture data if necessary.
caching_query.py - Represent functions and classes
which allow the usage of Dogpile caching with SQLAlchemy.
Introduces a query option called FromCache.
model.py - The datamodel, which represents Person that has multiple
Address objects, each with PostalCode, City, Country.
fixture_data.py - Installs some sample data. Here we have a handful of postal codes for
a few US/Canadian cities. Then, 100 Person records are installed, each
with a randomly selected postal code.
helloworld.py - Illustrate how to load some data, and cache the results.
relationship_caching.py - Illustrates how to add cache options on
relationship endpoints, so that lazyloads load from cache.
advanced.py - Illustrate usage of Query combined with the FromCache option,
including front-end loading, cache invalidation and collection caching.
local_session_caching.py - This example creates a new dogpile.cache backend that will persist data in a
dictionary which is local to the current session. remove() the session and
the cache is gone.
PostGIS Integration
A naive example illustrating techniques to help
embed PostGIS functionality.
This example was originally developed in the hopes that it would be
extrapolated into a comprehensive PostGIS integration layer. We are
pleased to announce that this has come to fruition as GeoAlchemy.
The example illustrates:
a DDL extension which allows CREATE/DROP to work in
conjunction with AddGeometryColumn/DropGeometryColumn
a Geometry type, as well as a few subtypes, which
convert result row values to a GIS-aware object,
and also integrates with the DDL extension.
a GIS-aware object which stores a raw geometry value
and provides a factory for functions such as AsText().
an ORM comparator which can override standard column
methods on mapped objects to produce GIS operators.
an attribute event listener that intercepts strings
and converts to GeomFromText().
a standalone operator example.
The implementation is limited to only public, well known
and simple to use extension points.
E.g.:
print session.query(Road).filter(
Road.road_geom.intersects(r1.road_geom)).all()
Listing of files: