""" LEFT OFF: Setting up the config file dbvars.cfg to set the self.columns[table] var """ from checklist import checklist from inspect import getmembers from inspect import stack from logger import check_logger from logger import create_logger # from functions import * import custom_errors import ConfigParser import MySQLdb import re import sys import time class db(object): def __init__(self, host = None, database = None, port = 3306, user = None, password = None, log = None, ): # try: # Check if logger passed, else create new one self.log = check_logger(log, self) # Set error handler self.error = custom_errors.error(self) # Sets database parameters from dbvars.cfg self._set_db_vars() # Check for overriding vars passed into __init__ if host is not None: self.dbvars['host'] = str(host) if database is not None: self.dbvars['database'] = str(database) if port is not None: self.dbvars['port'] = str(port) if user is not None: self.dbvars['user'] = str(user) if password is not None: self.dbvars['password'] = str(password) # Actually create the DB connection and cursor self._set_db() self.values = {} self.log.info("Done.") # except Exception, e: # self.lasterr = error(self, e, getmembers(self), stack() def _checklistlengths(self, columns, values): if ((checklist(columns)) and (checklist(values))): if (len(columns) != len(values)): e = "".join(["'dbhandler.checklistlengths': The number of items ", "in 'columns' and 'values' ", "do not appear to match. ", "If you need to insert a blank value, use ''.", "Skipping."]) self.log.error(e) self.log.error("".join(["columns: (",str(len(columns)),")", str(columns)])) self.log.error("".join(["values: (",str(len(values)),")", str(columns)])) return False else: return True else: e = "".join(["'dbhandler.checklistlengths': The parameters ", "'columns' and 'values' ", "do not appear to be lists. ", "Each of these must be passed ", "as a list of strings."]) self.log.error(e) self.log.error("".join(["columns: (", str(len(columns)), ")", str(columns)])) self.log.error("".join(["values: (", str(len(values)), ")", str(values)])) return False def _set_db(self): self.log.info("Starting MySQL database connection ...") self.log.debug("Creating connection...") self.conn=MySQLdb.connect(host = self.dbvars['host'], db = self.dbvars['database'], port = int(self.dbvars['port']), user = self.dbvars['user'], passwd = self.dbvars['password'] ) self.log.debug("Done.") self.log.debug("Verifying connection...") if not self._verify_connection(): e = ('Fatal: DBStartFailure') raise Exception(e) self.log.debug("Done.") self.log.debug("Creating cursor...") self.cursor = self.conn.cursor() self.log.debug("Done.") self.tables = [] self.cursor.execute("""show tables;""") for row in self.cursor: self.tables.append(row[0]) self.log.debug("Done.") def _set_db_vars(self, filename = "../etc/dbvars.cfg"): """ _set_db_vars([filename]) DESCRIPTION: Uses ConfigParser to set the column names for each table into variable self.columns[] filename can be set for non-default configuration file name default = dbvars.cfg dbvars.cfg format [tablename] column = 'ColumnsName1' column = 'ColumnsName2' NOTE: _set_db_vars should only be run ONCE in __init__ """ self.log.debug("Loading DB variables...") try: self.columns = {} self.dbvars = {} config = ConfigParser.ConfigParser() self.log.debug("".join(["Reading: ", str(filename)])) config.read(filename) for section in config.sections(): # Check for specific section, otherwise assume table if (section == 'database'): options = config.options(section) for option in options: self.dbvars[option]= str(config.get(section, option)) continue try: # Check if key "section" (for table) already exists if self.columns[section]: e = "".join([ "Table '", section, "' has already been set.", "Redundant section in configuration file.", "Skipping. Please check dbvars.cfg" ]) self.log.error(e) # "Table" key doesn't exist, create it as a list except (AttributeError, KeyError, IndexError): self.columns[section] = [] # Start loading list with the sections values options = config.options(section) for option in options: self.columns[section].append(str(config.get(section, option))) return True except Exception, e: raise self.log.debug("".join(['dbhandler._set_db_varsDone: ', e])) return False def _cleanMysqlInsertString(self, _list, _quotes = False): """ Takes a list of strings, and formats then into a single string which meets format for a MySQL insert statement """ if checklist(_list): # If is a list result = "(" # Start result to be returned for _item in _list: # Strip carrage returns from _item _item = str(_item) _item = "".join(c for c in _item if c not in "\n\r") # If a number, strip spaces try: if ((int(_item)) or (float(_item))): _item = "".join(c for c in _item if not re.match("\s", c)) except ValueError: # Fails if _item has string chars pass # String escape _item = _item.encode("string_escape") # Add quotes if needed if _quotes: _item = _item.center(len(_item)+2, "'") # Add item to the final return result = "".join([result, _item, ","]) result = result[0:(len(result)-1)] # Remove last comma result = "".join([result, ")"]) # Add last paren return result # Raise error if _list is not a list else: e = "".join(["'dbhandler._cleanMysqlInsertString' ", "parameter is not a list."]) raise TypeError(e) def _verify_connection(self): # try: self.log.debug("".join(['Checking DB connection to database: ', str(self.dbvars['database'])])) self.conn.query("""show tables;""") test = self.conn.use_result() test = test.fetch_row() self.log.debug("Query result: " + str(test)) return True # except Exception, e: # e = "".join(['DBPassThroughException:', str(e)]) # self.lasterr = error(self, e, getmembers(self), stack()) # return False def writeRowsBuffer(self, table): """ writeRowsBuffer(table) DESCRIPTION: Forms and writes an SQL statement to place data into "table" The columns for the table are stored in non-volatile parameter self.columns[
]. This is loaded at __init__ The values for the row are stored in dynamic variable self.values[
]. These are created dynamically as script runs by the method loadRowsBuffer(table, values).. """ try: # Assemble write query # Check params _columns = self._cleanMysqlInsertString(self.columns[table]) _values = "" try: if len(self.values[table]) < 1: self.log.info("self.values buffer is empty.") return #End method execution except (AttributeError, KeyError, ValueError): self.log.info("self.values buffer is empty.") return #End method execution # Parse the values and add them to an SQL statement string for _valuelist in self.values[table]: _valuestring = self._cleanMysqlInsertString( _valuelist, _quotes = True) _values = "".join([_values,",", _valuestring]) _values = _values[1:] # Pop leading comma off _sql = "".join(["INSERT INTO ", str(table), " ", _columns, " ", "VALUES ", _values, ';']) self.log.debug("Attempting insert with:") self.log.debug(_sql) # Only turn on for deep debugging # self.log.debug(_sql) try: with self.conn: cur = self.conn.cursor() cur.execute(_sql) self.log.debug("Successful.") return True except Exception, e: self.log.debug("".join(["Failed with", str(e)])) if (("duplicate" in str(e).lower()) and overwrite): self.log.error("".join(["Duplicate entry(s) found. ", "Overwrite is disabled in this script. ", "Continuing without writing."])) # self.log.debug("Overwrite true, attempting update...") # where1 = str(columns[0]) # where2 = str(values[0]) # if self.updateRow( # columns, values, table, # where = "".join([where1,"=",where2]), # prikey = where1 # ): # self.log.debug("Successful.") # return True # else: # self.log.debug("Failed.") return False else: # A method for hanlding duplicate overwrites # needs to be created raise except Exception, e: e = "".join(["PassThroughException: Failed attempting to modify ", "database row: ", str(e)]) self.lasterr = self.error.handle(e, getmembers(self), stack()) def loadRowsBuffer(self, table = None, values = None): """ loadRowsBuffer(table, values) DESCRIPTION: Creates a list of lists "self.values[table]" as a buffer. Each list within self.values[table] is a row of data saved for "table". When 'table' is sent to self.writeRowsBuffer(table), each list found within self.values[table] is written as a row to 'table'. """ #### ENABLE BELOW FOR CODING DEBUG ### # Check that keys for table have been set # try: # self.log.debug("".join(["Attempting loadRowsBuffer with '", # str(table), "with:"])) # self.log.debug("".join(["Values: (", # str(len(values)), ")", str(values)])) # # except (AttributeError, KeyError): # e = "".join(["FatalScriptError: dbahndler.write: ", # "Table '", str(table), "' is not in self.columns.", # "Variables for dbahndler.columns['", # str(table), "' may not have been set." ]) # self.lasterr = self.error.handle(e,inspect.getmembers(self),inspect.stack()) #### ENABLE ABOVE BELOW FOR CODING DEBUG ### # Check that values passed is a list that contains at least one element try: if not checklist(values): raise TypeError values[0] # Errors if list is empty except (AttributeError, TypeError, IndexError): e = "".join(["FatalScriptError: dbahndler.write: ", "Variable 'values' passed does not appear to be a ", "valid list type or is of zero length. ." ]) try: # Check if key "table" already exists self.values[table] # "Table" key doesn't exist, create it as a list except (AttributeError, KeyError, IndexError): self.values[table] = [] # Ensure the columns and values are lists of same length # NOTE: Checking PASSED values not self.values[table] if self._checklistlengths(self.columns[table], values): # If no error, append self.values[table] # NOTE: This creates a list of list, don't uuse # self.values[table] = values # self.values[table] will end up as a list of lists, with # each list being a row to be added self.values[table].append(values) else: self.log.error("".join(["The length of the value list ", "does not appear to match the columns. ", "Skipping this value set."])) self.log.error("".join([str(values)])) if __name__ == "__main__": d = db( log = None, )