Setup to best support multi-threads

[Paul]

I'm new to sqlalchemy and was looking at a good starting point. I currently have
an application which has multiple threads at certain parts, some of which need
access to the same sqlite database at the same time. Mostly only one writing but
sometimes more than one thread will need to write to the database. So simply
using sqlite3 wont cut it.

I was under the impression that this kind of setup can be achieved with
sqlalchemy but I was wondering which sort of setup with engine, pools and a
design pattern to best achieve this would be?

Performance isn't a huge concern as small amounts of data will be being written
at a time so blocking or queueing would be acceptable solutions.

Thanks!

[Michael Bayer]

the standard SQLAlchemy patterns should work fine here:

- in 0.7, create_engine() will set up a NullPool when using SQLite against a file. SQLite doesn't really need connection pooling in the usual case. nothing special is required here on your part.
- when using a Connection, if you're doing so explicitly, you use it in only one thread at a time.
- when using the ORM, you use a Session as well as all the objects attached to that Session, in only one thread, without sharing across threads. Other threads use their own Session/objects. The scoped_session() registry is normally used to provide a "session per thread" pattern.

that's it !

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

[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()

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