ffm_ctr.py

# -*- 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


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 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

def rate2file():
    output_path = DIRECTORY_PATH + "ffm_ctr.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)
    date = get_yesterday_date()
    temp_data = ctr(date)
    rate2file()