Pedro, I don't really have much of anything special as far as the connection goes--SQLAlchemy already does a pretty awesome job of handling all that for you. I just keep a module-level variable that I can import as needed, and call a function in that module to set up the connection with settings from a config file.
As far as the models/mapping goes, I have all of the tables, classes, and mapping between then defined in one module, and I can just import from there whenever needed. Nothing about sessions/connections in there at all--that way there's no need to worry about them. It's really a pretty ordinary set-up. Unfortunately, all this code is for work, so I can't share it. I'll happily answer your questions, though. -Jeff On Mar 15, 2:42 pm, "Pedro Algarvio, aka, s0undt3ch" <u...@ufsoft.org> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
