import pymysql
import pandas as pd
from multiprocessing import Pool
import numpy as np
import datetime
import time


def con_sql(db, sql):
    cursor = db.cursor()
    cursor.execute(sql)
    result = cursor.fetchone()[0]

    return result


def test(days):
    start = (temp - datetime.timedelta(days)).strftime("%Y-%m-%d")
    print(start)
    sql = "select (select count(*) from esmm_train_data where stat_date = '{}' and y = 0)/(select count(*) " \
          "from train_data where stat_date = '{}' and z = 1)".format(start,start)
    db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_test')
    exp = con_sql(db, sql)
    print(exp)
    sql = "select (select count(*) from train_data where stat_date = '{}' and y = 1 and z = 0)/(select count(*) " \
          "from train_data where stat_date = '{}' and z = 1)".format(start,start)
    click = con_sql(db, sql)
    return start,exp,click


if __name__ == "__main__":
    temp = datetime.datetime.strptime("2019-03-14", "%Y-%m-%d")
    DIRECTORY_PATH = "/home/gmuser/"
    output_path = DIRECTORY_PATH + "esmm_train_eda.csv"
    for i in range(1,41):
        a,b,c = test(i)
        with open(output_path, 'a+') as f:
            line = str(a) + ',' + str(b)+ ',' + str(c) + '\n'
            f.write(line)

