foundev.github.io

"Python Web Framework Series – Pylons: Part 4 Introduction For Database Support With SQL Alchemy"

We last left off with Views with Mako, now Pylons does not enforce on you an ORM at all, so you can use hand crafted SQL if you prefer. However, since I’ve done enough of that for a career or two we’re going to use my Python ORM of choice and the preferred one for Pylons SQLAlchemy.

Where does SQLAlchemy fit in as an ORM?

If you used NHibernate, you should feel pretty close to right at home with SQLAlchemy. If you come from an ActiveRecord, Entity Framework, Subsonic,  or even Linq2Sql background this will be a bit more hand management than you are used to. If that is the case, I suggest  Elixir (which I’ve heard great things about) or SQLObject (which I’ve used as well and works fine).

Mapping and Setup

There are three different ways to map tables to classes with SQLAlchemy, I will pick the most commonly used one, but will show the other two in a later post. In our PylonsForum project open model__init__.py  and change the file to look like so:

 

“””The application’s model objects”””
from sqlalchemy import orm
import sqlalchemy as sa
import datetime
from pylonsforum.model import meta</p>

def now():
    return datetime.datetime.now()

def init_model(engine):
    “””Call me before using any of the tables or classes in the model”””
    meta.Session.configure(bind=engine)
    meta.engine = engine

class Post(object):
    pass

class Thread(object):
    pass

posts_table = sa.Table(“posts”, meta.metadata, 
sa.Column(“id”, sa.types.Integer, primary_key=True),
sa.Column(“threadid”, sa.types.Integer,sa.ForeignKey(‘threads.id’)),
sa.Column(“content”, sa.types.String(4000), nullable=False),
sa.Column(“author”, sa.types.String(255), nullable=False),
sa.Column(“created”, sa.types.TIMESTAMP(), default=now()),
sa.Column(“isparent” , sa.types.Boolean, nullable=True)
)
threads_table = sa.Table(“threads”, meta.metadata,
sa.Column(“id”, sa.types.Integer, primary_key = True),
sa.Column(“subject”, sa.types.String(255)),</div>

sa.Column(“dateadded”, sa.types.TIMESTAMP(), default=now())
)
orm.mapper(Post, posts_table)
orm.mapper(Thread, threads_table,properties={‘posts’:orm.relation(Post, backref=‘thread’)})
</div>

 

Not the best table structure and you’re welcome to improve this on your own but I wanted to create a default setup that was easy to read.  Lets take a bit to recap the pieces:


def init_model(engine):
    “””Call me before using any of the tables or classes in the model”””
    meta.Session.configure(bind=engine)
    meta.engine = engine

Straightforward method here sets up a Session object with the database engine passed into the method.  Pylons will call init_model itself when the site is accessed.


class Post(object):
    pass</p>

class Thread(object):
    pass </div> </div>

So a couple of empty classes?  Python being a dynamic language can get away with this and just add the properties at runtime.  These are the objects we’ll be interacting with when we want to store data.


posts_table = sa.Table(“posts”, meta.metadata, 
sa.Column(“id”, sa.types.Integer, primary_key=True),
sa.Column(“threadid”, sa.types.Integer,sa.ForeignKey(‘threads.id’)),
sa.Column(“content”, sa.types.String(4000), nullable=False),
sa.Column(“author”, sa.types.String(255), nullable=False),
sa.Column(“dateadded”, sa.types.TIMESTAMP(), default=now()),
sa.Column(“isparent” , sa.types.Boolean, nullable=True)
)
threads_table = sa.Table(“threads”, meta.metadata,
sa.Column(“id”, sa.types.Integer, primary_key = True),
sa.Column(“subject”, sa.types.String(255))
)

Ok these table declarations are providing the data definition logic, including some basic relationship, nothing too interesting here post in comments if you have specific questions.


orm.mapper(Post, posts_table)
orm.mapper(Thread, threads_table,properties={‘posts’:orm.relation(Post, backref=‘thread’)})

Here the orm.mapper calls take the Page and Thread classes and map them with the table data definitions typed in earlier.  You can also specify relationships here as we have done in the thread mapping, the properties argument is referencing the Post class and mapping it to a property called posts on the Thread class, while also mapping the other direction and putting thread on the Post class.


Finally run paster setup-app development.ini from the root pylonsforum directory and you should see a bunch of SQL flying by which indicates it has build the database schema for us:

image

Making Our New Thread Store In The Database

In the interest of space and time I’ll skip the testing story for another post. 

Open up controllershome.py .

  • Remove the Post class we created several posts ago
  • under the imports add import pylonsforum.model as model
  • under the imports add import pylonsforum.model.meta as meta
  • change the submitnewthread method to the following
      thread = model.Thread() 
      thread.subject = request.POST[‘subject’]
      post = model.Post()
      post.author =users.get_current_user(self)
      post.isparent = True
      post.content = request.POST[‘content’]
      thread.posts.append(post) #adding post to the thread object
      meta.Session.add(thread) #look only have to add the thread object
      meta.Session.flush() #optional when AutoCommit is on, but useful for control in data integrity cases
      meta.Session.commit() #makes changes real
      thread_query = meta.Session.query(model.Thread) #query back submitted data to display to ui
      thread = thread_query.filter_by(id=thread.id).first() # yes actually querying using the thread id of our created object above
              c.username = thread.posts[].author
      c.subject = thread.subject
      c.content = thread.posts[].content
              return render(‘submitnewthread.mako’)

Finally run the newthread action

  http://localhost:5000/home/newthread

then create a thread

http://localhost:5000/home/submitnewthread

Should be no change in the actual outward appearance of from what we were doing before.

Summary and Recap

This was a very quick and basic introduction to SQLAlchemy and I will do more with it over the next couple of posts, but please add any comments to things I did not make clear. SQLAlchemy and ORM’s in general are a very large subjects and those of us that have used them for a long time tend to forget not all of this was so obvious when we started.