fromsqlalchemyimportcreate_engineimportpandasaspdimportosimportcsvimportMySQLdbfromsqlalchemyimporttypes,create_engine# MySQL ConnectionMYSQL_USER='root'MYSQL_PASSWORD='Mharooney'MYSQL_HOST_IP='127.0.0.1'MYSQL_PORT=3306MYSQL_DATABASE='mydb'engine=create_engine('mysql+mysqlconnector://'+MYSQL_USER+'
:'+MYSQL_PASSWORD+'@'+MYSQL_HOST_IP+':'+str(MYSQL_PORT)+'/'+MYSQL_DATABASE,echo=False)#engine = create_engine('mysql+mysqldb://root:@localhost:123456/myDB?charset=utf8mb4&binary_prefix=true', echo=False)
mydir = (os.getcwd()).replace('\\', '/') + '/'
all_data = pd.read_sql('SELECT*FROM govtracker', engine)
# .drop(['#'], axis=1)myOutlook_inBox=pd.read_csv(r''+mydir+'test.CSV',usecols=['Subject','Body','From: (Name)','To: (Name)'],encoding='latin-1')myOutlook_inBox.columns=myOutlook_inBox.columns.str.replace(' ','')#this object extract 5 chars and 5 numbers from specific column in csvreplaced_sbj_value=myOutlook_inBox['Subject'].str.extract(pat='(L(?:DEL|CAI|SIN).\d{5})').dropna()#this columns I want to filter in databasemyOutlook_inBox["Subject"]=replaced_sbj_value# this conditions filters and get and dublicate repeated data from outlookexported file# Condition 1 any mail from mowafy to tefrm_mwfy_to_te=myOutlook_inBox.loc[myOutlook_inBox['From:
(Name)'].str.contains("mowafy",na=False)&myOutlook_inBox['To:
(Name)'].str.contains("te",na=False)].drop_duplicates(keep=False)frm_mwfy_to_te.Subjectfiltered_data=all_data.loc[all_data.site_code.str.contains('|'.join(frm_mwfy_to_te.Subject))]print(myOutlook_inBox)all_data.replace('\n','',regex=True)df=all_data.where((pd.notnull(all_data)),None)print(df)print("Success")print(frm_mwfy_to_te.Subject)print(filtered_data)# rows = engine.execute("SELECT * FROM govtracker")#.fetchall()# print(rows)update_db_query=engine.execute("UPDATE govtracker SET pending = 'TE'
WHERE site_code = "+filtered_data)"""engine = create_engine('postgresql+psycopg2://user:pswd@mydb')
df.to_sql('temp_table', engine, if_exists='replace')"""# select_db_query = pd.read_sql("SELECT * FROM govtracker", con = engine)#print(update_db_query)