Posts Tagged ‘sqlalchemy’

On sqlalchemy – what do you put in your base model?

My previous post regarded the reflective use of sqlalchemy, and there I showed how to set up a codebase for BaseModel and schema mapper. I put only one method in my BaseModel, and that was actually the property session, which returns the session that holds the instance (None if the instance is not added).

Now, before I continue, just a short description of this test database. The director table holds the movie directors, lastname is required and unique. The movie table holds the movies, movie field is required and unique, director_id is the link to the director (can be null, for unknown/not-added director), and year – also optional. The genre table holds just the genre names, and the movie_genre is only association table. Before you continue, you should check the mappers we added in the previous post.

There is an easy way to view all the information packed in a list with a coupled query, but we’ll do it the sqlalchemy’s way, that is, we’ll query it with its orm.

from sqlalchemy.orm import eagerload_all
...............options(eagerload_all(Movie.director, Movie.genres))\

Notice that we added an option called eagerload_all that specifies which mapped properties of the queried objects should be loaded on the fly into the sessions – the so-called eagerloading. Use this if and only if you are certain that you will need this properties. We added limit of 10 only for clarity. If I print these objects like this:

for m in movies:
....print "\"%s\" (%s) by %s %s (%s)" \
..........% (, m.year, m.director.firstname, m.director.lastname,
..........", ".join([g.genre for g in m.genres]))

I get my sample database:

"A Clockwork Orange" (1971) by Stanley Kubrick (Sci-fi)
"Dr. Strangelove" (None) by Stanley Kubrick (Comedy)
"2001: A Space Oddisey" (1969) by Stanley Kubrick (Post-modern, Sci-fi)
"Lolita" (None) by Stanley Kubrick ()
"Shadow of a Doubt" (None) by Alfred Hitchcock (Noir, Mystery)
"Vertigo" (1957) by Alfred Hitchcock (Mystery)
"Citizen Kane" (1941) by Orson Welles ()
"Europa" (1991) by Lars von Trier ()
"Annie Hall" (None) by Woody Allen (Comedy)
"Element of Crime" (1984) by Lars von Trier ()

Now that we have a test data, I’ll continue with my BaseModel.

There is always the question of what kind of foreign key we should use. We can agree that incremental unsigned integers for every table is a good way of efficiently linking the tables, removing the complexity of using composite foreign keys or keys with slow look-up, but sometimes people tend to put these id-s into the application. Surely, the director with id 4 has bears no information, but when you say director ‘Hitchcock’ you get a meaningful data in your application. So, my approach is – keep the id-s in your database and out of the application. Use ‘natural’ key to refer to records from the application.

If I proceed in this manner, it would be a good thing to have a method load() associated to each model that can return a unique record/object (if exists) given a set of arguments. Retrieving unique records with sqlalchemy is very easy, sqlalchemy’s quering mechanism has a method one() that in the background makes a LIMIT 2: if there are two records found – throws MultipleResultsFound; if there are no results – throws NoResultsFound; and if it finds one – it returns it. Here’s how:

d = session.query(Director)\
print d.firstname

One genuine feature of the python language are the class methods, which implicitely take the class as the first parameter. So, instead of doing this:

class Director(BaseModel):
....def load(session, lastname):
........return session.query(Director)\

which we ought to do for every model, we can put something more generic in the BaseModel:

....def load(cls, session, **kwargs):
........q = session.query(cls)
........filters = [getattr(cls, field_name)==kwargs[field_name] \
...................for field_name in kwargs]
........return q.filter(and_(*filters)).one()

With this – we get static load method for all of the models! Sorry, but I don’t think you can do a similar thing in some other language – please note me if one can. The tricky part in this method is the list comprehension, but it is quite obvious, we just ‘compile’ a list of filters for every named argument. For example, for the Movie model, the call:

Movie.load(session, movie='Europa', year=1991)

translates the list comprehension into:

['Europa', Movie.year==1991]

Later, we pass the list as an argument-list to and_() (from sqlalchemy.sql.expression) which compiles filter clause of these two filters. The cls argument is actually the Movie class/model, which is implicitly passed when the method is called.

In fact, a very basic method is a class method. Actually, this is also one of the python’s unique features: the __new__ method. This one returns the object if it is not overridden, and if it is, it may not return the object, it may return some specific object, or the default/desired object. If it returns some object, the __init__ method will be called upon the newly created object. This method is actually quite good at constructing the singleton pattern. No one will try to put a BaseModel instance into a session, but anyway, we’ll forbid it:

def __new__(cls):
....if cls==BaseModel:
........raise TypeError, "You can not instantiate a BaseModel"
....return super(BaseModel, cls).__new__(cls)

So, we raise a TypeError if we try to create a BaseModel instance, and if it is an instance from a child class, we simply return such instance.

For deleting a single object (using the session), sqlalchemy provides:


We would want to provide class access for deletion, simply:

def drop(self):

where one can see the usefulness of having a session property from the instance. To use:

d = Director.load(session, lastname='Hitchcock')

My next item in the BaseModel is a serializer. I’ll make a json serialize method. I simply will serialize the dictionary of the object without the
_sa_instance_state attribute which can not be encoded. Simply:

def serialize(self):
....tmp = self.__dict__.copy()
....del tmp['_sa_instance_state']
....return simplejson.dumps(tmp)

Finally, I needed a specific method for one model in a project, and it found its way to the BaseModel. Namely, I want to what is the position of a record in a table when I sort by a particular column, and it is actually the count of records which satisfy the condition that their values are smaller than the value of the particular record with regard of the sorting column. I can put this in my BaseModel:

def position(self, order):
....cls = self.__class__
....return self.session.query(cls)\
................filter(getattr(cls, order)<getattr(self, order))\

The __class__ property of the instance simply returns the particular model.

The next post on sqlalchemy will probably have some methods and properties in the specific models – all done with sqlalchemy’s orm querying – just some stuff I had to leard with trial and error. But I think my next post will deal with pyparsing again, and I will parse in a different way than that time.

A reflective approach on sqlalchemy usage

Sometimes we are forced to work with legacy databases and we don’t have the beautiful green field to create our schema declaratevely. Sqlalchemy provides a very thorough way of reflecting the schema over our bussines logic and mapping the tables and relations with our models.

Say we have a simple database for videostore with according to the following entity relationship diagram.
Videostore ER diagram
(created with dbvis)

I’ll split the code in 2 modules: base and models. The base module will contain the reusable code that we can use with any db schema later on, while the models module will contain the specifics for this schema. We’ll have a simple mysql database with unicode charset.

The base module contains one function and one class. Let’s import some stuff:

from sqlalchemy import MetaData, create_engine
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy.orm.session import object_session

We will put our BaseModel class here, and extend the other models from it.

class BaseModel(object):
....def session(self):
........return object_session(self)

I won’t argue about the usefullness of having a base model. I’ll show what we can put here in the post yet to come. Sqlalchemy gives us the ability to fetch the session that holds an instance of some model by using object_session. Of course, if the object is not added in a session, this will return None. The next is the reflect function:

def reflect(connection_string, models):
....metadata = MetaData()
....metadata.bind = create_engine(connection_string)
....mappers = {}
....for table_name in metadata.tables:
........model_name = "".join(part.capitalize()\
.............................for part in table_name.split("_"))
............model = getattr(models, model_name)
........except AttributeError:
............raise NameError, "Model %s not found in module %s" \
..............................%(model_name, repr(models))
........mappers[table_name] = mapper(model, metadata.tables[table_name])
....Session = sessionmaker(metadata.bind, autocommit=False)
....return (mappers, metadata.tables, Session)

In the reflect function, after metadata.reflect() is called, the metadata contains the reflected tables from our schema. We proceed to create mappers over our models. To do this in a more automated manner, let us make a convention that our model names will be simply the names of the tables titlecased, e.g., director table will map to the model Director and movie_genre to MovieGenre. We need to have these models declared in a module that gets passed as a second parameter to the reflect function.

After the iteration over metadata.tables, our models will be mapped to our tables. One thing that also make use of the metadata is the Session class which we generate with the sqlalchemy’s sessionmaker. The autocommit is a matter of personal preference, I personally want to have more control over wheather instances in the session are commited or expunged in my applications. We complete our reflect module by returning the mappers, tables and the session class.

Now we move to models, the code specific to our schema. We will need these:

from sys import modules
from sqlalchemy.orm import relationship
from base import BaseModel, reflect

One should notice the 0.5->0.6 sqlalchemy transition of relation to relationship here. We won’t put any code in the models (maybe in the next post):

class Director(BaseModel):
class Movie(BaseModel):
class Genre(BaseModel):
class MovieGenre(BaseModel):

And, we define the mappers in a function that is some kind of functional extension to the reflect function:

def map(connection_string):
....mappers, tables, Session = reflect(connection_string, models)
........"movies": relationship(models.Movie,
...............................cascade="all, delete-orphan")
........"genres": relationship(models.Genre,
....return (mappers, tables, Session)

Notice that we got the ‘models’ module (needed for the second argument in the reflect function) from the modules[] – it is this module itself. One can argue about how to implement/extend the relationships, but the point is that we want an easy access for getting a session, here being simply:

session = map(conn_str)[2]()

Finnaly, I want to stress that this is not meant to be code that you simply put in production environment, but to outline a way to get the most from already build database schema using sqlalchemy. After all, we live and work in a world where you can not rely on your current database setup and have to make frequent changes to the schema. Declarative way of doing things in unaffordable.

Categories: programming Tags: , , ,

For starters…

Sometimes a simple google search simply can not give you the answers you are looking for. You might have to look up a book reference, browse through some blogs (the forums are not that useful no more), and, at times, even the official documentation is not enough. I recently read a very thoughtful post which underlines (among the multitude of things) the importance of the negative feedback. And, a few years ago, I learned from a very good physics lecturer that the best way to learn about a topic is to write a book on it.

Well, I’m not going to write a book, but I can try to write a blog. Its intention is not to teach someone on the topics on programming, (mostly on python and linguistic processing), but to learn along the path of writing it and to learn from the negative feedback.

I recently started working on linguistic processing. I use pyparsing and nltk for it.

I like using werkzeug and jinja2 for a web interface. I use sqlalchemy to talk to a database. Goodbye sql.

A friend of mine who has quite an experience in this field recommended django. I was hesitating but decided to go the the loosely coupled option since I like jinja2 better than django templates and I like sqlalchemy more than the django orm. The convenience of the other django stuff is mostly unnecessary.

That’s it for now, stay tuned if any of the attached tags interests you.