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