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 __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 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 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 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 test_fetch_or_insert(self):
connection = ConnectionHelper().get_named_connection("test")
cursors = Cursors(connection)
binds = {
"rule_name": "TEST_COND",
"table_name": "etl_sale",
"msg": "I don't know",
"sql_text":
"select id from etl_sale where etl_file_id = %(ETL_FILE_ID)",
"narrative": "huh",
"severity": 3,
"format_str": "id % is %s",
# "CORRECTIVE_ACTION" : "Fix it"
}
id = UtConditionPersistence.fetch_or_insert(cursors, binds)
connection.commit()
assert (id is not None)
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