[Grok-dev] Multiple Databases with SQLAlchemy, z3c.saconfig, megrok.rdb

[Jeffrey D Peterson]

FYI: Martijn, List

Thanks for the reply.  You confirmed what I had guessed.  I had found that
named_scoped_session function and had tried it with megrok.rdb, that was a
FAIL as you suspected. Naming the utilities works but not for megrok.rdb
which wants one and only one engine, metadata and session. But, as you said,
naming them does give you the ability to grab them from the utility
machinery.  You have to then create your tables the sqlalchemy declarative
way, which is ok, even up against the sugary sweetness of building them with
megrok.rdb.

Here is my solution:

*** oracle/config.py ***

import grok
from sqlalchemy.ext.declarative import declarative_base
from z3c.saconfig import EngineFactory, GloballyScopedSession
from z3c.saconfig.interfaces import IEngineFactory, IScopedSession,
IEngineCreatedEvent

DSN = 'oracle://webserv:********@MIGR75X'

engine_factory = EngineFactory(DSN, echo=False)
grok.global_utility(engine_factory, provides=IEngineFactory, direct=True,
name='oracle_engine')

scoped_session = GloballyScopedSession(engine='oracle_engine')
grok.global_utility(scoped_session, provides=IScopedSession, direct=True,
name='oracle_session')

engine = engine_factory()
OracleBase = declarative_base(bind=engine)

oracle_schema = 'crar1app'

@grok.subscribe(IEngineCreatedEvent)
def engine_created(event):
    OracleBase.metadata.reflect(engine)


*** oracle/rdb/salesparts.py ***

from sqlalchemy import Column, ForeignKey, and_
from sqlalchemy.types import String
from crary.oracle import (OracleBase, oracle_schema)
from z3c.saconfig import named_scoped_session

class SalesPart(OracleBase):
    __tablename__ = 'crary_sales_part'
    __table_args__ = (
        dict(schema=oracle_schema, useexisting=True, autoload=True)
    )

    contract = Column('contract', String(5), nullable=False,
primary_key=True)
    catalog_no = Column('catalog_no', String(25), nullable=False,
primary_key=True)
    catalog_group = Column('catalog_group', String(10), nullable=False,
primary_key=True)


    @classmethod
    def all_wholegoods(cls):
        session = named_scoped_session('oracle_session')
        Session = session()
        return
Session.query(cls).filter(and_(cls.catalog_group.like('POT%'),
cls.catalog_group != 'POTSP', cls.contract == '20')).all()

for my test cases I have similar modules for a MySQL DB which also work,
giving me a viable solution to my problem.  I don't think I need to create
the engine_factory utility but I did it anyway, I will test it further and
determine for sure if it's needed.

Thanks again!

--
Jeffrey Peterson
bgpete3 at gmail.com

> -----Original Message-----
> From: grok-dev-bounces at zope.org [mailto:grok-dev-bounces at zope.org] On
> Behalf Of Martijn Faassen
> Sent: Thursday, September 16, 2010 10:15 AM
> To: grok-dev at zope.org
> Subject: Re: [Grok-dev] Multiple Databases with SQLAlchemy,
> z3c.saconfig, megrok.rdb
> 
> On 09/14/2010 11:15 PM, Jeffrey D Peterson wrote:
> > I have multiple legacy databases I need to connect to.
> >
> > It appears that using megrok.rdb, et, al will not provide me with
> that
> > functionality, though SQLAlchemy will. Am I missing something? Just
> want
> > to make sure before I embark on rolling my own.
> 
> You'll probably have the most difficulty with megrok.rdb.
> 
> z3c.saconfig however exposes a function 'named_scoped_session'. You can
> supply it with a name to get a session object that works with a
> particular database setup. If you register the session utilities with
> names this should work. (I think the same applies to the engine stuff)
> 
> Regards,
> 
> Martijn
> 
> _______________________________________________
> Grok-dev mailing list
> Grok-dev at zope.org
> https://mail.zope.org/mailman/listinfo/grok-dev