Traversal with SQLAlchemy¶
This is a stub page, written by a non-expert. If you have expertise, please verify the content, add recipes, and consider writing a tutorial on this.
Traversal works most naturally with an object database like ZODB because both
are naturally recursive. (I.e., "/a/b" maps naturally to root["a"]["b"]
.)
SQL tables are flat, not recursive. However, it's possible to use traversal
with SQLAlchemy, and it's becoming increasingly popular. To see how to do this,
it helps to consider recursive and non-recursive usage separately.
Non-recursive¶
A non-recursive use case is where a certain URL maps to a table, and the following component is a record ID. For instance:
1# /persons/123 => root["persons"][123]
2
3import myapp.model as model
4
5class Resource(dict):
6 def __init__(self, name, parent):
7 self.__name__ = name
8 self.__parent__ = parent
9
10class Root(Resource):
11 """The root resource."""
12
13 def add_resource(self, name, orm_class):
14 self[name] = ORMContainer(name, self, self.request, orm_class)
15
16 def __init__(self, request):
17 self.request = request
18 self.add_resource('persons', model.Person)
19
20root_factory = Root
21
22class ORMContainer(dict):
23 """Traversal component tied to a SQLAlchemy ORM class.
24
25 Calling .__getitem__ fetches a record as an ORM instance, adds certain
26 attributes to the object, and returns it.
27 """
28 def __init__(self, name, parent, request, orm_class):
29 self.__name__ = name
30 self.__parent__ = parent
31 self.request = request
32 self.orm_class = orm_class
33
34 def __getitem__(self, key):
35 try:
36 key = int(key)
37 except ValueError:
38 raise KeyError(key)
39 obj = model.DBSession.query(self.orm_class).get(key)
40 # If the ORM class has a class method '.get' that performs the
41 # query, you could do this: ``obj = self.orm_class.get(key)``
42 if obj is None:
43 raise KeyError(key)
44 obj.__name__ = key
45 obj.__parent__ = self
46 return obj
Here, root["persons"]
is a container object whose __getitem__
method
fetches the specified database record, sets name and parent attribues on it,
and returns it. (We've verified that SQLAlchemy does not define __name__
or
__parent__
attributes in ORM instances.) If the record is not found, raise
KeyError to indicate the resource doesn't exist.
TODO: Describe URL generation, access control lists, and other things needed in a complete application.
One drawback of this approach is that you have to fetch the entire record in order to generate a URL to it. This does not help if you have index views that display links to records, by querying the database directly for the IDs that match a criterion (N most recent records, all records by date, etc). You don't want to fetch the entire record's body, or do something silly like asking traversal for the resource at "/persons/123" and then generate the URL -- which would be "/persons/123"! There are a few ways to generate URLs in this case:
Define a generation-only route; e.g.,
config.add_route("person", "/persons/{id}", static=True)
Instead of returning an ORM instance, return a proxy that lazily fetches the instance when its attributes are accessed. This causes traversal to behave somewhat incorrectly. It should raise KeyError if the record doesn't exist, but it can't know whether the record exists without fetching it. If traversal returns a possibly-invalid resource, it puts a burden on the view to check whether its context is valid. Normally the view can just assume it is, otherwise the view wouldn't have been invoked.
Recursive¶
The prototypical recursive use case is a content management system, where the user can define URLs arbitrarily deep; e.g., "/a/b/c". It can also be useful with "canned" data, where you want a small number of views to respond to a large variety of URL hierarchies.
Kotti is the best current example of using traversal with SQLAlchemy recursively. Kotti is a content management system that, yes, lets users define arbitrarily deep URLs. Specifically, Kotti allows users to define a page with subpages; e.g., a "directory" of pages.
Kotti is rather complex and takes some time to study. It uses SQLAlchemy's polymorphism to make tables "inherit" from other tables. This is an advanced feature which can be complex to grok. On the other hand, if you have the time, it's a great way to learn how to do recursive traversal and polymorphism.
The main characteristic of a recursive SQL setup is a self-referential table; i.e., table with a foreign key colum pointing to the same table. This allows each record to point to its parent. (The root record has NULL in the parent field.)