import sqlite3
import os

class DBOperate:

    def __init__(self,dbPath=os.path.join(os.getcwd(),"db")):
        self.dbPath=dbPath
        self.connect=sqlite3.connect(self.dbPath)

    def Query(self,sql:str)->list:
        """傳統(tǒng)查詢語句"""
        print(f"query sql is\n{sql}")
        queryResult = self.connect.cursor().execute(sql).fetchall()
        return queryResult if queryResult else []

    def QueryAsDict(self,sql:str)->dict:
        """調(diào)用該函數(shù)返回結(jié)果為字典形式"""
        self.connect.row_factory=self.dictFactory
        cur=self.connect.cursor()
        queryResult=cur.execute(sql).fetchall()
        return queryResult if queryResult else {}

    def Insert(self,sql:str)->bool:
        print(f"執(zhí)行的sql語句為\n{sql}")
        insertRows=self.connect.cursor().execute(sql)
        self.connect.commit()
        return True if insertRows.rowcount else False

    def Update(self,sql:str)->bool:
        updateRows=self.connect.cursor().execute(sql)
        self.connect.commit()
        print(f"update sql is\n{sql}")
        return  True if updateRows.rowcount else False


    def Delete(self,sql:str)->bool:
        delRows=self.connect.cursor().execute(sql)
        self.connect.commit()
        print(f"delete sql is\n{sql}")
        return True if delRows.rowcount else False

    def CloseDB(self):
        self.connect.cursor().close()
        self.connect.close()

    def dictFactory(self,cursor,row):
        """將sql查詢結(jié)果整理成字典形式"""
        d={}
        for index,col in enumerate(cursor.description):
            d[col[0]]=row[index]
        return d