For this small object model, we can implement the entire access layer in a single class. This class will include methods to perform CRUD operations on each of our persistent classes. 

This example won't painstakingly include all of the methods for a complete access layer. We'll show you the important ones. We'll break this down into several sections to deal with Blogs, Posts, and iterators. Here's the first part of our access layer:

# An access layer to map back and forth between Python objects and SQL rows.
class Access:
    get_last_id = """
        SELECT last_insert_rowid()
    """

    def open(self, path: Path) -> None:
        self.database = sqlite3.connect(path)
        self.database.row_factory = sqlite3.Row

    def get_blog(self, id: str) -> Blog:
        query_blog = """
            SELECT * FROM blog WHERE id=?
        """
        row = self.database.execute(query_blog, (id,)).fetchone()
        blog = Blog(title=row["TITLE"])
        blog._id = row["ID"]
        blog._access = ref(self)
        return blog

    def add_blog(self, blog: Blog) -> Blog:
        insert_blog = """
            INSERT INTO blog(title) VALUES(:title)
        """
        self.database.execute(insert_blog, dict(title=blog.title))
        row = self.database.execute(self.get_last_id).fetchone()
        blog._id = str(row[0])
        blog._access = ref(self)
        return blog

In addition to opening the database, the open() method sets Connection.row_factory to use the sqlite3.Row class instead of a simple tuple. The Row class allows access via the numeric index, as well as the column name.

The get_blog() method constructs a Blog object from the database row that is fetched. Because we're using the sqlite3.Row object, we can refer to columns by name. This clarifies the mapping between SQL and the Python class. The two additional attributes, _id and _access must be set separately; they're part of the access layer, and not central to the problem domain.

The add_blog() method inserts a row into the blog table based on the value of a Blog object. This is a three-step operation. First, we create the new row. Then, we perform an SQL query to get the row key that was assigned to the row. Finally, the original blog instance is updated with the assigned database key and a reference to the Access instance.

Note that our table definitions use INTEGER PRIMARY KEY AUTOINCREMENT. Because of this, the table's primary key is the _id attribute of the row, and the assigned row key will be available through the last_insert_rowid() function. This allows us to retrieve the row key that was created by SQLite; we can then put this into the Python object for future reference. Here's how we can retrieve an individual Post object from the database:

def get_post(self, id: str) -> Post:
    query_post = """
        SELECT * FROM post WHERE id=?
    """
    row = self.database.execute(query_post, (id,)).fetchone()
    post = Post(
        title=row["TITLE"], date=row["DATE"], rst_text=row["RST_TEXT"]
    )
    post._id = row["ID"]
    # Get tag text, too
    query_tags = """
        SELECT tag.*
        FROM tag JOIN assoc_post_tag ON tag.id = assoc_post_tag.tag_id
        WHERE assoc_post_tag.post_id=?
    """
    results = self.database.execute(query_tags, (id,))
    for tag_id, phrase in results:
        post.append(phrase)
    return post

To build Post, we have two queries: first, we fetch a row from the post table to build part of the Post object. This includes injecting the database ID into the resulting instance. Then, we fetch the association rows joined with the rows from the tag table. This is used to build the tag list for the Post object.

When we save a Post object, it will also have several parts. A row must be added to the POST table. Additionally, rows need to be added to the assoc_post_tag table. If a tag is new, then a row might need to be added to the tag table. If the tag exists, then we simply associate the post with an existing tag's key. Here's the add_post() method function:

def add_post(self, blog: Blog, post: Post) -> Post:
    insert_post = """
        INSERT INTO post(title, date, rst_text, blog_id) VALUES(:title, :date, :rst_text, :blog_id)
    """
    query_tag = """
        SELECT * FROM tag WHERE phrase=?
    """
    insert_tag = """
        INSERT INTO tag(phrase) VALUES(?)
    """
    insert_association = """
        INSERT INTO assoc_post_tag(post_id, tag_id) VALUES(:post_id, :tag_id)
    """
    try:
        with closing(self.database.cursor()) as cursor:
            cursor.execute(
                insert_post,
                dict(
                    title=post.title,
                    date=post.date,
                    rst_text=post.rst_text,
                    blog_id=blog._id,
                ),
            )
            row = cursor.execute(self.get_last_id).fetchone()
            post._id = str(row[0])
            for tag in post.tags:
                tag_row = cursor.execute(query_tag, (tag,)).fetchone()
                if tag_row is not None:
                    tag_id = tag_row["ID"]
                else:
                    cursor.execute(insert_tag, (tag,))
                    row = cursor.execute(self.get_last_id).fetchone()
                    tag_id = str(row[0])
                cursor.execute(
                    insert_association, 
                    dict(tag_id=tag_id, post_id=post._id)
                )
        self.database.commit()
    except Exception as ex:
        self.database.rollback()
        raise
    return post

The process of creating a complete post in the database involves several SQL steps. We've used the insert_post statement to create the row in the post table. We'll also use the generic get_last_id query to return the assigned primary key for the new post row.

The query_tag statement is used to determine whether the tag exists in the database or not. If the result of the query is not None, it means that a tag row was found, and we have the ID for that row. Otherwise, the insert_tag statement must be used to create a row; the get_last_id query must be used to determine the assigned key.

Each post is associated with the relevant tags by inserting rows into the assoc_post_tag table. The insert_association statement creates the necessary row. Here are two iterator-style queries to locate Blogs and Posts:

def blog_iter(self) -> Iterator[Blog]:
    query = """
        SELECT * FROM blog
    """
    results = self.database.execute(query)
    for row in results:
        blog = Blog(title=row["TITLE"])
        blog._id = row["ID"]
        blog._access = ref(self)
        yield blog

def post_iter(self, blog: Blog) -> Iterator[Post]:
    query = """
        SELECT id FROM post WHERE blog_id=?
    """
    results = self.database.execute(query, (blog._id,))
    for row in results:
        yield self.get_post(row["ID"])

The blog_iter() method function locates all the BLOG rows and builds Blog instances from the rows. The post_iter() method function locates POST IDs that are associated with a BLOG ID. The POST IDs are used with the get_post() method to build the Post instances. As get_post() will perform another query against the POST table, there's an optimization possible between these two methods.

Let's see how to implement container relationships in the next section.