'''
建议查看最后的主程序main

'''











import pymysql

class OperDB:

    db =None
    cursor =None

    def __init__(self,dbname,pword=""):

        try:
            self.db =pymysql.connect(host = "localhost", user = "root",\
		password = pword, db = dbname, charset = "utf8")

            self.cursor =self.db.cursor()

        except Exception as err:
            print("打开数据库失败：",str(err)[1:-1])

############################
    #析构函数，断开连接
    def __del__(self):
        self.db.close()
################################
    #查询表方法,查询数据库中的表table的数据  
    def findAll(self,table):
        return self.execute("select * from %s"%(table))

    #删除表方法，
    def del_table(self,table):
        self.execute("delete * from %s"%(table))

    #添加表
    def add_unitable(self,table):
        self.execute("\
            create table %s(\
            id int unsigned not null auto_increment,\
            name varchar(32) not null,\
            age int unsigned not null,\
            classid varchar(32) default null,\
            primary key (id)\
            )engine=innodb auto_increment=1 default charset=utf8;"%(table))

     #查看表
    def see_tables(self):
        return self.execute("show tables")

    

    #传入aql字符串，函数执行数据库操作
    def execute(self,sql):

        #flag用于判断是否需要返回数据，=0为，不返回值
        flag =0
        a =sql.split(" ")
        if a[0] == "select" or "Select":
            flag = 1

        try:

            #执行sql语句
            self.cursor.execute(sql)
            #提交
            self.db.commit()

            if flag ==1:
                result =self.cursor.fetchall()
        except Exception as err:
            #事务回滚，清除未完成的操作
            self.db.rollback()
            print("SQL表达式错误：",str(err)[1:-1])

            if flag ==1:
                return None
            else:
                return False

        if flag ==1:
            return result
        else:
            return True

    def insert(self,tbname,dic): #dic是一个数组
        data ="insert into %s(name,age,classid) values('%s','%s','%s')"%(tbname,dic[0],dic[1],dic[2])

        print(data,"=",type(data))
        self.execute(data)

    def insert1(self,tbname,dic): #dic是一个zidian
        data ="insert into %s(name,age,classid) values('%s','%s','%s')"\
               %(tbname,dic['name'],dic['age'],dic['classid'])

        ##print(data,"=",type(data))
        self.execute(data)



#继承基类的学生数据库类(适用于teminal界面),

class StuDB(OperDB):
    def __init__(self):
        #使用父类属性方法
        super(StuDB,self).__init__("mydb")

###################################
    #表内数据添加操作
    def add_data(self,tbname):

        count=0
        tn={}
        while True:

            tn["name"] = input("请输入姓名：")
            data1 = self.findAll("stulist")
            
            m = 0
            #***#用于遍历是否有重复的数据
            while m<len(data1):
                if data1[m][1] == tn["name"]:
                    m=0
                    tn["name"] =input("姓名输入重复！\n请重新输入姓名：")
                else:
                    m = m+1
            
            tn["age"] =input("请正确输入年龄：")
            
            while True:
                if tn["age"].isdigit():
                    break
                else:
                    tn["age"] =input("年龄输入有误！\n请正确输入年龄：")
                
            tn["classid"] =input("请输入班级：")

            ##print("***",tn)

            val = self.insert1(tbname,tn)
            self.show_data1(tbname)
            
            if val == False:
                print("共添加信息%d条。"%count)
                return

            count+=1

            while True:
                i = input("按1，继续添加，\n按0结束添加\n>>>")

                if i == '1':
                    break
                elif i == '0':
                    print("共添加信息%d条。"%count)
                    return
                else:
                    print("请正确输入！")

###################################
    #表内数据查看操作
    def show_data(self,tbname):

        data = self.findAll(tbname)

        pa = "||{:<5}||{:<10}||{:<5}||{:<10}||"
        
        print("##" + "="*36 + "##")
        print("||{:^36}||".format("student list"))
        print(pa.format("ID", "name", "age", "classid"))

        for i in data:
            print(pa.format(i[0], i[1], i[2], i[3]))
        print("||" + " "*36 + "||")
        print("##" + "="*36 + "##")            
        
    def show_data1(self,tbname):

        data = self.findAll(tbname)

        pa = "|{:<5}|{:<10}|{:<5}|{:<10}|"
        
        print("#" + "="*33 + "#")
        print("|{:^33}|".format("student list"))
        print(pa.format("ID", "name", "age", "classid"))

        for i in data:
            print(pa.format(i[0], i[1], i[2], i[3]))
        print("|" + " "*33 + "|")
        print("#" + "="*33 + "#")


#############################
    #表内数据删除操作
    def del_data(self,tbname):
        count =0

        while True:
            sid = input("请输入待删除学生的id号：")
            m=0
            n=self.findAll(tbname)
            while m<len(n):

                if int(sid) == n[m][0]:
                    break
                else:
                    m+=1

                if (m ==len(n)-1) and (int(sid) != n[m][0]):
                    print("删除的id号不存在！")
                    while True:
                        ifa = input("继续按1，结束按0：")
                        if ifa=="1":
                            self.del_data(tbname)
                        elif ifa=="0":
                            return
                        else:
                            print("错误，请重新输入按键值！")

            val = self.execute("delete from stulist where id = %d"%int(sid))
            self.show_data1(tbname)
            if val == False:
                print("共删除信息%d条。"%count)
                return

            count+=1

            while True:
                i = input("按1，继续删除，\n按0结束删除\n>>>")

                if i == '1':
                    break
                elif i == '0':
                    print("共删除信息%d条。"%count)
                    return
                else:
                    print("请正确输入！")            

                





            
        
            
            
        
        


    





'''
s = StuDB()
s.add_data("stulist")
for i in s.findAll("stulist"):
    print(i)
a=s.findAll("stulist")
'''



def main():
    ##
    s = StuDB()  #默认使用"mydb"
    s.show_data1("stulist") #查看数据
    s.add_data("stulist") #增加数据
    #s.del_data("stulist")#删除数据


if __name__ == "__main__":
    main()





'''
##
##s=OperDB("mydb")
b=["ppp42","45","p9"]
a=s.insert("stulist",b)   show     
print(type(b))

#print(s.findAll("stulist"))

for i in s.findAll("stulist"):
    print(i)

s=OperDB("mydb")
b={"name":"ddhk","age":"23","classid":"pp9"}
a=s.insert1("stulist",b)        
print(type(b))

#print(s.findAll("stulist"))

for i in s.findAll("stulist"):
    print(i)

'''