def list_das_tag_by_date(cls, fromDate, toDate): listAdsTagDate = [] try: cn = Database.connection() cursor = cn.cursor(dictionary=True) sqlQuery = "select tag_id, ad_id, rev_real, click, imp " \ "from stats_ads_tags_date where date >= %s and date <= %s;" cursor.execute(sqlQuery, ("2019-07-01", "2019-07-30")) # cursor.execute(sqlQuery, (fromDate, toDate)) result = cursor.fetchall() if cursor.rowcount > 0: df = pd.DataFrame(result) df = pd.DataFrame( df.groupby(['tag_id', 'ad_id']).sum()[['rev_real', 'click', 'imp']]) print(df) # row.name[0] = tag_id , row.name[1] = ad_id for index, row in df.iterrows(): tagDate = ObjTagDate(row.name[0], row.name[1], row["rev_real"], row["click"], row["imp"]) listAdsTagDate.append(tagDate) except Exception as e: print("An exception occurred...." + e) finally: if cn.is_connected(): cursor.close() cn.close() return listAdsTagDate
def create_ads_tags_operate(cls, recordsToInsert): print("recordsToInsert", recordsToInsert) try: cn = Database.connection() cursor = cn.cursor(dictionary=True) sqlQuery = " insert into operate_tag_ad_date(tag_id,ad_id,date,cpm_7,ctr_7)" \ " values(%s,%s,%s,%s,%s)" \ " on duplicate key update cpm_7=values(cpm_7), ctr_7=values(ctr_7)" cursor.executemany(sqlQuery, recordsToInsert) cn.commit() if cursor.rowcount > 0: print("rowcount :", cursor.rowcount) except Exception as e: print("Exception ..." + e) finally: if cn.is_connected(): cursor.close() cn.close()