# -- coding: utf-8 --
"""
代碼功能：使用python操作sql sever數(shù)據(jù)庫惕耕。
"""
import pymssql
import pandas as pd

class SqlServer(object):  

    def __init__(self): 
        self.get_conn()


    def get_conn(self): 
        try:
            self.conn = pymssql.connect(
                host = "127.0.0.1",
                user = "sa",
                password = "ljc123",
                database = "Iris_Data",
                charset = 'utf8'
                )
        except Exception as e:
            print("Error : %s" % e)

    def close_conn(self):   
        try:
            if self.conn:
                self.conn.close()
        except Exception as e:
            print("Error: %s" % e)

    def ExecQuery(self):
        sql = "select * from iris"
        cursor = self.conn.cursor()
        cursor.execute(sql)
        # 轉(zhuǎn)換為字典的形式化
        resList = [dict(zip([k[0] for k in cursor.description], row))
            for row in cursor.fetchall() ]
        self.conn.close()
        return resList


    def add_one(self,sepal_length, sepal_width, petal_length, petal_width, target):
        try:
            sql =(
                "INSERT INTO iris VALUES ( %s, %s, %s, %s, %s );"
            )
            cursor = self.conn.cursor()
            cursor.execute(sql, (sepal_length, sepal_width, petal_length, petal_width, target))
            self.conn.commit()
            cursor.close()
        except :
            print("Error")
            self.conn.rollback() 
        self.close_conn()

    def add_more(self):
        try:
            sql =(
                "INSERT INTO iris VALUES ( %s, %s, %s, %s, %s );"
            )
            cursor = self.conn.cursor()
            df =  pd.DataFrame(pd.read_csv('iris.csv',header=None))

            for index in df.index:
                sepal_length = df.loc[index].values[0]
                sepal_width = df.loc[index].values[1]
                petal_length = df.loc[index].values[2]
                petal_width = df.loc[index].values[3]
                target = df.loc[index].values[4]
                print(sepal_length, sepal_width, petal_length, petal_width, target)
                cursor.execute(sql, (sepal_length, sepal_width, petal_length, petal_width, target))
            self.conn.commit()
            cursor.close()
            
        except :
            print("Error")
            self.conn.rollback()
        self.close_conn()
        pass    

def main():
    obj = SqlServer()
    #obj.add_more()
    print(obj.ExecQuery())
    print("************************** Finish **************************")

if __name__ == '__main__':
    main()