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
meta.reflect()

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/__init__.py file

import sqlalchemy as sa

class users(object):
    pass

class dbs(object):
    pass

class db_courses(object):
    pass

class summary_stats(object):
    pass

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)
    
    Session.configure(bind=engine)
    Base.metadata.reflect(bind=engine)

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)