3.3. Understanding SQLObject and TurboGears Models
At the core of an MVC style application is your domain model, which in TurboGears is usually connected to a relational database through SQLObject. As mentioned in Chapter 1, "Introduction to TurboGears," SQLObject makes database rows into objects and the data they contain into attributes.
This means you can write simple commands like these:
> User(first_name="Jane", last_name="Goodall") > users_named_jane = UserTable.selectBy(first_name="Jane")
Figure 3.2. SQLObjects are automatically stored in table rows.
For now, we'll assume that the User class is already created. So, the first command creates a User object for Jane Goodall and inserts it into the database in a new row. The second returns an iterator of all the user objects (all the rows in the user table) that have a first_name of "Jane."
As you can see from the example, you don't need to write SQL to update or do simple queries to the database. Not only that, you can create whole new databases from SQLObject after you've defined your model objects (or create model objects from your database!). That way you don't have to duplicate the database definition in both your model objects and SQL files.
This also has the side effect of making it incredibly easy to change database back ends midstream if you determine that your initial choice isn't cutting it.
Okay, enough generic SQLObject stuff. How does this all fit into the MVC paradigm?
With SQLObject, it is incredibly easy to define a simple API for your views to see your model data, without having to know anything about the database.
Let's create a simple SQLObject class and play with it:
mark@ubuntu:~/tg0.9$ tg-admin quickstart Enter project name: SQL Playground Enter package name [sqlplayground]: Do you need Identity (usernames/passwords) in this project? [no]
Now you can navigate into the SQL-Playground directory and edit the dev.cfg file to tell it where your database is. Because we are using SQLite, you need to find the line that looks like this:
You must change it to include the full path to your database file and remove the # sign from the front of the line so that it will no longer be commented out.
For Linux and Mac OS X users, that might look something like this:
For Windows users, it will look something like this:
Now we are going to make our data model, generate our database, and fire up an interactive model so that we can play with the SQLObject API. And we are going to do all of this in less than a dozen lines of code!
The first step is to cd into the sqlplayground directory inside your project directory, SQL-Playground, and edit the model.py file. Just add something like this:
class User(SQLObject): user_name = StringCol(alternateID=True, length = 20) first_name = StringCol() middle_initial = StringCol(length=1, default=None) last_name = StringCol()
When you save this file, you can run tg-admin sql create from the SQL-Playground directory to automatically generate and run the SQL commands necessary to create the tables to go along with the model you just described! If you aren't following along and using SQLlite, you might need to create your database manually, before SQLObject can create your tables for you.
Your final step is to run tg-admin shell and start playing with the interactive shell. Now you use SQLObject to create, delete, and use objects. For example, you can create a new user with this command:
>>> User(user_name="fwray", first_name="fay", last_name="wray") <User 1 userName='fwray' first_name='fay' middle_initial=None last_name='wray'>
If you add another user, that user is automatically added to the next row:
>>> User(user_name="bigfish", first_name="carl", last_name="Blankenmeyer") <User 2 user_name='bigfish' first_name='carl' middle_initial=None last_name='Blankenmeyer'>
You can retrieve a user from the database like this:
>>>User.get(2) <User 2 user_name='bigfish' first_name='carl' middle_initial=None last_name='Blankenmeyer'>
Or you can get a set of users with a key like this:
>>>u = User.selectBy(user_name="fwray") >>>list(u)
And you can update a user with simple Python commands like this:
>>> u=User.get(2) >>> u <User 2 user_name='bigfish' first_name='carl' middle_initial=None last_name='Blankenmeyer'> >>> u.first_name="Carl" >>> u <User 2 user_name='bigfish' first_name='Carl' middle_initial=None last_name='Blankenmeyer'>
SQLObject also makes it easy to do fancier things such as add foreign key relationships and many-to-many relationships with join tables, but we come back later to explore more of the SQLObject APIs as we go through the tutorials.
The key thing for right now is to have some some idea about how model objects work in TurboGears, which will help you to understand SQLObject's part in TurboGears's MVC implementation.
If you don't need to keep your information in a relational database, you can also save and retrieve model data to standard Python objects, flat files, or wherever your application requires. You can even have model objects that grab and parse information from external services.
You can also build model objects that combine several SQLObject operations within a single method, or combine SQLObject updates with XML-RPC updates, file system changes, and so forth, so that you can present your views with the cleanest possible API.
You should never change your model directly from the view. Instead, the view should allow the user to generate actions that are run by the controller, and if necessary the controller will update the model.