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

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

初始化数据库

环境

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

数据库操作

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

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

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

  1. class DbManager(object):
  2.     def __init__(self, *args):
  3.         self.db = sqlite3.connect(*args)
  4.         self.cursor = self.db.cursor()
  6.     def __enter__(self):
  7.         return self.cursor
  9.     def __exit__(self, types, value, traceback):
  10.         self.db.commit()
  11.         return False
  13.     def __del__(self):
  14.         self.db.commit()
  15.         self.db.close()
  17.     def switchDb(self, *args):
  18.         self.db.close()
  19.         self.db = sqlite3.connect(*args)
  20.         self.cursor = self.db.cursor()
  22.     def createTable(self, tableString):
  23.         self.cursor.execute(tableString)
  24.         self.db.commit()
  26.     def commitAndClose(self):
  27.         self.db.commit()
  28.         self.db.close()

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

  1. class UserDbManager(DbManager):
  2.     def __init__(self, database=dbpath, *args):
  3.         super().__init__(database, *args)
  4.         self.initDb()
  6.     def initDb(self):
  7.         self.createTable(createUserTableString)
  9.     def initDatabase(self):
  10.         password='admin123'
  11.         hl = hashlib.md5()                                             #将密码进行md5加密
  12.         hl.update(password.encode(encoding='utf-8'))
  13.         md5password = hl.hexdigest()
  14.         self.addAdminUser('admin','Fengqi',md5password)                #添加管理员账号
  16.         password='user123'
  17.         hl = hashlib.md5()                                             #将密码进行md5加密
  18.         hl.update(password.encode(encoding='utf-8'))
  19.         md5password = hl.hexdigest()
  20.         self.addUser('user000000','user000000',md5password)            #添加普通用户
  23.     def addUser(self,userid, Name, Password,IsAdmin=0):
  24.         '''  添加普通用户    '''
  25.         insertData = self.cursor.execute("""INSERT INTO user 
  26.                 (userid, Name, Password,IsAdmin,TimesBorrowed,NumBorrowed) VALUES 
  27.                 ('{0}', '{1}', '{2}','{3}','{4}','{5}')
  28.                 """.format(userid, Name, Password,IsAdmin,0,0))
  29.         self.db.commit()
  31.     def addAdminUser(self,userid, Name, Password):
  32.         ''' 添加管理员用户'''
  33.         self.addUser(userid, Name, Password,IsAdmin=1)
  34.     def querybyUserid(self,userid):
  35.         fetchedData = self.cursor.execute("SELECT * FROM user WHERE userid='%s'" % (userid) )
  36.         #a=fetchedData.fetchall()#通过fetchall接受全部数据，是一个list,list的每个元素是tuple类型数据
  38.         return fetchedData.fetchall()
  40.     def getAdmineUserinfo(self):
  41.         '''获取管理员用户 '''
  42.         fetchedData = self.cursor.execute("SELECT userid,Name FROM user WHERE IsAdmin=1")
  43.         return fetchedData
  46.     def getUserinfo(self):
  47.         '''获取一般用户'''
  48.         fetchedData = self.cursor.execute("SELECT userid,Name FROM user WHERE IsAdmin=0")
  49.         return fetchedData
  51.     def updatePassword(self,password,userid):
  52.         fetchedData = self.cursor.execute("UPDATE User SET Password='%s' WHERE userid=%s" % (password,userid))
  53.         self.db.commit()
  55.     def borrowOrReturnBook(self,userid,borrow=1):
  56.         if borrow == 1 :
  57.             fetchedData = self.cursor.execute("UPDATE User SET TimesBorrowed=TimesBorrowed+1,NumBorrowed=NumBorrowed+1 WHERE userid='%s'" % userid)
  58.         else:
  59.             fetchedData = self.cursor.execute("UPDATE User SET TimesBorrowed=TimesBorrowed-1,NumBorrowed=NumBorrowed-1 WHERE userid='%s'" % userid)
  60.         self.db.commit()

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

  1. class BookDbManager(DbManager):
  2.     def __init__(self, database=dbpath, *args):
  3.         super().__init__(database, *args)
  4.         self.initDb()
  6.     def initDb(self):
  7.         self.createTable(createBookTableString)
  9.     def initDatabase(self):
  10.         self.addBOOK('力学', 'IS1000', '刘斌', '教育', '中国科学技术大学 ', '1999-01-01', 100, 100, 0)
  11.         self.addBOOK('微积分', 'IS1001', '牛顿莱布尼兹', '教育', '中国科学技术大学', '1998-01-01', 14, 14, 0)
  12.         self.addBOOK('电磁场论', 'IS1002', '叶邦角', '教育', '中国科学技术大学', '1997-01-01', 24, 24, 0)
  13.         self.addBOOK('热学', 'IS1003', '张鹏飞', '教育', '中国科学技术大学', '2002-01-01', 45, 45, 0)
  14.         self.addBOOK('电动力学', 'IS1004', '叶邦角', '教育', '中国科学技术大学', '2003-01-01', 100, 100, 0)
  15.         self.addBOOK('数据库', 'IS1006', '袁平波', '教育', '中国科学技术大学', '2010-01-01', 10, 10, 0)
  16.         self.addBOOK ('电磁学', 'IS1005', '叶邦角', '教育', '中国科学技术大学 ', '2012-01-01', 43, 43, 0)
  17.         self.addBOOK ('数学分析', 'IS1007', '陈卿', '教育', '中国科学技术大学', '2013-01-01', 23, 23, 0)
  18.         self.addBOOK('吉米多维奇题解1', 'IS1008', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
  19.         self.addBOOK('吉米多维奇题解2', 'IS1009', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
  20.         self.addBOOK('吉米多维奇题解3', 'IS1010', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
  21.         self.addBOOK('吉米多维奇题解4', 'IS1011', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
  22.         self.addBOOK('吉米多维奇题解5', 'IS1012', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
  23.         self.addBOOK('吉米多维奇题解6', 'IS1013', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
  24.         self.addBOOK('朗道力学', 'IS1014', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  25.         self.addBOOK('朗道电动力学', 'IS1015', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  26.         self.addBOOK('朗道量子力学', 'IS1016', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  27.         self.addBOOK('朗道量子电动力学', 'IS1017', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  28.         self.addBOOK('朗道统计物理学', 'IS1018', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  29.         self.addBOOK('朗道流体力学', 'IS1019', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  30.         self.addBOOK('朗道弹性理论力学', 'IS1020', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  31.         self.addBOOK('朗道物理动力学', 'IS1021', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  32.         self.addBOOK('植物学', 'IS1022', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
  33.         self.addBOOK('动物学', 'IS1023', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
  34.         self.addBOOK('细胞生物学', 'IS1024', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
  35.         self.addBOOK('动物生理学', 'IS1025', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
  36.         self.addBOOK('古生物学', 'IS1026', '佚名', '生物学', '高等教育出版社', '2011-05-01', 100, 100, 0)
  37.         self.addBOOK('高等数学', 'IS1027', '佚名', '教育', '高等教育出版社', '2011-05-01', 50, 50, 0)
  38.         self.addBOOK('线性代数', 'IS1029', '佚名', '教育', '高等教育出版社', '2011-05-01', 50, 50, 0)
  39.         self.addBOOK('C++程序设计', 'IS1030', '孙广中', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
  40.         self.addBOOK('C程序设计', 'IS1031', '郑重', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
  41.         self.addBOOK('数据结构', 'IS1032', '顾为兵', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
  42.         self.addBOOK('信号与系统', 'IS1033', '李卫平', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
  43.         self.addBOOK('线性电子线路', 'IS1034', '陆伟', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
  45.     def addBOOK(self,BookName,BookID, Auth, Category,Publisher,PublishTime,NumStorage,NumCanBorrow,NumBorrowed):
  46.         '''  添加书籍    '''
  47.         insertData = self.cursor.execute("""INSERT INTO Book 
  48.                 (BookName,BookID, Auth, Category,Publisher,PublishTime,NumStorage,NumCanBorrow,NumBorrowed) VALUES 
  49.                 ('{0}', '{1}', '{2}','{3}','{4}','{5}','{6}','{7}','{8}')
  50.                 """.format(BookName,BookID, Auth, Category,Publisher,PublishTime,NumStorage,NumCanBorrow,NumBorrowed))
  51.         self.db.commit()
  53.     def dropBook(self,bookId):
  54.         insertData = self.cursor.execute("DELETE  FROM Book WHERE BookID='%s'" % (bookId))
  55.         self.db.commit()
  57.     def updateBookinfo(self,addBookNum,bookId,addFlag=1):
  58.         if addFlag == 1:
  59.             self.cursor.execute("UPDATE Book SET NumStorage=NumStorage+%d,NumCanBorrow=NumCanBorrow+%d WHERE BookID='%s'" % (
  60.                     addBookNum, addBookNum, bookId))
  61.         else:
  62.             self.cursor.execute("UPDATE Book SET NumStorage=NumStorage-%d,NumCanBorrow=NumCanBorrow-%d WHERE BookID='%s'" % (
  63.                     addBookNum, addBookNum, bookId))
  64.         self.db.commit()
  66.     def getBookinfo(self):
  67.         '''获得所有书籍'''
  68.         fetchedData = self.cursor.execute("SELECT * from Book ")
  69.         return fetchedData.fetchall()
  71.     def querybyBookID(self,BookID):
  72.         fetchedData = self.cursor.execute("SELECT * FROM Book WHERE BookID='%s'" % (BookID) )
  73.         return fetchedData.fetchall()
  74.         #return self.queryBookByKeywords(userid)
  76.     def queryBookByKeywords(self,keywords):
  77.         fetchedData = self.cursor.execute("SELECT * from Book ORDER BY %s limit %s,%s" % (keywords,0,5))
  78.         return fetchedData.fetchall()
  80.     def borrowOrReturnBook(self,BookID,borrowflag=1):
  81.         if borrowflag == 1 :
  82.             fetchedData = self.cursor.execute( "UPDATE Book SET NumCanBorrow=NumCanBorrow-1,NumBorrowed=NumBorrowed+1 WHERE BookID='%s'" % BookID)
  83.         else:
  84.             fetchedData = self.cursor.execute( "UPDATE Book SET NumCanBorrow=NumCanBorrow+1,NumBorrowed=NumBorrowed-1 WHERE BookID='%s'" % BookID)
  85.         self.db.commit()

最后是添加删除类

  1. class AddOrDropManager(DbManager):
  2.     def __init__(self, database=dbpath, *args):
  3.         super().__init__(database, *args)
  4.         self.initDb()
  6.     def initDb(self):
  7.         self.createTable(createAddOrDropBookTableString)
  9.     def initDatabase(self):
  10.         self.insertValue('IS1000', '2018-04-22', 1, 100)
  11.         self.insertValue('IS1001', '2018-04-22', 1, 14)
  12.         self.insertValue('IS1002', '2018-04-22', 1, 24)
  13.         self.insertValue('IS1003', '2018-04-22', 1, 45)
  14.         self.insertValue('IS1004', '2018-04-22', 1, 100)
  15.         self.insertValue('IS1004', '2018-04-27', 1, 45)
  16.         self.insertValue('IS1005', '2018-04-27', 1, 45)
  17.         self.insertValue('IS1006', '2018-04-28', 1, 10)
  18.         self.insertValue('IS1007', '2018-04-28', 1, 23)
  19.         self.insertValue('IS1008', '2018-04-28', 1, 50)
  20.         self.insertValue('IS1009', '2018-04-28', 1, 50)
  21.         self.insertValue('IS1010', '2018-04-28', 1, 50)
  22.         self.insertValue('IS1011', '2018-04-28', 1, 50)
  23.         self.insertValue('IS1012', '2018-04-28', 1, 50)
  24.         self.insertValue('IS1013', '2018-04-28', 1, 50)
  25.         self.insertValue('IS1014', '2018-04-28', 1, 50)
  26.         self.insertValue('IS1015', '2018-04-28', 1, 50)
  27.         self.insertValue('IS1016', '2018-04-28', 1, 50)
  28.         self.insertValue('IS1017', '2018-04-28', 1, 50)
  29.         self.insertValue('IS1018', '2018-04-28', 1, 50)
  30.         self.insertValue('IS1019', '2018-04-28', 1, 50)
  31.         self.insertValue('IS1020', '2018-04-28', 1, 50)
  32.         self.insertValue('IS1021', '2018-04-28', 1, 50)
  33.         self.insertValue('IS1022', '2018-04-28', 1, 50)
  34.         self.insertValue('IS1023', '2018-04-28', 1, 50)
  35.         self.insertValue('IS1024', '2018-04-28', 1, 50)
  36.         self.insertValue('IS1025', '2018-04-28', 1, 50)
  37.         self.insertValue('IS1026', '2018-04-28', 1, 100)
  38.         self.insertValue('IS1027', '2018-04-28', 1, 50)
  39.         self.insertValue('IS1029', '2018-04-28', 1, 50)
  40.         self.insertValue('IS1030', '2018-04-28', 1, 50)
  41.         self.insertValue('IS1031', '2018-04-28', 1, 50)
  42.         self.insertValue('IS1032', '2018-04-28', 1, 50)
  43.         self.insertValue('IS1033', '2018-04-28', 1, 50)
  44.         self.insertValue('IS1034', '2018-04-28', 1, 50)
  47.     def insertValue(self,BookID,time,AddorDrop,addBookNum):
  48.         insertData = self.cursor.execute("INSERT INTO AddOrDrop VALUES ('%s','%s',%d,%d)" % (BookID, time, AddorDrop,addBookNum))
  49.         self.db.commit()
  51.     def addinfo(self,BookID,time,addBookNum):
  52.         self.insertValue(BookID,time,1,addBookNum)
  54.     def dropinfo(self,BookID,time,addBookNum):
  55.         self.insertValue(BookID,time,0,addBookNum)
  57.     def getAllinfo(self):
  58.         '''获得所有书籍'''
  59.         fetchedData = self.cursor.execute("SELECT * from AddOrDrop ")
  60.         return fetchedData.fetchall()

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

  1. # -*- coding: utf-8 -*-
  2. # @Date     : 2018-12-12 15:31:22
  3. # @Author   : Jimy_Fengqi (jmps515@163.com)
  4. # @Link     : https://blog.csdn.net/qiqiyingse
  5. # @Version  : V1.0
  6. # @pyVersion: 3.6
  8. import os
  9. import os.path
  10. import sqlite3
  11. import hashlib
  13. home = os.path.expanduser('~')
  15. if '.BookManagerSystem' not in os.listdir(home):
  16.     os.mkdir(os.path.join(home, '.BookManagerSystem'))
  18. dbpath = os.path.join(home, '.BookManagerSystem', 'LibraryManagement.db')
  20. createUserTableString = """
  21. CREATE TABLE IF NOT EXISTS user(
  22.     userid CHAR(10) PRIMARY KEY ,
  23.     Name VARCHAR(20),
  24.     Password CHAR(32),
  25.     IsAdmin BIT,
  26.     TimesBorrowed INT,
  27.     NumBorrowed INT    
  28. )"""
  30. createUser_BookTableString = """
  31. CREATE TABLE IF NOT EXISTS User_Book(
  32.     userid CHAR(10),
  33.     BookID CHAR(6) PRIMARY KEY,
  34.     BorrowTime DATE,
  35.     ReturnTime DATE,
  36.     BorrowState BIT
  37. )"""
  39. createBookTableString = """
  40. CREATE TABLE IF NOT EXISTS Book(
  41.     BookName VARCHAR(30),
  42.     BookID CHAR(6),
  43.     Auth VARCHAR(20),
  44.     Category VARCHAR(10),
  45.     Publisher VARCHAR(20),
  46.     PublishTime DATE,
  47.     NumStorage INT,
  48.     NumCanBorrow INT,
  49.     NumBorrowed INT 
  50. )"""
  53. createAddOrDropBookTableString = """
  54. CREATE TABLE IF NOT EXISTS AddOrDrop(
  55.     BookID CHAR(6),
  56.     ModifyTime  DATE,
  57.     AddOrDrop INT,
  58.     Numbers INT    
  59. )"""
  62. class DbManager(object):
  64.     def __init__(self, *args):
  65.         self.db = sqlite3.connect(*args)
  66.         self.cursor = self.db.cursor()
  68.     def __enter__(self):
  70.         return self.cursor
  72.     def __exit__(self, types, value, traceback):
  73.         self.db.commit()
  75.         return False
  77.     def __del__(self):
  78.         self.db.commit()
  79.         self.db.close()
  81.     def switchDb(self, *args):
  82.         self.db.close()
  84.         self.db = sqlite3.connect(*args)
  85.         self.cursor = self.db.cursor()
  87.     def createTable(self, tableString):
  88.         self.cursor.execute(tableString)
  89.         self.db.commit()
  91.     def commitAndClose(self):
  92.         self.db.commit()
  93.         self.db.close()
  95. class UserBookManager(DbManager):
  96.     def __init__(self, database=dbpath, *args):
  97.         super().__init__(database, *args)
  98.         self.initDb()
  100.     def initDb(self):
  101.         self.createTable(createUser_BookTableString)
  103.     def queryBorrowBook(self,userid,BookID):
  104.        result=self.cursor.execute( "SELECT * FROM User_Book WHERE userid='%s' AND BookID='%s' AND BorrowState=1" %(userid,BookID))
  105.        return result.fetchall()
  107.     def countBorrowNum(self,userid):
  108.        result=self.cursor.execute(" SELECT COUNT(userid) FROM User_Book WHERE userid='%s' AND BorrowState=1" % (userid))
  109.        return result.fetchall()
  111.     def borrowStatus(self,userid,BookID):
  112.        result=self.cursor.execute( "SELECT COUNT(userid) FROM User_Book WHERE  userid='%s' AND BookID='%s' AND BorrowState=1" % (
  113.         userid,BookID))
  114.        return result.fetchall()
  116.     def borrowOrReturnBook(self,userid, BookID, timenow,borrowflag=1):
  117.         if borrowflag == 1:
  118.             result=self.cursor.execute( "INSERT INTO User_Book VALUES ('%s','%s','%s',NULL,1)" % (userid, BookID, timenow))
  119.         else:
  120.            result=self.cursor.execute("UPDATE User_Book SET ReturnTime='%s',BorrowState=0 WHERE userID='%s' AND BookID='%s' AND BorrowState=1" % (timenow,userid,BookID))
  121.         self.db.commit()
  122. class AddOrDropManager(DbManager):
  123.     def __init__(self, database=dbpath, *args):
  124.         super().__init__(database, *args)
  125.         self.initDb()
  127.     def initDb(self):
  128.         self.createTable(createAddOrDropBookTableString)
  130.     def initDatabase(self):
  131.         self.insertValue('IS1000', '2018-04-22', 1, 100)
  132.         self.insertValue('IS1001', '2018-04-22', 1, 14)
  133.         self.insertValue('IS1002', '2018-04-22', 1, 24)
  134.         self.insertValue('IS1003', '2018-04-22', 1, 45)
  135.         self.insertValue('IS1004', '2018-04-22', 1, 100)
  136.         self.insertValue('IS1004', '2018-04-27', 1, 45)
  137.         self.insertValue('IS1005', '2018-04-27', 1, 45)
  138.         self.insertValue('IS1006', '2018-04-28', 1, 10)
  139.         self.insertValue('IS1007', '2018-04-28', 1, 23)
  140.         self.insertValue('IS1008', '2018-04-28', 1, 50)
  141.         self.insertValue('IS1009', '2018-04-28', 1, 50)
  142.         self.insertValue('IS1010', '2018-04-28', 1, 50)
  143.         self.insertValue('IS1011', '2018-04-28', 1, 50)
  144.         self.insertValue('IS1012', '2018-04-28', 1, 50)
  145.         self.insertValue('IS1013', '2018-04-28', 1, 50)
  146.         self.insertValue('IS1014', '2018-04-28', 1, 50)
  147.         self.insertValue('IS1015', '2018-04-28', 1, 50)
  148.         self.insertValue('IS1016', '2018-04-28', 1, 50)
  149.         self.insertValue('IS1017', '2018-04-28', 1, 50)
  150.         self.insertValue('IS1018', '2018-04-28', 1, 50)
  151.         self.insertValue('IS1019', '2018-04-28', 1, 50)
  152.         self.insertValue('IS1020', '2018-04-28', 1, 50)
  153.         self.insertValue('IS1021', '2018-04-28', 1, 50)
  154.         self.insertValue('IS1022', '2018-04-28', 1, 50)
  155.         self.insertValue('IS1023', '2018-04-28', 1, 50)
  156.         self.insertValue('IS1024', '2018-04-28', 1, 50)
  157.         self.insertValue('IS1025', '2018-04-28', 1, 50)
  158.         self.insertValue('IS1026', '2018-04-28', 1, 100)
  159.         self.insertValue('IS1027', '2018-04-28', 1, 50)
  160.         self.insertValue('IS1029', '2018-04-28', 1, 50)
  161.         self.insertValue('IS1030', '2018-04-28', 1, 50)
  162.         self.insertValue('IS1031', '2018-04-28', 1, 50)
  163.         self.insertValue('IS1032', '2018-04-28', 1, 50)
  164.         self.insertValue('IS1033', '2018-04-28', 1, 50)
  165.         self.insertValue('IS1034', '2018-04-28', 1, 50)
  168.     def insertValue(self,BookID,time,AddorDrop,addBookNum):
  169.         insertData = self.cursor.execute("INSERT INTO AddOrDrop VALUES ('%s','%s',%d,%d)" % (BookID, time, AddorDrop,addBookNum))
  170.         self.db.commit()
  172.     def addinfo(self,BookID,time,addBookNum):
  173.         self.insertValue(BookID,time,1,addBookNum)
  175.     def dropinfo(self,BookID,time,addBookNum):
  176.         self.insertValue(BookID,time,0,addBookNum)
  178.     def getAllinfo(self):
  179.         '''获得所有书籍'''
  180.         fetchedData = self.cursor.execute("SELECT * from AddOrDrop ")
  181.         return fetchedData.fetchall()
  183. class BookDbManager(DbManager):
  184.     def __init__(self, database=dbpath, *args):
  185.         super().__init__(database, *args)
  186.         self.initDb()
  188.     def initDb(self):
  189.         self.createTable(createBookTableString)
  191.     def initDatabase(self):
  192.         self.addBOOK('力学', 'IS1000', '刘斌', '教育', '中国科学技术大学 ', '1999-01-01', 100, 100, 0)
  193.         self.addBOOK('微积分', 'IS1001', '牛顿莱布尼兹', '教育', '中国科学技术大学', '1998-01-01', 14, 14, 0)
  194.         self.addBOOK('电磁场论', 'IS1002', '叶邦角', '教育', '中国科学技术大学', '1997-01-01', 24, 24, 0)
  195.         self.addBOOK('热学', 'IS1003', '张鹏飞', '教育', '中国科学技术大学', '2002-01-01', 45, 45, 0)
  196.         self.addBOOK('电动力学', 'IS1004', '叶邦角', '教育', '中国科学技术大学', '2003-01-01', 100, 100, 0)
  197.         self.addBOOK('数据库', 'IS1006', '袁平波', '教育', '中国科学技术大学', '2010-01-01', 10, 10, 0)
  198.         self.addBOOK ('电磁学', 'IS1005', '叶邦角', '教育', '中国科学技术大学 ', '2012-01-01', 43, 43, 0)
  199.         self.addBOOK ('数学分析', 'IS1007', '陈卿', '教育', '中国科学技术大学', '2013-01-01', 23, 23, 0)
  200.         self.addBOOK('吉米多维奇题解1', 'IS1008', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
  201.         self.addBOOK('吉米多维奇题解2', 'IS1009', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
  202.         self.addBOOK('吉米多维奇题解3', 'IS1010', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
  203.         self.addBOOK('吉米多维奇题解4', 'IS1011', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
  204.         self.addBOOK('吉米多维奇题解5', 'IS1012', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
  205.         self.addBOOK('吉米多维奇题解6', 'IS1013', '吉米多维奇', '教育', '俄罗斯出版社', '2010-01-01', 50, 50, 0)
  206.         self.addBOOK('朗道力学', 'IS1014', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  207.         self.addBOOK('朗道电动力学', 'IS1015', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  208.         self.addBOOK('朗道量子力学', 'IS1016', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  209.         self.addBOOK('朗道量子电动力学', 'IS1017', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  210.         self.addBOOK('朗道统计物理学', 'IS1018', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  211.         self.addBOOK('朗道流体力学', 'IS1019', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  212.         self.addBOOK('朗道弹性理论力学', 'IS1020', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  213.         self.addBOOK('朗道物理动力学', 'IS1021', '朗道', '教育', '高等教育出版社', '2012-01-01', 50, 50, 0)
  214.         self.addBOOK('植物学', 'IS1022', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
  215.         self.addBOOK('动物学', 'IS1023', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
  216.         self.addBOOK('细胞生物学', 'IS1024', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
  217.         self.addBOOK('动物生理学', 'IS1025', '佚名', '生物学', '高等教育出版社', '2011-05-01', 50, 50, 0)
  218.         self.addBOOK('古生物学', 'IS1026', '佚名', '生物学', '高等教育出版社', '2011-05-01', 100, 100, 0)
  219.         self.addBOOK('高等数学', 'IS1027', '佚名', '教育', '高等教育出版社', '2011-05-01', 50, 50, 0)
  220.         self.addBOOK('线性代数', 'IS1029', '佚名', '教育', '高等教育出版社', '2011-05-01', 50, 50, 0)
  221.         self.addBOOK('C++程序设计', 'IS1030', '孙广中', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
  222.         self.addBOOK('C程序设计', 'IS1031', '郑重', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
  223.         self.addBOOK('数据结构', 'IS1032', '顾为兵', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
  224.         self.addBOOK('信号与系统', 'IS1033', '李卫平', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
  225.         self.addBOOK('线性电子线路', 'IS1034', '陆伟', '教育', '中国科学技术大学', '2011-05-01', 50, 50, 0)
  227.     def addBOOK(self,BookName,BookID, Auth, Category,Publisher,PublishTime,NumStorage,NumCanBorrow,NumBorrowed):
  228.         '''  添加书籍    '''
  229.         insertData = self.cursor.execute("""INSERT INTO Book 
  230.                 (BookName,BookID, Auth, Category,Publisher,PublishTime,NumStorage,NumCanBorrow,NumBorrowed) VALUES 
  231.                 ('{0}', '{1}', '{2}','{3}','{4}','{5}','{6}','{7}','{8}')
  232.                 """.format(BookName,BookID, Auth, Category,Publisher,PublishTime,NumStorage,NumCanBorrow,NumBorrowed))
  233.         self.db.commit()
  235.     def dropBook(self,bookId):
  236.         insertData = self.cursor.execute("DELETE  FROM Book WHERE BookID='%s'" % (bookId))
  237.         self.db.commit()
  239.     def updateBookinfo(self,addBookNum,bookId,addFlag=1):
  240.         if addFlag == 1:
  241.             self.cursor.execute("UPDATE Book SET NumStorage=NumStorage+%d,NumCanBorrow=NumCanBorrow+%d WHERE BookID='%s'" % (
  242.                     addBookNum, addBookNum, bookId))
  243.         else:
  244.             self.cursor.execute("UPDATE Book SET NumStorage=NumStorage-%d,NumCanBorrow=NumCanBorrow-%d WHERE BookID='%s'" % (
  245.                     addBookNum, addBookNum, bookId))
  246.         self.db.commit()
  250.     def getBookinfo(self):
  251.         '''获得所有书籍'''
  252.         fetchedData = self.cursor.execute("SELECT * from Book ")
  253.         return fetchedData.fetchall()
  255.     def querybyBookID(self,BookID):
  256.         fetchedData = self.cursor.execute("SELECT * FROM Book WHERE BookID='%s'" % (BookID) )
  257.         return fetchedData.fetchall()
  258.         #return self.queryBookByKeywords(userid)
  261.     def queryBookByKeywords(self,keywords):
  262.         fetchedData = self.cursor.execute("SELECT * from Book ORDER BY %s limit %s,%s" % (keywords,0,5))
  263.         return fetchedData.fetchall()
  265.     def borrowOrReturnBook(self,BookID,borrowflag=1):
  266.         if borrowflag == 1 :
  267.             fetchedData = self.cursor.execute( "UPDATE Book SET NumCanBorrow=NumCanBorrow-1,NumBorrowed=NumBorrowed+1 WHERE BookID='%s'" % BookID)
  268.         else:
  269.             fetchedData = self.cursor.execute( "UPDATE Book SET NumCanBorrow=NumCanBorrow+1,NumBorrowed=NumBorrowed-1 WHERE BookID='%s'" % BookID)
  270.         self.db.commit()
  273. class UserDbManager(DbManager):
  274.     def __init__(self, database=dbpath, *args):
  275.         super().__init__(database, *args)
  276.         self.initDb()
  278.     def initDb(self):
  279.         self.createTable(createUserTableString)
  282.     def initDatabase(self):
  284.         password='admin123'
  285.         hl = hashlib.md5()                                                  #将密码进行md5加密
  286.         hl.update(password.encode(encoding='utf-8'))
  287.         md5password = hl.hexdigest()
  288.         self.addAdminUser('admin','Fengqi',md5password)                     #添加管理员账号
  290.         password='user123'
  291.         hl = hashlib.md5()                                                  #将密码进行md5加密
  292.         hl.update(password.encode(encoding='utf-8'))
  293.         md5password = hl.hexdigest()
  294.         self.addUser('user000000','user000000',md5password)               #添加普通用户
  297.     def addUser(self,userid, Name, Password,IsAdmin=0):
  298.         '''  添加普通用户    '''
  299.         insertData = self.cursor.execute("""INSERT INTO user 
  300.                 (userid, Name, Password,IsAdmin,TimesBorrowed,NumBorrowed) VALUES 
  301.                 ('{0}', '{1}', '{2}','{3}','{4}','{5}')
  302.                 """.format(userid, Name, Password,IsAdmin,0,0))
  303.         self.db.commit()
  305.     def addAdminUser(self,userid, Name, Password):
  306.         ''' 添加管理员用户'''
  307.         self.addUser(userid, Name, Password,IsAdmin=1)
  308.     def querybyUserid(self,userid):
  309.         fetchedData = self.cursor.execute("SELECT * FROM user WHERE userid='%s'" % (userid) )
  310.         #a=fetchedData.fetchall()#通过fetchall接受全部数据，是一个list,list的每个元素是tuple类型数据
  312.         return fetchedData.fetchall()
  314.     def getAdmineUserinfo(self):
  315.         '''获取管理员用户 '''
  316.         fetchedData = self.cursor.execute("SELECT userid,Name FROM user WHERE IsAdmin=1")
  317.         return fetchedData
  320.     def getUserinfo(self):
  321.         '''获取一般用户'''
  322.         fetchedData = self.cursor.execute("SELECT userid,Name FROM user WHERE IsAdmin=0")
  323.         return fetchedData
  325.     def updatePassword(self,password,userid):
  326.         fetchedData = self.cursor.execute("UPDATE User SET Password='%s' WHERE userid=%s" % (password,userid))
  327.         self.db.commit()
  329.     def borrowOrReturnBook(self,userid,borrow=1):
  330.         if borrow == 1 :
  331.             fetchedData = self.cursor.execute("UPDATE User SET TimesBorrowed=TimesBorrowed+1,NumBorrowed=NumBorrowed+1 WHERE userid='%s'" % userid)
  332.         else:
  333.             fetchedData = self.cursor.execute("UPDATE User SET TimesBorrowed=TimesBorrowed-1,NumBorrowed=NumBorrowed-1 WHERE userid='%s'" % userid)
  334.         self.db.commit()
  337. def testuserdb():
  338.     userDb = UserDbManager()
  339.     userDb.addAdminUser('admin', 'admin', '123456')
  340.     userDb.addAdminUser('administrator', 'admin1', '123456')
  341.     userDb.addUser('Test', 'AAA', '123456')
  342.     userDb.addUser('Test1', 'BBB', '123456')
  343.     userDb.addUser('Test2', 'CCC', '123456')
  344.     userDb.getAdmineUser()
  345.     userDb.getUser()
  346.     userDb.queryUser('admins')
  347.     userDb.queryUser('admin')
  349. def testAddDropBookData():
  350.     userDb = AddOrDropManager()
  352.     allbook=userDb.getAllinfo()
  353.     for book in allbook:
  354.         print(book)
  355.         #print(" ".join('%s' %ids for ids in a))
  356.         #a=list(book)
  357.         #print(a)
  359. def testBookDB():
  360.     userDb = BookDbManager()
  361.     if len(userDb.querybyBookID('IS1006')):
  362.         print('书籍已经存在，更新数量')
  363.         userDb.updateBookinfo(10,'IS1005')
  364.     else:
  365.         print('书籍不存在，直接插入')
  366.         userDb.addBOOK('力学3',   'IS1006'  ,'刘斌3',  '教育',  '中国科学技术大学', '1999-01-01',  '34' , '34' , '1')
  368.     allbook=userDb.getBookinfo()
  370.     print('all book length =%d' % len(allbook))
  371.     for book in allbook:
  372.         print(book)
  374.     print('按照bookid查询')
  375.     bookid=userDb.querybyBookID('IS1006')
  376.     if len(bookid):
  377.         print(bookid)
  379.     print('按照auth排序查询前几页')
  380.     keybook=userDb.queryBookByKeywords('Auth')
  381.     print(keybook)
  383. if __name__ == '__main__':
  384.     testuserdb()
  385.     testAddDropBookData()
  386.     testBookDB()

发表评论

表情：
评论列表 （有 0 条评论，318人围观）

还没有评论，来说两句吧...

相关阅读