import pandas as pd import os import glob import cx_Oracle from datetime import date import os def init(): global df #list_of_files = glob.glob('*.xlsx') # * means all if need specific format then *.csv latest_file = max(glob.iglob('*.xlsx'),key=os.path.getctime) if latest_file.startswith("~$"): latest_file=latest_file.replace("~$", "") print(latest_file) df = pd.read_excel(latest_file, engine="openpyxl", sheet_name="daily") if df.size > 0: createHtmlSummary(df) write_oracle_db(df) def write_oracle_db(df): dsnStr = cx_Oracle.makedsn("plapexgma01.bmwgroup.net", "1534", "apex5p") #print(dsnStr) db_connection_string='username/passwort@hostname.de.oracle.com:1521/servicename' con = cx_Oracle.connect(user="b2vadmin",password="B2V@aDmIn7", dsn=dsnStr) try: with con.cursor() as cursor: sql = 'INSERT INTO RELIABILITY_DAILY VALUES (:1, :2,:3,:4, :5, :6, :7)' #daily_value = (str(datetime.now().replace(microsecond=0)), 'emea', '3434','mobile', 97.43,'warning') daily_value = ('emea') df_list = df.values.tolist() # print(str(s1[0])) #cursor.execute('''INSERT INTO RELIABILITY_DAILY_TEST values ('emea')''') cursor.execute(sql, ( today, df_list[0][0], "efef" , "efef", "efef", 2, "efef")) # commit work con.commit() except cx_Oracle.Error as error: print('Error occurred:') print(error) print("Database version:", con.version) con.close() def createHtmlSummary(df): #df = self.totalall df=df.sort_values(by=['HUB']) unique_hub=df["HUB"].unique() SummaryString='

Processed SLO count: '+ str(len(df)) +'

' #Adding colum headers for ever hub for hub in unique_hub: SummaryString+="" SummaryString+="" #################################### ####### Between 99 and 100 ########## #################################### SummaryString+='' tmp99 = df[(df.evaluatedPercentage >=99) & (df.evaluatedPercentage <= 100)].groupby("HUB")["evaluatedPercentage"].count().reset_index() for hub in unique_hub: #for index, row in tmp99.iterrows(): SummaryString+=''.format(tmp99[(tmp99.HUB == hub)]["evaluatedPercentage"].values[0] if tmp99[(tmp99.HUB == hub)].size != 0 else 0) SummaryString+=''.format(len(df[(df.evaluatedPercentage >=99) & (df.evaluatedPercentage <= 100)])) #################################### ####### Between 98 and 99 ########## #################################### SummaryString+='' #if tmp98.size != 0: tmp98 = df[(df.evaluatedPercentage >=98) & (df.evaluatedPercentage < 99)].groupby("HUB")["evaluatedPercentage"].count().reset_index() for hub in unique_hub: SummaryString+=''.format(tmp98[(tmp98.HUB == hub)]["evaluatedPercentage"].values[0] if tmp98[(tmp98.HUB == hub)].size != 0 else 0) SummaryString+=''.format(len(df[(df.evaluatedPercentage >=98) & (df.evaluatedPercentage < 99)])) #################################### ####### Between 0 and 98 ########### #################################### SummaryString+='' tmp0 = df[(df.evaluatedPercentage >=0) & (df.evaluatedPercentage < 98)].groupby("HUB")["evaluatedPercentage"].count().reset_index() for hub in unique_hub: SummaryString+=''.format(tmp0[(tmp0.HUB == hub)]["evaluatedPercentage"].values[0] if tmp0[(tmp0.HUB == hub)].size != 0 else 0) SummaryString+=''.format(len(df[(df.evaluatedPercentage >=0) & (df.evaluatedPercentage < 98)])) #################################### ####### -1 --> N/A ########### #################################### SummaryString+='' tmpNA = df[(df.evaluatedPercentage == -1)].groupby("HUB")["evaluatedPercentage"].count().reset_index() for hub in unique_hub: SummaryString+=''.format(tmpNA[(tmpNA.HUB == hub)]["evaluatedPercentage"].values[0] if tmpNA[(tmpNA.HUB == hub)].size != 0 else 0) SummaryString+=''.format(len(df[(df.evaluatedPercentage == -1)])) SummaryString+="
"+hub+"total
>= 99{0}{0}
>= 98{0}{0}
< 98{0}{0}
-1{0}{0}
" with open('summary.txt', 'w') as f: f.write(SummaryString) if __name__ == "__main__": init()