from pymadlib.pymadlib import *
from pymadlib import example
from pymadlib.example import *
conn = DBConnect()
conn.conn

#View Documentation
mdl = LinearRegression(conn)
print(mdl.train.__doc__)

#Train Model and Score
lreg = LinearRegression(conn)
lreg.train('public.wine_training_set',['1','alcohol','proline','hue','color_intensity','flavanoids'],'quality')
cursor = lreg.predict('public.wine_test_set','quality')
#Print Prediction Results
rowset = conn.printTable(cursor,['id','quality','prediction']) 

#Generate Scatter Plot
cols = conn.fetchColumns(rowset,['quality','prediction'])
actual = cols['quality']
predicted = cols['prediction'] 
scatterPlot(actual,predicted, 'wine_test_set') 

#Train Linear Regression Model on a mixture of Numeric and Categorical Variables
lreg.train('public.auto_mpg_train',['1','height','width','length','highway_mpg','engine_size','make','fuel_type','fuel_system'],'price')
cursor = lreg.predict('public.auto_mpg_test','price')

#Show prediction results
rowset = conn.printTable(cursor,['id','price','prediction']) 

#Display Scatter Plot of Actual Vs Predicted Values
cols = conn.fetchColumns(rowset,['price','prediction'])
actual = cols['price']
predicted = cols['prediction'] 
scatterPlot(actual,predicted, 'auto_mpg_test')  

#1) Logistic Regression with Numeric Variables Alone
log_reg = LogisticRegression(conn)
#Train Model
log_reg.train('public.wine_bool_training_set','indep','quality_label')
#Scoring
cursor = log_reg.predict('wine_bool_test_set','',None)

#Display ROC Curve
cols = conn.fetchColumns(cursor,['quality_label','prediction'])
actual = cols['quality_label']
predicted = cols['prediction']
ROCPlot('ROC curve Logistic Reg. on Continuous Features ',['Logistic Regression'],actual,predicted) 

#2) Logistic Regression with Mixture of Numeric and Categorical Variables

#Train Model
log_reg.train('public.auto_mpg_bool_train',['1','height','width','length','highway_mpg','engine_size','make','fuel_type','fuel_system'],'is_expensive')
#Scoring
cursor = log_reg.predict('auto_mpg_bool_test','is_expensive',None)

#Display ROC Curve
cols = conn.fetchColumns(cursor,['is_expensive','prediction'])
actual = cols['is_expensive']
predicted = cols['prediction']  
ROCPlot('ROC curve Logistic Reg. including categorical data',['Logistic Regression'],actual,predicted) 

#Demonstrate K-Means
example.kmeansDemo(conn)

networkVizDemo()