Reflecting in Pyramid/SqlAlchemy

app-models-__init__.py

import logging
log = logging.getLogger(__name__)


from sqlalchemy import Table 
from sqlalchemy import MetaData 
from sqlalchemy.orm import mapper 
from sqlalchemy.orm import scoped_session 
from sqlalchemy.orm import sessionmaker 
from zope.sqlalchemy import ZopeTransactionExtension 

import app
import types


DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) 
DBMetadata = MetaData() 


class ReflectedTable(object): 
    """Base class for database objects that are mapped to tables by reflection.
       Have your various model classes inherit from this class.  If class.__tablename__ is defined, it will reflect

       Example:
		  class Useraccount(ReflectedTable):
			  __tablename__ = "useraccount"
    """ 
    __tablename__ = None


def map_tables( app_model ):
    """
    """
    to_reflect = []
    for content in dir( app_model ):
        module = getattr( app_model , content )
        if not isinstance( module , types.ModuleType ):
            continue
        for module_element in dir( module ):
            module_element = getattr( module, module_element )
            if not isinstance( module_element , types.TypeType ):
                continue
            if issubclass( module_element , ReflectedTable ):
                to_reflect.append( module_element )
    for _class in to_reflect: 
        table_name = _class.__tablename__
        if table_name:
            log.info("Reflecting : %s (table: %s)" % (_class , table_name) )
            table= Table( table_name, DBMetadata, autoload=True ) 
            mapper( _class , table) 


def initialize_sql(engine):
    """Call this once per engine from app.__init__.main 

		engine = sqlalchemy.engine_from_config(settings, prefix="sqlalchemy.")
		sqlahelper.add_engine(engine)
		models.initialize_sql(engine)
    
    """
    log.debug( "\ninitialize_sql" )
    DBSession.configure(bind=engine) 
    DBMetadata.bind = engine 
    map_tables( app.models )


## import various classes here.  
import models_1
import models_2
import models_3

I like your idea of having __tablename__ as a class attribute of ReflectedTable. You've also made me realize that there's no good reason for the map_by_reflection class method. But your introspection code in map_tables to tease the ReflectedTable classes out of app_model doesn't thrill me.

In my present app all of the model classes are reflected tables, so I can't see the value in putting them in another module. I may think differently if I do something where I have a mixture of reflected table models and app-specific ones. So, for now I've opted to keep everything in models.py, refactored to:

import logging

from sqlalchemy import Table
from sqlalchemy import MetaData
from sqlalchemy.orm import mapper
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker
from zope.sqlalchemy import ZopeTransactionExtension


log = logging.getLogger(__name__)


DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
DBMetadata = MetaData()


class ReflectedTable(object):
    """Base class for database objects that are mapped to tables by reflection.
    """
    __tablename__ = None


class SomeTable(ReflectedTable):
    __tablename__ = 'sometable'


class AnotherTable(ReflectedTable):
    __tablename__ = 'anothertable'

def map_tables(to_reflect):
    for _class in to_reflect:
        log.info('Reflecting {0} from table {1}'
                 .format(_class, _class.__tablename__))
        table = Table(_class.__tablename__, DBMetadata, autoload=True)
        mapper(_class, table)


def initialize_sql(engine):
    DBSession.configure(bind=engine)
    DBMetadata.bind = engine
    to_reflect = (
        SomeTable, AnotherTable,
    )
    map_tables(to_reflect)

yeah, the introspection is a bit ugly.

the reason why i decided to do it that way is that i've been refactoring a bunch of legacy code that i used as a 'bootstrap' module under pylons into pyramid - and splitting out the database portion of it

for the database code, i want to:
a_ reflect tables
b_ setup and manage multiple db handles ( read, write, log, etc )

right now, it looks more like this:
https://gist.github.com/1686834

ideally when I'm done , i'd do something like this:

1. deploy the final version to github or pypi & have my apps require it
2. drop a line into app/init.py to push some setup stuff into app/models/init.py
3. models/init.py just sets up the handles , and calls a master init in my package (which then does introspection)

i have at least 6 pylons apps that i'm migrating to pyramid, and am in the process of building 3 apps right now -- the more I can take stuff like this and abstract/simplify/standardize, the happier i am. the introspection is ugly, but its ultimately easier to migrate & easier to maintain.

For future readers.. The DeferredReflection mixin is now built in to sqlaclehemy and should remove the need for special handling.

See docs here: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html#using-reflection-with-declarative

and here: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html#sqlalchemy.ext.declarative.DeferredReflection