This is multi-part series on how to getting running with Pylons, a MVC framework for Python. In Part 1 I discussed how to set up your project, modify the configuration environment and base files. Here I will show you how to set up your model to use SQLAlchemy 0.4.4 and tie it together nicely with Pylons 0.9.6. Some of the code was used straight from the Pylons Official Docs, while other parts were modified to get all my middleware functioning properly. If something is confusing or I didn’t do a good job of explaining, please see that reference. If you are still confused post a comment, and I’ll try to help you out. Thanks!

Building the model

There are 4 main parts to setting up the model.

  1. initializing the model
  2. creating the SQLAlchemy object and mapper
  3. mapping the relations themselves
  4. seeding the database

Initializing the model

/model/__init__.py

import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy import engine_from_config
 
from MYFIRSTAPP.model import meta             # import SQLAlchemy Metadata and Session object
from MYFIRSTAPP.model.tables import *         # import database tables/objects
 
def init_model(engine):
    """
    Setup the model.
    This gets called by config/environment/load_environment.py and lib/app_globals.py
    """
 
    print "Initializing model..."
    sm = orm.sessionmaker(autoflush=True, transactional=True, bind=engine)
 
    meta.engine = engine
    meta.Session = orm.scoped_session(sm)
 
def create_all(app_conf):
    """
    This is called from websetup to create everything.
    """
 
    # During development and test only
    # Import the correct seed file.  If config var isn't defined then we use the default 'database_seedset_default.py'
    seed_set = app_conf.get('database.seed_set', '')
    if seed_set is not '':
        seed_set = 'database_seedset_' + seed_set
    else:
        seed_set = 'database_seedset_default'
 
    print "\nBuilding database from seed set."
    exec('from '+seed_set+' import load_test_data')
    print "Using seed from config <%s>" % seed_set
 
    # For some reason we need to bind the engine when setting up the app
    meta.metadata.bind = meta.engine
 
    print "\nDropping all tables to start fresh..."
    meta.metadata.drop_all()
    print "Recreating all tables..."
    meta.metadata.create_all()
 
    # this method is called from the import we just brought in using exec()
    print "\nLoading seed data..."
    load_test_data()
    print "\nSeed data from <%s> was used for population." % seed_set

/config/environment/load_environment.py is like the entry point when you serve up your app. It does the work of initializing the middleware and will also start up your model. It calls init_model to do the work. __init__ will load your table schema and relations (tables.py) and establish a connection to the database.

create_all is only used by websetup.py to (re)build the database with optional seed data. In Part 1 of this tutorial I showed you what my development.ini file looks like. This is where I am defining what seed set to use. All you have to do is modify this file and change the seed set. Here are a couple examples…

For development

# development.ini
database.seed_set = default
 
# actual seed file: /model/database_seedset_default.py
...

For test

# test.ini
database.seed_set = test
 
# actual seed file: /model/database_seedset_test.py
...

For production

# production.ini
database.seed_set = prod-1.0
 
# actual seed file: /model/database_seedset_prod-1.0.py
...

Creating the SQLAlchemy object and mapper

/model/meta.py

"""SQLAlchemy Metadata and Session object"""
from sqlalchemy import MetaData
 
__all__ = ['engine', 'metadata', 'Session']
 
# SQLAlchemy database engine.  Updated by model.init_model().
#
# A SQLAlchemy engine is a pool of connections to a particular database.
engine = None
 
# SQLAlchemy session manager.  Updated by model.init_model().
#
# The Session is used with the object-relational mapper.
Session = None
 
# Global metadata. If you have multiple databases with overlapping table
# names, you'll need a metadata for each database.
#
# The metadata is an object that will contain your table definitions.
metadata = MetaData()

This is basic SQLAlchemy stuff. I got it straight from the Using SQLAlchemy with Pylons tutorial.

Mapping the relations

/model/tables.py

import sqlalchemy as sa
from sqlalchemy import orm
 
# import SQLAlchemy Metadata and Session object
from MYFIRSTAPP.model import meta
 
#
# Define database tables
#
user_table = sa.Table('User', meta.metadata,
sa.Column('user_id', sa.types.Integer, primary_key=True),
sa.Column('first', sa.types.String(50), nullable=False),
sa.Column('nickname', sa.types.String(50), nullable=False, unique=True),
sa.Column('password', sa.types.String(50), nullable=False),
mysql_engine='MyISAM')
 
group_table = sa.Table('Group', meta.metadata,
sa.Column('group_id', sa.types.Integer, primary_key=True),
sa.Column('user_id', sa.types.Integer, sa.ForeignKey('User.user_id')), mysql_engine='MyISAM')
 
sa.Index('idx_group', group_table.c.group_id, group_table.c.user_id, unique=True)
 
#
# Define objects for the model
#
class Group(object):
    def __init__(self):
        self.group_id = None
    def __str__(self):
        return 'Group #' + self.group_id
 
class User(object):
    def __init__(self, first, nickname, encrypted_password):
        self.first = first
        self.nickname = nickname
        self.password = encrypted_password
    def __str__(self):
        return self.first
 
#
# Map the objects to the tables
#
orm.mapper(Group, group_table)
orm.mapper(User, user_table,
properties = {
        'group' : orm.relation(Group, secondary=group_table, lazy=False)
    }
)

I’m defining 2 tables and their schemas. Using a model is a great way to abstract your database and write less (or no) SQL. Personally I have always written my own SQL queries, so I can tune and tweak them if needed, so this abstraction layer was actually more frustrating for me, but it will prove useful is the long term (and once you get the hang of it). With this extra layer I can change my database at will without requiring any code changes. That is awesome!

You should see the SQLAlchemy manual: Define and create a Table for help on the syntax for defining more complex tables or SQLAlchemy manual: Querying.

Seeding the database

/model/database_seedset_default.py

from sqlalchemy import *
from MYFIRSTAPP.model import meta
from tables import *
 
def load_test_data():
    # Obtain a new database session
    conn = meta.engine.connect()
    meta.Session.configure(bind=conn)
 
    #
    # seed data
    #
 
    user1 = User("User1", "nickname1", func.MD5("password1"))
    meta.Session.save(user1)
 
    user2 = User("User2", "nickname2", func.MD5("password2"))
    meta.Session.save(user2)
 
    user3 = User("User3", "nickname3", func.MD5("password3"))
    meta.Session.save(user3)
 
    #
    # Commit data then close connection
    #
    meta.Session.commit()
    print "Done loading seed data."
 
    meta.Session.remove()
    conn.close()
 
    print "Closed database connection."

This is the seed file that gets called by /model/__init__.py in create_all. You’ll need to refer to the SQLAlchemy manual for syntax. This syntax is no different than what you would use to save new instances. Actually, that is exactly what I am doing, but I’m using predefined data instead of dynamic data sent from a form.

Read on…

Continue to Part 3 and find out how to organize the presentation view and work with Mako templates

Feeling anxious? Download the bare bones project now.