Using sqlalchemy in twisted.

[一首诗]

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 ....

[Gunnlaugur Briem]

Hi 一首诗,

what database engine are you using? On PostgreSQL at least, table
creation and dropping (and some other operations) take an ACCESS
EXCLUSIVE lock on the database, and will wait for this lock
indefinitely if there are open transactions hanging around. My app
creates and drops tables willy-nilly, not just at start-up, and I find
that I have to be very careful about session lifetimes. So I gave up
scoped_session entirely.

Note that you don't need to avoid creating sessions again and again
(not to save on performance anyway), see
http://www.sqlalchemy.org/docs/05/session.html#frequently-asked-questions
"Sessions are very inexpensive to make, and don't use any resources
whatsoever until they are first used...so create some!" In particular,
note that a session is distinct from a database connection -- database
connections are (typically) expensive to create, and SQLAlchemy
handles connection pooling to save on those costs. That's probably
what you were intending, and it happens even if you keep making new
sessions.

Regards,

- Gulli

[一首诗]

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.

[Jeff FW]

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

[一首诗]

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)
#=========================================

[Jeff FW]

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

[一首诗]

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?

> > > > > before ....- Hide quoted text -
>
> - Show quoted text -

[Jeff FW]

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

[一首诗]

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!

[Pedro Algarvio, aka, s0undt3ch]

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.

[Jeff FW]

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: