[Zope-dev] Re: SQLAlchemy (zope.sqlalchemy) integration

[Laurence Rowe]

Hermann Himmelbauer wrote:
> Hi,
> Regarding to the discussion some days ago with the SQLAlchemy Zope3 
> integration, I still have problems with retrieving the session. I currently 
> use a utility for the engine, which seems to work well.
> 
> However, for retrieving the session, I tried to use the following pattern 
> (many thanks to Michael Bayer, btw.):
> 
> -------- database module -----------
> SASession = scoped_session(sessionmaker(
>     transactional = True,
>     autoflush = True,
>     extension = ZopeTransactionExtension()))
> 
> def getSASession():
>     SASession.remove()
>     engine = getUtility(ISAEngineUtility).getEngine()
>     s = SASession()
>     s.bind = engine
>     return s
> --------------------------------------------
> 
> In my application, I then use getSASession() to retrieve my session.
> 
> However, what I think is not that beautiful is the "s.bind = engine" part. Are 
> there any suggestions how to improve this?

You have two options

If you ever need to mix objects from different `sites` into the same 
session, you should use an adapter on your root object like:

Sessions = {}

@adapter(MyRoot)
@provider(ISASession)
def getSASession(context):
   Session = Sessions.get(context.uid, None)
   if Session is None:
     Session = Sessions.setdefault(
       context.uid,
       scoped_session(sessionmaker(
         transactional=True, autoflush=True,
         extension=ZopeTransactionExtension(),
         bind=Engine(context.engine_url) # or getUtility...
       ))
     )
   return Session()

And register orm.object_session also if you like to call consistently
     session = ISASession(context)


If you don't need to mix objects from different `sites` then you can 
register a local utility for ISessionConfig.

def scope():
   return getUtility(ISessionConfig).uid, thread.get_ident()

def factory():
   engine = Engine(getUtility(ISessionConfig).url)
   return create_session(
     transactional=True, autoflush=True, bind=engine
     extension=ZopeTransactionExtension(),
     ))

Session = scoped_session(factory, scopefunc=scope)

Then you can just import Session and use:
    session = Session()

Laurence