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)