[sqlalchemy] Re: Setup to best support multi-threads

[Paul]

I'm getting the error sqlalchemy.exc.ProgrammingError: (ProgrammingError) 
SQLite objects created in a thread can only be used in that same thread.The 
object was created in thread id 5808 and this is thread id 7936 None None

with my current setup, I'm not sure what I've done wrong.

I set up this little test to see if I could write to the same table from 
multiple threads. the table has 3 columns all of type int.


from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import scoped_session,sessionmaker
from sqlalchemy.ext.declarative import declarative_base

db_engine= create_engine(sqlite:///database.db),echo=True)
Base= declarative_base(db_engine)

class Failures(Base):
  __tablename__= failures
  __table_args__= {autoload:True}
  
  def __repr__(self):
return Failures('%s','%s','%s') %(self.Id,self.action,self.reason)

metadata= Base.metadata
Session= scoped_session(sessionmaker(bind=db_engine))

class TestWriteToDB(threading.Thread):
  
  def __init__(self,start):
threading.Thread.__init__(self)
self.session= Session()
self.insert_list=[]
for i in range(start,start+10):
  f=Failures(resourceId=i,action=i,reason=i)
  self.insert_list.append(f)
  
  def run(self):
self.session.add_all(self.insert_list)
self.session.commit()


if __name__ == __main__:
  for i in range(1,40,10):
t=TestWriteToDB(i)
t.start()
  





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

Re: [sqlalchemy] Re: Setup to best support multi-threads

[Michael Bayer]

a little programming puzzle.The scoped_session() is a thread local registry 
which links a Session to the current thread.  When you say self.session = 
Session(), you're invoking the registry, generating a Session local to the 
current thread, then assigning it to a local variable.In fact all four 
TestWriteToDB objects get a hold of the same Session object.  Then they enter 
run() within a new thread and all begin using the same session.

So you'd need to either use the proxy pattern provided by the scoped_session, 
that is just call Session.add_all(), or if you want to assign the local Session 
object do it in the thread.


On Oct 31, 2011, at 4:24 PM, Paul wrote:

 from sqlalchemy import create_engine, MetaData
 from sqlalchemy.orm import scoped_session,sessionmaker
 from sqlalchemy.ext.declarative import declarative_base
 
 db_engine= create_engine(sqlite:///database.db),echo=True)
 Base= declarative_base(db_engine)
 
 class Failures(Base):
  __tablename__= failures
  __table_args__= {autoload:True}
 
  def __repr__(self):
return Failures('%s','%s','%s') %(self.Id,self.action,self.reason)
 
 metadata= Base.metadata
 Session= scoped_session(sessionmaker(bind=db_engine))
 
 class TestWriteToDB(threading.Thread):
 
  def __init__(self,start):
threading.Thread.__init__(self)
self.session= Session()
self.insert_list=[]
for i in range(start,start+10):
  f=Failures(resourceId=i,action=i,reason=i)
  self.insert_list.append(f)
 
  def run(self):
self.session.add_all(self.insert_list)
self.session.commit()
 
 
 if __name__ == __main__:
  for i in range(1,40,10):
t=TestWriteToDB(i)
t.start()

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