def insert(self,dict,name=None):
"""
Insert the dictionary of columns to value into the database
If the optional argument name is not given uses the tableName set by
load_schema.
"""
if name is None:
name = self.tableName
columnNames = [sanitizeString(x) for x in dict.keys()]
values = [sanitizeString(x) if isinstance(x,str) else x for x in dict.values()]
columns = ', '.join(columnNames)
placeholders = ', '.join(['?'] * len(dict))
insertStr = 'INSERT INTO %s ' % name
insertStr += '(%s) VALUES ( %s )' % (columns,placeholders)
self.execute(insertStr,values)
def select(self,cols,cond=None,name=None):
"""
Select an item or set of items from the database
The optional parameter cond is a named tuple consisiting of two as arguments
as strings and a comparison operator to perform on them. if cond is passed in
the named tuple's strings are sanitized and it's turned into a conditional
string to use with "WHERE". See below for a list of
valid comparison operators:
if cond is not given then all of the rows of each column in cols are returned
http://www.sqlite.org/lang_expr.html#binaryops
If the optional argument name is not given, uses the tableName set by
load_schema.
"""
if name is None:
name = self.tableName
colNames = [sanitizeString(x) for x in cols]
selectStr = "SELECT " + ','.join(colNames) + " "
selectStr += "from " + name + " "
if cond:
operand1 = sanitizeString(cond.arg1)
operand2 = cond.arg2
if not operand2.isdigit():
operand2 = sanitizeString(operand2)
operand2 = "\"" + operand2 + "\""
op = cond.operator
selectStr += "where "
selectStr += operand1
selectStr += op
selectStr += operand2
return self.execute(selectStr)
def select_from_last_row(self,cols,name=None):
"""
Select an item or set of items from the last row in the table
If the optional argument name is not given, uses the tableName set by
load_schema.
"""
if name is None:
name = self.tableName
colNames = [sanitizeString(x) for x in cols]
# In order to support somewhat multi-threaded support get the last
# row using MAX(rowid) instead of last_insert_rowid()
lastInsertRow = self.execute("SELECT MAX(rowid) from " + name)[0][0]
selectStr = "SELECT " + ','.join(colNames) + " "
selectStr += "from " + name + " "
selectStr += "WHERE rowid="+ str(lastInsertRow)
return self.execute(selectStr)
def create_table(self):
"""
Create the database if it doesn't not exists using the dictionary in self.schema
Raises RuntimeError if schema hasn't been loaded
"""
logger.debug("Creating database")
if self.schema is None:
raise RuntimeError("No schema loaded! Can't create table!")
dbCreateStr = "CREATE TABLE IF NOT EXISTS "
dbCreateStr+= self.tableName + "("
for colName in self.schema.keys():
type = self.schema[colName]
sanitizedColName = sanitizeString(colName)
dbCreateStr+= "%s %s," % (sanitizedColName, type)
dbCreateStr = dbCreateStr[:-1]
dbCreateStr+= ")"
self.execute(dbCreateStr)
