PyQT5一起学做图书管理系统（2）初始化数据库

深藏阁楼爱情的钟 2022-03-16 05:10 187阅读 0赞

# 初始化数据库 #

## 环境 ##

系统：windows10系统  
编辑器：Sublime3  
编程语言：python3+pyqt5

## 数据库操作 ##

本来数据库这一块是用到再说的，但是整个项目做完之后，发现先把这一块给弄好后，后续的就好办了  
感觉这个数据库就是后端、后台的内容，而界面设计就是前端的东西。

数据库的初始化完全就是sqlite3的内容,主要思路就是，检测本地有没有这个数据库，没有的话直接初始化数据

首先是构造一个数据库类，为后面真正使用的3个类做准备，这个类有一些基本功能，创建，删除，切换库

class DbManager(object):
        def __init__(self, *args):
            self.db = sqlite3.connect(*args)
            self.cursor = self.db.cursor()
    
        def __enter__(self):
            return self.cursor
    
        def __exit__(self, types, value, traceback):
            self.db.commit()
            return False
    
        def __del__(self):
            self.db.commit()
            self.db.close()
    
        def switchDb(self, *args):
            self.db.close()
            self.db = sqlite3.connect(*args)
            self.cursor = self.db.cursor()
    
        def createTable(self, tableString):
            self.cursor.execute(tableString)
            self.db.commit()
    
        def commitAndClose(self):
            self.db.commit()
            self.db.close()

接下来是用户类  
用户类中，实现这么几个函数：初始化数据，添加普通用户，添加管理员，查询用户信息，查询管理员，更新密码，借书还书。

class UserDbManager(DbManager):
        def __init__(self, database=dbpath, *args):
            super().__init__(database, *args)
            self.initDb()
    
        def initDb(self):
            self.createTable(createUserTableString)
    
        def initDatabase(self):
            password='admin123'
            hl = hashlib.md5()                                             #将密码进行md5加密
            hl.update(password.encode(encoding='utf-8'))
            md5password = hl.hexdigest()
            self.addAdminUser('admin','Fengqi',md5password)                #添加管理员账号
    
            password='user123'
            hl = hashlib.md5()                                             #将密码进行md5加密
            hl.update(password.encode(encoding='utf-8'))
            md5password = hl.hexdigest()
            self.addUser('user000000','user000000',md5password)            #添加普通用户
            
    
        def addUser(self,userid, Name, Password,IsAdmin=0):
            '''  添加普通用户    '''
            insertData = self.cursor.execute("""INSERT INTO user 
                    (userid, Name, Password,IsAdmin,TimesBorrowed,NumBorrowed) VALUES 
                    ('{0}', '{1}', '{2}','{3}','{4}','{5}')
                    """.format(userid, Name, Password,IsAdmin,0,0))
            self.db.commit()
    
        def addAdminUser(self,userid, Name, Password):
            ''' 添加管理员用户'''
            self.addUser(userid, Name, Password,IsAdmin=1)
        def querybyUserid(self,userid):
            fetchedData = self.cursor.execute("SELECT * FROM user WHERE userid='%s'" % (userid) )
            #a=fetchedData.fetchall()#通过fetchall接受全部数据，是一个list,list的每个元素是tuple类型数据
    
            return fetchedData.fetchall()
    
        def getAdmineUserinfo(self):
            '''获取管理员用户 '''
            fetchedData = self.cursor.execute("SELECT userid,Name FROM user WHERE IsAdmin=1")
            return fetchedData
            
    
        def getUserinfo(self):
            '''获取一般用户'''
            fetchedData = self.cursor.execute("SELECT userid,Name FROM user WHERE IsAdmin=0")
            return fetchedData
    
        def updatePassword(self,password,userid):
            fetchedData = self.cursor.execute("UPDATE User SET Password='%s' WHERE userid=%s" % (password,userid))
            self.db.commit()
    
        def borrowOrReturnBook(self,userid,borrow=1):
            if borrow == 1 :
                fetchedData = self.cursor.execute("UPDATE User SET TimesBorrowed=TimesBorrowed+1,NumBorrowed=NumBorrowed+1 WHERE userid='%s'" % userid)
            else:
                fetchedData = self.cursor.execute("UPDATE User SET TimesBorrowed=TimesBorrowed-1,NumBorrowed=NumBorrowed-1 WHERE userid='%s'" % userid)
            self.db.commit()

接下来是图书类，有书库初始化，添加删除图书，借书还书，书籍查询等功能

class BookDbManager(DbManager):
        def __init__(self, database=dbpath, *args):
            super().__init__(database, *args)
            self.initDb()
    
        def initDb(self):
            self.createTable(createBookTableString)
    
        def initDatabase(self):
            self.addBOOK('力学', 'IS1000', '刘斌', '教育', '中国科学技术大学 ', '1999-01-01', 100, 100, 0)
            self.addBOOK('微积分', 'IS1001', '牛顿莱布尼兹', '教育', '中国科学技术大学', '1998-01-01', 14, 14, 0)
            self.addBOOK('电磁场论', 'IS1002', '叶邦角', '教育', '中国科学技术大学', '1997-01-01', 24, 24, 0)
            self.addBOOK('热学', 'IS1003', '张鹏飞', '教育', '中国科学技术大学', '2002-01-01', 45, 45, 0)
            self.addBOOK('电动力学', 'IS1004', '叶邦角', '教育', '中国科学技术大学', '2003-01-01', 100, 100, 0)
            self.addBOOK('数据库', 'IS1006', '袁平波', '教育', '中国科学技术大学', '2010-01-01', 10, 10, 0)
            self.addBOOK ('电磁学', 'IS1005', '叶邦角', '教育', '中国科学技术大学 ', '2012-01-01', 43, 43, 0)
            self.addBOOK ('数学分析', 'IS1007', '陈卿', '教育', '中国科学技术大学', '2013-01-01', 23, 23, 0)
            self.addBOOK('吉米多维奇题解1', 'IS1008', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
            self.addBOOK('吉米多维奇题解2', 'IS1009', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
            self.addBOOK('吉米多维奇题解3', 'IS1010', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
            self.addBOOK('吉米多维奇题解4', 'IS1011', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
            self.addBOOK('吉米多维奇题解5', 'IS1012', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
            self.addBOOK('吉米多维奇题解6', 'IS1013', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
            self.addBOOK('朗道力学', 'IS1014', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道电动力学', 'IS1015', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道量子力学', 'IS1016', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道量子电动力学', 'IS1017', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道统计物理学', 'IS1018', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道流体力学', 'IS1019', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道弹性理论力学', 'IS1020', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道物理动力学', 'IS1021', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('植物学', 'IS1022', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
            self.addBOOK('动物学', 'IS1023', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
            self.addBOOK('细胞生物学', 'IS1024', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
            self.addBOOK('动物生理学', 'IS1025', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
            self.addBOOK('古生物学', 'IS1026', '佚名', '生物学', '高等教育出版社', '2011-05-01', 100, 100, 0)
            self.addBOOK('高等数学', 'IS1027', '佚名', '教育', '高等教育出版社', '2011-05-01', 50, 50, 0)
            self.addBOOK('线性代数', 'IS1029', '佚名', '教育', '高等教育出版社', '2011-05-01', 50, 50, 0)
            self.addBOOK('C++程序设计', 'IS1030', '孙广中', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
            self.addBOOK('C程序设计', 'IS1031', '郑重', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
            self.addBOOK('数据结构', 'IS1032', '顾为兵', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
            self.addBOOK('信号与系统', 'IS1033', '李卫平', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
            self.addBOOK('线性电子线路', 'IS1034', '陆伟', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
    
        def addBOOK(self,BookName,BookID, Auth, Category,Publisher,PublishTime,NumStorage,NumCanBorrow,NumBorrowed):
            '''  添加书籍    '''
            insertData = self.cursor.execute("""INSERT INTO Book 
                    (BookName,BookID, Auth, Category,Publisher,PublishTime,NumStorage,NumCanBorrow,NumBorrowed) VALUES 
                    ('{0}', '{1}', '{2}','{3}','{4}','{5}','{6}','{7}','{8}')
                    """.format(BookName,BookID, Auth, Category,Publisher,PublishTime,NumStorage,NumCanBorrow,NumBorrowed))
            self.db.commit()
    
        def dropBook(self,bookId):
            insertData = self.cursor.execute("DELETE  FROM Book WHERE BookID='%s'" % (bookId))
            self.db.commit()
    
        def updateBookinfo(self,addBookNum,bookId,addFlag=1):
            if addFlag == 1:
                self.cursor.execute("UPDATE Book SET NumStorage=NumStorage+%d,NumCanBorrow=NumCanBorrow+%d WHERE BookID='%s'" % (
                        addBookNum, addBookNum, bookId))
            else:
                self.cursor.execute("UPDATE Book SET NumStorage=NumStorage-%d,NumCanBorrow=NumCanBorrow-%d WHERE BookID='%s'" % (
                        addBookNum, addBookNum, bookId))
            self.db.commit()
    
        def getBookinfo(self):
            '''获得所有书籍'''
            fetchedData = self.cursor.execute("SELECT * from Book ")
            return fetchedData.fetchall()
    
        def querybyBookID(self,BookID):
            fetchedData = self.cursor.execute("SELECT * FROM Book WHERE BookID='%s'" % (BookID) )
            return fetchedData.fetchall()
            #return self.queryBookByKeywords(userid)
    
        def queryBookByKeywords(self,keywords):
            fetchedData = self.cursor.execute("SELECT * from Book ORDER BY %s limit %s,%s" % (keywords,0,5))
            return fetchedData.fetchall()
    
        def borrowOrReturnBook(self,BookID,borrowflag=1):
            if borrowflag == 1 :
                fetchedData = self.cursor.execute( "UPDATE Book SET NumCanBorrow=NumCanBorrow-1,NumBorrowed=NumBorrowed+1 WHERE BookID='%s'" % BookID)
            else:
                fetchedData = self.cursor.execute( "UPDATE Book SET NumCanBorrow=NumCanBorrow+1,NumBorrowed=NumBorrowed-1 WHERE BookID='%s'" % BookID)
            self.db.commit()

最后是添加删除类

class AddOrDropManager(DbManager):
        def __init__(self, database=dbpath, *args):
            super().__init__(database, *args)
            self.initDb()
    
        def initDb(self):
            self.createTable(createAddOrDropBookTableString)
    
        def initDatabase(self):
            self.insertValue('IS1000', '2018-04-22', 1, 100)
            self.insertValue('IS1001', '2018-04-22', 1, 14)
            self.insertValue('IS1002', '2018-04-22', 1, 24)
            self.insertValue('IS1003', '2018-04-22', 1, 45)
            self.insertValue('IS1004', '2018-04-22', 1, 100)
            self.insertValue('IS1004', '2018-04-27', 1, 45)
            self.insertValue('IS1005', '2018-04-27', 1, 45)
            self.insertValue('IS1006', '2018-04-28', 1, 10)
            self.insertValue('IS1007', '2018-04-28', 1, 23)
            self.insertValue('IS1008', '2018-04-28', 1, 50)
            self.insertValue('IS1009', '2018-04-28', 1, 50)
            self.insertValue('IS1010', '2018-04-28', 1, 50)
            self.insertValue('IS1011', '2018-04-28', 1, 50)
            self.insertValue('IS1012', '2018-04-28', 1, 50)
            self.insertValue('IS1013', '2018-04-28', 1, 50)
            self.insertValue('IS1014', '2018-04-28', 1, 50)
            self.insertValue('IS1015', '2018-04-28', 1, 50)
            self.insertValue('IS1016', '2018-04-28', 1, 50)
            self.insertValue('IS1017', '2018-04-28', 1, 50)
            self.insertValue('IS1018', '2018-04-28', 1, 50)
            self.insertValue('IS1019', '2018-04-28', 1, 50)
            self.insertValue('IS1020', '2018-04-28', 1, 50)
            self.insertValue('IS1021', '2018-04-28', 1, 50)
            self.insertValue('IS1022', '2018-04-28', 1, 50)
            self.insertValue('IS1023', '2018-04-28', 1, 50)
            self.insertValue('IS1024', '2018-04-28', 1, 50)
            self.insertValue('IS1025', '2018-04-28', 1, 50)
            self.insertValue('IS1026', '2018-04-28', 1, 100)
            self.insertValue('IS1027', '2018-04-28', 1, 50)
            self.insertValue('IS1029', '2018-04-28', 1, 50)
            self.insertValue('IS1030', '2018-04-28', 1, 50)
            self.insertValue('IS1031', '2018-04-28', 1, 50)
            self.insertValue('IS1032', '2018-04-28', 1, 50)
            self.insertValue('IS1033', '2018-04-28', 1, 50)
            self.insertValue('IS1034', '2018-04-28', 1, 50)
    
    
        def insertValue(self,BookID,time,AddorDrop,addBookNum):
            insertData = self.cursor.execute("INSERT INTO AddOrDrop VALUES ('%s','%s',%d,%d)" % (BookID, time, AddorDrop,addBookNum))
            self.db.commit()
    
        def addinfo(self,BookID,time,addBookNum):
            self.insertValue(BookID,time,1,addBookNum)
    
        def dropinfo(self,BookID,time,addBookNum):
            self.insertValue(BookID,time,0,addBookNum)
    
        def getAllinfo(self):
            '''获得所有书籍'''
            fetchedData = self.cursor.execute("SELECT * from AddOrDrop ")
            return fetchedData.fetchall()

有了数据库，后面的就简单了，整体代码如下：

# -*- coding: utf-8 -*-
    # @Date     : 2018-12-12 15:31:22
    # @Author   : Jimy_Fengqi (jmps515@163.com)
    # @Link     : https://blog.csdn.net/qiqiyingse
    # @Version  : V1.0
    # @pyVersion: 3.6
    
    import os
    import os.path
    import sqlite3
    import hashlib
    
    home = os.path.expanduser('~')
    
    if '.BookManagerSystem' not in os.listdir(home):
        os.mkdir(os.path.join(home, '.BookManagerSystem'))
    
    dbpath = os.path.join(home, '.BookManagerSystem', 'LibraryManagement.db')
    
    createUserTableString = """
    CREATE TABLE IF NOT EXISTS user(
        userid CHAR(10) PRIMARY KEY ,
        Name VARCHAR(20),
        Password CHAR(32),
        IsAdmin BIT,
        TimesBorrowed INT,
        NumBorrowed INT    
    )"""
    
    createUser_BookTableString = """
    CREATE TABLE IF NOT EXISTS User_Book(
        userid CHAR(10),
        BookID CHAR(6) PRIMARY KEY,
        BorrowTime DATE,
        ReturnTime DATE,
        BorrowState BIT
    )"""
    
    createBookTableString = """
    CREATE TABLE IF NOT EXISTS Book(
        BookName VARCHAR(30),
        BookID CHAR(6),
        Auth VARCHAR(20),
        Category VARCHAR(10),
        Publisher VARCHAR(20),
        PublishTime DATE,
        NumStorage INT,
        NumCanBorrow INT,
        NumBorrowed INT 
    )"""
    
    
    createAddOrDropBookTableString = """
    CREATE TABLE IF NOT EXISTS AddOrDrop(
        BookID CHAR(6),
        ModifyTime  DATE,
        AddOrDrop INT,
        Numbers INT    
    )"""
    
    
    class DbManager(object):
    
        def __init__(self, *args):
            self.db = sqlite3.connect(*args)
            self.cursor = self.db.cursor()
    
        def __enter__(self):
    
            return self.cursor
    
        def __exit__(self, types, value, traceback):
            self.db.commit()
    
            return False
    
        def __del__(self):
            self.db.commit()
            self.db.close()
    
        def switchDb(self, *args):
            self.db.close()
    
            self.db = sqlite3.connect(*args)
            self.cursor = self.db.cursor()
    
        def createTable(self, tableString):
            self.cursor.execute(tableString)
            self.db.commit()
    
        def commitAndClose(self):
            self.db.commit()
            self.db.close()
    
    class UserBookManager(DbManager):
        def __init__(self, database=dbpath, *args):
            super().__init__(database, *args)
            self.initDb()
    
        def initDb(self):
            self.createTable(createUser_BookTableString)
    
        def queryBorrowBook(self,userid,BookID):
           result=self.cursor.execute( "SELECT * FROM User_Book WHERE userid='%s' AND BookID='%s' AND BorrowState=1" %(userid,BookID))
           return result.fetchall()
    
        def countBorrowNum(self,userid):
           result=self.cursor.execute(" SELECT COUNT(userid) FROM User_Book WHERE userid='%s' AND BorrowState=1" % (userid))
           return result.fetchall()
    
        def borrowStatus(self,userid,BookID):
           result=self.cursor.execute( "SELECT COUNT(userid) FROM User_Book WHERE  userid='%s' AND BookID='%s' AND BorrowState=1" % (
            userid,BookID))
           return result.fetchall()
    
        def borrowOrReturnBook(self,userid, BookID, timenow,borrowflag=1):
            if borrowflag == 1:
                result=self.cursor.execute( "INSERT INTO User_Book VALUES ('%s','%s','%s',NULL,1)" % (userid, BookID, timenow))
            else:
               result=self.cursor.execute("UPDATE User_Book SET ReturnTime='%s',BorrowState=0 WHERE userID='%s' AND BookID='%s' AND BorrowState=1" % (timenow,userid,BookID))
            self.db.commit()
    class AddOrDropManager(DbManager):
        def __init__(self, database=dbpath, *args):
            super().__init__(database, *args)
            self.initDb()
    
        def initDb(self):
            self.createTable(createAddOrDropBookTableString)
    
        def initDatabase(self):
            self.insertValue('IS1000', '2018-04-22', 1, 100)
            self.insertValue('IS1001', '2018-04-22', 1, 14)
            self.insertValue('IS1002', '2018-04-22', 1, 24)
            self.insertValue('IS1003', '2018-04-22', 1, 45)
            self.insertValue('IS1004', '2018-04-22', 1, 100)
            self.insertValue('IS1004', '2018-04-27', 1, 45)
            self.insertValue('IS1005', '2018-04-27', 1, 45)
            self.insertValue('IS1006', '2018-04-28', 1, 10)
            self.insertValue('IS1007', '2018-04-28', 1, 23)
            self.insertValue('IS1008', '2018-04-28', 1, 50)
            self.insertValue('IS1009', '2018-04-28', 1, 50)
            self.insertValue('IS1010', '2018-04-28', 1, 50)
            self.insertValue('IS1011', '2018-04-28', 1, 50)
            self.insertValue('IS1012', '2018-04-28', 1, 50)
            self.insertValue('IS1013', '2018-04-28', 1, 50)
            self.insertValue('IS1014', '2018-04-28', 1, 50)
            self.insertValue('IS1015', '2018-04-28', 1, 50)
            self.insertValue('IS1016', '2018-04-28', 1, 50)
            self.insertValue('IS1017', '2018-04-28', 1, 50)
            self.insertValue('IS1018', '2018-04-28', 1, 50)
            self.insertValue('IS1019', '2018-04-28', 1, 50)
            self.insertValue('IS1020', '2018-04-28', 1, 50)
            self.insertValue('IS1021', '2018-04-28', 1, 50)
            self.insertValue('IS1022', '2018-04-28', 1, 50)
            self.insertValue('IS1023', '2018-04-28', 1, 50)
            self.insertValue('IS1024', '2018-04-28', 1, 50)
            self.insertValue('IS1025', '2018-04-28', 1, 50)
            self.insertValue('IS1026', '2018-04-28', 1, 100)
            self.insertValue('IS1027', '2018-04-28', 1, 50)
            self.insertValue('IS1029', '2018-04-28', 1, 50)
            self.insertValue('IS1030', '2018-04-28', 1, 50)
            self.insertValue('IS1031', '2018-04-28', 1, 50)
            self.insertValue('IS1032', '2018-04-28', 1, 50)
            self.insertValue('IS1033', '2018-04-28', 1, 50)
            self.insertValue('IS1034', '2018-04-28', 1, 50)
    
    
        def insertValue(self,BookID,time,AddorDrop,addBookNum):
            insertData = self.cursor.execute("INSERT INTO AddOrDrop VALUES ('%s','%s',%d,%d)" % (BookID, time, AddorDrop,addBookNum))
            self.db.commit()
    
        def addinfo(self,BookID,time,addBookNum):
            self.insertValue(BookID,time,1,addBookNum)
    
        def dropinfo(self,BookID,time,addBookNum):
            self.insertValue(BookID,time,0,addBookNum)
    
        def getAllinfo(self):
            '''获得所有书籍'''
            fetchedData = self.cursor.execute("SELECT * from AddOrDrop ")
            return fetchedData.fetchall()
    
    class BookDbManager(DbManager):
        def __init__(self, database=dbpath, *args):
            super().__init__(database, *args)
            self.initDb()
    
        def initDb(self):
            self.createTable(createBookTableString)
    
        def initDatabase(self):
            self.addBOOK('力学', 'IS1000', '刘斌', '教育', '中国科学技术大学 ', '1999-01-01', 100, 100, 0)
            self.addBOOK('微积分', 'IS1001', '牛顿莱布尼兹', '教育', '中国科学技术大学', '1998-01-01', 14, 14, 0)
            self.addBOOK('电磁场论', 'IS1002', '叶邦角', '教育', '中国科学技术大学', '1997-01-01', 24, 24, 0)
            self.addBOOK('热学', 'IS1003', '张鹏飞', '教育', '中国科学技术大学', '2002-01-01', 45, 45, 0)
            self.addBOOK('电动力学', 'IS1004', '叶邦角', '教育', '中国科学技术大学', '2003-01-01', 100, 100, 0)
            self.addBOOK('数据库', 'IS1006', '袁平波', '教育', '中国科学技术大学', '2010-01-01', 10, 10, 0)
            self.addBOOK ('电磁学', 'IS1005', '叶邦角', '教育', '中国科学技术大学 ', '2012-01-01', 43, 43, 0)
            self.addBOOK ('数学分析', 'IS1007', '陈卿', '教育', '中国科学技术大学', '2013-01-01', 23, 23, 0)
            self.addBOOK('吉米多维奇题解1', 'IS1008', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
            self.addBOOK('吉米多维奇题解2', 'IS1009', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
            self.addBOOK('吉米多维奇题解3', 'IS1010', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
            self.addBOOK('吉米多维奇题解4', 'IS1011', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
            self.addBOOK('吉米多维奇题解5', 'IS1012', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
            self.addBOOK('吉米多维奇题解6', 'IS1013', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
            self.addBOOK('朗道力学', 'IS1014', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道电动力学', 'IS1015', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道量子力学', 'IS1016', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道量子电动力学', 'IS1017', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道统计物理学', 'IS1018', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道流体力学', 'IS1019', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道弹性理论力学', 'IS1020', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('朗道物理动力学', 'IS1021', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
            self.addBOOK('植物学', 'IS1022', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
            self.addBOOK('动物学', 'IS1023', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
            self.addBOOK('细胞生物学', 'IS1024', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
            self.addBOOK('动物生理学', 'IS1025', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
            self.addBOOK('古生物学', 'IS1026', '佚名', '生物学', '高等教育出版社', '2011-05-01', 100, 100, 0)
            self.addBOOK('高等数学', 'IS1027', '佚名', '教育', '高等教育出版社', '2011-05-01', 50, 50, 0)
            self.addBOOK('线性代数', 'IS1029', '佚名', '教育', '高等教育出版社', '2011-05-01', 50, 50, 0)
            self.addBOOK('C++程序设计', 'IS1030', '孙广中', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
            self.addBOOK('C程序设计', 'IS1031', '郑重', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
            self.addBOOK('数据结构', 'IS1032', '顾为兵', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
            self.addBOOK('信号与系统', 'IS1033', '李卫平', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
            self.addBOOK('线性电子线路', 'IS1034', '陆伟', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
    
        def addBOOK(self,BookName,BookID, Auth, Category,Publisher,PublishTime,NumStorage,NumCanBorrow,NumBorrowed):
            '''  添加书籍    '''
            insertData = self.cursor.execute("""INSERT INTO Book 
                    (BookName,BookID, Auth, Category,Publisher,PublishTime,NumStorage,NumCanBorrow,NumBorrowed) VALUES 
                    ('{0}', '{1}', '{2}','{3}','{4}','{5}','{6}','{7}','{8}')
                    """.format(BookName,BookID, Auth, Category,Publisher,PublishTime,NumStorage,NumCanBorrow,NumBorrowed))
            self.db.commit()
    
        def dropBook(self,bookId):
            insertData = self.cursor.execute("DELETE  FROM Book WHERE BookID='%s'" % (bookId))
            self.db.commit()
    
        def updateBookinfo(self,addBookNum,bookId,addFlag=1):
            if addFlag == 1:
                self.cursor.execute("UPDATE Book SET NumStorage=NumStorage+%d,NumCanBorrow=NumCanBorrow+%d WHERE BookID='%s'" % (
                        addBookNum, addBookNum, bookId))
            else:
                self.cursor.execute("UPDATE Book SET NumStorage=NumStorage-%d,NumCanBorrow=NumCanBorrow-%d WHERE BookID='%s'" % (
                        addBookNum, addBookNum, bookId))
            self.db.commit()
    
    
    
        def getBookinfo(self):
            '''获得所有书籍'''
            fetchedData = self.cursor.execute("SELECT * from Book ")
            return fetchedData.fetchall()
    
        def querybyBookID(self,BookID):
            fetchedData = self.cursor.execute("SELECT * FROM Book WHERE BookID='%s'" % (BookID) )
            return fetchedData.fetchall()
            #return self.queryBookByKeywords(userid)
    
    
        def queryBookByKeywords(self,keywords):
            fetchedData = self.cursor.execute("SELECT * from Book ORDER BY %s limit %s,%s" % (keywords,0,5))
            return fetchedData.fetchall()
    
        def borrowOrReturnBook(self,BookID,borrowflag=1):
            if borrowflag == 1 :
                fetchedData = self.cursor.execute( "UPDATE Book SET NumCanBorrow=NumCanBorrow-1,NumBorrowed=NumBorrowed+1 WHERE BookID='%s'" % BookID)
            else:
                fetchedData = self.cursor.execute( "UPDATE Book SET NumCanBorrow=NumCanBorrow+1,NumBorrowed=NumBorrowed-1 WHERE BookID='%s'" % BookID)
            self.db.commit()
    
    
    class UserDbManager(DbManager):
        def __init__(self, database=dbpath, *args):
            super().__init__(database, *args)
            self.initDb()
    
        def initDb(self):
            self.createTable(createUserTableString)
    
    
        def initDatabase(self):
    
            password='admin123'
            hl = hashlib.md5()                                                  #将密码进行md5加密
            hl.update(password.encode(encoding='utf-8'))
            md5password = hl.hexdigest()
            self.addAdminUser('admin','Fengqi',md5password)                     #添加管理员账号
    
            password='user123'
            hl = hashlib.md5()                                                  #将密码进行md5加密
            hl.update(password.encode(encoding='utf-8'))
            md5password = hl.hexdigest()
            self.addUser('user000000','user000000',md5password)               #添加普通用户
            
    
        def addUser(self,userid, Name, Password,IsAdmin=0):
            '''  添加普通用户    '''
            insertData = self.cursor.execute("""INSERT INTO user 
                    (userid, Name, Password,IsAdmin,TimesBorrowed,NumBorrowed) VALUES 
                    ('{0}', '{1}', '{2}','{3}','{4}','{5}')
                    """.format(userid, Name, Password,IsAdmin,0,0))
            self.db.commit()
    
        def addAdminUser(self,userid, Name, Password):
            ''' 添加管理员用户'''
            self.addUser(userid, Name, Password,IsAdmin=1)
        def querybyUserid(self,userid):
            fetchedData = self.cursor.execute("SELECT * FROM user WHERE userid='%s'" % (userid) )
            #a=fetchedData.fetchall()#通过fetchall接受全部数据，是一个list,list的每个元素是tuple类型数据
    
            return fetchedData.fetchall()
    
        def getAdmineUserinfo(self):
            '''获取管理员用户 '''
            fetchedData = self.cursor.execute("SELECT userid,Name FROM user WHERE IsAdmin=1")
            return fetchedData
            
    
        def getUserinfo(self):
            '''获取一般用户'''
            fetchedData = self.cursor.execute("SELECT userid,Name FROM user WHERE IsAdmin=0")
            return fetchedData
    
        def updatePassword(self,password,userid):
            fetchedData = self.cursor.execute("UPDATE User SET Password='%s' WHERE userid=%s" % (password,userid))
            self.db.commit()
    
        def borrowOrReturnBook(self,userid,borrow=1):
            if borrow == 1 :
                fetchedData = self.cursor.execute("UPDATE User SET TimesBorrowed=TimesBorrowed+1,NumBorrowed=NumBorrowed+1 WHERE userid='%s'" % userid)
            else:
                fetchedData = self.cursor.execute("UPDATE User SET TimesBorrowed=TimesBorrowed-1,NumBorrowed=NumBorrowed-1 WHERE userid='%s'" % userid)
            self.db.commit()
    
    
    def testuserdb():
        userDb = UserDbManager()
        userDb.addAdminUser('admin', 'admin', '123456')
        userDb.addAdminUser('administrator', 'admin1', '123456')
        userDb.addUser('Test', 'AAA', '123456')
        userDb.addUser('Test1', 'BBB', '123456')
        userDb.addUser('Test2', 'CCC', '123456')
        userDb.getAdmineUser()
        userDb.getUser()
        userDb.queryUser('admins')
        userDb.queryUser('admin')
    
    def testAddDropBookData():
        userDb = AddOrDropManager()
    
        allbook=userDb.getAllinfo()
        for book in allbook:
            print(book)
            #print(" ".join('%s' %ids for ids in a))
            #a=list(book)
            #print(a)
        
    def testBookDB():
        userDb = BookDbManager()
        if len(userDb.querybyBookID('IS1006')):
            print('书籍已经存在，更新数量')
            userDb.updateBookinfo(10,'IS1005')
        else:
            print('书籍不存在，直接插入')
            userDb.addBOOK('力学3',   'IS1006'  ,'刘斌3',  '教育',  '中国科学技术大学', '1999-01-01',  '34' , '34' , '1')
    
        allbook=userDb.getBookinfo()
    
        print('all book length =%d' % len(allbook))
        for book in allbook:
            print(book)
    
        print('按照bookid查询')
        bookid=userDb.querybyBookID('IS1006')
        if len(bookid):
            print(bookid)
    
        print('按照auth排序查询前几页')
        keybook=userDb.queryBookByKeywords('Auth')
        print(keybook)
    
    if __name__ == '__main__':
        testuserdb()
        testAddDropBookData()
        testBookDB()