#--------------------------------------------------------------------
#範例13-3：查詢，新增查詢的欄位(sum, mean, max, min)
#-------------------------------------------------


#（1）步驟1：建議：mySQL範例要在anaconda裡面測試（不要再cola測試）
#要先安裝：python 3.x版本的安裝mysql的指令：pip install pymysql

#（2）步驟2：安裝mySQL
#安裝： wamp或xamp
#帳號為root，密碼不設定

#（3）步驟3：建立mydb資料庫，匯入scorechi資料表

#（4）步驟4：連線mydb資料庫指令
#import pymysql
#conn = pymysql.connect(host="localhost",user="root", password="", db="ch09")

#（5）步驟5：查詢scorechi資料表，轉成df指令
#import pandas as pd
#df = pd.read_sql_query("select * from books", conn)


#------------------------------
#練習1：讀取mySQL資料庫
#------------------------------

#連線資料庫指令
import pymysql
conn = pymysql.connect(host="localhost", user="root", password="", db="mydb")
#查詢資料表，轉成df指令
import pandas as pd
df = pd.read_sql_query("select * from  scorechi", conn)

print()
print("1-1.顯示全部資料=\n", df)


#------------------------------
#練習2：把欄位名稱『姓名』改成『name』
#SQL：把欄位名稱『姓名』改成『name』：df2 = pd.read_sql_query("select 姓名 as name, 中文, 英文, 數學 from  scorechi", conn)
#Pandas：把欄位名稱『姓名』改成『name』：df.rename(columns = {'姓名':'name'}, inplace = True)
#------------------------------

df2 = pd.read_sql_query("select 姓名 as name, 中文, 英文, 數學 from  scorechi", conn)

print()
print("2-1.SQL：把欄位名稱『姓名』改成『name』=\n", df2)

print()
df.rename(columns = {'姓名':'name'}, inplace = True)
print("2-2.Pandas：把欄位名稱『姓名』改成『name』=\n", df[['name','中文', '英文', '數學']])

#------------------------------
#練習3：新增欄位名稱『總分』
#SQL：新增欄位名稱『總分』=> df2 = pd.read_sql_query("select *,(中文+英文+數學) as 總分 from  scorechi", conn)
#SQL：計算國英數的全部『總分』= df2 => pd.read_sql_query("select *, sum(中文+英文+數學) as 總分 from  scorechi", conn)
#pandas：新增欄位名稱『總分』方法1 => df['總分'] = (df['中文'] + df['英文'] + df['數學'])
#pandas：新增欄位名稱『總分』方法2 => df = df.assign(總分2 = df['中文'] + df['英文'] + df['數學'])
#------------------------------

df2 = pd.read_sql_query("select *,(中文+英文+數學) as 總分 from  scorechi", conn)
print()
print("3-1.SQL：新增欄位名稱『總分』=\n", df2)

df2 = pd.read_sql_query("select *, sum(中文+英文+數學) as 總分 from  scorechi", conn)
print()
print("3-2.SQL：計算國英數的全部『總分』=\n", df2)
# sum(中文+英文+數學) = （中文欄位總分 + 英文欄位總分 + 數學欄位總分）

print()
df['總分'] = (df['中文'] + df['英文'] + df['數學'])
print("3-3.pandas：新增欄位名稱『總分』方法1 =\n", df)

print()
df = df.assign(總分2 = df['中文'] + df['英文'] + df['數學'])
print("3-4.pandas：新增欄位名稱『總分』方法2 =\n", df)


#------------------------------
#練習4：新增欄位名稱『平均』
#SQL：新增欄位名稱『平均』 => df2 = pd.read_sql_query("select *,(中文+英文+數學)/3 as 平均 from scorechi", conn)
#pandas：新增欄位名稱『平均』方法1 => df['平均'] = (df['中文'] + df['英文'] + df['數學'])/3
#pandas：新增欄位名稱『平均』方法2 =>df = df.assign(平均2 = (df['中文'] + df['英文'] + df['數學'])/3 )
#------------------------------

df2 = pd.read_sql_query("select *,(中文+英文+數學)/3 as 平均 from scorechi", conn)

print()
print("4-1.SQL：新增欄位名稱『平均』=\n",  df2)

print()
df['平均'] = (df['中文'] + df['英文'] + df['數學'])/3
print("4-2.pandas：新增欄位名稱『平均』方法1 =\n", df)

print()
df = df.assign(平均2 = (df['中文'] + df['英文'] + df['數學'])/3 )
print("4-3.pandas：新增欄位名稱『平均』方法2 =\n", df)

#------------------------------
#練習5：計算英文欄位的min,max,mean,median
#https://zhuanlan.zhihu.com/p/370851569
#計算英文欄位的min,max,mean,median => df['英文'].agg(['min', 'max', 'mean', 'median'])
#------------------------------
print()
print("5-1.pandas：計算英文欄位的min,max,mean,median=\n", df['英文'].agg(['min', 'max', 'mean', 'median']))

#------------------------------
#練習6：計算彼德的三科目欄位的min,max,mean,median
#https://zhuanlan.zhihu.com/p/370851569
#df.set_index('name', inplace=True)
#pandas：計算彼德的三科目欄位的min,max,mean,median => df[['中文','英文','數學']].loc['彼德'].agg(['min', 'max', 'mean', 'median']))
#------------------------------
print()
df.set_index('name', inplace=True)
print("6-1.pandas：計算彼德的三科目欄位的min,max,mean,median=\n", df[['中文','英文','數學']].loc['彼德'].agg(['min', 'max', 'mean', 'median']))



#關閉連線
conn.close()



#--------------------------------------------------------------------