[sqlalchemy] Re: Using sqlalchemy in twisted.

[Pedro Algarvio, aka, s0undt3ch]

On Mar 11, 2:13 pm, 一首诗 <newpt...@gmail.com> wrote:
> Hi Jeff,
>
> In my project I use the *model* to indicate an instance of Database.
> I don't really need multiple instances of Database.  But when I wrote
> unit test, I always create an new one database in setup, which create
> a new sqlite in memory database to avoid conflicts between test cases.
>
> About the trick to make *sess* a keywork parameter,  that's really
> clever!
> Thanks a lot!
>
> On Mar 11, 9:05 pm, Jeff FW <jeff...@gmail.com> wrote:
>
> > Logging SA objects *after* the session is gone will always be a
> > problem, unless you make sure to detach all of them from the session.
> > I'd just log the original request, instead.  In my case, I have to
> > convert all of my SA objects to something Perspective Broker
> > understands, so I actually log those *after* that, as they're no
> > longer part of the session--but I'm not sure if you can do that in
> > your case.
>
> > As for the decorator--I got a little confused with your names--you
> > call it "model" in your decorator, but it's really an instance of
> > Database when it gets passed in as "self".  One way to get rid of that
> > parameter would be to make sess a keyword argument, like so:
>
> >     def wrapper(*args, **kw):
> >         sess = model.Session()
> >         try:
> >             return f(sess=sess, *args, **kw)
>
> > and then change your method:
>
> >     def _getObjectById(self, klass, id, sess=None):
> >         return sess.query(klass).get(id)
>
> > That way, "self" will get passed in *args with no problem.
>
> > Are you planning to have multiple instances of your Database class?
> > If not, I'd suggest changing everything in it into class methods, so
> > that way you can call it *without* an instance at all, and don't have
> > to worry about connecting to the database multiple times by accident.
> > Just a thought.
>
> > -Jeff
>
> > On Mar 10, 10:38 am, 一首诗 <newpt...@gmail.com> wrote:
>
> > > Hi Jeff,
>
> > > Thanks for your kind suggestion.
>
> > > I first add some log decorators, but i found when it might cause to
> > > print sqalchemy objects which has not been bound to any session.
>
> > > And I am not quite sure about how to make the decorator mor genreal.
>
> > > Actually, I think I must use model as the first parameter because as a
> > > instance method, "_getObjectById" require the first parameter to be
> > > "self".
> > > Can you write a few lines of code to show your suggestion?
>
> > > On Mar 8, 5:06 am, Jeff FW <jeff...@gmail.com> wrote:
>
> > > > That's pretty similar to what I do, actually, if a bit simpler (but
> > > > that's good!)  One suggestion would be to throw an except (maybe for
> > > > the base SQLAlchemy exception class)  in your try block, otherwise you
> > > > run the risk of things dying in an ugly way.  I'm not familiar with
> > > > pyamf, so I don't know how it would handle errors, but twisted usually
> > > > carries on as if nothing happens.
>
> > > > Also, I'd make the decorator a bit more general--don't put the model
> > > > argument in wrapper().  Put sess first, then take *args and **kwargs,
> > > > and pass those right to the inner function f(). That way you can reuse
> > > > it for anything that requires a DB session.
>
> > > > Other things you could add (if so inclined) are decorators for logging
> > > > and other types of error handling (like catching IntegrityErros thrown
> > > > by duplicates.)  I do those things, but I might be a bit OCD :-)
>
> > > > -Jeff
>
> > > > On Mar 7, 1:41 am, 一首诗 <newpt...@gmail.com> wrote:
>
> > > > > Hi, Thanks for your reply.  I'm using it the way like you.  The only
> > > > > difference is that I am using pyamf instead of PB.
>
> > > > > On every request, I delegate required db operations to a class called
> > > > > Database, similar to these code below.
>
> > > > > I used to use scope_session instead of create and close session every
> > > > > time.   But as I said in my earlier mails, they don't work.
>
> > > > > These code below seems to work right now.  But if you have more
> > > > > suggestion,  I will be very thankful.
>
> > > > > #=========================================
>
> > > > > def require_session(f):
> > > > >     '''create and close session for each synchronous method'''
> > > > >     def wrapper(model, *args, **kw):
> > > > >         sess = model.Session()
> > > > >         try:
> > > > >             return f(model, sess, *args, **kw)
> > > > >         finally:
> > > > >             sess.close()
> > > > >     return wrapper
>
> > > > > class Database()
> > > > >     def __init__(self, conn_str):
> > > > >         self.conn_str = conn_str
> > > > >         self.engine = create_engine(self.conn_str, echo=False)
> > > > >         self.Session = sessionmaker(bind = self.engine,
> > > > > expire_on_commit=False)
>
> > > > >     def getObjectById(self, klass, id):
> > > > >         return threads.deferToThread(self._getObjectById, klass, id)
>
> > > > >     @require_session
>
> > > > >     def _getObjectById(self, sess, klass, id):
>
> > > > >         return sess.query(klass).get(id)
> > > > > #=========================================
>
> > > > > On Mar 6, 5:44 am, Jeff FW <jeff...@gmail.com> wrote:
>
> > > > > > Don't use scoped_session--you'll run into problems no matter what 
> > > > > > you
> > > > > > do.  I'm using Perspective Broker from Twisted with SQLAlchemy.  I
> > > > > > make sure to create and commit/rollback a session for *every* PB
> > > > > > request.  It works perfectly, and that's the only way I was really
> > > > > > able to get it to work in all cases.
>
> > > > > > Assuming you're using Twisted in a similar way, you could write a
> > > > > > simple decorator to wrap any functions that need a database session 
> > > > > > in
> > > > > > the begin/commit stuff as necessary.
>
> > > > > > If you can give more details of how you're using Twisted, I might be
> > > > > > able to offer some more insight.
>
> > > > > > -Jeff
>
> > > > > > On Mar 5, 12:33 am, 一首诗 <newpt...@gmail.com> wrote:
>
> > > > > > > I'm not quite sure, but I think I'm pretty careful of sharing 
> > > > > > > objects
> > > > > > > between threads.
>
> > > > > > > 1st, I only cached as few as possible orm objects.  I tried to 
> > > > > > > detach
> > > > > > > them, but I found that if I detach them,  I can't access any of 
> > > > > > > their
> > > > > > > fields any more.
>
> > > > > > > 2nd, I create new orm objects based on client request, pass them 
> > > > > > > to
> > > > > > > class Database and then merge them to scoped sessions, change, 
> > > > > > > commit
> > > > > > > and then discard these objects.
>
> > > > > > > 3rd, I switch to sqlite frequently to check if there is any 
> > > > > > > database
> > > > > > > operation outside Database, because sqlite doesn't allow 
> > > > > > > multi-thread
> > > > > > > access.
>
> > > > > > > Actually it seems to work until 2 or 3 days ago suddenly cases 
> > > > > > > hang
> > > > > > > the server.
>
> > > > > > > Ah, as I've already written lots of code in ORM, I think maybe I
> > > > > > > should try to change Database to use a dedicated thread to handle 
> > > > > > > all
> > > > > > > database operations.
>
> > > > > > > That might be a bottle neck of my application, but I really can't 
> > > > > > > give
> > > > > > > up orm as these mapper classes are used everywhere in my 
> > > > > > > application.
>
> > > > > > > On Mar 4, 7:26 pm, 一首诗 <newpt...@gmail.com> wrote:
>
> > > > > > > > Hi, all
>
> > > > > > > > I am using sqlalchemy in twisted in my project in the way below.
> > > > > > > > Defer any database operation so the twisted's main thread won't 
> > > > > > > > be
> > > > > > > > blocked.
>
> > > > > > > > And I use scoped_session, so that sessions won't have to be 
> > > > > > > > created
> > > > > > > > again and again.
>
> > > > > > > > ======================================
> > > > > > > > class Database()
> > > > > > > >     def __init__(self, conn_str):
> > > > > > > >         self.conn_str = conn_str
> > > > > > > >         self.engine = create_engine(self.conn_str, echo=False)
> > > > > > > >         self.Session = scoped_session(sessionmaker(bind = 
> > > > > > > > self.engine,
> > > > > > > >              expire_on_commit=False))
>
> > > > > > > >     def getObjectById(self, klass, id):
> > > > > > > >         return threads.deferToThread(self._getObjectById, 
> > > > > > > > klass, id)
>
> > > > > > > >     def _getObjectById(self, klass, id):
> > > > > > > >         sess = self.Session()
> > > > > > > >         return sess.query(klass).get(id)
> > > > > > > > ======================================
>
> > > > > > > > The code doesn't work.   When I limit the thread numbers to 1
>
> > > > > > > >     reactor.suggestThreadPoolSize(1)
>
> > > > > > > > Everything goes fine.  Other wise the server would be blocked 
> > > > > > > > and must
> > > > > > > > be killed by "kill 9 ...".
>
> > > > > > > > The result conflicts with my understanding of sqlalchemy.  
> > > > > > > > Since I
> > > > > > > > don't share any object between threads, there should be no 
> > > > > > > > problem!
>
> > > > > > > > Ah....  It always have risk to use something you haven't tried
> > > > > > > > before ....- Hide quoted text -
>
> > > > - Show quoted text -
>
>

Seems that I'm on the same spot as you guys are, I *want* to work with
both sqlalchemy
and twisted.

一首诗 uses a class, do you too Jeff?

What about setting up the database model object classes? and mapping?
Same as with non async SA code?
By any chance do you guys have any public code on how you're doin'
this?
One always learns just by looking....

Pedro Algarvio.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---