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@twistedmatrix.com
https://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
_______________________________________________
Twisted-Python mailing list
Twisted-Python@twistedmatrix.com
https://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
