def insert(cursors: Cursors, run_id: int, start_ts: datetime.date, condition: ConditionIdentificationRule) -> int: """ Inserts a row into ut_condition_run_step :param cursors Cursors :param run_id: int - ut_condition_run_id :param start_ts: datetime.date - timestamp start of test time :param condition: dict of current condition :return: ut_condition_run_step_id """ ut_condition_id = UtConditionPersistence.fetch_or_insert( cursors, condition) sql = """ insert into ut_condition_run_step ( ut_condition_id, ut_condition_run_id, start_ts ) values ( %(UT_CONDITION_ID)s, %(UT_CONDITION_RUN_ID)s, %(START_TS)s ) """ binds = { "UT_CONDITION_ID": ut_condition_id, "UT_CONDITION_RUN_ID": run_id, "START_TS": start_ts } returning = "returning ut_condition_run_step_id" cursor = cursors.get_cursor(sql) ut_condition_run_step_id = cursor.execute(sql, binds, returning=returning) return ut_condition_run_step_id
def insert(cursors: Cursors, condition: ConditionIdentificationRule) -> None: """ inserts into ut_condition unless there is an exact match in which case it returns the matching record id """ ut_condition_insert = """ insert into ut_condition ( condition_name, table_name, condition_msg, sql_text, narrative, condition_severity, condition_format_str ) values ( %(rule_name)s, %(table_name)s, %(msg)s, %(sql_text)s, %(narrative)s, %(severity)s, %(format_str)s ) """ cur = cursors.get_cursor(ut_condition_insert) if 'narrative' not in condition: condition['narrative'] = None retval = cur.execute(ut_condition_insert, condition, returning="returning ut_condition_id") return retval
def start_run(cursors: Cursors, binds: Dict[str, object]) -> int: """ :param cursors - Cursors from :param binds: dictionary of bind variables for condition statements :return: ut_condition_run_id """ pretty_binds = json.dumps(binds, indent=4, cls=JsonEncoder) logger.info("starting run with binds %s" % pretty_binds) sql = "insert into ut_condition_run (" \ " start_ts" \ ") values (" \ " %(start_ts)s" \ ")" returning = "returning ut_condition_run_id" cursor = cursors.get_cursor(sql) now = datetime.datetime.now() retval = cursor.execute(sql, {"start_ts": now}, returning=returning, verbose=True) # retval = None # for row in rows: # retval = [0] parms_sql = """ insert into ut_condition_run_parm ( ut_condition_run_id, parm_nm, parm_type, parm_value_str ) values ( %(UT_CONDITION_RUN_ID)s, %(PARM_NM)s, %(PARM_TYPE)s, %(PARM_VALUE)s )""" parms_cursor = cursors.get_cursor(parms_sql) for k, v in binds.items(): print("k: %s v: %s type v %s" % (k, v, type(v))) parms = { "UT_CONDITION_RUN_ID": retval, "PARM_NM": k, "PARM_TYPE": str(type(v)), "PARM_VALUE": str(v) } parms_cursor.execute(parms_sql, parms) return retval
def select_exact(cursors: Cursors, condition: ConditionIdentificationRule) -> int: """ :param cursors: a pdsutil.Cursors :param condition: pdsutil.ConditionIdentificationRule :return: the primary key of the existing or created ut_condition record """ """ Returns the primary key of a row in ut_condition If a row is not in the table that matches the condition """ sql = """ select ut_condition_id from ut_condition where condition_name = %(rule_name)s and table_name = %(table_name)s and condition_msg = %(msg)s and sql_text = %(sql_text)s and (narrative = %(narrative)s or narrative is null and %(narrative)s is null) and condition_severity = %(severity)s and condition_format_str = %(format_str)s """ """ --and --corrective_action = %(corrective_action)s""" cur = cursors.get_cursor(sql) if "narrative" not in condition: condition["narrative"] = None rows = cur.execute(sql, condition) retval = None for row in rows: retval = row[0] return retval
def insert(cursors: Cursors, ut_condition_run_step_id: int, primary_key: int, message: str) -> None: """ Inserts a row into ut_condition_row_msg :param cursors: :param ut_condition_run_step_id: :param primary_key: :param message: :return: """ parm_util.ensure("ut_condition_run_set_id", ut_condition_run_step_id, int) parm_util.ensure("primary_key", primary_key, int) parm_util.ensure("message", message, str) binds = { "UT_CONDITION_RUN_STEP_ID": ut_condition_run_step_id, "PRIMARY_KEY": primary_key, "MSG": message } ut_table_row_msg_sql = """ insert into ut_condition_row_msg ( ut_condition_run_step_id, table_pk, condition_msg ) values ( %(UT_CONDITION_RUN_STEP_ID)s, %(PRIMARY_KEY)s, %(MSG)s ) """ insert_cursor = cursors.get_cursor(ut_table_row_msg_sql) insert_cursor.execute(ut_table_row_msg_sql, binds)
class ConditionIdentification: """ Runs a series of sql statements that return as the first column, the primary key of a table being examined an 0 or more fields used in formatting a message. """ insert_cursor = None rules = {} messages = [] def __init__(self, conn, rules:List[Dict[str,str]], verbose=False): """ :param conn: A database connection :param rules: A list of ConditionIdentification :param verbose: """ self.connection = conn self.cursors = Cursors(self.connection) self.rules = rules self.verbose = verbose logger.debug("instantiated") def add_rule(self, rule:Dict[str,str]): self.rules[rule.rule_name] = rule def print_messages(self): for message in self.messages: print(message) def process(self, binds:Dict[str,object], verbosity=0): """ :param self: :param binds: dictionary of bind names and variables :param verbosity: int logging level :return: List of str - generated messages Each invocation starts a new run. """ # * A row is inserted into ut_condition_run TODO get to work in sphinx # * The bind variables are inserted into ut_condition_run_parm # * for each rule # * The rule is retrieved from ut_condition and created if necessary. # * For each row returned # * a row is inserted into ut_condition_row_msg # * A row is inserted into ut_condition_run_step self.messages = [] ut_condition_run_id = UtConditionRunPersistence.start_run(self.cursors, binds) # Record the start of the run rule_cursor = self.cursors.get_cursor("rule_sql") for rule in self.rules: rule_name = rule["rule_name"] start_ts = datetime.datetime.now() condition_sql = rule["sql_text"] rows = rule_cursor.execute(condition_sql, binds) run_step_id = UtConditionRunStepPersistence.insert( self.cursors,ut_condition_run_id,start_ts,rule) row_count = 0 for row in rows: row_count += 1 msg = rule["format_str"] % row # TODO LITERAL try: #def insert(cursors: Cursors, ut_condition_run_step_id: int, primary_key: int, message: str) -> None: UtConditionRowMsgPersistence.insert(self.cursors,run_step_id, row[0], msg) except Exception as e: raise Exception("While processing\n%s\n%s" % (condition_sql, str(e))) self.messages.append(msg) # if verbosity > 0: # logger.info(msg) end_ts = datetime.datetime.now() if verbosity > 0: #{: < 7}{: < 51}{: < 25}\n logger.info("Rule " + "{:<30}".format(rule_name) + " run_step: " + str(run_step_id).rjust(6) + " Number of exceptions: " + "{:>6}".format(row_count)) # (rule_name, run_step_id, row_count))) # logger.info("Rule %s run_step: %s Number of exceptions: %s" % self.connection.commit() self.cursors.close() #self.connection.close() # if verbosity > 2: # self.print_messages() return self.messages
class CdsDataloader: """ Loads a CDS format file into ETL tables """ def __init__(self): self.logger = logging.getLogger("__name__") yaml_file_name = get_file_path("config/etl_persistence_sql.yaml") self.statements = SqlStatements.from_yaml(yaml_file_name).statements self.cursors = None self.sql_statements = { "CD": self.statements["etl_customer_insert"], "CT": self.statements["etl_customer_tot_insert"], "IR": self.statements["etl_inventory_insert"], "IT": self.statements["etl_inventory_tot_insert"], "SA": self.statements["etl_sale_insert"], "AT": self.statements["etl_sale_tot_insert"], } def process(self, filename: str, conn, distributor_cd: str, validate: bool) -> None: """ :param filename: name of CDS formatted data file :param conn: A database connection :param distributor_cd - The reporting organization, must be in org :param validate: True - run validations # TODO should :return: """ start_time = time.time() self.cursors = Cursors(conn) reader = CdsFileReader(filename) # Initialize run file_id = self.initial_insert({"ORG_CD": distributor_cd}) line_count = 0 for line_nbr, record_type, record, binds in reader.read_line(): binds["ETL_FILE_ID"] = file_id binds["LINE_NUMBER"] = line_nbr if "EXTENDED_NET_AMT" in binds: binds["EXTENDED_NET_AMT"] = binds["EXTENDED_NET_AMT"] / 100 sql = self.sql_statements[record_type]["sql"] self.cursors.get_cursor(sql).execute(sql, binds) line_count += 1 reader.close() conn.commit() self.cursors.close() elapsed_time = time.time() - start_time self.logger.info( "loaded '%s' file id: %s record count %s in %s seconds" % (filename, file_id, line_count, elapsed_time)) def initial_insert(self, binds): logger = logging.getLogger(__name__ + ":initial_insert") etl_file_initial_insert = self.statements["etl_file_initial_insert"][ "sql"] returning = self.statements["etl_file_initial_insert"]["returning"] org_sql = "select org_id from org where org_cd = %(ORG_CD)s" cursor = self.cursors.get_cursor(org_sql) rows = cursor.execute(org_sql, binds) row_found = False for row in rows: row_found = True if not row_found: raise Exception("no data found for " + str(binds) + " in " + org_sql) cursor = self.cursors.get_cursor(etl_file_initial_insert) etl_file_id = cursor.execute(etl_file_initial_insert, binds, returning=returning) logger.info("returning etl_file_id " + str(etl_file_id)) return etl_file_id