Skip to content


Subversion checkout URL

You can clone with
Download ZIP
100644 115 lines (91 sloc) 4.932 kB
0f07e4b @mikeorr Traversal with SQLAlchemy page.
mikeorr authored
1 Traversal with SQLAlchemy
2 %%%%%%%%%%%%%%%%%%%%%%%%%
4 This is a stub page, written by a non-expert. If you have expertise, please
5 verify the content, add recipes, and consider writing a tutorial on this.
7 Traversal works most naturally with an object database like ZODB because both
8 are naturally recursive. (I.e., "/a/b" maps naturally to ``root["a"]["b"]``.)
9 SQL tables are flat, not recursive. However, it's possible to use traversal
10 with SQLAlchemy, and it's becoming increasingly popular. To see how to do this,
11 it helps to consider recursive and non-recursive usage separately.
13 Non-recursive
14 =============
16 A non-recursive use case is where a certain URL maps to a table, and the
17 following component is a record ID. For instance::
19 # /persons/123 => root["persons"][123]
21 import myapp.model as model
23 class Resource(dict):
24 def __init__(self, name, parent):
25 = name
26 self.parent = parent
28 class Root(Resource):
29 """The root resource."""
31 def __init__(self, request):
32 self.request = request
33 self["persons"] = ORMContainer(self, "persons", request,
34 model.Person)
36 root_factory = Root
38 class ORMContainer(dict):
39 """Traversal component tied to a SQLAlchemy ORM class.
41 Calling .__getitem__ fetches a record as an ORM instance, adds certain
42 attributes to the object, and returns it.
43 """
44 def __init__(self, name, parent, request, orm_class):
45 self.__name__ = name
46 self.__parent__ = parent
47 self.request = request
48 self.orm_class = orm_class
50 def __getitem__(self, key):
51 try:
52 key = int(key)
53 except ValueError:
54 raise KeyError(key)
55 obj = model.DBSession.query(self.orm_class).get(key)
56 # If the ORM class has a class method '.get' that performs the
57 # query, you could do this: ``obj = self.orm_class.get(key)``
58 if obj is None:
59 raise KeyError(key)
60 obj.__name__ = key
61 obj.__parent__ = self
62 return obj
64 Here, ``root["persons"]`` is a container object whose .__getattr__ method
65 fetches the specified database record, sets name and parent attribues on it,
66 and returns it. (We've verified that SQLAlchemy does not define '.__name__' or
67 '.__parent__' attributes in ORM instances.) If the record is not found, raise
68 KeyError to indicate the resource doesn't exist.
70 TODO: Describe URL generation, access control lists, and other things needed in
71 a complete application.
73 One drawback of this approach is that you have to fetch the entire record in
74 order to generate a URL to it. This does not help if you have index views that
75 display links to records, by querying the database directly for the IDs that
76 match a criterion (N most recent records, all records by date, etc). You don't
77 want to fetch the entire record's body, or do something silly like asking
78 traversal for the resource at "/persons/123" and then generate the URL -- which
79 would be "/persons/123"! There are a few ways to generate URLs in this case:
81 * Define a generation-only route; e.g.,
82 ``config.add_route("person", "/persons/{id}", static=True)``
83 * Instead of returning an ORM instance, return a proxy that lazily fetches the
84 instance when its attributes are accessed. This causes traversal to behave
85 somewhat incorrectly. It *should* raise KeyError if the record doesn't exist,
86 but it can't know whether the record exists without fetching it. If traversal
87 returns a possibly-invalid resource, it puts a burden on the view to check
88 whether its context is valid. Normally the view can just assume it is,
89 otherwise the view wouldn't have been invoked.
91 Recursive
92 =========
94 The prototypical recursive use case is a content management system, where the
95 user can define URLs arbitrarily deep; e.g., "/a/b/c". It can also be useful
96 with "canned" data, where you want a small number of views to respond to a
97 large variety of URL hierarchies.
99 Kotti_ is the best current example of using traversal with SQLAlchemy
100 recursively. Kotti is a content management system that, yes, lets users define
101 arbitrarily deep URLs. Specifically, Kotti allows users to define a page with
102 subpages; e.g., a "directory" of pages.
104 .. _Kotti:
106 Kotti is rather complex and takes some time to study. It uses SQLAlchemy's
107 polymorphism to make tables "inherit" from other tables. This is an advanced
108 feature which can be complex to grok. On the other hand, if you have the time,
109 it's a great way to learn how to do recursive traversal and polymorphism.
111 The main characteristic of a recursive SQL setup is a self-referential table;
112 i.e., table with a foreign key colum pointing to the same table. This allows
113 each record to point to its parent. (The root record has NULL in the parent
114 field.)
Something went wrong with that request. Please try again.