1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
import pandas as pd
import pymysql
from datetime import datetime
from datetime import timedelta
def get_city():
sql = "select distinct city_id from data_feed_exposure where stat_date >= '2018-10-01' order by city_id"
db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_prod')
cursor = db.cursor()
print("开始获取")
cursor.execute(sql)
print("成功获取")
result = cursor.fetchall()
db.close()
user = pd.DataFrame(list(result))[0].values.tolist()
print(user)
sql = "select distinct name from api_tag where tag_type = 4"
db = pymysql.connect(host='rdsfewzdmf0jfjp9un8xj.mysql.rds.aliyuncs.com', port=3306,
user='work', passwd='BJQaT9VzDcuPBqkd', db='zhengxing')
cursor = db.cursor()
print("开始获取")
cursor.execute(sql)
print("成功获取")
result = cursor.fetchall()
db.close()
user = pd.DataFrame(list(result))[0].values.tolist()
print(user)
# def get_tail8():
# sql = "select distinct device_id from data_feed_click \
# where stat_date='{}' \
# and cid_type='{}' \
# and device_id regexp '8$';".format(stat_date,cid_type)
# db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_test')
# cursor = db.cursor()
# print("开始获取")
# cursor.execute(sql)
# print("成功获取")
# result = cursor.fetchall()
# db.close()
# user = pd.DataFrame(list(result))[0].values.tolist()
# user = tuple(user)
# print("尾号是8的用户个数")
# print(len(user))
# return user
# def get_ctr(user_tuple):
# sql = "select count(device_id) from data_feed_click \
# where stat_date='{}' \
# and cid_type='{}' \
# and device_id in {}".format(stat_date, cid_type, user_tuple)
# db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_test')
# cursor = db.cursor()
# print("开始获取")
# cursor.execute(sql)
# click = cursor.fetchall()[0][0]
# print(click)
#
# sql = "select count(device_id) from data_feed_exposure \
# where stat_date='{}' \
# and cid_type='{}' \
# and device_id in {}".format(stat_date, cid_type, user_tuple)
# cursor = db.cursor()
# print("开始获取")
# cursor.execute(sql)
# exp = cursor.fetchall()[0][0]
# db.close()
# print(exp)
# print(click / exp)
# def get_tail6():
# df = pd.read_csv(path+"{}predictTail6Unique.csv".format(stat_date))
# pre_list = tuple(eval(df.loc[0,"list"]))
# print(len(pre_list))
# print(pre_list[:2])
# db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_test')
# sql = "select distinct device_id from data_feed_click \
# where stat_date='{}' \
# and cid_type='{}' \
# and device_id in {}".format(stat_date,cid_type,pre_list)
# cursor = db.cursor()
# print("开始获取")
# cursor.execute(sql)
# print("成功获取")
# result = cursor.fetchall()
# db.close()
# print(pd.DataFrame(list(result)).empty)
# user = pd.DataFrame(list(result))[0].values.tolist()
# user = tuple(user)
# print("用户个数")
# print(len(user))
# return user
if __name__ == "__main__":
get_city()
# path = "/data/models/"
# cid_type = "diary"
# now = datetime.now()
# year = now.year
# month = now.month
# day = now.day
# stat_date = datetime(year, month, day)
# stat_date = (stat_date - timedelta(days=1)).strftime("%Y-%m-%d")
# print(stat_date)
# tail6 = get_tail6()
# get_ctr(tail6)
# tail8 = get_tail8()
# get_ctr(tail8)