splee.blog

I’ve noticed a lot of people asking questions about SQLAlchemy in the #turbogears channel on Freenode and the TurboGears mailing list, and there aren’t that many step-by-step tutorials in using TG and SA so I decided to write one.

The tutorial will be published in two sections; Part 1 will cover the basics and part 2 will extend part 1 to cover a few of the trickier aspects of creating a project with SA.

Requirements:

• SQLAlchemy
• DocUtils

SimpleBlog will be a simple blogging app built from scratch showing off the power of SQLAlchemy. It is suggested that you go through the SQLAlchemy tutorial before reading this.

Throughout this tutorial we’ll be using assign_mapper which makes life easier when dealing with SA’s objects. I’ll try and highlight where the use of assign_mapper makes a difference, but the basic syntax for selecting objects stays the same.

We will also build SimpleBlog in a number of stages. At the beginning of each stage we will drop our database and re-initialise it as there will be schema changes to build our knowledge of SA in small steps.

Quickstart

First we need to ask TurboGears to quickstart the project for us.

shell> tg-admin quickstart --sqlalchemy -i

The above command has two extra arguments that you may not be familiar with:

• --sqlalchemy tells quickstart that you want to use SQLAlchemy for this project rather than the default, SQLObject.
• -i tells quickstart that you want to use the Identity module.

The next step is to use your favourite editor to open model.py. As you can see, quickstart has already created the default SQLAlchemy (SA) tables, classes and mappings using a combination of plain SA and ActiveMapper.

For now, we’re going to ignore this code and build the other SA tables and objects we need for this project. We’ll come back to the identity code later.

The basic theory, simplified

SA requires slightly more lines of code than SQLObject to start with, but it’s a price that pays off in the long run due to SA’s flexibility and power.

Each database object created in SQLObject has three main ’stages’ in an equivalent SA model:

1. Table creation
2. Object creation
3. Mapping of table to object

We’re now going to go through the stages with one simple table and expand on it throughout the tutorial.

Your first SA table

The first table we’re going to create will hold all the data for your blog posts.

First we need a new import to allow us to use datetime.now() easily:

from datetime import datetime

Next, the posts table:

post_table = Table("posts", metadata,
    Column("id", Integer, primary_key=True),
    Column("title", Unicode(50)),
    Column("content", Unicode),
    Column("post_date", DateTime, default=datetime.now()),
    Column("is_published", Boolean, default=False)
)

As you can see, the table definitions are fairly simple. The first argument to Table() will be the name of the table in your SQL Database. The second, metadata, must be passed to Table for every use as it tells SA all sorts of information about your project when it’s running (you can see that metadata is imported from turbogears.database in your model.py).

Next come the Column definitions. Column() takes a minimum of two arguments:

1. The name of the column.
2. The type of the column.

Lets break down this table definition.

• Column("id", Integer, primary_key=True): SQLObject creates its own Integer primary key when you create an object, but with SA you have to create it yourself. The advantage of this is that a table’s primary key does not have to be an integer, which is a point we’ll touch on later.
• Column("title", Unicode(50)) and Column("content", Unicode): Both columns are fairly self explanatory. The Unicode column type is very similar to SQLObject’s UnicodeCol. The text is encoded and decoded automatically by SA between the database and the results.
• Column("post_date", DateTime, default=datetime.now()): This column has an extra argument - default. This is, again, very similar to SQLObject and will run the method passed to it on object creation if no value has already been assigned to it.
• Column("is_published", Boolean, default=False): Given the descriptions above this is fairly self explanatory.

Great, we have our table defined. Now we create our object:

class Post(object):
    pass

And we’re done. “Surely you’re missing something!” I can hear you cry. I’m happy to say, I’m not. For simple objects SA does all the grunt work for you using mappers.

Mappers allow you to map your table to an object for use in your projects. We’re going to use assign_mapper which is an extension to SA that allows a more SQLObject-like approach to selecting your data from the database.

First we need to add a new import for assign_mapper as it is not part of SA’s core:

from sqlalchemy.ext.assignmapper import assign_mapper

Once this is done you can now link the post table we created to the bare Post object:

assign_mapper(session.context, Post, post_table)

That’s it, we’re done. Your model.py should now look like this:

from datetime import datetime

from sqlalchemy import *
from sqlalchemy.ext.activemapper import ActiveMapper, column, \
                                    one_to_many, one_to_one, many_to_many
from sqlalchemy.ext.assignmapper import assign_mapper

from turbogears import identity 
from turbogears.database import metadata, session

from datetime import datetime

post_table = Table("posts", metadata,
    Column("id", Integer, primary_key=True),
    Column("title", Unicode(50)),
    Column("content", Unicode),
    Column("post_date", DateTime, default=datetime.now()),
    Column("is_published", Boolean, default=False)
)

class Post(object):
    pass

assign_mapper(session.context, Post, post_table)

# Identity details omitted for brevity.
...

To create the database tables you need to make sure your database configuration is present and correct in your dev.cfg (most of the time the default settings will be fine), then run tg-admin sql create.

WARNING: The 1.0b1 release of TG contains a bug in it’s SA quickstart code that will cause tg-admin sql create to fail when using certain RDBMS’s.

To fix this, find the line in the VisitIdentity class that defines visit_key:

visit_key = column(String, # foreign_key="visit.visit_key",

and add (40) to the String type:

visit_key = column(String(40), # foreign_key="visit.visit_key",

This bug has been fixed and will be available in the next TurboGears release.

Using the model

Now we’re going to write the controller code that will allow you to publish your blog posts. We need to import our model:

from simpleblog import model

Open controllers.py and replace the default index() method with the following:

@expose(template="simpleblog.templates.index")
   def index(self):
       posts = model.Post.select()
       return dict(posts=posts)

This is where assign_mapper comes into it’s own. Those of you familiar with SQLObject will note that the syntax looks identical for a simple select. The SQLAlchemy Tutorial shows that to get the same result without using assign_mapper you’d have to use session.query(model.Post).select().

Now we have to create our template. Create index.kid in the simpleblog/templates directory and open in an editor.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:py="http://purl.org/kid/ns#"
    py:extends="'master.kid'">
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="content-type" py:replace="''"/>
    <title>My Blog</title>
</head>
<body>
    <div id="posts">
        <div py:for="post in posts" id="post_${post.id}">
            <h2 py:content="post.title">Post title here</h2>
            <div class="postmeta">
                Posted on <span class="postdate" py:content="post.post_date">01/01/01</span>
            </div>
            <div class="content" py:content="post.content">
                This is where your post's content is displayed.
            </div>
        </div>
    </div>
</body>
</html>

The template above is extremely simple but will suffice for the purposes of this tutorial.

You may realise that we don’t yet have any posts to display. Lets add one so we can take a look at our template at work. To add a post we’ll need to go into the tg-admin shell as Catwalk currently only supports SQLObject.

Once in the shell, we can add a post very easily:

>>> p = Post()
>>> p.title = "First post on simpleblog!"
>>> p.content = """This is the first post using simpleblog.
... 
... And so we continue coding..."""
>>> p.flush()

Now, simply start the application (python start-simpleblog.py) and browse to http://localhost:8080/ to see the fruits of your labour.

Tweaking the objects

You may notice that we put a paragraph in the p.content field when we added the first post via the shell, but the newlines are not displayed properly when viewing the post on our new blog page. We’re going to remedy this now by adding a new property to our Post object.

We’re going to add a new property to our object that will format our plain text content field as html using docutils. You’ll need to import the function from docutils that we’re going to use in model.py:

from docutils.core import publish_parts

Then find our Post class as change it as follows:

class Post(object):
    def _get_html_content(self):
        return publish_parts(self.content,
                    writer_name="html")["html_body"]
    html_content = property(_get_html_content)

Now, when we have a post object, we can access the html formatted version of the content! This will require a slight change to our template, changing:

<div class="content" py:content="post.content">

to:

<div class="content" py:content="XML(post.html_content)">

Lo, and behold! We now have HTML instead of plain text for our post!

We could have just used the _get_html_content() function (renamed without the prepended _ of course), but I personally prefer to use python’s built-in property() function as it saves me typing a pair of parentheses every time I use it.

So what have we achieved?

In this section of the series we have:

• Created a simple SQLAlchemy table and object, mapping them together
• Used the model in a simple controller and template
• Modified the Post object to automatically create HTML output of plain text using python’s property() function.

This concludes Part 1 of the SimpleBlog series. Part 1 was simple so we all start on common ground for the upcoming sections. The next chapters of the series will become increasingly challenging and fun, illustrating the power and flexibility of SQLAlchemy.

See you next week!