# -*- coding: UTF-8 -*- import pymysql import datetime import pandas as pd DIRECTORY_PATH="/data2/ffm/" def get_yesterday_date(): #自动获取昨天的日期,如"2018-08-08" """ :rtype : str """ today = datetime.date.today() yesterday = today - datetime.timedelta(days=1) yesterday = yesterday.strftime("%Y-%m-%d") print(yesterday) return yesterday #today = datetime.date.today().strftime("%Y%m%d") #return today date = get_yesterday_date() def get_data(): conn2db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='eagle') cursor = conn2db.cursor() sql = "select device_id from eagle.ffm_diary_queue_temp where device_id regexp '[5|6]$'" cursor.execute(sql) result = cursor.fetchall() device_id = tuple(pd.DataFrame(list(result))[0].values.tolist()) cursor.close() return device_id def ctr(date): device_id = get_data() sql_click = "select count(cid) from data_feed_click " \ "where cid_type = 'diary' " \ "and stat_date = '{}' and device_id in {};".format(date,device_id) db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_prod') cursor = db.cursor() cursor.execute(sql_click) click = cursor.fetchone()[0] print("点击数:"+str(click)) sql_exp = "select count(cid) from data_feed_exposure " \ "where cid_type = 'diary' and stat_date = '{}' and " \ "device_id in {}".format(date,device_id) cursor.execute(sql_exp) exp = cursor.fetchone()[0] print("曝光数:"+str(exp)) if exp != 0: print("点击率:"+str(click/exp)) return click,exp,click/exp temp_data = ctr(date) def rate2file(): output_path = DIRECTORY_PATH + "rate.csv" with open(output_path,'a+') as f: line = get_yesterday_date().replace('-', '')+','+str(temp_data[0])+','+str(temp_data[1])+','+str(temp_data[2])+'\n' f.write(line) if __name__ == "__main__": #ctr(date) rate2file()