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:
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>
)
orm.mapper(Post, posts_table)
orm.mapper(Thread, threads_table,properties={‘posts’:orm.relation(Post, backref=‘thread’)})
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:
“””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.
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.
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(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:
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.