def __init__(self, configuration, pytrainer_main=None): self.pytrainer_main = pytrainer_main self.configuration = configuration self.ddbb_type = self.configuration.getValue("pytraining", "prf_ddbb") if self.ddbb_type == "mysql": #TODO no longer supported? from mysqlUtils import Sql else: from sqliteUtils import Sql self.confdir = self.configuration.confdir self.ddbb_path = "%s/pytrainer.ddbb" % self.confdir ddbb_host = self.configuration.getValue("pytraining", "prf_ddbbhost") ddbb = self.configuration.getValue("pytraining", "prf_ddbbname") ddbb_user = self.configuration.getValue("pytraining", "prf_ddbbuser") ddbb_pass = self.configuration.getValue("pytraining", "prf_ddbbpass") self.ddbbObject = Sql(ddbb_host, ddbb, ddbb_user, ddbb_pass, self.configuration)
def __init__(self, configuration, pytrainer_main=None): self.pytrainer_main = pytrainer_main self.configuration = configuration self.ddbb_type = self.configuration.getValue("pytraining","prf_ddbb") if self.ddbb_type == "mysql": #TODO no longer supported? from mysqlUtils import Sql else: from sqliteUtils import Sql self.confdir = self.configuration.confdir self.ddbb_path = "%s/pytrainer.ddbb" %self.confdir ddbb_host = self.configuration.getValue("pytraining","prf_ddbbhost") ddbb = self.configuration.getValue("pytraining","prf_ddbbname") ddbb_user = self.configuration.getValue("pytraining","prf_ddbbuser") ddbb_pass = self.configuration.getValue("pytraining","prf_ddbbpass") self.ddbbObject = Sql(ddbb_host,ddbb,ddbb_user,ddbb_pass,self.configuration)
class DDBB: def __init__(self, configuration, pytrainer_main=None): self.pytrainer_main = pytrainer_main self.configuration = configuration self.ddbb_type = self.configuration.getValue("pytraining", "prf_ddbb") if self.ddbb_type == "mysql": #TODO no longer supported? from mysqlUtils import Sql else: from sqliteUtils import Sql self.confdir = self.configuration.confdir self.ddbb_path = "%s/pytrainer.ddbb" % self.confdir ddbb_host = self.configuration.getValue("pytraining", "prf_ddbbhost") ddbb = self.configuration.getValue("pytraining", "prf_ddbbname") ddbb_user = self.configuration.getValue("pytraining", "prf_ddbbuser") ddbb_pass = self.configuration.getValue("pytraining", "prf_ddbbpass") self.ddbbObject = Sql(ddbb_host, ddbb, ddbb_user, ddbb_pass, self.configuration) def get_connection_url(self): return self.ddbbObject.get_connection_url() def connect(self): connection_ok, connection_msg = self.ddbbObject.connect() if not connection_ok: print "ERROR: Unable to connect to database" print connection_msg sys.exit(connection_ok) def disconnect(self): self.ddbbObject.disconnect() def select(self, table, cells, condition=None, mod=None): return self.ddbbObject.select(table, cells, condition, mod) def select_dict(self, table, cells, condition=None, mod=None): ''' Function to query DB -- inputs ---- table - string tablename(s) ---- cells - list of cells to select ---- condition - string to fit SQL where clause or None ---- mod - string of select clause modifier, eg "order by date" -- returns ---- list of dicts with cells as keys ''' logging.debug(">>") global tablesList return_value = [] #Only query db if table and cells are supplied if table is not None and cells is not None: if table.find(',') != -1: #multiple tables in select #TODO fix so works.... logging.info( 'TODO fix select_dict to work with multiple tables') cellString = ','.join(cells) #create cell list string results = self.ddbbObject.select(table, cellString, condition, mod) for result in results: dict = {} #Loop through cells and create dict of results for i, cell in enumerate(cells): #cell_type = tablesList[table][cell] dict[cell] = result[i] return_value.append(dict) elif table in tablesList: cellString = ','.join(cells) #create cell list string results = self.ddbbObject.select(table, cellString, condition, mod) for result in results: dict = {} #Loop through cells and create dict of results for i, cell in enumerate(cells): #check result is correct type if cell not in tablesList[table]: logging.error( 'select includes invalid cell (%s) for table %s' % (cell, table)) else: cell_type = tablesList[table][cell] dict[cell] = self.parseByCellType( result[i], cell_type) return_value.append(dict) else: logging.error('select on invalid table name') logging.debug("<<") return return_value def parseByCellType(self, value, cell_type): ''' Function to validate that value is of type cell_type ''' #TODO need to check if multivalue cell type specified # eg integer primary key autoincrement #print "Checking if %s is of type %s" % (value, cell_type) if cell_type.startswith('float'): try: result = float(value) return result except Exception as e: #print "%s not float" % value return None elif cell_type.startswith('int'): try: result = int(value) return result except Exception as e: #print "%s not int" % value return None elif cell_type.startswith('text'): #Text so is OK?? #TODO anytests required here?? return value elif cell_type.startswith('varchar'): #Text so is OK?? #TODO check length against spec? return value elif cell_type.startswith('char'): #Text so is OK?? #TODO check length against spec? return value elif cell_type.startswith('date'): try: result = dateutil.parser.parse(value).date() return result except Exception as e: #print type(e) #print e #print "%s not date" % value return None print "Unknown datatype: (%s) for data (%s)" % (cell_type, value) return None def insert(self, table, cells, values): self.ddbbObject.insert(table, cells, values) def insert_dict(self, table, data): logging.debug(">>") global tablesList if not table or not data or table not in tablesList: print "insert_dict called with invalid table or no data" logging.debug("!<<") return False cells = [] values = [] for cell in data: cell_type = tablesList[table][cell] cell_value = self.parseByCellType(data[cell], cell_type) if cell_value is not None: cells.append(cell) values.append(cell_value) #Create string of cell names for sql... #TODO fix sql objects so dont need to join... cells_string = ",".join(cells) self.ddbbObject.insert(table, cells_string, values) logging.debug("<<") def delete(self, table, condition): self.ddbbObject.delete(table, condition) def update(self, table, cells, value, condition): self.ddbbObject.update(table, cells, value, condition) def update_dict(self, table, data, condition): logging.debug(">>") global tablesList if not table or not data or table not in tablesList: print "update_dict called with invalid table or no data" logging.debug("!<<") return False cells = [] values = [] for cell in data: cell_type = tablesList[table][cell] cell_value = self.parseByCellType(data[cell], cell_type) if cell_value is not None: cells.append(cell) values.append(cell_value) #Create string of cell names for sql... #TODO fix sql objects so dont need to join... cells_string = ",".join(cells) self.ddbbObject.update(table, cells_string, values, condition) logging.debug("<<") def lastRecord(self, table): id = "id_" + table[: -1] #prune 's' of table name and pre-pend 'id_' to get id column sql = "select %s from %s order by %s Desc limit 0,1" % (id, table, id) ret_val = self.ddbbObject.freeExec(sql) return ret_val[0][0] def create_tables(self): """Initialise the database schema from an empty database.""" logging.info("Creating database tables") for entry in tablesList: self.ddbbObject.createTableDefault(entry, tablesList[entry]) if entry in tablesDefaultData: logging.debug("Adding default data to %s" % entry) for data_dict in tablesDefaultData[entry]: self.insert_dict(entry, data_dict) def create_backup(self): """Create a backup of the current database.""" self.ddbbObject.createDatabaseBackup()
class DDBB: def __init__(self, configuration, pytrainer_main=None): self.pytrainer_main = pytrainer_main self.configuration = configuration self.ddbb_type = self.configuration.getValue("pytraining","prf_ddbb") if self.ddbb_type == "mysql": #TODO no longer supported? from mysqlUtils import Sql else: from sqliteUtils import Sql self.confdir = self.configuration.confdir self.ddbb_path = "%s/pytrainer.ddbb" %self.confdir ddbb_host = self.configuration.getValue("pytraining","prf_ddbbhost") ddbb = self.configuration.getValue("pytraining","prf_ddbbname") ddbb_user = self.configuration.getValue("pytraining","prf_ddbbuser") ddbb_pass = self.configuration.getValue("pytraining","prf_ddbbpass") self.ddbbObject = Sql(ddbb_host,ddbb,ddbb_user,ddbb_pass,self.configuration) def get_connection_url(self): return self.ddbbObject.get_connection_url() def connect(self): connection_ok, connection_msg = self.ddbbObject.connect() if not connection_ok: print "ERROR: Unable to connect to database" print connection_msg sys.exit(connection_ok) def disconnect(self): self.ddbbObject.disconnect() def select(self,table,cells,condition=None, mod=None): return self.ddbbObject.select(table,cells,condition,mod) def select_dict(self,table,cells,condition=None, mod=None): ''' Function to query DB -- inputs ---- table - string tablename(s) ---- cells - list of cells to select ---- condition - string to fit SQL where clause or None ---- mod - string of select clause modifier, eg "order by date" -- returns ---- list of dicts with cells as keys ''' logging.debug(">>") global tablesList return_value = [] #Only query db if table and cells are supplied if table is not None and cells is not None: if table.find(',') != -1: #multiple tables in select #TODO fix so works.... logging.info('TODO fix select_dict to work with multiple tables') cellString = ','.join(cells) #create cell list string results = self.ddbbObject.select(table,cellString,condition,mod) for result in results: dict = {} #Loop through cells and create dict of results for i, cell in enumerate(cells): #cell_type = tablesList[table][cell] dict[cell] = result[i] return_value.append(dict) elif table in tablesList: cellString = ','.join(cells) #create cell list string results = self.ddbbObject.select(table,cellString,condition,mod) for result in results: dict = {} #Loop through cells and create dict of results for i, cell in enumerate(cells): #check result is correct type if cell not in tablesList[table]: logging.error('select includes invalid cell (%s) for table %s' % (cell, table)) else: cell_type = tablesList[table][cell] dict[cell] = self.parseByCellType(result[i], cell_type) return_value.append(dict) else: logging.error('select on invalid table name') logging.debug("<<") return return_value def parseByCellType(self, value, cell_type): ''' Function to validate that value is of type cell_type ''' #TODO need to check if multivalue cell type specified # eg integer primary key autoincrement #print "Checking if %s is of type %s" % (value, cell_type) if cell_type.startswith('float'): try: result = float(value) return result except Exception as e: #print "%s not float" % value return None elif cell_type.startswith('int'): try: result = int(value) return result except Exception as e: #print "%s not int" % value return None elif cell_type.startswith('text'): #Text so is OK?? #TODO anytests required here?? return value elif cell_type.startswith('varchar'): #Text so is OK?? #TODO check length against spec? return value elif cell_type.startswith('char'): #Text so is OK?? #TODO check length against spec? return value elif cell_type.startswith('date'): try: result = dateutil.parser.parse(value).date() return result except Exception as e: #print type(e) #print e #print "%s not date" % value return None print "Unknown datatype: (%s) for data (%s)" % (cell_type, value) return None def insert(self,table,cells,values): self.ddbbObject.insert(table,cells,values) def insert_dict(self, table, data): logging.debug(">>") global tablesList if not table or not data or table not in tablesList: print "insert_dict called with invalid table or no data" logging.debug("!<<") return False cells = [] values = [] for cell in data: cell_type = tablesList[table][cell] cell_value = self.parseByCellType(data[cell], cell_type) if cell_value is not None: cells.append(cell) values.append(cell_value) #Create string of cell names for sql... #TODO fix sql objects so dont need to join... cells_string = ",".join(cells) self.ddbbObject.insert(table,cells_string,values) logging.debug("<<") def delete(self,table,condition): self.ddbbObject.delete(table,condition) def update(self,table,cells,value,condition): self.ddbbObject.update(table,cells,value,condition) def update_dict(self, table, data, condition): logging.debug(">>") global tablesList if not table or not data or table not in tablesList: print "update_dict called with invalid table or no data" logging.debug("!<<") return False cells = [] values = [] for cell in data: cell_type = tablesList[table][cell] cell_value = self.parseByCellType(data[cell], cell_type) if cell_value is not None: cells.append(cell) values.append(cell_value) #Create string of cell names for sql... #TODO fix sql objects so dont need to join... cells_string = ",".join(cells) self.ddbbObject.update(table,cells_string,values,condition) logging.debug("<<") def lastRecord(self,table): id = "id_" + table[:-1] #prune 's' of table name and pre-pend 'id_' to get id column sql = "select %s from %s order by %s Desc limit 0,1" %(id,table,id) ret_val = self.ddbbObject.freeExec(sql) return ret_val[0][0] def create_tables(self): """Initialise the database schema from an empty database.""" logging.info("Creating database tables") for entry in tablesList: self.ddbbObject.createTableDefault(entry, tablesList[entry]) if entry in tablesDefaultData: logging.debug("Adding default data to %s" % entry) for data_dict in tablesDefaultData[entry]: self.insert_dict(entry, data_dict) def create_backup(self): """Create a backup of the current database.""" self.ddbbObject.createDatabaseBackup()