[Twisted-Python] How to correctly run Sqlite with Twisted?

[Werner Thie]

Aloha Goffi

I'm using minimal code like the one below for MySQL interaction, should be easy to transition this to SQLite

from twisted.internet   import reactor, task, defer

from store import Store

#debugging func, printing the result on the console
def _transformResult(result):
##  print '####', result
   if result:
       return result[0]  #unpack the list
   else:
       return None

class Somestore(object):
   name = "somestore"

   def __init__(self, dbapiName, **params):
     self.store = Store(dbapiName, **params)
     ka = task.LoopingCall(self.keepAlive)             #db keepalive
     ka.start(307)

   def dbdisconn(self, reason):
     print 'db disconnected for ', reason

   def keepAlive(self):
     try:
       d = self.store.runQuery('SELECT 1')
       d.addErrback(self.dbdisconn)
     except:
       pass
     else:
       pass        #do whatever MUST occur here in all cases

   def getTableCount(self):
     d = self.store.runQuery('SELECT tables FROM user WHERE servername = %s', 'total')
     d.addCallback(_transformResult)
     d.addErrback(self.dbdisconn)
     return d

   def setUserCount(self, waiting, playing, tables):
     d = self.store.runOperation('UPDATE user SET waiting = %s, playing = %s, tables = %s WHERE servername = %s', waiting, playing, tables, self.loggername)
     d.addErrback(self.dbdisconn)
     return d


module store.py
from itertools import izip

from twisted.enterprise import adbapi

using it with canned queries like


class Store(object):
   def __init__(self, dbapiName, **params):
     self.__pool   = adbapi.ConnectionPool(dbapiName, **params)
     print self.__pool.__getstate__()
     self.runOperation('SET autocommit = %s', 1)

   def runQuery(self, query, *args):
     d = self.__pool.runInteraction(self.mapQuery, query, args)
     return d

   def runInsert(self, query, *args):
     def mapQ(curs, query, *args):
       try:
         curs.execute(query, *args)
       except adbapi.ConnectionLost:
         print
         print '++++++++++++ rerunning query'
         print
         curs.execute(query, *args)                    #simply resend query, assuming cp_reconnect=True
         return {'lastrowid': -1}
       return {'lastrowid': curs.lastrowid}
     d = self.__pool.runInteraction(mapQ, query, args)
     return d

   def mapQuery(self, curs, query, *args):
     try:
       curs.execute(query, *args)
     except adbapi.ConnectionLost:
       curs.execute(query, *args)                    #simply resend query, assuming cp_reconnect=True
     result = curs.fetchall()
     columns = [d[0] for d in curs.description]
     return [dict(zip(columns, r)) for r in result]

   def runOperation(self, query, *args):
     d = self.__pool.runOperation(query, args)
     return d


On 9/19/2017 10:59 AM, Goffi wrote:
> Hello,
>
> I'm using Sqlite3 module through Twisted's enterpirse.adbapi, I create the
> ConnectionPool instance like this:
>
>     self.dbpool = ConnectionPool("sqlite3", db_filename,
> check_same_thread=False)
>
> You can see the code at https://repos.goffi.org/sat/file/tip/src/memory/sqlite.py
>
> Sometime, the writing is failing with following exception:
>
>    Failure instance: Traceback: <class 'sqlite3.OperationalError'>: database is
> locked
>
> So I wonder if the database is correctly used, did anybody experienced
> something similar with Twisted and Sqlite ?
>
> Should I just augment timeout as advised at https://stackoverflow.com/a/
> 8618328? Looks like more a workaround than a clean solution.
>
> Python 2 documentation doesn't talk about check_same_thread argument, but
> Python 3 at https://docs.python.org/3.5/library/sqlite3.html#sqlite3.connect
> says that writing operation should be serialized by the user (I thought it was
> the default as said in https://sqlite.org/threadsafe.html), how should I
> achieve that?
>
> Also PRAGMA are not working (specially "PRAGMA foreign_keys = ON"), I guess
> because of multi-threading, what is the good way to activate foreign_keys for
> all following request?
>
> Thanks in advance
>
> Goffi
>
> _______________________________________________
> Twisted-Python mailing list
> Twisted-Python at twistedmatrix.com
> https://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python