初始化數據庫

環境

系統：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.cursordef __exit__(self, types, value, traceback):self.db.commit()return Falsedef __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 fetchedDatadef getUserinfo(self):'''獲取一般用戶'''fetchedData = self.cursor.execute("SELECT userid,Name FROM user WHERE IsAdmin=0")return fetchedDatadef 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.6import os import os.path import sqlite3 import hashlibhome = 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.cursordef __exit__(self, types, value, traceback):self.db.commit()return Falsedef __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 fetchedDatadef getUserinfo(self):'''獲取一般用戶'''fetchedData = self.cursor.execute("SELECT userid,Name FROM user WHERE IsAdmin=0")return fetchedDatadef 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()

?

總結

以上是生活随笔為你收集整理的PyQT5一起学做图书管理系统（2）初始化数据库的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。