When using an ORM, we will fundamentally change the way we design and implement our persistent classes. We're going to expand the semantics of our class definitions to have the following three distinct levels of meaning:

• The class will be used to create Python objects. The method functions are used by these objects.
• The class will also describe an SQL table and can be used by the ORM to create the SQL DDL that builds and maintains the database structure. The attributes will be mapped to SQL columns.
• The class will also define the mappings between the SQL table and Python class. It will be the vehicle to turn Python operations into SQL DML and build Python objects from SQL query results.

Most ORMs are designed to use descriptors to formally define the attributes of our class. We do not simply define attributes in the __init__() method. For more information on descriptors, see Chapter 4, Attribute Access, Properties, and Descriptors.

SQLAlchemy requires us to build a declarative base class. This base class provides a metaclass for our application's class definitions. It also serves as a repository for the metadata that we're defining for our database. If we follow the defaults, it's easy to call this class Base.

Here's the list of imports that might be helpful:

from sqlalchemy.ext.declarative import declarative_base 
from sqlalchemy import Column, Table
from sqlalchemy import (
    BigInteger,
    Boolean,
    Date,
    DateTime,
    Enum,
    Float,
    Integer,
    Interval,
    LargeBinary,
    Numeric,
    PickleType,
    SmallInteger,
    String,
    Text,
    Time,
    Unicode,
    UnicodeText,
    ForeignKey,
)
from sqlalchemy.orm import relationship, backref

We imported the essential definitions to create a column of a table, column, and to create the rare table that doesn't specifically map to a Python class, Table. We imported all of the generic column type definitions. We'll only use a few of these column types. Not only does SQLAlchemy define these generic types; it defines the SQL standard types, and it also defines vendor-specific types for the various supported SQL dialects. It is best to stick to the generic types and allow SQLAlchemy to map between generic, standard, and vendor types.

We also imported two helpers to define the relationships among tables, relationship, and backref. SQLAlchemy's metaclass is built by the declarative_base() function as follows:

Base = declarative_base() 

The Base object must be the superclass for any persistent class that we're going to define. We'll define three tables that are mapped to Python classes. We'll also define a fourth table that's simply required by SQL to implement a many-to-many relationship.

Here's the Blog class:

class Blog(Base):
    __tablename__ = "BLOG"
    id = Column(Integer, primary_key=True)
    title = Column(String)

    def as_dict(self):
        return dict(
            title=self.title,
            underline="=" * len(self.title),
            entries=[e.as_dict() for e in self.entries],
        )

Our Blog class is mapped to a table named "BLOG". We've included two descriptors for the two columns we want in this table. The id column is defined as an Integer primary key. Implicitly, this will be an autoincrement field so that surrogate keys are generated for us.

The title column is defined as a generic string. We could have used Text, Unicode, or even UnicodeText for this. The underlying engine might have different implementations for these various types. In our case, SQLite will treat all of these nearly identically. Also note that SQLite doesn't need an upper limit on the length of a column; other database engines might require an upper limit on the size of String.

The as_dict() method function refers to an entries collection that is clearly not defined in this class. When we look at the definition of the Post class, we'll see how this entries attribute is built. Here's the definition of the Post class:

class Post(Base):
    __tablename__ = "POST"
    id = Column(Integer, primary_key=True)
    title = Column(String)
    date = Column(DateTime)
    rst_text = Column(UnicodeText)
    blog_id = Column(Integer, ForeignKey("BLOG.id"))
    blog = relationship("Blog", backref="entries")
    tags = relationship("Tag", secondary=assoc_post_tag, backref="posts")

    def as_dict(self):
        return dict(
            title=self.title,
            underline="-" * len(self.title),
            date=self.date,
            rst_text=self.rst_text,
            tags=[t.phrase for t in self.tags],
        )

This class has five attributes, two relationships, and a method function. The id attribute is an integer primary key; this will have an autoincremented value by default. The title attribute is a simple string.

The date attribute will be a DateTime column; rst_text is defined as UnicodeText to emphasize our expectation of any Unicode character in this field.

The blog_id is a foreign key reference to the parent blog that contains this post. In addition to the foreign key column definition, we also included an explicit relationship definition between the post and the parent blog. This relationship definition becomes an attribute that we can use for navigation from the post to the parent blog.

The backref option includes a backward reference that will be added to the Blog class. This reference in the Blog class will be the collection of Posts that are contained within the Blog. The backref option names the new attribute in the Blog class to reference the child Posts.

The tags attribute uses a relationship definition; this attribute will navigate via an association table to locate all the Tag instances associated with the post. We'll look at the following association table. This, too, uses backref to include an attribute in the Tag class that references the related collection of the Post instances.

The as_dict() method makes use of the tags attribute to locate all of Tags associated with this Post. Here's a definition for the Tag class:

class Tag(Base):
    __tablename__ = "TAG"
    id = Column(Integer, primary_key=True)
    phrase = Column(String, unique=True)

We defined a primary key and a String attribute. We included a constraint to ensure that each tag is explicitly unique. An attempt to insert a duplicate will lead to a database exception. The relationship in the Post class definition means that additional attributes will be created in this class.

As required by SQL, we need an association table for the many-to-many relationship between tags and posts. This table is purely a technical requirement in SQL and need not be mapped to a Python class:

assoc_post_tag = Table(
    "ASSOC_POST_TAG",
    Base.metadata,
    Column("POST_ID", Integer, ForeignKey("POST.id")),
    Column("TAG_ID", Integer, ForeignKey("TAG.id")),
)

We have to explicitly bind this to the Base.metadata collection. This binding is automatically a part of the classes that use Base as the metaclass. We defined a table that contains two Column instances. Each column is a foreign key to one of the other tables in our model.

Let's see how to build the schema with the ORM layer.