The examples in the previous sections show us what we can call procedural SQL processing. We've eschewed any object-oriented design from our problem domain objects. Rather than working with the Blog and Post objects, we're working with the data elements that SQLite can process string, date, float, and integer values. We've used mostly procedural-style programming.

We can see that a series of queries can be done to locate a blog, all posts that are part of the blog, and all tags that are associated with a post associated with a blog. The processing to retrieve a complete blog, all posts, and all tags on the posts would look like the following code:

query_blog_by_title = """
    SELECT * FROM blog WHERE title=?
"""
query_post_by_blog_id = """
    SELECT * FROM post WHERE blog_id=?
"""
query_tag_by_post_id = """
    SELECT tag.*
    FROM tag 
    JOIN assoc_post_tag ON tag.id = assoc_post_tag.tag_id
    WHERE assoc_post_tag.post_id=?
"""
with closing(database.cursor()) as blog_cursor:
    blog_cursor.execute(query_blog_by_title, ("2013-2014 Travel",))
    for blog in blog_cursor.fetchall():
        print("Blog", blog)
        with closing(database.cursor()) as post_cursor:
            post_cursor.execute(query_post_by_blog_id, (blog[0],))
            for post in post_cursor:
                print("Post", post)
                with closing(database.cursor()) as tag_cursor:
                    tag_cursor.execute(query_tag_by_post_id, (post[0],))
                    for tag in tag_cursor.fetchall():
                        print("Tag", tag)

We defined three SQL queries. The first fetches the blogs by the title. For each blog, we fetched all the posts that belong to this blog. Finally, we fetched all tags that are associated with a given post.

The second query implicitly repeats the REFERENCES definition between the post table and the blog table. We're finding child posts of a specific blog parent; we need to repeat some of the table definitions during the query.

The third query involves a relational join between rows of the assoc_post_tag table and the tag table. The JOIN clause recapitulates the foreign key reference in the table definitions. The WHERE clause also repeats a REFERENCES clause in the table definitions.

Because multiple tables were joined in the third query, using SELECT * will produce columns from all of the tables. We're really only interested in attributes of the TAG table, so we use SELECT TAG.* to produce only the desired columns.

These queries provide us with all of the individual bits and pieces of the data. However, these queries don't reconstruct Python objects for us. If we have more complex class definitions, we have to build objects from the individual pieces of data that we retrieved. In particular, if our Python class definitions have important method functions, we'll need a better SQL-to-Python mapping to make use of more complete Python class definitions.

In the next section, we'll see how to implement class-like processing in pure SQL.