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
import pandas as pd
import pymysql
import datetime
from sqlalchemy import create_engine
def con_sql(db,sql):
cursor = db.cursor()
try:
cursor.execute(sql)
result = cursor.fetchall()
df = pd.DataFrame(list(result))
except Exception:
print("发生异常", Exception)
df = pd.DataFrame()
finally:
db.close()
return df
def multi():
db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_prod')
sql = "select diary_id,level2_ids from diary_feat"
df = con_sql(db, sql).dropna()
print(df.shape)
df = df.rename(columns={0: "cid", 1: "level"})
df["l1"] = "lost"
df["l2"] = "lost"
df["l3"] = "lost"
for i in list(df["level"].unique()):
l = [int(j) for j in i.split(";")]
l = sorted(l)
if len(l) >= 3:
df.loc[df["level"] == i, ["l1"]] = l[0]
df.loc[df["level"] == i, ["l2"]] = l[1]
df.loc[df["level"] == i, ["l3"]] = l[2]
elif len(l) == 2:
df.loc[df["level"] == i, ["l1"]] = l[0]
df.loc[df["level"] == i, ["l2"]] = l[1]
elif len(l) == 1:
df.loc[df["level"] == i, ["l1"]] = l[0]
df = df.drop("level",axis=1)
print(df.head())
# a = list(df["l1"].unique())
# b = list(df["l2"].unique())
# c = list(df["l3"].unique())
# print(len(a))
# print(a)
# print(len(b))
# print(b)
# print(len(c))
# print(c)
yconnect = create_engine('mysql+pymysql://root:3SYz54LS9#^9sBvC@10.66.157.22:4000/jerry_test?charset=utf8')
n = 200000
for i in range(0, df.shape[0], n):
if i == 0:
temp = df.iloc[0:n]
elif i + n > df.shape[0]:
temp = df.iloc[i:]
else:
temp = df.iloc[i:i + n]
pd.io.sql.to_sql(temp, "cid_level2", yconnect, schema='jerry_test', if_exists='append', index=False)
print("insert done")
if __name__ == "__main__":
multi()