PyQt5图书管理系统练习（1）初始化数据库

班公湖里洗过脚

于 2020-09-07 23:45:19 发布

阅读量701

点赞数 1

分类专栏： pyqt5 文章标签： pyqt5学习

本文链接：https://blog.csdn.net/chenyijun/article/details/108458769

版权

pyqt5 专栏收录该内容

24 篇文章 18 订阅

订阅专栏

初始化数据库

环境

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

数据库操作

图书管理系统所有的操作都依赖于数据库，本系统使用sqlite3数据库，python3自带这个数据库功能。

数据库的初始化首先检测本地有没有这个数据库，没有的话直接初始化数据

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

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

3，BookDbManager图书类，有书库初始化，添加删除图书，借书还书，书籍查询等功能

4，添加删除类AddOrDropManager类

实现这些数据库功能，后面的项目依赖就可以放手做了，下面是完整的代码

initDB.py

""""
brief:pyqt5图片管理系统。
        数据库的初始化
date:2020-07-18
author:chenyijun
version: python V3.8.1 pyqt5 V5.14.0
"""

import os
import os.path
import sqlite3
import hashlib

# home = os.path.expanduser('~') #获取当前用户目录
# print(home) # C:\Users\username
# if '.BookManagerSystem' not in os.listdir(home):
#     os.mkdir(os.path.join(home, '.BookManagerSystem'))
#
# dbpath = os.path.join(home, '.BookManagerSystem', 'LibraryManagement.db')
# print(dbpath) # C:\Users\username\.BookManagerSystem\LibraryManagement.db

apath = os.path.abspath('.') #获取当前工作目录
print(apath)
if 'db' not in os.listdir(apath):
    os.mkdir(os.path.join(apath, 'db'))

dbpath = os.path.join(apath, 'db', 'LibraryManagement.db')
print(dbpath)

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(20),
    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 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()  #
        hl.update(password.encode(encoding = 'utf-8'))
        md5password = hl.hexdigest()
        self.addAdminUser('admin', 'scott', md5password)

        password = 'user123'
        hl = hashlib.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))
        byUserid = fetchedData.fetchall()#通过fetchall接受全部数据，是一个list,list的每个元素是tuple类型数据
        print(byUserid)
        return fetchedData.fetchall()

    def getAdmineUserInfo(self):
        """获取管理员用户"""
        fetchedData = self.cursor.execute("SELECT userid, Name FROM user WHERE IsAdmin = 1")
        adminUser = fetchedData.fetchall()  # 通过fetchall接受全部数据，是一个list,list的每个元素是tuple类型数据
        print(adminUser)
        return fetchedData

    def getUserinfo(self):
        """获取一般用户"""
        fetchedData = self.cursor.execute("SELECT userid, Name FROM user WHERE IsAdmin = 0")
        normalUser = fetchedData.fetchall()  # 通过fetchall接受全部数据，是一个list,list的每个元素是tuple类型数据
        print(normalUser)
        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()

    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()

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.getAdmineUserInfo()
    userDb.getUserinfo()
    userDb.querybyUserid('admins')
    userDb.querybyUserid('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()

参考

https://blog.csdn.net/qiqiyingse/category_9285730.html
https://blog.csdn.net/weixin_38312031/category_9274444.html

班公湖里洗过脚

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
PyQt5图书管理系统练习（1）初始化数据库

初始化数据库环境系统：windows10系统编辑器：PyCharm编程语言：python3+pyqt5数据库操作图书管理系统所有的操作都依赖于数据库，本系统使用sqlite3数据库，python3自带这个数据库功能。数据库的初始化首先检测本地有没有这个数据库，没有的话直接初始化数据1，构造一个数据库类DbManager，为后面真正使用的3个类做准备，这个类有一些基本功能，创建，删除，切换库2，用户类UserDbManager，实现这么几个函数：初始化数据，添加普通用户，添.
复制链接

扫一扫