Home > programming > A reflective approach on sqlalchemy usage

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):
....@property
....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)
....metadata.reflect()
....mappers = {}
....for table_name in metadata.tables:
........model_name = "".join(part.capitalize()\
.............................for part in table_name.split("_"))
........try:
............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):
....pass
class Movie(BaseModel):
....pass
class Genre(BaseModel):
....pass
class MovieGenre(BaseModel):
....pass

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

def map(connection_string):
....models=modules['models']
....mappers, tables, Session = reflect(connection_string, models)
....mappers["director"].add_properties({
........"movies": relationship(models.Movie,
...............................backref="director",
...............................cascade="all, delete-orphan")
....})
....mappers["movie"].add_properties({
........"genres": relationship(models.Genre,
...............................backref="movies",
...............................secondary=tables['movie_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.

About these ads
Categories: programming Tags: , , ,
  1. Andrew
    7 August 2013 at 00:08 | #1

    Using this technique, what happened to the ability to initialize objects with keyword arguments?

    d = Director( firstname=’fred’ )
    TypeError: __init__() got an unexpected keyword argument ‘firstname’

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: