# -*- coding: utf-8 -*-
import pymssql
import pandas as pd
import numpy as np
connect = pymssql.connect(
host = '10.0.0.136\mssql',
user = 'sa',
password = 'PasswordGS15',
database='test',
charset='utf8')
cursor = connect.cursor()
sql='select * from dbo.Advertising'
cursor.execute(sql)
data = cursor.fetchall()
df = pd.DataFrame(list(data),columns = ['No','TV','Radio','Newspaper','Sales'])
train_df=df.head(20)
#转换类型
TV=np.array(train_df['TV'].astype('float64'))
Radio=np.array(train_df['Radio'].astype('float64'))
Newspaper=np.array(train_df['Newspaper'].astype('float64'))
Sales =np.array(train_df['Sales'].astype('float64'))
#归一化过程
TV_=(TV-min(TV))/(max(TV)-min(TV))
Radio_=(Radio-min(Radio))/(max(Radio)-min(Radio))
Newspaper_=(Newspaper-min(Newspaper))/(max(Newspaper)-min(Newspaper))
Sales_=(Sales-min(Sales))/(max(Sales)-min(Sales))
train=np.vstack((TV_,Radio_,Newspaper_))
train_=np.transpose(train)
Sales_1=np.vstack(Sales_)
def nonlin(x,deriv=False):
if(deriv==True):
return x*(1-x)
return 1/(1+np.exp(-x))
X = train_#10*3
y = Sales_1 #10*1
np.random.seed(1)
syn0 = 2*np.random.random((3,5)) - 1 #初始化连接权
syn1 = 2*np.random.random((5,1)) - 1
for j in xrange(60000):
l0 = X
l1 = nonlin(np.dot(l0,syn0)) #10*3.*3*5=10*5
l2 = nonlin(np.dot(l1,syn1)) #10*1
l2_error = y - l2
if (j% 10000) == 0:
print "Error:" + str(np.mean(np.abs(l2_error)))
l2_delta = l2_error*nonlin(l2,deriv=True) #=10*1.*
l1_error = l2_delta.dot(syn1.T)#=
l1_delta = l1_error * nonlin(l1,deriv=True)
syn1 += l1.T.dot(l2_delta)
syn0 += l0.T.dot(l1_delta)
print l2
print y.dtype
#test
test=[(218.4-min(TV))/(max(TV)-min(TV)),(27.7-min(Radio))/(max(Radio)-min(Radio)),(53.4-min(Newspaper))/(max(Newspaper)-min(Newspaper))] #1*3
l1_test=nonlin(np.dot(test,syn0)) #1*5
l2_test=nonlin(np.dot(l1_test,syn1)) #1*1
predict=l2_test*(max(Sales)-min(Sales))+min(Sales)
print predict
SQL="insert into predict values(s%)"
cur.execute(SQL)
