def kam_plan_to_sql(self): cursor,conn = simple_connection_to_base(base_path) cursor.execute("DROP TABLE kam_annual_sales_plan_final") cursor.execute( "CREATE TABLE IF NOT EXISTS kam_annual_sales_plan_final (month_local,item_quadra,code,manager_name,cip_kam,plan_packs,plan_euro_kam);") report_path = 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\preparation\\raw\\' report_file = '2.kam_plan_by_manager.xlsx' full_path_report = report_path + report_file wb_obj = openpyxl.load_workbook(full_path_report) sheet_obj = wb_obj.active rows_count = str(sheet_obj.calculate_dimension()).rsplit(':') print(rows_count) rows_count = int(str(rows_count[1])[1:]) print(rows_count) string = [] classified_base_2021 = [] for row in range(2, rows_count + 1): str_ = [] for col in range(1, 8): cell_obj = sheet_obj.cell(row=row, column=col) str_.append(cell_obj.value) string.append(str_) y = Plan_kam_by_manager_classification() for i in string: x = y.classify(i) classified_base_2021.append(x) cursor.execute( "INSERT INTO kam_annual_sales_plan_final VALUES (?,?,?,?,?,?,?);", x) conn.commit() logging.info("; Exporting kam plan to database ; OK ; kam_plan_to_sql(self) ; Kam_plans_by_manager") print('OK')
def get_kam_plan(self): structure_path = 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\preparation\\raw\\' file = '2.structure_kam.xlsx' full_path = structure_path + file df = pd.read_excel(full_path) logging.info(f'; Reading KAM structure file ({file}) at {structure_path} ; OK ; get_kam_plan(self) ; Kam_plans_by_manager') df_num = df.to_numpy() logging.info(f'; Converting file ({file}) at {structure_path} to numpy array ; OK ; get_kam_plan(self) ; Kam_plans_by_manager') code_ = [] cursor,conn = simple_connection_to_base(base_path) logging.info(f'; Connecting to local sqlite base ; OK ; get_kam_plan(self) ; Kam_plans_by_manager') cursor.execute("DROP TABLE kam_structure_final") logging.info(f'; Dropping kam_structure_final tab in base ; OK ; get_kam_plan(self) ; Kam_plans_by_manager') cursor.execute( "CREATE TABLE IF NOT EXISTS kam_structure_final (code,name,territory,region,regional_manager_code,regional_manager_name,depart_sales_methond,boss_code,boss_name,plan_multiplicator);") logging.info(f'; Creating kam_structure_final tab in base ; OK ; get_kam_plan(self) ; Kam_plans_by_manager') dict_code_name = {} for i in df_num: plan_multiplicator = i[9] boss_name = i[8] boss_code = i[7] depart_sales_methond = i[6] regional_manager_name = i[5] regional_manager_code = i[4] region = i[3] territory = i[2] name = i[1] code = i[0] x = Structure_kam(code, name, territory, region, regional_manager_code, regional_manager_name, depart_sales_methond, boss_code, boss_name, plan_multiplicator) dict_code_name.update({x.code: x.name}) code_.append(x.code) strin = [x.code, x.name, x.territory, x.region, x.regional_manager_code, x.regional_manager_name,x.depart_sales_methond,x.boss_code,x.boss_name,x.plan_multiplicator] cursor.execute( "INSERT INTO kam_structure_final VALUES (?,?,?,?,?,?,?,?,?,?);", strin) logging.info(f'; Inserting values to kam_structure_final tab in base ; OK ; get_kam_plan(self) ; Kam_plans_by_manager') conn.commit() logging.info(f'; Reading raw Kam plan by manager; OK ; get_kam_plan(self) ; Kam_plans_by_manager') kam_page_name_SF03 = "Старченко уп" items_dict, df_plan_by_manager_SF03 = self.get_df_plan_for_manager_by_manager(kam_page_name_SF03,dict_code_name) kam_page_name_SF01 = "Аношина уп" items_dict, df_plan_by_manager_SF01 = self.get_df_plan_for_manager_by_manager(kam_page_name_SF01,dict_code_name) kam_page_name_SF02 = "Ковальчук уп" items_dict, df_plan_by_manager_SF02 = self.get_df_plan_for_manager_by_manager(kam_page_name_SF02,dict_code_name) dfs_list = [df_plan_by_manager_SF01,df_plan_by_manager_SF02,df_plan_by_manager_SF03] merge = pd.DataFrame() for df in dfs_list: merge = merge.append(df,ignore_index=True) file_merged = '2.kam_plan_by_manager.xlsx' logging.info(f'; Creating a common plan file {file_merged} at {structure_path} ; OK; get_kam_plan(self) ; Kam_plans_by_manager') full_path_ = structure_path + file_merged merge.to_excel(full_path_,index=False) logging.info(f'; Creating dataframe and export it to excel; OK ; get_kam_plan(self) ; Kam_plans_by_manager') return merge
def update_item(conn, sales: Tertiary_sales): pass with mySql.connect(host="localhost", user="******", password="******", database="chinook") as conn: cursor = conn.cursor() # cursor.execute("UPDATE Tertiary_sales SET Name = 'TNMK' WHERE ArtistId = 276;") cursor.execute( f"UPDATE Tertiary_sales SET Name = '{items.brand}' WHERE ItemsId = {items.item};" ) conn.commit()
def insert_item(conn, sales: Tertiary_sales): with sqlite3.connect("tertiary_sales_database.db") as conn: cursor = conn.cursor() # cursor.execute("INSERT INTO Tertiary_sales VALUES (276,'Djamala');") # cursor.execute(f"INSERT INTO Tertiary_sales VALUES (?,?)",(artist.item,f'{artist.brand}')) cursor.execute(f"INSERT INTO sales VALUES (?,?,?,?,?,?,?,?)", (f'({sales.year}', (f'{sales.month}'), (f'{sales.item}'), (f'{sales.weight_penetration}'), (f'{sales.sro}'), (f'{sales.quantity}'), (f'{sales.volume_euro}'))) conn.commit()
def tert_reg_to_sqlite(self): cursor, conn = simple_connection_to_base(base_path) logging.info("; Connecting to 'local_main_base.db' ; OK") cursor.execute("DROP TABLE tertiary_by_reg") logging.info( "; Dropping the table 'tertiary_by_reg' in 'local_main_base.db' ; OK" ) cursor.execute( "CREATE TABLE IF NOT EXISTS tertiary_by_reg (year, period_name,region, full_medication_name,market_org,quantity,volume,sro,weight_sro);" ) path = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\tertiary_sales\\0.tertiary_by_region.xlsx" logging.info( "; Creating the table 'tertiary_by_reg' in 'local_main_base.db' ; OK" ) wb_obj = openpyxl.load_workbook(path) sheet_obj = wb_obj.active rows_count = str(sheet_obj.calculate_dimension()).rsplit(':') print(rows_count) rows_count = int(str(rows_count[1])[1:]) print(rows_count) logging.info( f"; transform_tertiary_by_region' - number of rows - {rows_count} ; OK" ) string = [] classified_base_2021 = [] for row in range(1, rows_count + 1): str_ = [] for col in range(1, 10): cell_obj = sheet_obj.cell(row=row, column=col) str_.append(cell_obj.value) string.append(str_) logging.info("; Classifying received data ; OK") for i in string[1:]: x = Tertiary_workout() string_class = x.classify(i) classified_base_2021.append(string_class) for string in classified_base_2021: for row in string: strin = [ row.year, row.period_name, row.region, row.full_medication_name, row.market_org, row.quantity, row.volume, row.sro, row.weight_sro ] cursor.execute( "INSERT INTO tertiary_by_reg VALUES (?,?,?,?,?,?,?,?,?);", strin) conn.commit() logging.info("; Inserting received data to 'local_main_base.db' ; OK")
def tert_totals_to_sqlite(self): cursor,conn = simple_connection_to_base(base_path) logging.info("Connecting to 'local_main_base.db' - OK") cursor.execute("DROP TABLE tertiary_totals") logging.info("Dropping the table 'tertiary_totals' in 'local_main_base.db' - OK") cursor.execute("CREATE TABLE IF NOT EXISTS tertiary_totals (year, period_name,period,trade_name,drug_form, brand, full_medication_name,market_org,quantity,volume,sro,weight_sro,penetration, weighted_penetration);") path = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\tertiary_sales\\tertiary_month_actual.xlsx" logging.info("Creating the table 'tertiary_totals' in 'local_main_base.db' - OK") wb_obj = openpyxl.load_workbook(path) sheet_obj = wb_obj.active rows_count = str(sheet_obj.calculate_dimension()).rsplit(':') print(rows_count) rows_count = int(str(rows_count[1])[1:]) print(rows_count) logging.info(f"transform_tertiary_by_region' - number of rows - {rows_count} - OK") string = [] classified_base_2021 = [] for row in range(1, rows_count + 1): str_ = [] for col in range(1, 15): cell_obj = sheet_obj.cell(row=row, column=col) str_.append(cell_obj.value) string.append(str_) logging.info("Classifying received data - OK") for i in string[1:]: trade_name = i[4] brand = i[6] drug_form = i[5] period = i[2] weight_sro = i[11] sro = i[10] volume = i[9] quantity = i[8] market_org = i[7] full_medication_name = i[3] period_name = i[1] year = i[0] weighted_penetration = i[13] penetration = i[12] x = Tertiary_by_hub_totals_download_structure(year, period_name,period,trade_name,drug_form, brand, full_medication_name,market_org,quantity,volume,sro,weight_sro,penetration, weighted_penetration) string_class = x classified_base_2021.append(string_class) for row in classified_base_2021: strin = [row.year, row.period_name,row.period,row.trade_name,row.drug_form, row.brand, row.full_medication_name,row.market_org,row.quantity,row.volume,row.sro,row.weight_sro,row.penetration, row.weighted_penetration] cursor.execute("INSERT INTO tertiary_totals VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?);",strin) conn.commit() logging.info("Inserting received data to 'local_main_base.db' - OK")
def ff_structure_to_sql(self): cursor, conn = simple_connection_to_base(base_path) logging.info(f'; Connecting to local base ; OK') cursor.execute("DROP TABLE ff_structure_final") logging.info(f'; Dropping ff_structure_final tab at local base ; OK') cursor.execute( "CREATE TABLE IF NOT EXISTS ff_structure_final (code,name,territory,region,regional_manager_code,regional_manager_name,depart_sales_methond,boss_code,boss_name,plan_multiplicator);" ) logging.info(f'; Creating ff_structure_final tab at local base ; OK') report_path = 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\preparation\\raw\\' report_file = '1.structure.xlsx' full_path_report = report_path + report_file wb_obj = openpyxl.load_workbook(full_path_report) sheet_obj = wb_obj.active rows_count = str(sheet_obj.calculate_dimension()).rsplit(':') print(rows_count) rows_count = int(str(rows_count[1])[1:]) print(rows_count) string = [] classified_base_2021 = [] for row in range(2, rows_count + 1): str_ = [] for col in range(1, 11): cell_obj = sheet_obj.cell(row=row, column=col) str_.append(cell_obj.value) string.append(str_) for i in string: x = Structure_ff(i[0], i[1], i[2], i[3], i[4], i[5], i[6], i[7], i[8], i[9]) classified_base_2021.append(x) for string in classified_base_2021: # for row in string: strin = [ string.code, string.name, string.territory, string.region, string.regional_manager_code, string.regional_manager_name, string.depart_sales_methond, string.boss_code, string.boss_name, string.plan_multiplicator ] cursor.execute( "INSERT INTO ff_structure_final VALUES (?,?,?,?,?,?,?,?,?,?);", strin) logging.info( f'; Inserting values to ff_structure_final tab at local base ; OK') conn.commit() print('OK')
def items_actual_to_base(self): logging.info( f'; Connecting to local base; OK ; items_actual_to_base(self) ; Prices_to_sql()' ) cursor, conn = simple_connection_to_base(base_path) cursor.execute("DROP TABLE items_actual_connected") logging.info( f'; Dropping tab items_actual_connected at local base; OK ; items_actual_to_base(self) ; Prices_to_sql()' ) cursor.execute( "CREATE TABLE IF NOT EXISTS items_actual_connected ('year', 'month', 'promotion', 'purpose', 'sku', 'sku_sales_report', 'sku_translit', 'brand', 'cip');" ) logging.info( f'; Creating tab items_actual_connected at local base; OK ; items_actual_to_base(self) ; Prices_to_sql()' ) dir_ = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\material_master\\" file1 = '1.item_actual_updated.xlsx' path = dir_ + file1 wb_obj = openpyxl.load_workbook(path) sheet_obj = wb_obj.active rows_count = str(sheet_obj.calculate_dimension()).rsplit(':') rows_count = int(str(rows_count[1])[1:]) string = [] classified_base_2021 = [] for row in range(1, rows_count): str_ = [] for col in range(1, 10): cell_obj = sheet_obj.cell(row=row, column=col) str_.append(cell_obj.value) string.append(str_) for i in string: x = Prices_to_sql() string_class = x.classify_prices(i) classified_base_2021.append(string_class) for string in classified_base_2021: cursor.execute( "INSERT INTO items_actual_connected VALUES (?,?,?,?,?,?,?,?,?);", string) logging.info( f'; Inserting values to tab items_actual_connected at local base from {file1} at {dir_}; OK ; items_actual_to_base(self) ; Prices_to_sql()' ) conn.commit() print('OK')
def ff_plan_to_sql(self): plan_ff = self.get_splitted_plan_by_rep() cursor, conn = simple_connection_to_base(base_path) logging.info(f'; Connecting to local base ; OK') cursor.execute("DROP TABLE ff_annual_sales_plan_final") logging.info(f'; Dropping ff_annual_sales_plan_final tab at base ; OK') cursor.execute( "CREATE TABLE IF NOT EXISTS ff_annual_sales_plan_final (region_quadra, item_quadra, month_local, plan_rep,code, name);" ) logging.info(f'; Creating ff_annual_sales_plan_final tab at base ; OK') report_path = 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\plan_by_rep\\' report_file = 'plan_ready_ff.xlsx' full_path_report = report_path + report_file wb_obj = openpyxl.load_workbook(full_path_report) sheet_obj = wb_obj.active rows_count = str(sheet_obj.calculate_dimension()).rsplit(':') print(rows_count) rows_count = int(str(rows_count[1])[1:]) print(rows_count) string = [] classified_base_2021 = [] for row in range(2, rows_count + 1): str_ = [] for col in range(1, 7): cell_obj = sheet_obj.cell(row=row, column=col) str_.append(cell_obj.value) string.append(str_) for i in string: x = Plan_ff_sql(i[0], i[1], i[2], i[3], i[4], i[5]) classified_base_2021.append(x) for string in classified_base_2021: # for row in string: strin = [ string.region_quadra, string.item_quadra, string.month_local, string.plan_packs, string.code, string.name ] cursor.execute( "INSERT INTO ff_annual_sales_plan_final VALUES (?,?,?,?,?,?);", strin) logging.info( f'; Inserting values to ff_annual_sales_plan_final tab at base ; OK' ) conn.commit() print('OK')
def normalize_weekly_tertiary(self): filename1 = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\transform_files\\0.tertiary_week_normalized.xlsx" cursor, conn = simple_connection_to_base(base_path) cursor.execute(f"SELECT distinct items.item_proxima from items") results = cursor.fetchall() list_crm = [] for i in results: list_crm.append(i) filename = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\transform_files\\0.tertiary_week_normalized.xlsx" path = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\tertiary_sales\\tertiary_week_actual.xlsx" wb_obj = openpyxl.load_workbook(path) sheet_obj = wb_obj.active rows_count = str(sheet_obj.calculate_dimension()).rsplit(':') print(rows_count) rows_count = int(str(rows_count[1])[1:]) print(rows_count) logging.info(f"; number of rows - {rows_count} ; OK") string = [] classified_base_2021 = [] for row in range(1, rows_count + 1): str_ = [] for col in range(1, 70): cell_obj = sheet_obj.cell(row=row, column=col) str_.append(cell_obj.value) string.append(str_) for i in string[2:]: count = 0 item_proxima_dirty = '' index_ = process.extractOne(str(i[0])[7:], list_crm) if index_[1] > 95: item_proxima_dirty = str(str(i[0])[8:]).replace("('", "").replace( "',)", "") else: count += 1 w01 = i[2] w02 = i[3] w03 = i[4] w04 = i[5] w05 = i[6] w06 = i[7] w07 = i[8] w08 = i[9] w09 = i[10] w10 = i[11] w11 = i[12] w12 = i[13] w13 = i[14] w14 = i[15] w15 = i[16] w16 = i[17] w17 = i[18] w18 = i[19] w19 = i[20] w20 = i[21] w21 = i[22] w22 = i[23] w23 = i[24] w24 = i[25] w25 = i[26] w26 = i[27] w27 = i[28] w28 = i[29] w29 = i[30] w30 = i[31] w31 = i[32] w32 = i[33] w33 = i[34] w34 = i[35] w35 = i[36] w36 = i[37] w37 = i[38] w38 = i[39] w39 = i[40] w40 = i[41] w41 = i[42] w42 = i[43] w43 = i[44] w44 = i[45] w45 = i[46] w46 = i[47] w47 = i[48] w48 = i[49] w49 = i[50] w50 = i[51] w51 = i[52] w52 = i[53] w53 = i[54] x = Tertiary_by_week_download_structure_for_power_bi( item_proxima_dirty, w01, w02, w03, w04, w05, w06, w07, w08, w09, w10, w11, w12, w13, w14, w15, w16, w17, w18, w19, w20, w21, w22, w23, w24, w25, w26, w27, w28, w29, w30, w31, w32, w33, w34, w35, w36, w37, w38, w39, w40, w41, w42, w43, w44, w45, w46, w47, w48, w49, w50, w51, w52, w53) string_class = x classified_base_2021.append(string_class) cursor, conn = simple_connection_to_base(base_path) cursor.execute("DROP TABLE tertiary_week_normalized") logging.info( "; Dropping the table 'tertiary_week_normalized' in 'local_main_base.db' ; OK" ) cursor.execute( "CREATE TABLE IF NOT EXISTS tertiary_week_normalized (item_proxima_dirty,'01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53');" ) path = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\transform_files\\0.tertiary_week_normalized.xlsx" logging.info( "; Creating the table 'tertiary_week_normalized' in 'local_main_base.db' ; OK" ) for row in classified_base_2021: strin = [ row.item_proxima_dirty, row.w01, row.w02, row.w03, row.w04, row.w05, row.w06, row.w07, row.w08, row.w09, row.w10, row.w11, row.w12, row.w13, row.w14, row.w15, row.w16, row.w17, row.w18, row.w19, row.w20, row.w21, row.w22, row.w23, row.w24, row.w25, row.w26, row.w27, row.w28, row.w29, row.w30, row.w31, row.w32, row.w33, row.w34, row.w35, row.w36, row.w37, row.w38, row.w39, row.w40, row.w41, row.w42, row.w43, row.w44, row.w45, row.w46, row.w47, row.w48, row.w49, row.w50, row.w51, row.w52, row.w53 ] cursor.execute( "INSERT INTO tertiary_week_normalized VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);", strin) conn.commit() logging.info("; Inserting received data to 'local_main_base.db' ; OK") cursor, conn = simple_connection_to_base(base_path) cursor.execute( f"select items.item_quadra, tertiary_week_normalized.'01',tertiary_week_normalized.'02',tertiary_week_normalized.'03',tertiary_week_normalized.'04',tertiary_week_normalized.'05',tertiary_week_normalized.'06',tertiary_week_normalized.'07',tertiary_week_normalized.'08',tertiary_week_normalized.'09',tertiary_week_normalized.'10',tertiary_week_normalized.'11',tertiary_week_normalized.'12',tertiary_week_normalized.'13',tertiary_week_normalized.'14',tertiary_week_normalized.'15',tertiary_week_normalized.'16',tertiary_week_normalized.'17',tertiary_week_normalized.'18', " + "tertiary_week_normalized.'19',tertiary_week_normalized.'20',tertiary_week_normalized.'21',tertiary_week_normalized.'22',tertiary_week_normalized.'23',tertiary_week_normalized.'24',tertiary_week_normalized.'25',tertiary_week_normalized.'26',tertiary_week_normalized.'27',tertiary_week_normalized.'28',tertiary_week_normalized.'29',tertiary_week_normalized.'30',tertiary_week_normalized.'31',tertiary_week_normalized.'32',tertiary_week_normalized.'33',tertiary_week_normalized.'34',tertiary_week_normalized.'35',tertiary_week_normalized.'36', " + "tertiary_week_normalized.'37',tertiary_week_normalized.'38',tertiary_week_normalized.'39',tertiary_week_normalized.'40',tertiary_week_normalized.'41',tertiary_week_normalized.'42',tertiary_week_normalized.'43',tertiary_week_normalized.'44',tertiary_week_normalized.'45',tertiary_week_normalized.'46',tertiary_week_normalized.'47',tertiary_week_normalized.'48',tertiary_week_normalized.'49',tertiary_week_normalized.'50',tertiary_week_normalized.'51',tertiary_week_normalized.'52',tertiary_week_normalized.'53' from items join tertiary_week_normalized on tertiary_week_normalized.item_proxima_dirty = items.item_proxima group by items.item_quadra" ) normalized_ = cursor.fetchall() workbook1 = xlsxwriter.Workbook(filename1) logging.info(f"; Opening f'{filename1}' for writing ; OK") worksheet1 = workbook1.add_worksheet() # Widen the first column to make the text clearer. # worksheet.set_column('A:A', 20) bold = workbook1.add_format({'bold': True}, ) worksheet1.write('A1', "item_proxima", bold) worksheet1.write('B1', "01", bold) worksheet1.write('C1', "02", bold) worksheet1.write('D1', "03", bold) worksheet1.write('E1', "04", bold) worksheet1.write('F1', "05", bold) worksheet1.write('G1', "06", bold) worksheet1.write('H1', "07", bold) worksheet1.write('I1', "08", bold) worksheet1.write('J1', "09", bold) worksheet1.write('K1', "10", bold) worksheet1.write('L1', "11", bold) worksheet1.write('M1', "12", bold) worksheet1.write('N1', "13", bold) worksheet1.write('O1', "14", bold) worksheet1.write('P1', "15", bold) worksheet1.write('Q1', "16", bold) worksheet1.write('R1', "17", bold) worksheet1.write('S1', "18", bold) worksheet1.write('T1', "19", bold) worksheet1.write('U1', "20", bold) worksheet1.write('V1', "21", bold) worksheet1.write('W1', "22", bold) worksheet1.write('X1', "23", bold) worksheet1.write('Y1', "24", bold) worksheet1.write('Z1', "25", bold) worksheet1.write('AA1', "26", bold) worksheet1.write('AB1', "27", bold) worksheet1.write('AC1', "28", bold) worksheet1.write('AD1', "29", bold) worksheet1.write('AE1', "30", bold) worksheet1.write('AF1', "31", bold) worksheet1.write('AG1', "32", bold) worksheet1.write('AH1', "33", bold) worksheet1.write('AI1', "34", bold) worksheet1.write('AJ1', "35", bold) worksheet1.write('AK1', "36", bold) worksheet1.write('AL1', "37", bold) worksheet1.write('AM1', "38", bold) worksheet1.write('AN1', "39", bold) worksheet1.write('AO1', "40", bold) worksheet1.write('AP1', "41", bold) worksheet1.write('AQ1', "42", bold) worksheet1.write('AR1', "43", bold) worksheet1.write('AS1', "44", bold) worksheet1.write('AT1', "45", bold) worksheet1.write('AU1', "46", bold) worksheet1.write('AV1', "47", bold) worksheet1.write('AW1', "48", bold) worksheet1.write('AX1', "49", bold) worksheet1.write('AY1', "50", bold) worksheet1.write('AZ1', "51", bold) worksheet1.write('BA1', "52", bold) worksheet1.write('BB1', "53", bold) logging.info("; Adding headers ; OK") row_index = 1 for item in normalized_: print(item) worksheet1.write(int(row_index), int(0), str(item[0])) worksheet1.write(int(row_index), int(1), str(item[1]).replace(".", ",")) worksheet1.write(int(row_index), int(2), str(item[2]).replace(".", ",")) worksheet1.write(int(row_index), int(3), str(item[3]).replace(".", ",")) worksheet1.write(int(row_index), int(4), str(item[4]).replace(".", ",")) worksheet1.write(int(row_index), int(5), str(item[5]).replace(".", ",")) worksheet1.write(int(row_index), int(6), str(item[6]).replace(".", ",")) worksheet1.write(int(row_index), int(7), str(item[7]).replace(".", ",")) worksheet1.write(int(row_index), int(8), str(item[8]).replace(".", ",")) worksheet1.write(int(row_index), int(9), str(item[9]).replace(".", ",")) worksheet1.write(int(row_index), int(10), str(item[10]).replace(".", ",")) worksheet1.write(int(row_index), int(11), str(item[11]).replace(".", ",")) worksheet1.write(int(row_index), int(12), str(item[12]).replace(".", ",")) worksheet1.write(int(row_index), int(13), str(item[13]).replace(".", ",")) worksheet1.write(int(row_index), int(14), str(item[14]).replace(".", ",")) worksheet1.write(int(row_index), int(15), str(item[15]).replace(".", ",")) worksheet1.write(int(row_index), int(16), str(item[16]).replace(".", ",")) worksheet1.write(int(row_index), int(17), str(item[17]).replace(".", ",")) worksheet1.write(int(row_index), int(18), str(item[18]).replace(".", ",")) worksheet1.write(int(row_index), int(19), str(item[19]).replace(".", ",")) worksheet1.write(int(row_index), int(20), str(item[20]).replace(".", ",")) worksheet1.write(int(row_index), int(21), str(item[21]).replace(".", ",")) worksheet1.write(int(row_index), int(22), str(item[22]).replace(".", ",")) worksheet1.write(int(row_index), int(23), str(item[23]).replace(".", ",")) worksheet1.write(int(row_index), int(24), str(item[24]).replace(".", ",")) worksheet1.write(int(row_index), int(25), str(item[25]).replace(".", ",")) worksheet1.write(int(row_index), int(26), str(item[26]).replace(".", ",")) worksheet1.write(int(row_index), int(27), str(item[27]).replace(".", ",")) worksheet1.write(int(row_index), int(28), str(item[28]).replace(".", ",")) worksheet1.write(int(row_index), int(29), str(item[29]).replace(".", ",")) worksheet1.write(int(row_index), int(30), str(item[30]).replace(".", ",")) worksheet1.write(int(row_index), int(31), str(item[31]).replace(".", ",")) worksheet1.write(int(row_index), int(32), str(item[32]).replace(".", ",")) worksheet1.write(int(row_index), int(33), str(item[33]).replace(".", ",")) worksheet1.write(int(row_index), int(34), str(item[34]).replace(".", ",")) worksheet1.write(int(row_index), int(35), str(item[35]).replace(".", ",")) worksheet1.write(int(row_index), int(36), str(item[36]).replace(".", ",")) worksheet1.write(int(row_index), int(37), str(item[37]).replace(".", ",")) worksheet1.write(int(row_index), int(38), str(item[38]).replace(".", ",")) worksheet1.write(int(row_index), int(39), str(item[39]).replace(".", ",")) worksheet1.write(int(row_index), int(40), str(item[40]).replace(".", ",")) worksheet1.write(int(row_index), int(41), str(item[41]).replace(".", ",")) worksheet1.write(int(row_index), int(42), str(item[42]).replace(".", ",")) worksheet1.write(int(row_index), int(43), str(item[43]).replace(".", ",")) worksheet1.write(int(row_index), int(44), str(item[44]).replace(".", ",")) worksheet1.write(int(row_index), int(45), str(item[45]).replace(".", ",")) worksheet1.write(int(row_index), int(46), str(item[46]).replace(".", ",")) worksheet1.write(int(row_index), int(47), str(item[47]).replace(".", ",")) worksheet1.write(int(row_index), int(48), str(item[48]).replace(".", ",")) worksheet1.write(int(row_index), int(49), str(item[49]).replace(".", ",")) worksheet1.write(int(row_index), int(50), str(item[50]).replace(".", ",")) worksheet1.write(int(row_index), int(51), str(item[51]).replace(".", ",")) worksheet1.write(int(row_index), int(52), str(item[52]).replace(".", ",")) worksheet1.write(int(row_index), int(53), str(item[53]).replace(".", ",")) row_index += 1 workbook1.close()
def classified_stock_to_sqlite(self): x = TestOnestockwoselecting() x.test_onestockwoselecting() time.sleep(30) list_of_files = glob.glob( 'C:\\Users\\Anastasia Siedykh\\Downloads\\*.xlsx' ) # * means all if need specific format then *.csv latest_file = max(list_of_files, key=os.path.getctime) os.rename( latest_file, 'C:\\Users\\Anastasia Siedykh\\Downloads\\stock_wh_to_merge.xlsx') os.replace( 'C:\\Users\\Anastasia Siedykh\\Downloads\\stock_wh_to_merge.xlsx', 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\stock_distributors_wh\\stock_wh_to_merge.xlsx' ) x.merge_stock_and_all_years_secondary() os.remove( 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\stock_distributors_wh\\stock_wh_to_merge.xlsx' ) os.remove( 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\stock_distributors_wh\\stock_at_distributors_wh.xlsx' ) os.rename( 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\stock_distributors_wh\\stock_at_distributors_wh_new.xlsx', 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\stock_distributors_wh\\stock_at_distributors_wh.xlsx' ) print('Stock file is updated') cursor, conn = simple_connection_to_base(base_path) logging.info("; Connecting to 'local_main_base.db' ; OK") cursor.execute("DROP TABLE stock_at_distributors_wh") logging.info( "; Dropping the table 'stock_at_distributors_wh' at 'local_main_base.db' ; OK" ) cursor.execute( "CREATE TABLE IF NOT EXISTS stock_at_distributors_wh (week,date,country_region,distributor,item_quadra,quantity_packs,amount_euro,num);" ) logging.info( "; Creating the table 'stock_at_distributors_wh' at 'local_main_base.db' ; OK" ) path = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\stock_distributors_wh\\stock_at_distributors_wh.xlsx" logging.info("; Opening the 'stock_at_distributors_wh.xlsx' ; OK") wb_obj = openpyxl.load_workbook(path) sheet_obj = wb_obj.active rows_count = str(sheet_obj.calculate_dimension()).rsplit(':') rows_count = int(str(rows_count[1])[1:]) logging.info( f"; Calculating rows at 'stock_at_distributors_wh.xlsx' - number of rows - {rows_count} ; OK" ) string = [] classified_base_2021 = [] for row in range(2, rows_count + 1): str_ = [] for col in range(1, 9): cell_obj = sheet_obj.cell(row=row, column=col) if cell_obj is not None: str_.append(cell_obj.value) string.append(str_) logging.info(f"; Launching stock classification ; OK") for i in string: x = CStock_quadra_workout() string_class = x.classify_stock_quadra(i) classified_base_2021.append(string_class) logging.info(f"; Stock classified ; OK") logging.info(f"; Inserting stock data to base ; OK") for string in classified_base_2021: for row in string: strin = [ row.week, row.date, row.country_region, row.distributor, row.item_quadra, row.quantity_packs, row.amount_euro, row.num ] cursor.execute( "INSERT INTO stock_at_distributors_wh VALUES (?,?,?,?,?,?,?,?);", strin) conn.commit() print('OK, check the base') return classified_base_2021
def delete_item(conn): with sqlite3.connect("tertiary_sales_database.db") as conn: cursor = conn.cursor() cursor.execute("DELETE FROM sales WHERE sales.item_id = '(90'") #cursor.execute(f"DELETE FROM Tertiary_sales WHERE ItemsId = {sales.item}") conn.commit()