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.
Additional SQLAlchemy examples, some user contributed, are available on the wiki at http://www.sqlalchemy.org/trac/wiki/UsageRecipes.
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:
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:
sqlalchemy.ext.associationproxy
to make explicit references
to OrderItem
optional.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:
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:
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:
Large collection example.
Illustrates the options to use with
relationship()
when the list of related
objects is very large, including:
passive_deletes=True
to greatly improve the performance of
related collection deletion.Listing of files:
Illustrates the “materialized paths” pattern for hierarchical data using the SQLAlchemy ORM.
Listing of files:
Illustrates a rudimentary way to implement the “nested sets” pattern for hierarchical data using the SQLAlchemy ORM.
Listing of files:
Examples of various orm.relationship()
configurations,
which make use of the primaryjoin
argument to compose special types
of join conditions.
Listing of files:
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.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:
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.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.
Usage is illustrated via a unit test module test_versioning.py
, which can
be run via nose:
cd examples/versioning
nosetests -v
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:
history_meta.py
module functions.Illustrates an extension which versions data by storing new rows for each change; that is, what would normally be an UPDATE becomes an INSERT.
Listing of files:
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:
Working examples of single-table, joined-table, and concrete-table inheritance as described in datamapping_inheritance.
Listing of files:
Two examples illustrating modifications to SQLAlchemy’s attribute management system.
Listing of files:
sqlalchemy.ext.instrumentation
extension package.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:
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:
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
as well.
Changed in version 0.8: The example was modernized to use dogpile.cache, replacing Beaker as the caching library in use.
In this demo, the following techniques are illustrated:
Query
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:
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:
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: