Saturday, July 10, 2010

Using sqlalchemy reflection with pylons

Okay I have been working on a project using pylons. I already have a mysql database that I wanted to use with my project. I didn't want to create the model classes in pylons under the myapp/model folder. The solution for me was to reflect database tables using sqlalchemy. Reflection is the process through which sqlalchemy reads table information from databases.

Reflection in sqlalchemy is simple can can be done with code like:

from sqlalchemy import *
engine = create_engine('mysql://username:password@localhost:3600/mydb')
meta = MetaData()
meta.bind = engine

for tname in meta.tables:
print "Table: %s" % tname
T = meta.tables[tname]
for c in T.columns:
print " %s" % c

The problem with pylons is that sqlalchemy is used within the project structure and we need to make changes to a few files in order to get reflection working.

The database I am working with has 4 tables users, dbs, db_courses, summary_stats. I am going to describe the procedure for these tables (of course, you can modify the code to work with your tables).

First step is to add code to model/ file

import sqlalchemy as sa

class users(object):

class dbs(object):

class db_courses(object):

class summary_stats(object):

t_dbs = None
t_users = None
t_db_courses = None
t_summary_stats = None

def init_model(engine):
"""Call me before using any of the tables or classes in the model"""
global t_dbs, t_users, t_db_courses, t_summary_stats

t_dbs = sa.Table("dbs", Base.metadata, autoload=True, autoload_with=engine)
t_users = sa.Table("users", Base.metadata, autoload=True, autoload_with=engine)
t_db_courses = sa.Table("db_courses", Base.metadata, autoload=True, autoload_with=engine)
t_summary_stats = sa.Table("summary_stats", Base.metadata, autoload=True, autoload_with=engine)

sa.orm.mapper(dbs, t_dbs)
sa.orm.mapper(users, t_users)
sa.orm.mapper(db_courses, t_db_courses)
sa.orm.mapper(summary_stats, t_summary_stats)


This automatically fetches table information from the DB and allows for using the table classes dbs, users, db_courses and summary_tables just like if we had created the table structure in those classes (without reflection)

Note that in controllers the sqlalchemy MetaData object is accessible through appname.model.meta.Base.metadata

Fetching records from any table is now very simple. For example to fetch all users following code will do:

from myapp.model import *
users = meta.Session.query(model.users)


Dario said...

Thank you!
Your post is really helpful.
BTW I'm wondering if reflection is the right way to go.
I have 20 tables with many foreign keys and I don't know if it's better to relay on reflection or remain at a lower level describing each table.
The pros are that I can design my db in mysql workbench instead inside the app,
the cons that I'm afraid to get stuck at some point with something that it's not mapped correctly.
I'm at an early stage of development, so for now it's not an urgent decision.

hahahaha said...

hey brother

hahahaha said...

pakhh pallllayyyy...