Section 12.2. More-Advanced SQLObject Customization

12.2. More-Advanced SQLObject Customization

The sqlmeta class gives you quite a few opportunities to customize particular aspects of SQLObject's behavior. But sqlmeta has a limited and predefined set of behaviors for you to play with. Sometimes you want more.

You might also want to add additional attributes, or intercept attribute access and modify it in some way. As you will see in the next section, SQLObject provides helpers for these things, too.

12.2.1. Adding Magic Attributes

To gain the benefits of the model-viewer-controller (MVC) architecture, you can't go throwing business logic in your controller objects. Their job is to handle user actions. Wouldn't it be convenient if you could override attribute access in your model classes? That way you could keep your controller logic clean and keep your model logic together in one obvious place. Fortunately, SQLObject makes this easy, too.

Let's take a look at a simple example. Consider an Exam class that has simple arithmetic exercises and solutions to check against. One design is to have for each row an exercise column and a solution column. The problem with this approach is that you have to trust whoever populates this table to put in the right solutions. An elegant solution is to compute the solution on-the-fly when it is needed. This saves space because the table has only one column and also reduces traffic to the DB (could be important for remote DB). The question is where to calculate the solution. One obvious answer is to do it in the controller. However, if the computation is complicated and there are many applications that use the same SQLObject model classes, you can do it one time in the model class. Here is the code:

class Exam(SQLObject):     exercise = StringCol()     def _solve(self):         return eval(self.exercise)     solution = property(fget=_solve) Exam.dropTable(ifExists=True) Exam.createTable() e = Exam(exercise='1+1') print e.exercise, '=', e.solution

Output:

1+1 = 2

The solution attribute is a Python property that calls the _solve() method upon get access. The _solve() method solves the exercise by calling Python's eval function and returns the solution. Nice, simple, and uses standard Python features. SQLObject provides a shortcut. Instead of defining a full-fledged property, you can take advantage of a special prefix convention of _set_,_get_,_del_, and _doc_. If you have a method whose name starts with one of these prefixes, SQLObject will create a property for you. Here is the solution attribute with this shortcut:

def _get_solution(self):     return eval(self.exercise)

It's just a little syntactic sugar. _get_solution magically creates the _solution attribute for you to get whenever you need it. In the same way, you could use _set_exercise to test the expression that is passed in to make sure it does not produce an error on eval before you save the exercise into the database.

12.2.2. Overriding Attribute Access

This is where the pedal really hits the metal. This is the holy grail of ORM because it enables you to massage the data before/after it gets into/out of the DB and provide useful services to the applications. Consider an account object with an encrypted credit card number field. If this object is being accessed from multiple applications, each application must know how and remember to encrypt/decrypt the credit card number on the way into and out of the DB. A better solution is to have the application provide the encryption key and have the model class do the encryption/decryption automatically upon access. In the following code, the application sets the encryption key as a regular Python class attribute called encryptionKey. The _get_ and _set_ property methods of the creditCard take care of encrypting/decrypting (using the super-secure xor algorithm) the credit card number before/after the actual DB access, which is done using the _SO_get_ and _SO_set_ special methods:

class Account(SQLObject):     encryptionKey = 0     name = StringCol()     creditCard = IntCol()     def _get_creditCard(self):         value = self._SO_get_creditCard()         print ('_get_creditCard() - encrypted credit ' + '                'card # from DB:', value)         return value ^ Account.encryptionKey     def _set_creditCard(self, value):         self._SO_set_creditCard(value ^ Account.encryptionKey) Account.dropTable(ifExists=True) Account.createTable() Account.encryptionKey = 100334062 creditCard = 11111116 a = Account(name='Kozmo', creditCard=11111116) assert a.creditCard == creditCard print a

Output:

_get_creditCard() - encrypted credit card # from DB: 89355042 _get_creditCard() - encrypted credit card # from DB: 89355042 <Account 1 name='Kozmo' creditCard=11111116>

*DISCLAIMER: 11111116 is not really my credit card number.

Note

_get_creditCard was called twice (once for the assert and once when printing a). This means that caching doesn't happen at the _get_ level, but in the _SO_get level. If you are trying to monitor the actual DB activity, _get_ is not the right place.