class StatementRunner():
logger = logging.getLogger("StatementRunner")
# TODO document
def __init__(self,
connection,
statements,
continue_on_error=False,
show_statements=False):
# types statements list of StatementHelper
self.connection = connection
self.statements = statements
self.continue_on_error = continue_on_error
self.cursor = CursorHelper(connection.cursor())
# logging.info("continue_on_error %s" % (continue_on_error))
# logging.info("number of statements: %s" % (len(self.statements)))
def process(self, binds):
for stmt in self.statements:
logging.info(stmt.description)
if self.continue_on_error:
try:
self.cursor.execute(stmt, binds)
self.connection.commit()
except Exception as e:
self.connection.rollback()
print(stmt + str(e))
raise e
else:
self.cursor.execute(stmt.sql, binds) # stmt is StatementHelper
self.connection.commit() # TODO is this a good idea?
class UtProcess:
def __init__(self, connection):
self.connection = connection
self.process_cursor = CursorHelper(connection.cursor())
self.step_cursor = CursorHelper(connection.cursor())
def insert_process(self, binds):
insert_sql = """
insert into ut_process_status (
schema_nm,
process_nm,
thread_nm,
process_run_nbr,
status_msg,
status_id,
status_ts,
ignore_flg
) values (
%(schema_nm)s,
%(process_nm)s,
%(thread_nm)s,
%(process_run_nbr)s,
%(status_msg)s,
%(status_id)s,
%(status_ts)s,
%(ignore_flg)s
) """
self.process_cursor.execute(insert_sql, binds)
def get_process_for_id(self, id):
sql = "select * from ut_process where ut_process_id"
def insert_process_step(self, binds):
sql = """
insert into ut_process_log (
ut_process_status_id,
log_seq_nbr,
log_msg_id,
log_msg,
log_msg_ts,
caller_name,
line_nbr,
call_stack,
log_level
) values
(
%(ut_process_status_id)s,
%(log_seq_nbr)s,
%(log_msg_id)s,
%(log_msg)s,
%(log_msg_ts)s,
%(caller_name)s,
%(line_nbr)s,
%(call_stack)s,
%(log_level)s
)
"""
self.step_cursor.execute(sql, binds)
def test_sqlite_returning(self):
logger = logging.getLogger(__name__ + ":test_sqlite_returning")
drop_sql = "drop table if exists a"
create_sql = "create table a (b serial primary key, c numeric)"
insert_sql = "insert into a (c) values (%(c)s)"
returning_text = "returning b"
ch = ConnectionHelper(ConnectionHelperTest.yaml_file_path)
connection = ch.get_named_connection("sqlite3_mem")
cursor = CursorHelper(connection.cursor())
cursor.execute(drop_sql)
cursor.execute(create_sql)
new_id = cursor.execute(insert_sql, {"c": 3}, returning=returning_text)
self.assertEqual(1, new_id)
def create_schema(self) -> None:
if self.test_schema:
cursor = CursorHelper(self.connection.cursor())
try:
cursor.execute("create schema %s" % self.test_schema)
except Exception as e:
logger.warning(e)
self.connection.rollback()
logger.warning("create schema %s failed, connection rolled back " % self.test_schema)
set_schema_sql = "set schema '%s'" % self.test_schema
logger.info("about to %s" % set_schema_sql)
cursor.execute(set_schema_sql)
logger.info("should be new schema")
cursor.close()
def process(self,
conn,
binds: Dict[str, object],
rerun: bool = False,
verbose=False) -> None:
"""
:param conn:
:param binds: etl_file_id numeric
:param rerun: if True, deletes all the other runs
:return:
"""
self.logger.info("Connection is " + str(conn))
self.logger.info("Rerun %s" % rerun)
assert len(binds) == 1
etl_file_id = binds["ETL_FILE_ID"]
assert isinstance(etl_file_id, int)
# if rerun:
# self.delete_run(conn, binds)
sql = "select count(*) from etl_file where etl_file_id = %(ETL_FILE_ID)s"
cursor = CursorHelper(conn.cursor())
row_count = None
rows = cursor.execute(sql, binds)
for row in rows:
row_count = row[0]
if row_count == 0:
raise Exception("No such etl_file_id %s" % etl_file_id)
processor = ConditionIdentification(conn, self.rules)
processor.process(binds, verbosity=3)
def test_wrapped_cursor_with_return(self):
cursor = CursorHelper(self.connect.cursor())
returning_id = cursor.execute(self.insert,
self.binds,
returning="returning etl_sale_id")
logger.info("returning id is %s " % returning_id)
rows = cursor.execute("select count(*) from etl_sale")
for row in rows:
value = row[0]
rows = cursor.execute("select max(etl_sale_id) from etl_sale")
for row in rows:
actual_id = row[0]
logger.debug("actual_id %s" % actual_id)
self.assertEqual(actual_id, returning_id)
logger.info("value is %s" % value)
def test_no_rows(self, sql) -> None:
cursor = CursorHelper(self.connection.cursor())
if self.verbose:
logger.info("testing: %s" % sql)
rows = cursor.execute(sql)
for row in rows:
assert row[0] == 0
cursor.close()
def to_sqlite(self, table_name, connection=None, verbose=False):
"""
Creates a memory sqlite database, creates a table based on
column names and types inferred by column data types
:param table_name:
:param connection if a connection is specified the table will be created using the connection
:return: a sqlite connection with a populated table with the specified table_name
If no connection is specified a sqlite3 ":memory:" database will be created
"""
logger = logging.getLogger(__name__ + ":to_sqlite")
if connection is None:
connection = sqlite3.connect(":memory:")
cursor = CursorHelper(connection.cursor())
ddl_cols = self.get_ddl_columns()
ddlgenner = DdlUtil(dialects.DIALECT_POSTGRES) # TODO use dialects
sql = ddlgenner.get_create_table(table_name, ddl_cols)
cursor.execute(sql)
insert_sql = ddlgenner.get_insert(table_name, ddl_cols)
logger.debug("\n%s" % insert_sql)
for row in self.rows:
binds = {}
col_idx = 0
for col_name in self.column_names:
val = row[col_idx]
if val is not None and isinstance(val,decimal.Decimal):
val = float(val)
binds[col_name] = val
if verbose:
logging.debug("col_name %s value %s type %s" % (col_name,row[col_idx], str(type(row[col_idx]))))
col_idx += 1
cursor.execute(insert_sql, binds)
connection.commit()
sql = "select count(*) from %s" % table_name
rows = cursor.execute(sql, None)
rowcount = None
for row in rows:
rowcount = row[0]
Dataset.logger.debug("rowcount %s" % rowcount)
return connection
def unload_all(self):
cursor = CursorHelper(self.connection.cursor())
sql = "select etl_file_id from etl_file"
rows = cursor.execute(sql)
for row in rows:
etl_file_id = row[0]
# self.process(etl_file_id, "../pdssr_testdata/%s.cds" % etl_file_id, False)
self.process(etl_file_id, "/tmp/python/%s.cds" % etl_file_id, False)
def unload_all(cls):
sql = "select etl_file_id from etl_file"
cursor = CursorHelper(cls.connection.cursor())
rows = cursor.execute(sql)
for row in rows:
unloader = CdsUnload(cls.connection)
etl_file_id = row[0]
unloader.process(etl_file_id, "/tmp/%s.cds" % etl_file_id, False)
def check_customers(cls):
sql = "select etl_file_id, count(*)from etl_file where etl_file_id = %(ETL_FILE_ID)s group by etl_file_id"
cursor = CursorHelper(cls.connection.cursor())
binds = {"ETL_FILE_ID": 1}
rows = cursor.execute(sql, binds)
count = 0
for row in rows:
row_count = row[1]
count += row_count
print("etl_customer count: %s %s" % (row[0], row_count))
assert (count > 1)
def conditions(cls):
logger.info("running conditions")
processor = CdsDataloadConditions()
file_id_sql = "select etl_file_id from etl_file"
cursor = CursorHelper(cls.connection.cursor())
rows = cursor.execute(file_id_sql)
for row in rows:
etl_file_id = row[0]
binds = {"ETL_FILE_ID": etl_file_id}
logger.info("conditions for %s" % etl_file_id)
processor.process(cls.connection, binds=binds)
cursor.close()
def test_one(self):
"""
This test ensures that a returning clause returns # TODO test under sqlite
:return:
"""
cursor = CursorHelper(self.connection.cursor())
stmt = self.statements["etl_file_initial_insert"]
# self.assertEquals(stmt["name"],"etl_file_initial_insert")
sql = stmt["sql"]
returning = stmt["returning"]
self.assertEqual(stmt["returning"], "returning etl_file_id")
binds = {"ORG_CD": "TX EXOTIC"}
id = cursor.execute(sql, binds=binds, returning=returning, verbose=False)
self.assertIsNotNone(id)
def read_sql(sql, connection, params):
retval = Dataset()
cursor = CursorHelper(connection.cursor())
rows = cursor.execute(sql, params)
retval.column_names = []
for column in cursor.description:
retval.column_names.append(column[0])
for column in retval.column_names:
Dataset.logger.debug("column: %s" % column)
retval.rows = []
for row in rows:
retval.rows.append(row)
cursor.close()
return retval
def from_sql(connection,sql:str, params:Dict[str,object]):
retval = Dataset()
cursor = CursorHelper(connection.cursor())
rows = cursor.execute(sql, params)
retval.column_names = []
for column in cursor.description:
retval.column_names.append(column[0])
for column in retval.column_names:
Dataset.logger.debug("column: %s" % column)
retval.rows = []
for row in rows:
retval.rows.append(row)
cursor.close()
retval.determine_data_types()
return retval
def render_cursor(self, workbook, connection, binds):
"""
Renders the sheet
:param workbook:
:param connection:
:param binds:
:return:
"""
cursor = CursorHelper(connection.cursor())
logger.info("sql: " + str(self.sql)) # TODO logging in Dexterous
logger.info("binds: " + str(binds))
rows = cursor.execute(self.sql, binds)
# if self.column_names is None:
# self.populate_column_names_and_headers(cursor.description)
self.populate_dataset_column_index(cursor.description)
self.infer_metadata(cursor.description)
self.render_data(workbook, rows)
def seed_distributor(self):
self.cursor.execute("insert into org(org_cd) values ( %(ORG_CD)s)",
{"ORG_CD": "EXOTICTX"})
print('EXOTICTX inserted into org')
verify_cursor = CursorHelper(self.connection.cursor())
rows = verify_cursor.execute('select * from org order by org_cd')
for row in rows:
print('org_id ' + str(row[0]) + ' org_cd ' + row[1])
self.cursor.execute(
"insert into org_distrib(org_id,distrib_id) "
"select org_id,'EXOTICTX' "
"from org "
"where org_cd = %(ORG_CD)s", {"ORG_CD": "EXOTICTX"})
self.cursor.execute(
"insert into org_datafeed(org_id) "
"select org_id "
"from org "
"where org_cd = %(ORG_CD)s", {"ORG_CD": "EXOTICTX"})
def wrapped_cursor(self):
cursor = CursorHelper(self.connect.cursor())
cursor.execute(self.insert, self.binds, verbose=True)
class Prepost:
"""
Pre-post
* Attempt to identify the product_id based on
"""
def __init__(self, connection):
self.connection = connection
self.cursor = CursorHelper(connection.cursor())
self.binds = {}
fdir = os.path.dirname(os.path.realpath(__file__))
logger.debug("fdir %s" % fdir)
meta_file = os.path.join(fdir, 'config/post_dml.yaml')
logger.debug("using meta_file %s" % meta_file)
self.statements = SqlStatements.from_yaml(meta_file).statements
def process(self, etl_file_id: int):
self.binds = {"ETL_FILE_ID": etl_file_id}
self.update_etl_sale_by_gtin()
self.product_nomen_insert()
effective_date = self.get_max_effective_date()
self.binds["EFFECTIVE_DATE"] = effective_date
self.vp_distributor_customer_insert()
self.connection.commit()
def run_statement(self, statement_name):
stmt = self.statements[statement_name]
rows = self.cursor.execute(stmt['sql'], self.binds)
for row in rows:
count = row[0]
logger.debug("%s updated %s" % (statement_name, count))
def run_query(self, statement_name):
stmt = self.statements[statement_name]
rows = self.cursor.execute(stmt['sql'], self.binds)
nt = namedtuple(statement_name,
[i[0] for i in self.cursor.description])
rows = []
for row in map(nt._make, self.cursor.fetchall()):
print(row)
rows.append(row)
return rows
def update_etl_sale_by_gtin(self):
self.run_statement("etl_sale_update")
def product_nomen_insert(self):
self.run_statement("product_nomen_insert")
def get_max_effective_date(self):
rows = self.run_query("effective_date_select")
row = rows[0]
print(row)
return rows[0].max_invoice_dt
def vp_distributor_customer_insert(self):
self.run_statement("vp_distributor_customer_insert")
class SeedSalesReportingDatabase:
def __init__(self, connection, verbose=False):
"""
:param connection: a database connection
"""
self.connection = connection
self.cursor = CursorHelper(self.connection.cursor())
logger = logging.getLogger("SeedSalesReportingDatabase")
self.verbose = verbose
def seed_manufacturers(self):
"""
Populates the tables org and org_mfr with CDS manufacturer identifiers
:return: None
"""
# TODO load from resources/cds_manufactures.csv
mfrs = [
("0000000020", "F-L", "Frito-Lay"),
("0000000030", "GM", "General Mills"),
("0000000040", "HVEND", "Hershey Vending"),
("0000000050", "HFUND", "Hershey Fund Raising"),
("0000000055", "HCONC", "Hershey Concession"),
("0000000060", "SNYDERS", "Snyder's of Hanover"),
("0000000080", "KELLOGG", "Kellogg, Keebler"),
("0000000115", "KARS", "Kar Nut Product (Kar's)"),
("0000000135", "MARS", "Mars Chocolate "),
("0000000145", "POORE", "Inventure Group (Poore Brothers)"),
("0000000150", "WOW", "WOW Foods"),
("0000000160", "CADBURY", "Cadbury Adam USA, LLC"),
("0000000170", "MONOGRAM", "Monogram Food"),
("0000000185", "JUSTBORN", "Just Born"),
("0000000190", "HOSTESS", "Hostess, Dolly Madison"),
("0000000210", "SARALEE", "Sara Lee"),
]
for mfr in mfrs:
binds = dict(zip(("CDS_MFR_ID", "ORG_CD", "ORG_NM"), mfr))
if self.verbose:
logging.info("binds: %s", binds)
self.cursor.execute(
"insert into org (org_cd, org_nm) values (%(ORG_CD)s, %(ORG_NM)s)",
binds)
self.cursor.execute(
"""
insert into org_mfr(
org_id,cds_mfr_id
)
select org_id, %(CDS_MFR_ID)s
from org
where org_cd = %(ORG_CD)s""", binds)
def seed_distributor(self):
self.cursor.execute("insert into org(org_cd) values ( %(ORG_CD)s)",
{"ORG_CD": "EXOTICTX"})
print('EXOTICTX inserted into org')
verify_cursor = CursorHelper(self.connection.cursor())
rows = verify_cursor.execute('select * from org order by org_cd')
for row in rows:
print('org_id ' + str(row[0]) + ' org_cd ' + row[1])
self.cursor.execute(
"insert into org_distrib(org_id,distrib_id) "
"select org_id,'EXOTICTX' "
"from org "
"where org_cd = %(ORG_CD)s", {"ORG_CD": "EXOTICTX"})
self.cursor.execute(
"insert into org_datafeed(org_id) "
"select org_id "
"from org "
"where org_cd = %(ORG_CD)s", {"ORG_CD": "EXOTICTX"})
def process(self):
"""
Populates all the prerequisite tables
:return:
"""
self.seed_manufacturers()
self.seed_distributor()
class CdsUnload:
"""
Extracts the data from the following tables:
etl_customer
etl_customer_tot
etl_sale
etl_sale_tot
etl_inventory
etl_inventory_tot
The records are retrieved by the original order and the output file should be
identical to the input file.
Extracting without preserving line number order is 3 to 4 times as fast.
"""
def __init__(self, connection, calc_totals: bool = False):
"""
:param connection:
:param calc_totals:
"""
# """
# Creates a CDS load format file
#
# :param connection:
# :param file_name:
# :param etl_file_id:
# :param by_line_number boolean
# True emit in the original sequence from which this was loaded
# False emit customers, customer_total, sales, sales_total, inventory, inventory_total
# :param calc_totals - True - compute from the associated records False - emit what is stored in the tables
#
# """
self.connection = connection
print ("connection %s " % connection)
self.record_defs = CdsReportingMetadata().record_defs
#
self.logger = logging.getLogger(__name__)
# cursors
self.line_number_cursor = None
self.customer_cursor = None
self.customer_total_cursor = None
self.inventory_cursor = None
self.inventory_total_cursor = None
self.sale_cursor = None
self.sale_total_cursor = None
#
self.customer_column_names = None
self.customer_total_column_names = None
self.sale_column_names = None
self.sale_total_column_names = None
self.inventory_column_names = None
self.inventory_total_column_names = None
self.outfile = None # An open file to be written to
def get_line_numbers(self, etl_file_id: int):
"""
retrieve all the records in the load from the origina
:param etl_file_id:
:return:
"""
if self.line_number_cursor is None:
self.line_number_cursor = CursorHelper(self.connection.cursor())
rows = self.line_number_cursor.execute("""
select table_name, id, line_number
from
(
select 'etl_customer' table_name, etl_customer_id id, line_number
from etl_customer
where etl_file_id = %(ETL_FILE_ID)s
union
select 'etl_sale', etl_sale_Id, line_number
from etl_sale
where etl_file_id = %(ETL_FILE_ID)s
union
select 'etl_inventory', etl_inventory_id, line_number
from etl_inventory
where etl_file_id = %(ETL_FILE_ID)s
union
select 'etl_inventory_tot', etl_file_id, line_number
from etl_inventory_tot
where etl_file_id = %(ETL_FILE_ID)s
union
select 'etl_customer_tot', etl_file_id, line_number
from etl_customer_tot
where etl_file_id = %(ETL_FILE_ID)s
) as by_line_number
order by line_number
""",
{"ETL_FILE_ID": etl_file_id})
return rows
def emit(self, string: str) -> None:
"""
writes the string to outfile
:param string:
:return:
"""
REQUIRED_LEN = 170
if len(string) != REQUIRED_LEN:
raise Exception("required len %s actual %s\n%s" % (REQUIRED_LEN, len(string), string))
self.outfile.write(string)
self.outfile.write("\n")
# print(string)
def emit_sale(self, data_map: Dict[str, object]):
# Union(str, int, datetime.datetime)]):
"""
Defines filler bind variables not return from the database and
converts Decimal values to int where appropriate so that int formats will work
The writes to the output file
:param data_map: Bind parameters
:return:
"""
data_map["FILLER_12"] = " "
data_map["FILLER"] = " "
data_map["RECORD_TYPE"] = "SA"
data_map["EXTENDED_NET_AMT"] = int(data_map["EXTENDED_NET_AMT"] * 100)
data_map["CASES_SHIPPED"] = int(data_map["CASES_SHIPPED"])
data_map["BOXES_SHIPPED"] = int(data_map["BOXES_SHIPPED"])
data_map["UNITS_SHIPPED"] = int(data_map["UNITS_SHIPPED"])
record = field_metadata.format_line(self.record_defs["sales"], data_map)
try:
self.emit(record)
except Exception as e:
msg = ("binds %s\n, record: %s exception: %s" % (data_map, record, e))
def unload_sale(self, id, by_etl_sale_id=True):
if self.sale_cursor is None:
self.sale_cursor = CursorHelper(self.connection.cursor())
if by_etl_sale_id:
sql = "select * from etl_sale where etl_sale_id = %(ETL_SALE_ID)s"
rows = self.sale_cursor.execute(sql, {"ETL_SALE_ID": id})
else:
sql = "select * from etl_sale where etl_file_id = %(ETL_FILE_ID)s"
rows = self.sale_cursor.execute(sql, {"ETL_FILE_ID": id})
if self.sale_column_names is None:
self.sale_column_names = [i[0].upper() for i in self.sale_cursor.description]
# print ("cursor columns: %s" % self.sale_column_names) #TODO
for row in rows:
data_map = dict(zip(self.sale_column_names, row))
self.emit_sale(data_map)
def emit_inventory(self, data_map: Dict[str, object]) -> None:
# Union(str, int, datetime.datetime)]) -> None:
data_map["RECORD_TYPE"] = "IT"
data_map["CASES"] = int(data_map["CASES"])
data_map["BOXES"] = int(data_map["BOXES"])
data_map["UNITS"] = int(data_map["UNITS"])
data_map["FILLER"] = " "
if data_map["CASE_GTIN"] is None:
data_map["CASE_GTIN"] = "000000000000000"
outrec = field_metadata.format_line(self.record_defs["inventory"], data_map)
try:
self.emit(outrec)
except Exception as e:
msg = "binds: %s\n outrec: %s %s" % (data_map, outrec, e)
raise Exception(msg)
def unload_inventory(self, id: int, by_etl_inventory_id: bool = True) -> None:
"""
:param id: int - if by_etl_inventory_id is True, then etl_inventory_id else etl_file_id
:param by_etl_inventory_id: True - unload one record False - unload all records
:return:
"""
if self.inventory_cursor is None:
self.inventory_cursor = CursorHelper(self.connection.cursor())
sql = None
if (by_etl_inventory_id):
sql = "select * from etl_inventory where etl_inventory_id = %(ETL_INVENTORY_ID)s"
rows = self.inventory_cursor.execute(sql, {"ETL_INVENTORY_ID": id})
else:
sql = "select * from etl_inventory where etl_file_id = %(ETL_FILE_ID)s"
rows = self.inventory_cursor.execute(sql, {"ETL_FILE_ID": id})
if self.inventory_column_names is None:
self.inventory_column_names = [i[0].upper() for i in self.inventory_cursor.description]
for row in rows:
data_map = dict(zip(self.inventory_column_names, row))
self.emit_inventory(data_map)
def unload_customer_total(self, etl_file_id: int, compute: bool = False) -> None:
"""
Unload Customer Total
:param etl_file_id:
:param compute: True - compute from etl_customer records False - extract only if the record exists
:return: None
"""
if self.customer_total_cursor is None:
self.customer_total_cursor = CursorHelper(self.connection.cursor())
sql = "select * from etl_customer_tot where etl_file_id = %(ETL_FILE_ID)s"
rows = self.customer_total_cursor.execute(sql, {"ETL_FILE_ID": etl_file_id})
if self.customer_total_column_names is None:
self.customer_total_column_names = [i[0].upper() for i in self.customer_total_cursor.description]
# print ("cursor columns: %s" % self.customer_column_names)
for row in rows:
data_map = dict(zip(self.customer_total_column_names, row))
data_map["HEADER"] = "9999999999"
data_map["RECORD_TYPE"] = "CT"
data_map["FILLER_127"] = "".ljust(127)
data_map["CUSTOMER_COUNT"] = int(data_map["CUSTOMER_COUNT"])
data_map["FILLER_22"] = "".ljust(22)
outrec = field_metadata.format_line(self.record_defs["customer_total"], data_map)
self.emit(outrec)
def unload_sale_tot(self, etl_file_id: int, compute: bool = False) -> None:
"""
fetch etl_inventory
:param etl_file_id: the primary key of etl_file being unloaded
:param compute: True - compute from actual values False - extract only if record exists
:return: None
"""
# TODO Compute
if self.sale_total_cursor is None:
self.sale_total_cursor = CursorHelper(self.connection.cursor())
sql = "select * from etl_sale_tot where etl_file_id = %(ETL_FILE_ID)s"
rows = self.sale_total_cursor.execute(sql, {"ETL_FILE_ID": etl_file_id})
if self.sale_total_column_names is None:
self.sale_total_column_names = [i[0].upper() for i in self.sale_total_cursor.description]
for row in rows:
data_map = dict(zip(self.sale_total_column_names, row))
data_map["HEADER"] = "9999999999"
data_map["FILLER_28"] = "".ljust(28)
data_map["SALES_REC_CNT"] = int(data_map["SALES_REC_CNT"])
data_map["SUM_EXT_NET_AMT"] = int(data_map["SUM_EXT_NET_AMT"]) * 100
data_map["FILLER_86"] = "".ljust(86)
data_map["RECORD_TYPE"] = "ST"
outrec = field_metadata.format_line(self.record_defs["sales_total"], data_map)
self.emit(outrec)
def unload_inventory_tot(self, etl_file_id: int, compute: bool = False) -> None:
"""
Unload Customer Total
:param etl_file_id:
:param compute: boolean True -compute the totals rather than extract False - extract if exist else ignore
:return: None
"""
logger = logging.getLogger("unload_inventory_tot")
logger.setLevel(logging.DEBUG)
if self.inventory_total_cursor is None:
self.inventory_total_cursor = CursorHelper(self.connection.cursor())
sql = "select * from etl_inventory_tot where etl_file_id = %(ETL_FILE_ID)s"
rows = self.inventory_total_cursor.execute(sql, {"ETL_FILE_ID": etl_file_id})
if self.inventory_total_column_names is None:
self.inventory_total_column_names = [i[0].upper() for i in self.inventory_total_cursor.description]
for row in rows:
data_map = dict(zip(self.inventory_total_column_names, row))
for k, v in data_map.items():
print("inventory_tot k %s v %s type(v) %s" % (k, v, type(v)))
data_map["HEADER"] = "9999999999"
data_map["FILLER36"] = "".ljust(36)
data_map["RECORD_TYPE"] = "IT"
data_map["RECORD_CNT_REPORTED"] = int(data_map["RECORD_CNT_REPORTED"])
data_map["FILLER97"] = "".ljust(97)
logger.debug("record definition %s" % self.record_defs["inventory_total"])
logger.debug("data_map = %s" % data_map)
outrec = field_metadata.format_line(self.record_defs["inventory_total"], data_map, trace=True)
self.emit(outrec)
def emit_customer(self, data_map):
data_map["FILLER_00_05"] = " " # TODO should work in FieldMeta and not be required
data_map["CLASS_OF_TRADE"] = " "
data_map["FILLER_1"] = " "
data_map["RECORD_TYPE"] = "SA"
outrec = field_metadata.format_line(self.record_defs["customer"], data_map)
self.emit(outrec)
def unload_customer(self, id: int, by_etl_customer_id: bool) -> None:
if self.customer_cursor is None:
self.customer_cursor = CursorHelper(self.connection.cursor())
if by_etl_customer_id:
sql = "select * from etl_customer where etl_customer_id = %(ETL_CUSTOMER_ID)s"
rows = self.customer_cursor.execute(sql, {"ETL_CUSTOMER_ID": id})
else:
sql = "select * from etl_customer where etl_file_id = %(ETL_FILE_ID)s"
rows = self.customer_cursor.execute(sql, {"ETL_FILE_ID": id})
if self.customer_column_names is None:
self.customer_column_names = [i[0].upper() for i in self.customer_cursor.description]
# print ("cursor columns: %s" % self.customer_column_names)
for row in rows:
data_map = dict(zip(self.customer_column_names, row))
self.emit_customer(data_map)
def process_by_line_number(self, etl_file_id: int, file_name: str) -> None:
self.outfile = open(file_name, "w") # TODO use using
for table_name, id, line_number in self.get_line_numbers(etl_file_id):
# print ("table_name %s id: %s line_number: %s" % (table_name, id, line_number))
if table_name == 'etl_customer':
self.unload_customer(id, True)
elif table_name == "etl_sale":
self.unload_sale(id, True)
elif table_name == "etl_inventory":
self.unload_inventory(id, True)
elif table_name == "etl_customer_tot":
self.unload_customer_total(etl_file_id)
elif table_name == "etl_inventory_tot":
self.unload_inventory_tot(etl_file_id)
elif table_name == "etl_sale_tot":
self.unload_sale_tot(etl_file_id)
else:
raise Exception("unsupported record %s" % table_name)
self.outfile.close()
def process_by_table(self, etl_file_id: int, file_name: str) -> None:
self.outfile = open(file_name, "w") # TODO use using
self.unload_customer(etl_file_id, False)
self.unload_sale(etl_file_id, False)
self.unload_inventory(etl_file_id, False)
self.unload_customer_total(etl_file_id, False)
self.unload_inventory_tot(etl_file_id, False)
self.unload_sale_tot(etl_file_id, False)
self.outfile.close()
def process(self, etl_file_id: int, file_name: str, by_line_number: bool) -> None:
"""
:param etl_file_id: the etl_file.etl_file_id to extract
:param file_name: output file name
:param by_line_number: boolean - much slower but records in original order
:return: None
"""
if by_line_number:
self.process_by_line_number(etl_file_id, file_name)
else:
self.process_by_table(etl_file_id, file_name)
def unload_all(self):
cursor = CursorHelper(self.connection.cursor())
sql = "select etl_file_id from etl_file"
rows = cursor.execute(sql)
for row in rows:
etl_file_id = row[0]
# self.process(etl_file_id, "../pdssr_testdata/%s.cds" % etl_file_id, False)
self.process(etl_file_id, "/tmp/python/%s.cds" % etl_file_id, False)
from pdsutil.DbUtil import ConnectionHelper, CursorHelper
from pdsutil.Dataset import Dataset
import datetime
import logging
logging.basicConfig(level=logging.INFO)
sql = "select * from etl_sale where etl_file_id = %(ETL_FILE_ID)s"
binds = {"ETL_FILE_ID" : 201723}
connection = ConnectionHelper().get_named_connection("current")
cursor = CursorHelper(connection.cursor())
sales = Dataset.from_sql(connection,sql,binds)
# sales.to_csv(sys.stdout)
# to_csv
out_file = open("/tmp/sales.csv","w")
sales.to_csv(out_file)
# to_sqlite
sales.set_column_meta("curr_cd",str,3)
sales.set_column_meta("org_customer_id",str,10)
db = sales.to_sqlite("etl_sale",verbose=False)
cursor = CursorHelper(db.cursor())
rows = cursor.execute("select count(*) from etl_sale")
for row in rows:
print (row)
from pdsutil.DbUtil import ConnectionHelper, CursorHelper
import sqlite3
sql = """
insert into org_mfr(org_id,cds_mfr_id)
select org_id, %(CDS_MFR_ID)s
from org
where org_cd = %(ORG_CD)s
"""
binds = {"CDS_MFR_ID": "0000000020", "ORG_CD": "F-L", "ORG_NM": "Frito-Lay"}
connection = sqlite3.Connection("/tmp/wtf.dbf")
cursor = CursorHelper(connection.cursor())
cursor.execute(
"insert into org (org_cd, org_nm) "
"values (%(ORG_CD)s, %(ORG_NM)s)", binds)
cursor.execute(sql, binds)
sql = "select * from org_mfr"
rows = cursor.execute(sql)
for row in rows:
print(row)
class isql:
def __init__(self):
self.statements = SqlStatements.from_statement_list([])
print(self.statements)
self.statement = None
self.connection = None
self.cursor = None
self.binds = {}
def dump_statements(self):
print("about to dump")
print(yaml.dump(self.statements))
def connect(self, name: str):
self.connection = ConnectionHelper().get_named_connection(name)
self.cursor = CursorHelper(self.connection.cursor())
def load(self, filename: str):
self.statements = SqlStatements.from_yaml(filename).statements
self.list_statements()
def list_statements(self, verbose=False):
for i, v in enumerate(self.statements):
print("%s %s" % (i, v))
# for k in self.statements:
# print (k)
def bind_date(self, name, year, month, day):
self.binds[name] = datetime.datetime(year, month, day)
def use(self, number: int):
for i, k in enumerate(self.statements):
if i == number:
self.statement = self.statements[k]
def use_statement(self, name: str):
self.statement = name
def run(self, index=None):
if index == None:
print("stmt: %s" % self.statement)
else:
for i, k in enumerate(self.statements):
if i == index:
self.statement = self.statements[k]
print("k: %s sql: %s" % (k, self.statements["sql"]))
rows = self.cursor.execute(self.statement["sql"], self.binds)
for row in rows:
print(row)
def bind(self, name, value):
self.binds[name] = value
def execute(self, statement_name: str = None):
if self.connection is None:
print("use connect before use")
if statement_name:
sql = self.statements[statement_name]
elif self.statement is None:
sql = self.statements[statement_name]
else:
print("pass statement or call use_statement() before execute")
result = self.cursor.execute(sql, self.binds)
import sys
def to_csv(self, headers=True, outfile=sys.stdout):
import csv
rows = self.execute()
column_names = [i for i[0] in self.cursor.description]
print(column_names)
writer = csv.writer(outfile,
dialect="excel",
delimiter=',',
quotechar='"',
quoting=csv.QUOTE_NONNUMERIC)
if headers:
writer.writerow(column_names)
for row in rows:
writer.writerow(row)
@staticmethod
def help():
print("connect()")
print("list_statements()")
print("execute(statement_name)")
print("bind_date(name,yr,month,day)")
print("load('filename')")
print("use statement(statement_name")
print("use")
from pdsutil.DbUtil import ConnectionHelper, CursorHelper
import sys
import csv
if __name__ == "__main__":
connection_name = sys.argv[0]
connection = ConnectionHelper().get_named_connection(connection_name)
cursor = CursorHelper(connection.cursor())
rows = cursor.execute(sql,binds)
quoting_types = [csv.QUOTE_NONNUMERIC, csv.QUOTE_ALL, csv.QUOTE_MINIMAL, csv.QUOTE_NONE]
def to_csv(self, file, emit_headers: bool = True, dialect: str = "excel", delimiter: str = ",",
quotechar: str = "'", quoting: str = csv.QUOTE_NONNUMERIC):
writer = csv.writer(file, dialect="excel",
delimiter=',', quotechar='"',
quoting=csv.QUOTE_NONNUMERIC)
if emit_headers:
writer.writerow(self.column_names)
for row in self.rows:
writer.writerow(row)