In order to work with objects, we'll need a session cache. This is bound to an engine. We'll add new objects to the session cache. We'll also use the session cache to query objects in the database. This assures us that all objects that need to be persistent are in the cache. Here is a way to create a working session:

from sqlalchemy.orm import sessionmaker 
Session = sessionmaker(bind=engine) 
session = Session() 

We used the SQLAlchemy sessionmaker() function to create a Session class. This is bound to the database engine that we created previously. We then used the Session class to build a session object that we can use to perform data manipulation. A session is required to work with the objects in general.

Generally, we build one sessionmaker class along with the engine. We can then use that one sessionmaker class to build multiple sessions for our application processing.

For simple objects, we create them and load them into the session, as in the following code:

blog = Blog(title="Travel 2013") 
session.add(blog) 

This puts a new Blog object into the session named session. The Blog object is not necessarily written to the database. We need to commit the session before the database writes are performed. In order to meet the atomicity requirements, we'll finish building a post before committing the session.

First, we'll look up the Tag instances in the database. If they don't exist, we'll create them. If they do exist, we'll use the tag found in the database as follows:

tags = [] 
for phrase in "#RedRanger", "#Whitby42", "#ICW": 
    try: 
        tag = session.query(Tag).filter(Tag.phrase == phrase).one() 
    except sqlalchemy.orm.exc.NoResultFound: 
        tag = Tag(phrase=phrase) 
        session.add(tag) 
    tags.append(tag) 

We use the session.query() function to examine instances of the given class. Each filter() function appends a criterion to the query. The one() function ensures that we've found a single row. If an exception is raised, then it means that Tag doesn't exist. We need to build a new Tag and add it to the session.

Once we've found or created the Tag instance, we can append it to a local list named tags; we'll use this list of Tag instances to create the Post object. Here's how we build a Post:

p2 = Post(date=datetime.datetime(2013,11,14,17,25), 
    title="Hard Aground", 
    rst_text="""Some embarrassing revelation. Including ☹ and ⚓""", 
    blog=blog, 
    tags=tags 
    ) 
session.add(p2) 
blog.posts = [p2] 

This includes a reference to the parent blog. It also includes the list of Tag instances that we built (or found in the database).

The Post.blog attribute was defined as a relationship in the class definitions. When we assign an object, SQLAlchemy plucks out the proper ID values to create the foreign key reference that the SQL database uses to implement the relationship.

The Post.tags attribute was also defined as a relationship. The Tag objects are referenced via the association table. SQLAlchemy tracks the ID values properly to build the necessary rows in the SQL association table for us.

In order to associate the Post with the Blog, we'll make use of the Blog.posts attribute. This, too, was defined as a relationship. When we assign a list of Post objects to this relationship attribute, the ORM will build the proper foreign key reference in each Post object. This works because we provided the backref attribute when defining the relationship. Finally, we commit the session as follows:

session.commit() 

The database inserts are all handled in a flurry of automatically generated SQL. The objects remained cached in the session. If our application continues using this session instance, then the pool of objects remains available without necessarily performing any actual queries against the database.

If, on the other hand, we would like to be absolutely sure that any updates written by other concurrent processes are included in a query, we can create a new, empty session for that query. When we discard a session and use an empty session, objects must be fetched from the database to refresh the session.

We can write a simple query as follows to examine and print all of the Blog objects:

session = Session() 
for blog in session.query(Blog): 
    print("{title}
{underline}
".format(**blog.as_dict())) 
    for p in blog.entries: 
        print(p.as_dict()) 

This will retrieve all the Blog instances. The Blog.as_dict() method will retrieve all of the posts within a blog. The Post.as_dict() will retrieve all of the tags. The SQL queries will be generated and executed automatically by SQLAlchemy.

We didn't include the rest of the template-based formatting from Chapter 10, Serializing and Saving - JSON, YAML, Pickle, CSV, and XML. It doesn't change. We are able to navigate from the Blog object via the entries list to the Post objects without writing elaborate SQL queries. Translating navigation into queries is the job of SQLAlchemy. Using a Python iterator is sufficient for SQLAlchemy to generate the right queries to refresh the cache and return the expected objects.

If we have echo=True defined for the Engine instance, then we'll be able to see the sequence of SQL queries performed to retrieve the Blog, Post, and Tag instances. This information can help us understand the workload that our application places on the database server process.

Let's see how to query posts that are given a tag.