Pysqlite does not support sharing connections between threads yet: http://lists.initd.org/pipermail/pysqlite/2006-June/000608.html
So, for the moment, just create a new connection each time (it is cheap with sqlite). If you need to have shared properties on your sqlite connections (like user-defined functions), then use pysqlite connection factory. I posted a small patch to sqlobject and a description of how to use it earlier on the list: http://article.gmane.org/gmane.comp.python.sqlobject/6514 On 6/28/06, Alex Le Dain <[EMAIL PROTECTED]> wrote: > David Creemer wrote: > > FYI, As of SQLite 3.3.1, some of the threading restrictions on > > database connection sharing have been relaxed. I have not yet tried > > it with SQLObject, but it looks like threaded connection pooling > > might now work. See: > > > > http://www.sqlite.org/cvstrac/wiki?p=MultiThreading > > > > I am using the latest version (pysqlite 2.3.1) which I believe is > against the latest or near latest sqlite so probably is better than > 3.3.1. But the connection pooling still doesn't work as expected. I > guess it comes down to what the connection pool does and how it works > when interfaced with cherrypy. > > From the cherrypy side I think a separate thread is created to handle > incoming requests (ie the threadPool). Those requests are passed to > handler objects and calls made to handler methods. Within these methods > the SQLObjects are accessed. > > Thus if there are 10 threads on the cherrypy side then all 10 will have > the same ConnectionHub instance, but have separate connectionForURI > instances as shown:: > > hub = dbconnection.ConnectionHub() > def connect (threadIndex) : > hub.threadConnection = connectionForURI('sqlite:///c|db/books.db') > cherrypy.threadData.db = hub > cherrypy.server.onStartThreadList = [connect] > > Now my reading of this code is that a hub instance is created and it's > threadConnection attribute is set to the SQLite connection instance, > then the the hub instance is stored as thread specific data. This hub > object's threadConnection is the connection that is retrieved when > accessing the data within the cherrypy methods. What I don't understand > here is what the threadConnection attribute is doing. I read this as the > threadConnection attribute being overwritten each time the connect > methods is called. But the code does imply that each time connect is > called a new thread specific connection is created and stored ... does > the hub handle each 'setting' of threadConnection itself (placing it > into the pool)? > > Is the mistake to have the instantiation of the connectionForURI within > the connect method? Should the code instead be:: > > hub = dbconnection.ConnectionHub() > hub.threadConnection = connectionForURI('sqlite:///c|db/books.db') > def connect (threadIndex) : > cherrypy.threadData.db = hub > cherrypy.server.onStartThreadList = [connect] > > The way I read this code then, is that a single connection would be > shared between all the threads, however I don't think this will stop the > exception because the access is within the cherrypy thread and not the > thread used to create the SQLite connection (which is what the error > complains about : created in thread x, but used in thread y). > > For thread unsafe databases, what I think should happen is the > connection hub should maintain a map of threadConnection (ie the thread > id when the thread is created) and a single connectionForURI within it's > own thread (ie thread 11). All database transactions would use thread 11 > and communications between the handler methods would be via the > connection hub. Can anyone tell me if this is so? > > cheers, Alex. > > -- > > Using Tomcat but need to do more? Need to support web services, security? > Get stuff done quickly with pre-integrated technology to make your job easier > Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642 > _______________________________________________ > sqlobject-discuss mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > -- Julien Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642 _______________________________________________ sqlobject-discuss mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
