def close(self):
""""close the database"""
for sqlitefile, alias in self.attached:
try:
self.detach(alias)
except:
printyellow("could not detach", alias)
pass
if self.transaction:
msg = '''you are trying to close a transacting database (%s)
please chose :
1 : commit and close
2 : rollback since last savepoint and close
3 : rollback since begin transaction and close''' % self.sqlitefile
msg = "\n".join([s.strip() for s in msg.split('\n')])
#choice = generic_nobspy.readinput(msg, timeout = 60., defaultanswer = "2", expectedanswers = ["1", "2", "3"])
choice = raw_input('%s\n' % msg)
while not choice in "123":
choice = raw_input('?')
if choice == "1":
self.commit()
elif choice == "2":
self.rollback(crash=False, ignoresavepoint=False)
elif choice == "3":
self.rollback(crash=False, ignoresavepoint=True)
if self.verbose:
printyellow("closing : %s" % self.sqlitefile)
self.cursor.close()
self.cnx.close()
def begintransaction(self):
"""start a new transaction, insersions will be faster, see rollback"""
if self.transaction:
raise Exception('a transaction is already open')
if self.verbose:
printyellow("starting transaction : %s" % self.sqlitefile)
self.cursor.execute('begin transaction')
self.transaction = True
def attach(self, sqlitefile, alias):
"""attach another sqlite file to self, see self.detach"""
self.cursor.execute('''
attach database "%s" as %s
''' % (sqlitefile, alias))
if self.verbose:
printyellow("attaching database : %s" % sqlitefile)
self.attached.append((sqlitefile, alias))
def detach(self, alias):
"""detach all attached databases"""
for bddfile_, alias_ in self.attached:
if alias == alias_: break
else: return
self.cursor.execute('detach database %s' % alias)
self.attached.remove((bddfile_, alias_))
if self.verbose:
printyellow("detaching database : %s" % bddfile_)
def commit(self):
"""commit entries since begintransaction, closes the current transaction"""
if self.verbose:
printyellow("commiting : %s" % self.sqlitefile)
if not self.transaction: raise Exception('no transaction in process')
if self.lowtransaction:
self.cursor.execute('RELEASE SAVEPOINT LASTSP')
self.lowtransaction = False
self.cnx.commit()
self.transaction = False
def __enter__(self):
"""called by the with operator, connect to the database,
returns self for the "to" keyword"""
if self.verbose:
printyellow("connecting to :",
os.path.abspath(self.sqlitefile))
self.cnx = sqlite3.connect(self.sqlitefile, timeout=self.timeout)
self.cnx.isolation_level = None
self.cursor = self.cnx.cursor()
self.cursor.execute("PRAGMA foreign_keys = ON;") #!!!
return self
def restarttransaction(self):
"""like savepoint except that uncommited modifications will be commited,
this might be used if other connections are waiting for their turn to access the database (use timeout >> 1)
other connection may see the commited changes"""
if not self.transaction: raise Exception('no transaction is open')
verbose = self.verbose
self.verbose = False
self.commit()
self.begintransaction()
self.verbose = verbose
if self.verbose:
printyellow("restart transaction : %s" % self.sqlitefile)
def savepoint(self):
"""leave a save point during the current transaction, rollback may
remove entries from "begin transaction" or from "last savepoint"
"""
if not self.transaction:
raise Exception('no transaction is open')
if self.verbose:
printyellow("savepoint : %s" % self.sqlitefile)
if self.lowtransaction:
#release old save point and start a new one
self.cursor.execute('RELEASE SAVEPOINT LASTSP')
self.cursor.execute('SAVEPOINT LASTSP')
else:
#start a new savepoint
self.cursor.execute('SAVEPOINT LASTSP')
self.lowtransaction = True
def rollback(self, crash=True, ignoresavepoint=False):
""" delete recent insersions
:param crash: bool, raise an exception after deletions
:param ignoresavepoint: bool, if True, the rollback includes all entries since begin transaction
"""
if not self.transaction:
raise Exception('no transaction is open')
if self.lowtransaction and not ignoresavepoint:
if self.verbose:
printyellow("rolling back : to last savepoint, %s" %
self.sqlitefile)
self.cnx.execute('''rollback transaction to savepoint LASTSP''')
self.lowtransaction = False
self.cnx.commit()
self.transaction = False
else:
if self.verbose:
printyellow("rolling back : to begin transaction, %s" %
self.sqlitefile)
self.cnx.rollback()
self.transaction = False
if crash:
printyellow(errormsg())
raise Exception('transaction failed')
def select(self, cmd, tup=None, explainquery=False):
"""database.select : transmit the select command to the database
return None if the selection is empty
return a generator otherwise
example :
s = mydatabase.select('''select CUSTOMERID, NAME from CUSTOMERS where (NAME = ?)''', ("DUPOND", ))
if s is None:
print "empty selection"
else:
for CUSTOMERID, NAME in s:
print CUSTOMERID, NAME
"""
if explainquery:
if cmd.split()[0].strip().lower().startswith('select'):
if tup is not None:
for entry in self.cursor.execute(
'explain query plan %s' % cmd, tup).fetchall():
printyellow("eplain query : %s" % entry[3])
else:
for entry in self.cursor.execute('explain query plan %s' %
cmd).fetchall():
printyellow("eplain query : %s" % entry[3])
#-------
def generator(selection):
for item in selection:
yield item
selection.close()
raise StopIteration
#-------
assert isinstance(cmd, str) or isinstance(cmd, unicode)
assert tup is None or isinstance(tup, tuple)
cmd = cmd.strip()
if cmd.strip().split()[0].lower() not in ['select', "explain"]:
raise Exception(
'cmd must start with key word "select" or "explain", see http://www.sqlite.org/lang_select.html'
)
cursortmp = self.cnx.cursor()
try:
if tup is not None:
item0 = cursortmp.execute(cmd, tup).fetchone()
else:
item0 = cursortmp.execute(cmd).fetchone()
except:
cursortmp.close()
raise
try:
if tup is not None:
selection = cursortmp.execute(cmd, tup)
else:
selection = cursortmp.execute(cmd)
except:
cursortmp.close()
raise
if item0 is None:
cursortmp.close()
return None
else:
#the generator will close the temporary selection
#return generator(firstitem=item0, remainingselection=selection)
return generator(selection)