Are there references yet for SQLalchemy reflection and using multiple engines under pyramid ?

[Jonathan Vanasco]

I'm now using pyramid for some projects.

Under Pylons I would almost always do the following:

1. Use multiple Engines ( Write, Read, Config, Log ) which each had
different permissions in PostgreSQL.
2. Have my models reflect the database. With some custom routines, I
largely just had to pass in a string for the tablename and a sometimes
primary key .

I'm not sure how to do that under Pyramid ( I am using the Akhet
scaffold ). I might just use MongoDb for a bit, but would love to
figure this out.

[Doug Latornell]

On Sunday, December 18, 2011 2:07:40 PM UTC-8, Jonathan Vanasco wrote:

...

2. Have my models reflect the database.  With some custom routines, I
largely just had to pass in a string for the tablename and a sometimes
primary key .

I'm not sure how to do that under Pyramid ( I am using the Akhet
scaffold ).

I'm using database reflection quite happily in a project. I started from a pyramid_routesalchemy scaffold, so I'm not sure how applicable the following is to an Akhet project, but I suspect there is some common ground.

In my models.py module I created a ReflectedTable class to handle the reflection and mapper setup, a bunch of class stubs that inherit from it, and a map_tables function that gets called in initialize_sql() to make everything happen:

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

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

class ReflectedTable(object):
    """Base class for database objects that are mapped to tables by
    reflection.
    """
    @classmethod
    def map_by_reflection(cls, table_name):
        """Map the specified database table to the object by
        reflection.
        """
        table = Table(table_name, metadata, autoload=True)
        mapper(cls, table)

class SomeTable(ReflectedTable): pass

class SomeOtherTable(ReflectedTable): pass

def map_tables():
    objs_and_tables = [
        (SomeTable, 'some_table_name'),
        (SomeOtherTable, 'some_other_table_name'),
    ]
    for obj, table_name in objs_and_tables:
        obj.map_by_reflection(table_name)

def initialize_sql(engine):
    DBSession.configure(bind=engine)
    metadata.bind = engine
    map_tables()

initialize_sql gets called in __init__.main() so the reflection is all done during app startup. I suspect that's where you would want to do your multiple engine setup too, but I've never tried that.

Hope that helps...

[Jonathan Vanasco]

Doug- thanks a ton. I think that should be enough to get me going.

I've been prototyping everything in mongodb w/pylons, and then writing
raw sql for postgres, and just about done with that step in my
project. excited to tackle this later tonight!

[Jonathan Vanasco]

Thanks. This ended up helping quite a bit!

I haven't gotten into multiple databases yet, but I think it should be
supported trivially

I refactored your code for re-use into this pastebin - https://gist.github.com/1556734

I tried to make it simpler, but there is a bit of a janky hack in
there.

1. I stripped the "ReflectedTable" down to just being an inherited
class type , and one which defines a "__tablename__"
2. The map_tables routine now just takes an app_model directory and
looks for items that have both a) inherited from ReflectedTable and b)
define "__tablename__"

To make it work, just replace models/__init__.py with this

This obviously needs a few more passes, but I'd like to really
abstract this into something that can just be a standalone module to
handle all this stuff.