[Grok-dev] megrok.rdb, Oracle, and views.

Jeffrey D Peterson bgpete at gmail.com
Thu Feb 11 19:26:05 EST 2010

Hello again,

A while back we looked at some issues I was having with megrok.rdb, Oracle
and reflecting views.  I had fiddled around with megrok.rdb until I got what
seemed like something that worked, and it did, but it was a little hack-is,
pulling stuff out of the metadata to not create it.  Well after several
emails with the sqlalchemy list, I think I have a better solution, this is
based mostly on advice from Michael Bayer about how views should be handled
in SQLA.

His suggestion was very simple...keep them in a separate metadata

So, to that end, here is what it looks like to me.

Setup stuff (oracled.py):

DSN = 'oracle://webserv@MIGR75X'
engine_factory = EngineFactory(DSN, echo=True)
grok.global_utility(engine_factory, direct=True)

scoped_session = GloballyScopedSession()
grok.global_utility(scoped_session, direct=True)

# Keep two metadata and fire up engines for both
reflected_views_md = rdb.MetaData()
created_md = rdb.MetaData()

def reflected_engine_created(event):
    rdb.setupDatabase(reflected_views_md, True)

def engine_created(event):

Changes in megrok.rdb:

In setup.py I simply added a parameter and conditional to the setupDatabase

def setupDatabase(metadata, skip_create=False):
    """Set up of ORM for engine in current site.

    This will:

    * reflect any reflected tables that need to be reflected from the
      into classes.

    * create any tables in the database that haven't been yet reflected.
    if not skip_create:

Then when reflecting views simply use the correct metadata and set the flag
skip_create to True. The reason there was always trouble was that
rdb.setupDatabase _always_ called create_all() regardless. SQLA, while it
can see that reflected tables exist in the DB it can't do the same with
views and would try and create those views as tables if create_all() was
called with the metadata containing the reflected views.

This is a very small subtle change that would not at all effect existing

And then you can fire up reflected views to your heart's content:

from portal.lib.db.oracled import reflected_views_md as metadata1,
created_md as metadata2

class TestSA(rdb.Container):

class CrarySalesPart(rdb.Model): 
    Reflected view, notice the metadata that uses the rdb.setupDatabase with
    Don't run create_all() on this metadata SQLA will see this as a table
that needs creation and
    Try and create it in the DB, which we do not want.
    rdb.tableargs(schema='crar1app', useexisting=True)

    contract = Column('contract', String, nullable=False, primary_key=True)
    catalog_no = Column('catalog_no', String, nullable=False,

class CraryOutputTab(rdb.Model): 
    Reflected table, notice the metadata that uses the rdb.setupDatabase
with skip_create=False(default), 
    SQLA handles the create properly because it can see this as a table and
therefore will not try and create it.
    This metadata would then also be used for any tables you did want to be
dynamically created as well.
    rdb.tableargs(schema='crar1app', useexisting=True)

I don't know about you but this method feels right to me, and it also causes
a lot less pain.  
Very little code to change and a lot of functionality gained.

Here is a diff of setup.py:

< def setupDatabase(metadata):
> def setupDatabase(metadata, skip_create=False):
<     createTables(metadata)
>     if not skip_create:
>         createTables(metadata)


Jeff Peterson

More information about the Grok-dev mailing list