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
import pandas as pd
import pymysql
from sklearn.preprocessing import MinMaxScaler
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 normal():
db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_test')
sql = "select * from device_read_time"
df = con_sql(db, sql)
df = df.rename(columns={0:"device_id",1:"kongbai",2:"eye",3:"simi",4:"zitizhifang",5:"banyongjiu",6:"teeth",
7:"kouchun",8:"ear",9:"nose",10:"banyongjiuzhuang",11:"qita",12:"lunkuo",
13:"shoushen",14:"skin",16:"shenghuo",
17:"breast",18:"hair",19:"kangshuai",20:"shili",21:"chanhou",22:"zhushe"})
# device_id = df[["device_id"]]
# df = df.drop("device_id",axis=1)
# minMax = MinMaxScaler()
# result = pd.DataFrame(minMax.fit_transform(df),columns=["0","1","10","1024","1080","11",
# "12","13","2","2054","2214","3","4","5","6933",
# "7","9","922","929","971","992"])
# result = device_id.join(result)
l = list(df.columns)
l.remove("device_id")
df["sum"] = df.sum(axis=1)
for i in l:
df[i] = df[i]/df["sum"]
df = df.drop("sum",axis=1)
yconnect = create_engine('mysql+pymysql://root:3SYz54LS9#^9sBvC@10.66.157.22:4000/jerry_test?charset=utf8')
pd.io.sql.to_sql(df, "device_read_time_normal", yconnect, schema='jerry_test', if_exists='fail', index=False)
if __name__ == "__main__":
normal()