Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Reflecting in Pyramid/SqlAlchemy
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
@douglatornell

This comment has been minimized.

Copy link

@douglatornell douglatornell commented Jan 17, 2012

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)
@jvanasco

This comment has been minimized.

Copy link
Owner Author

@jvanasco jvanasco commented Jan 31, 2012

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.

@robertpyke

This comment has been minimized.

Copy link

@robertpyke robertpyke commented Feb 19, 2014

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.