이를 위해 read_data() 라는 function을 만들어보자.
작업환경
- OS : Windows 10
- PC : LattePanda 4G RAM 64G eMMC (DFR0419)
- NAS : DS1618+
- Python : 3.6.8
- Tool : Pycharm
- PC와 NAS는 다른 네트워크
- PC와 NAS는 각각 Internet이 가능한 상태
read_data 함수에서는 cursor와 table과 f_param 을 파라미터로 받게된다.
전체 코드는 다음과 같다.
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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
# -*- coding: utf-8 -*-
"""
Created on Wed Jul 11 10:10:31 2018
@author: namta
"""
from __future__ import print_function
import pickle
import numpy as np
import pandas as pd
from sklearn.neighbors import KNeighborsClassifier
from sklearn import linear_model
import mysql.connector as mariadb
from mysql.connector import Error
def read_data(cursor, table,f_param):
sql_select_Query="select * from " + table
cursor.execute(sql_select_Query)
temp_param = [] # list for temporary raw data
for row in cursor:
# print("time_peak = ", row[3], )
# print("time_rms = ", row[2])
# print("crest_factor = ", row[4])
# print("frequency_peak = ", row[5])
# print("frequency_band = ", row[11], "\n")
# print(type(row))
temp_param = []
temp_param.append(table) # id
temp_param.append(row[3]) # time_peak
temp_param.append(row[2]) # time_rms
temp_param.append(row[4]) # crest_factor
temp_param.append(row[5]) # frequency_peak
temp_param.append(row[11]) # frequency_band
f_param.append(temp_param)
# cursor.close()
print(f_param)
return f_param
try:
connection = mariadb.connect(host='#07의 2번에서 확인한 NAS의 외부주소',
user='#06의 3번에서 확인한 user 명 입력',
port='#06의 6번에서 확인된 Port 번호 입력',
password='#06의 3번에서 login에 사용되는 password 입력',
database='#06의 4번에서 확인된 db 명 입력')
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL database... MySQL Server version on ", db_Info)
cursor = connection.cursor()
cursor.execute("select database();")
record = cursor.fetchone()
print("Your connected to -", record)
#sql_select_Query = "select * from data_sensor"
#cursor.execute(sql_select_Query)
f_param = [] # list for parameters
f_param = read_data(cursor, "normal_data", f_param)
f_param = read_data(cursor, "impact_data", f_param)
f_param = read_data(cursor, "vibration_data", f_param)
key = []
value = []
print("Input data : {}".format(len(f_param)))
for v in range(len(f_param)):
# for v in range(151):
key.append(f_param[v][0])
value.append(f_param[v][1:])
key = np.asarray(key)
value = np.asarray(value)
# print(key)
# print(value)
# Change list to Dictionary
dataset = {}
dataset['label'] = key
dataset['data'] = value
# print(dataset.keys())
# print(dataset)
X_Dataset = dataset['data']
y_Dataset = dataset['label']
# print(X_Dataset.shape)
# print(y_Dataset.shape)
# print(y_Dataset)
####################################################################################################################################################################
####################################################################################################################################################################
# np.random.seed(0)
indices = np.random.permutation(len(X_Dataset))
# print(X_Dataset.shape)
# indices = np.arange(len(X_Dataset))
# np.random.shuffle(indices)
x_train = X_Dataset[indices[:-200]]
y_train = y_Dataset[indices[:-200]]
x_test = X_Dataset[indices[-200:]]
y_test = y_Dataset[indices[-200:]]
print(y_test)
knn = KNeighborsClassifier()
print("x_train : {}".format(x_train.shape))
print("y_train : {}".format(y_train.shape))
print("x_test : {}".format(x_test.shape))
print("y_test : {}".format(y_test.shape))
knn.fit(x_train, y_train)
kX = knn.predict(x_test)
print("test result: {:.2f}".format(knn.score(x_test, y_test)))
except Error as e:
print("Error while connecting to MySQL", e)
# finally:
# if(connection.is_connected()):
# cursor.close()
# connection.close()
# print("MySQL connection is closed")
| cs |
댓글 없음:
댓글 쓰기