Yes, actually, that's the pattern I used from the beginning, i.e. for every
request(actually tcp server connection request), create a session, do db
operation, commit, and close.
But it's too slow, for 1000 connections requests, it takes more than 40
seconds, so , just yesterday, when using multiprocessing to wrap db
operation, it's very fast, takes only 8 seconds to handle 1000 requests.
Seems sqlalchemy engine pool uses thread, eh? I know python's GIL.
Here is my code snippet:
from sqlalchemy.schema import Table
from sqlalchemy.orm import sessionmaker,scoped_session
from db import dba_logger,metadata,engine#session
from datetime import datetime
from exctrace import exctrace
from sqlalchemy import and_
direct_engine = True
use_raw = False
#import gevent
#from gevent import monkey
#monkey.patch_all()
import multiprocessing
def tmp_dbwrite(tablename,**kwargs):
"""
Used to insert exception info into database.
Params:
module : module name, indicating who raises the exception, e.g.
android,ios,psg,adpns,db .etc
type : exception type, 0 means service level while 1 is system
level.
message : exception description,length limit to 256 bytes
"""
try:
_table=Table(tablename, metadata, autoload=True)
i=_table.insert().values(**kwargs)
if direct_engine:
engine.execute(i)
#gevent.spawn(engine.execute,i)
#gevent.sleep(0)
#gevent.joinall([gevent.spawn(engine.execute,i)])
else:
session = scoped_session(sessionmaker(bind=engine))
session.execute(i)
session.commit()
session.close()
except Exception,e:
#dba_logger.log(40,'Exception when dbwriter:%s' % str(e))
#dba_logger.log(20,'Exception detail:%s' % str(kwargs))
exctrace('db','1','Error happened when writing
db',dba_logger,'Exception when dbwriter:%s' % str(e),'Exception detail:%s'
% str(kwargs))
if not direct_engine:
session.rollback()
session.close()
def tmp_dbupdate(tablename,whereclause,**kwargs):
"""
Used to insert exception info into database.
Params:
module : module name, indicating who raises the exception, e.g.
android,ios,psg,adpns,db .etc
type : exception type, 0 means service level while 1 is system
level.
message : exception description,length limit to 256 bytes
"""
try:
_table=Table(tablename, metadata, autoload=True)
i=_table.update().values(**kwargs).where(whereclause)
if direct_engine:
engine.execute(i)
#gevent.spawn(engine.execute,i)
else:
session = scoped_session(sessionmaker(bind=engine))
session.execute(i)
session.commit()
session.close()
except Exception,e:
#dba_logger.log(40,'Exception when dbwriter:%s' % str(e))
#dba_logger.log(20,'Exception detail:%s' % str(kwargs))
exctrace('db','1','Error happened when updating
db',dba_logger,'Exception when dbupdate:%s' % str(e),'Exception detail:%s'
% str(kwargs))
if not direct_engine:
session.rollback()
session.close()
def dbquery(tablename,whereclause):
try:
_table=Table(tablename, metadata, autoload=True)
i=_table.select().where(whereclause)
if direct_engine:
res = engine.execute(i)
return res
else:
session = scoped_session(sessionmaker(bind=engine))
res = session.execute(i)
return res
session.close()
except Exception,e:
#dba_logger.log(40,'Exception when dbwriter:%s' % str(e))
#dba_logger.log(20,'Exception detail:%s' % str(kwargs))
exctrace('db','1','Error happened when querying
db',dba_logger,'Exception when dbquery:%s' % str(e),'Exception detail:%s' %
str(whereclause))
#session.rollback()
if not direct_engine:
session.close()
#res =
session.execute(connection_writer._table.select().where(connection_writer._table.c.app_key==self.app_key).where(connection_writer._table.c.device_token==self._devicetoken))
pool = multiprocessing.Pool()
def dbwrite(tablename,**kwargs):
pool.apply_async(tmp_dbwrite, (tablename,), kwargs)
def dbupdate(tablename,whereclause,**kwargs):
pool.apply_async(tmp_dbupdate, (tablename,whereclause), kwargs)
在 2014年3月15日星期六UTC+8上午12时24分38秒，Jonathan Vanasco写道：
>
> You would probably do better with a pattern where you have a Session for
> every request , and just use that session. That is how most people
> implement SqlAlchemy for web.
>
> * request start
> * create a sqlalchemy session, either scoped or explicit
> * do things with your session : read , write, etc.
> * commit/rollback session
> * request close; cleanup with a session.remove or session.close
>
> Here are two great pieces from the documentation:
>
> *
> http://docs.sqlalchemy.org/en/latest/orm/session.html#session-frequently-asked-questions
> *
> http://docs.sqlalchemy.org/en/latest/orm/session.html#using-thread-local-scope-with-web-applications
>
>
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
