In our last adventure we created the skeleton of a simple blogging application using SQLAlchemy as the ORM. In part 2 we are going to look at using Foreign Keys and create a method of allowing comments.

If you need the simpleblog files from the end of Part 1 they can be found here.

The model evolves

No blog is complete unless people can tell you what they think about your posts so it’s about time we implemented comments. To do this we’re going to have to make some additions to our database schema and we’ll learn about SA’s use of foreign keys to keep related objects updated.

First we need our comments table:

comment_table = Table("comments", metadata,
    Column("id", Integer, primary_key=True),
    Column("post_id", Integer, ForeignKey("posts.id"), nullable=False),
    Column("author_name", Unicode(255), nullable=False),
    Column("author_email", String(255), nullable=False),
    Column("author_url", String(255)),
    Column("comment_date", DateTime, default=datetime.now()),
    Column("content", Unicode)
)

In this table definition we have defined our first Foreign Key. This helps SA determine what joins to make when creating its SQL further down the line and will also create an index in the database for the post_id column. Note that you need to use the table.column notation, so we’ve linked our comments table to the posts table on the id column.

You’ll also notice the new keyword argument I’ve added to some of the columns definitions: nullable=False. This means that SA will raise and exception if you try to create a row in the comments table with author_name or author_email as null (or None in python).

Next we create our object and mapper:

class Comment(object):
    pass


assign_mapper(session.context, Comment, comment_table,
    properties={
        "post": relation(Post)
    })

But wait! There’s something new here too!

Foreign Keys have relations

When you start using foreign keys in your schema you need to help SA build the full picture on how your objects are linked. This is done using the properties keyword argument and the relation() function.

In this case we’ve added a new property to the Comment object called post. What the relation() function does is tell SA to retrieve the Post object using the foreign key in comments_table when you call .post on any Comment instance.

If that sounds complicated, don’t worry. It will become clear once we run a few examples.

The first section of your model.py should now look like this:

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)
)

comment_table = Table("comments", metadata,
    Column("id", Integer, primary_key=True),
    Column("post_id", Integer, ForeignKey("posts.id"), nullable=False),
    Column("author_name", Unicode(255), nullable=False),
    Column("author_email", String(255), nullable=False),
    Column("author_url", String(255)),
    Column("comment_date", DateTime, default=datetime.now()),
    Column("content", Unicode)
)

class Post(object):

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

class Comment(object):
    pass

assign_mapper(session.context, Post, post_table)
assign_mapper(session.context, Comment, comment_table,
    properties={
        "post": relation(Post)
    })

You have to make sure that your tables, objects and calls to assign_mapper are grouped together. This is not just for readability/maintainability (although that is a factor); if you call assign_mapper before the object or table definition you are referencing you may find yourself running into issues further down the line.

Lets create the new table and give it a try. Use tg-admin sql create which will create the tables that are missing (only the comments table at the moment) and then start tg-admin shell.

>>> p = Post.get(1)
[2006-10-15 15:12:07,638] [engine]: SELECT posts.content AS posts_content,
posts.post_date AS posts_post_date, posts.is_published AS 
posts_is_published, posts.id AS posts_id, posts.title AS posts_title 
FROM posts 
WHERE posts.id = %s ORDER BY posts.id
[2006-10-15 15:12:07,640] [engine]: [1]
>>> p.title
u'First post on simpleblog!'
>>> c = Comment()
>>> c.post = p
>>> c.author_name = "CommentDude1"
>>> c.author_email = "cd1@example.com"
>>> c.content = "Great post!  Keep them coming!"
>>> c.flush()
[2006-10-15 15:21:42,180] [engine]: INSERT INTO comments (post_id,
author_name, author_email, author_url, comment_date, content)
VALUES (%s, %s, %s, %s, %s, %s)
[2006-10-15 15:21:42,181] [engine]: [1L, 'CommentDude1',
'cd1@example.com', None, datetime.datetime(2006, 10, 15, 15, 16, 38, 
469217), 'Great post!  Keep them coming!']
>>>

There we go, we’ve added a comment to the post we created in Part 1. Just to prove it we’ll retrieve the post via the comment we’ve created.

>>> p2 = c.post
>>> p2.title
u'First post on simpleblog!'
>>>

Notice that SA didn’t actually run any SQL to retrieve the object. This is because SA knows that it already has a reference to a Post object with the id of 1 and saves itself a trip to the database. Neat, huh?

Now, lets try to get all the comments - yup, all 1 of them - related to post 1. If we go by SQLObject’s standards we should be able to do that using p.comments:

>>> p.comments
Traceback (most recent call last):
  File "<console>", line 1, in ?
AttributeError: 'Post' object has no attribute 'comments'
>>>

Apparently not, but never fear, backref is here!

assign_mapper(session.context, Comment, comment_table,
    properties={
        "post": relation(Post, backref="comments")
    })

In this example the backref keyword adds another property to the Post object which does the equivalent of SQLObject’s MultipleJoin. If we save our model.py and restart tg-admin shell we can now get a posts comments with ease:

>>> p = Post.get(1)
[2006-10-15 15:37:24,715] [engine]: SELECT posts.post_date AS
posts_post_date, posts.title AS posts_title, posts.content AS
posts_content, posts.id AS posts_id, posts.is_published AS
posts_is_published 
FROM posts 
WHERE posts.id = %s ORDER BY posts.id
[2006-10-15 15:37:24,716] [engine]: [1]

>>> comments = p.comments
[2006-10-15 15:37:46,650] [engine]: SELECT comments.comment_date AS
comments_comment_date, comments.author_email AS comments_author_email,
comments.author_name AS comments_author_name, comments.content AS
comments_content, comments.post_id AS comments_post_id,
comments.author_url AS comments_author_url, comments.id AS comments_id 
FROM comments 
WHERE %s = comments.post_id ORDER BY comments.id
[2006-10-15 15:37:46,651] [engine]: [1L]

>>> [c.author_name for c in comments]
[u'CommentDude1']
>>>

Now it is time to put our hard work into practise on the front end.

Updating the app

We first need to create a ‘post view’. At the moment we only have our front page and blogs generally only show comments on the permalink page.

@expose(template="simpleblog.templates.post")
def post(self, id):
    p = model.Post.get(int(id))
    return dict(post=p)

I know, I know… I should be using validators, and checking that the post actually exists, but we’re looking at the details of SQLAlchemy here.

I will probably be extending the series to look at the fineries of building good controller code using validators, widgets and all the other nice tricks TG lays on for you, so keep your eyes peeled. :)

And the template, post.kid:

<!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>${post.title}</title>
</head>
<body>
    <div 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="XML(post.html_content)">
            This is where your post's content is displayed.
        </div>

        <div class="comments">
            <div py:for="comment in post.comments" id="comment_${comment.id}">
                <div class="commentmeta">
                    Comment by
                    <span py:if="comment.author_url" py:strip=''>
                        <a href="${comment.author_url}" py:content="comment.author_name">Author with link</a>
                    </span>
                    <span py:if="not comment.author_url" py:strip='' py:content="comment.author_name">
                        Author without link
                    </span>
                    on
                    <span py:content="comment.comment_date">01/01/01</span>
                </div>
                <div class="content" py:content="comment.content">
                    Comment content here.
                </div>        
            </div>
        </div>
    </div>
</body>
</html>

We also need to change our index.kid so each post has a link as the title:

<h2><a href="/post?id=${post.id}" py:content="post.title">Post title here</a></h2>

Now start the simpleblog app and have a quick browse. We now have comments! Although we still have no way for users to create them. To save posting reams of code here I will be uploading a new version of the simpleblog app with an admin interface and comment forms before Part 3 of this series.

Tying up loose ends

There are a few things that we could streamline with our new model code and we’re going to sort them out now. The first item on the agenda is object creation.

Whenever we create a new object, whether it is a Comment or Post, we are forced to enter each property on a new line of code. This is not very efficient and will quickly become bothersome. To remedy this we’re going to use some standard Python code, namely the __init__ method.

The long method of init

class Comment(object):

    def __init__(self, post, author_name, author_email,
                content, author_url=None, comment_date=None):
        self.post = post
        self.author_name = author_name
        self.author_email = author_email
        self.content = content
        if author_url:
            self.author_url = author_url
        if comment_date:
            self.comment_date = comment_date

Now, when creating a Comment object, you can use the following code:

>>> p = Post.get(1)
>>> c = Comment(post=post, author_name="Bob",
... author_email="bob@example.com", content="Bob loves this site.",
... author_url="http://bob.example.com/")
>>> c.flush()

This method of using init can give you very fine grained control over how items are initialised, but comes at a cost in terms of lines of code.

Note that as the post, author_name, author_email and content keywords must be present as they don’t have a default value in the __init__ method.

The short method of init

class Post(object):

    def __init__(self, *args, **kw):
        for k,v in kw:
            if hasattr(self, k):
                attr = getattr(self, k)
                attr = v
    ...

This method takes a little understanding, but also makes object creation a lot easier, especially if you have a table with many columns. The **kw argument picks up all the keywords passed to __init__ that aren’t explicitly defined.

We then loop through all the key/value pairs and use hasattr() to see if any of the column names match that of a keyword and assign the value to it using getattr().

You can read up on using getattr() and hasattr() on the python docs site.

Comments in HTML?

We will probably also want to view the comments in HTML. I’m going to leave it as an exercise for the reader to impliment it before the next part of the series.

Hint: Take a look at your Post model ;)

To summarise…

In this part of the series we have learned how to create one-to-many relationships between our Objects using foreign keys, assign_mapper’s properties, relation and backref.

Initially I thought that this series would only need to be in two parts but it seems I’m going to have to extend SimpleBlog to cover more aspects of SQLAlchemy and, possibly, TurboGears.

Coming up in the next part: many-to-many relations and more object tweaking.