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
import pymysql
import pandas as pd
DIRECTORY_PATH = '/data2/models/'
def get_video_id():
db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='eagle')
cursor = db.cursor()
sql = "select diary_id from feed_diary_boost;"
cursor.execute(sql)
result = cursor.fetchall()
df=pd.DataFrame(list(result))
df.to_csv('/data2/models/video_diary_id.csv',index=None)
print("成功")
db.close()
def queue():
db = pymysql.connect(host='rm-m5e842126ng59jrv6.mysql.rds.aliyuncs.com', port=3306, user='doris',
passwd='o5gbA27hXHHm', db='doris_prod')
cursor = db.cursor()
sql = "select native_queue from device_diary_queue where device_id = 'AB20292B-5D15-4C44-9429-1C2FF5ED26F6';"
cursor.execute(sql)
result = cursor.fetchall()
df = pd.DataFrame(list(result))
all = df.loc[0,0].split(",")
print(all[:10])
db.close()
return all
def get_local_device():
db = pymysql.connect(host='192.168.15.12', port=4000, user='root', db='jerry_test')
cursor = db.cursor()
sql = "select device_id,city_id from user_active_time"
cursor.execute(sql)
result = cursor.fetchall()
df = pd.DataFrame(list(result)).dropna()
df = df.rename(columns={"0": "device_id", "1": "city_id"})
db.close()
df.to_csv('/Users/mac/utils/test_device_city_id.csv', index=None)
print(1)
def save_queue():
queue_name_list = ["native_queue", "nearby_queue", "nation_queue", "megacity_queue"]
for i in queue_name_list:
sql = "select {} from device_diary_queue " \
"where device_id = '{}' and city_id = '{}';".format(i, device_id, city_id)
db = pymysql.connect(host='rm-m5e842126ng59jrv6.mysql.rds.aliyuncs.com', port=3306, user='doris',
passwd='o5gbA27hXHHm',
db='doris_prod')
cursor = db.cursor()
cursor.execute(sql)
result = cursor.fetchall()
df = pd.DataFrame(list(result))
print(df.shape)
df.to_csv("/data/models/{}.csv".format(i), index=None)
print("end")
def delete():
db = pymysql.connect(host='rm-m5e842126ng59jrv6.mysql.rds.aliyuncs.com', port=3306, user='doris',
passwd='o5gbA27hXHHm',
db='doris_prod')
cursor = db.cursor()
sql = "delete from device_diary_queue where device_id = '{}' and city_id = '{}';".format(device_id, city_id)
cursor.execute(sql)
db.close()
print("删除成功")
def insert():
queue_name_list = ["native_queue", "nearby_queue", "nation_queue", "megacity_queue"]
a = {}
for i in queue_name_list:
a[i] = pd.read_csv("/data/models/native_queue.csv")["0"].values.tolist()[0]
# columns = ["native_queue", "nearby_queue", "nation_queue", "megacity_queue","id","device_id","city_id"]
db = pymysql.connect(host='rm-m5e842126ng59jrv6.mysql.rds.aliyuncs.com', port=3306, user='doris',
passwd='o5gbA27hXHHm',
db='doris_prod')
sql = "INSERT INTO device_diary_queue (native_queue, nearby_queue, nation_queue, " \
"megacity_queue,id,device_id,city_id) VALUES ('{}','{}','{}','{}','{}','{}','{}');".format \
(a["native_queue"], a["nearby_queue"],a["nation_queue"], a["megacity_queue"],id,device_id, city_id)
cursor = db.cursor()
cursor.execute(sql)
db.commit()
db.close()
print("end")
if __name__=="__main__":
# 先把数据保存下来，调用上面的save函数，然后调上面delete函数删除数据，然后调insert函数插入数据
id = 334
device_id = '00CA20EB-2719-4518-85CC-60E765AC526F'
city_id = 'beijing'
save_queue()
delete()
insert()