基于华为云opengauss数据库的图书馆管理系统

这是我使用华为opengauss数据库和Python编程所制作的一个图书馆的管理项目。项目发表后深受好评。下面让我来介绍一下制作这个项目的准备工作。

首先我们需要在电脑的cmd窗口中运行pip install psycopg2，这个库是一个用于连接和操作 opengauss 数据库的 Python 库。它提供了在 Python 中执行 SQL 查询和操作数据库的功能

然后，为了用python来编写我们的前端代码，我们还需要下载以下几个python库：

import tkinter as tk

from tkinter import ttk

import tkinter.messagebox

其中，tkinter 是 Python 的标准 GUI（图形用户界面）库，用于创建和管理图形界面应用程序。它提供了一组工具和小部件，使开发者能够创建用户界面元素，如窗口、按钮、文本框等。

ttk 是 tkinter 的一个子模块，提供了一组更现代化、外观更好的小部件，例如按钮、标签、下拉框等。ttk 小部件与 tkinter 兼容，并且可以用于创建更具吸引力和一致性的图形界面。

tkinter.messagebox 模块提供了一些简单的对话框函数，用于在图形界面应用程序中显示各种消息框，例如警告框、信息框、错误框等。它可以用于向用户显示消息并接收用户的响应。

接下来展示一下前端类的设计图：

有了这个设计图后，我们就可以开始着手编写项目了

首先便是python与openguass数据库的连接：

具体步骤可以参照这篇博客：

https://www.cnblogs.com/chrisng/p/16211954.html

然后，首先着手于登录注册系统的设计：

对于用户登录和用户注册，我们在数据库里建立一个user表，检测到按钮提交状态后，在表中存放用户的用户名与密码，在用户登录时检验其账号密码是否在数据库的表中已经存在，如果已存在则用户登录成功，如果不存在则提示使用者用户不存在，需要注册。

创建注册用户代码如下：

import psycopg2
conn = psycopg2.connect(dbname="postgres",
                                user="jacky",
                                password="Bigdata@123",
                                host="123.249.29.62",
                                port="26000")
conn.set_client_encoding('utf8')
cur = conn.cursor()

# 创建表
cur.execute(
'''
CREATE TABLE book_user(
    userid          TEXT PRIMARY KEY    NOT NULL,
    username        TEXT    NOT NULL,
    password        TEXT    NOT NULL,
    phone           TEXT  ,
    status          TEXT  ,
    lend_num        TEXT  ,  
    max_num         TEXT  );
''')
# 数据插入
cur.execute("INSERT INTO book_user (userid,username,password,phone,status,lend_num,max_num) \
      VALUES ('2000300410', '王大锤','123', '1234567', '在借', '1', '10')")

conn.commit()

# # 数据查询
# cur.execute("SELECT 学号,姓名,性别  from student")
# rows = cur.fetchall()
# for row in rows:
#    print("学号 = ", row[0])
#    print("姓名 = ", row[1])
#    print("性别 = ", row[2])
conn.close()

登录代码如下：

def change(self):
        self.image_Label.destroy()
        self.Label_user.destroy()
        self.Entry_user.destroy()
        self.Label_password.destroy()
        self.Entry_password.destroy()
        self.Button_login.destroy()
        self.super_Button_login.destroy()
        # 创建会话连接
        conn = psycopg2.connect(dbname="postgres",
                                user="jacky",
                                password="Bigdata@123",
                                host="123.249.29.62",
                                port="26000")
        # 编码
        conn.set_client_encoding('utf8')
        # 创建游标
        cursor = conn.cursor()
        cursor.execute('select userid from book_user')
        userid = cursor.fetchall()
        found = 0
        for i in userid:
            for j in i:
                if self.useridStringvar.get() == j:
                    found = 1
                    page_3(root1)
        if found == 0:
            messagebox.showinfo('提示', self.useridStringvar.get() + '未找到用户信息，请注册！')
            page_2(root1)

注册代码如下：

class page_2:
    def __init__(self, root):
        # 创建会话连接
        conn = psycopg2.connect(dbname="postgres",
                                user="jacky",
                                password="Bigdata@123",
                                host="123.249.29.62",
                                port="26000")
        # 编码
        conn.set_client_encoding('utf8')
        # 创建游标
        cursor = conn.cursor()
        self.root = root
        root.title("mytest")
        root.geometry("620x490")

        # ttk中控件使用style对象设定
        self.root.Style01 = Style()
        self.root.Style01.configure("user.TLabel", font=("华文黑体", 20, "bold"), foreground="Black")
        self.root.Style01.configure("TEntry", font=("华文黑体", 20, "bold"))
        self.root.Style01.configure("TButton", font=("华文黑体", 20, "bold"), foreground="Black")

        Label(self.root, text='用户id：', style='user.TLabel').place(x=10, y=10)
        self.useridStringvar = StringVar()
        Entry(self.root, width=20, textvariable=self.useridStringvar, style='TEntry').place(x=130, y=15)

        Label(self.root, text='用户：', style='user.TLabel').place(x=10, y=60)
        self.usernameStringvar = StringVar()
        Entry(self.root, width=20, textvariable=self.usernameStringvar, style='TEntry').place(x=130, y=65)

        Label(self.root, text='密码：', style='user.TLabel').place(x=10, y=110)
        self.passwordStringvar = StringVar()
        Entry(self.root, width=20, textvariable=self.passwordStringvar, style='TEntry').place(x=130, y=115)

        Label(self.root, text='电话：', style='user.TLabel').place(x=10, y=160)
        self.phoneStringvar = StringVar()
        Entry(self.root, width=20, textvariable=self.phoneStringvar, style='TEntry').place(x=130, y=165)

        self.register_button = None
        self.button = None
        self.get_button()

        self.max_numStringvar = 10
        self.lend_numStringvar = 0
        self.statusStringvar = '未借'

    def get_button(self):
        self.register_button = Button(self.root, text="注册", command=self.insert)
        self.register_button.pack(side=LEFT)
        self.button = Button(self.root, text="返回", command=self.back)
        self.button.pack(side=BOTTOM)

    def back(self):
        self.register_button.destroy()
        self.button.destroy()
        page_1(self.root)
        # pass

    def insert(self):
        # 创建会话连接
        conn = psycopg2.connect(dbname="postgres",
                                user="jacky",
                                password="Bigdata@123",
                                host="123.249.29.62",
                                port="26000")
        # 编码
        conn.set_client_encoding('utf8')
        # 创建游标
        cursor = conn.cursor()
        cursor.execute("insert into book_user values ('{0}','{1}','{2}','{3}','{4}', '{5}', '{6}')".format(
            self.useridStringvar.get(), self.usernameStringvar.get(), self.passwordStringvar.get(),
            self.phoneStringvar.get(),
            self.statusStringvar, self.lend_numStringvar, self.max_numStringvar))
        conn.commit()
        messagebox.showinfo('提示', self.useridStringvar.get() + '信息注册成功！')
        conn.close()

接下来便是查询功能设计

设计了如下所示八大模块：

设计图书信息、图书借阅信息、借阅者信息、学生信息、教师信息、课程信息、论文信息、专利信息

以学生信息模块为例，代码如下：

import psycopg2
import tkinter as tk
from tkinter import ttk
import tkinter.messagebox


# win = tk.Tk()
# win.geometry('1000x800')

class student:
    def __init__(self, win):
        self.win = win
        tk.Label(win, text='学号：').grid(row=0, column=0)
        self.snoStringvar = tk.StringVar()
        tk.Entry(win, width=20, textvariable=self.snoStringvar).grid(row=0, column=1)

        tk.Label(win, text='姓名：').grid(row=1, column=0)
        self.nameStringvar = tk.StringVar()
        tk.Entry(win, width=20, textvariable=self.nameStringvar).grid(row=1, column=1)

        tk.Label(win, text='导师：').grid(row=2, column=0)
        self.genderStringvar = tk.StringVar()
        tk.Entry(win, width=20, textvariable=self.genderStringvar).grid(row=2, column=1)

        tk.Label(win, text='类别：').grid(row=3, column=0)
        self.telephoneStringvar = tk.StringVar()
        tk.Entry(win, width=20, textvariable=self.telephoneStringvar).grid(row=3, column=1)
        # tk.Label(win, text='出版社：').grid(row=3, column=0)
        # self.pressStringvar = tk.StringVar()
        # pressChosen = ttk.Combobox(win, width=17, textvariable=self.pressStringvar)
        # pressChosen['values'] = ("清华出版社", "北大出版社", "桂电出版社")  # 设置下拉列表的值
        # pressChosen.grid(row=3, column=1)  # 设置其在界面中出现的位置 column代表列 row 代表行
        # pressChosen.current(2)  # 设置下拉列表默认显示的值，0为 numberChosen[‘values’] 的下标值
        tk.Label(win, text='年级：').grid(row=4, column=0)
        self.addressStringvar = tk.StringVar()
        tk.Entry(win, width=20, textvariable=self.addressStringvar).grid(row=4, column=1)

        #tk.Label(win, text='开始时间：').grid(row=5, column=0)
        #self.begin_timeStringvar = tk.StringVar()
        #tk.Entry(win, width=20, textvariable=self.begin_timeStringvar).grid(row=5, column=1)
        # tk.Label(win, text='类别：').grid(row=4, column=0)
        # self.typeStringvar = tk.StringVar()
        # typeChosen = ttk.Combobox(win, width=17, textvariable=self.typeStringvar)
        # typeChosen['values'] = ("计算机类", "编程类", "文学类")  # 设置下拉列表的值
        # typeChosen.grid(row=4, column=1)  # 设置其在界面中出现的位置 column代表列 row 代表行
        # typeChosen.current(0)  # 设置下拉列表默认显示的值，0为 numberChosen[‘values’] 的下标值

        tk.Label(win, text='状态：').grid(row=6, column=0)
        self.end_timeStringvar = tk.StringVar()
        tk.Entry(win, width=20, textvariable=self.end_timeStringvar).grid(row=6, column=1)

        tk.Label(win, text='备注：').grid(row=7, column=0)
        self.beizhuStringvar = tk.StringVar()
        tk.Entry(win, width=20, textvariable=self.beizhuStringvar).grid(row=7, column=1)

        self.get_root()

        self.tree = ttk.Treeview(win)
        self.tree['column'] = ('学号', '姓名', '导师', '类别', '年级', '状态', '备注')
        self.tree.column('学号', width=100)
        self.tree.column('姓名', width=100)
        self.tree.column('导师', width=100)
        self.tree.column('类别', width=100)
        self.tree.column('年级', width=100)
        #self.tree.column('开始时间', width=100)
        self.tree.column('状态', width=100)
        self.tree.column('备注', width=100)
        self.tree.heading('学号', text='学号')
        self.tree.heading('姓名', text='姓名')
        self.tree.heading('导师', text='导师')
        self.tree.heading('类别', text='类别')
        self.tree.heading('年级', text='年级')
        #self.tree.heading('开始时间', text='开始时间')
        self.tree.heading('状态', text='状态')
        self.tree.heading('备注', text='备注')
        self.tree.grid(row=10, column=0, columnspan=10)

    def select(self):
        xh0 = self.snoStringvar.get()
        xh1 = self.nameStringvar.get()
        xh2 = self.genderStringvar.get()
        xh3 = self.telephoneStringvar.get()
        xh4 = self.addressStringvar.get()
        xh5 = self.end_timeStringvar.get()
        xh6 = self.beizhuStringvar.get()

        # 创建会话连接
        conn = psycopg2.connect(dbname="postgres",
                                user="jacky",
                                password="Bigdata@123",
                                host="123.249.29.62",
                                port="26000")
        # 编码
        conn.set_client_encoding('utf8')
        # 创建游标
        cursor = conn.cursor()
        sign = 0
        if len(xh0) == 0 and len(xh1) == 0 and len(xh2) == 0 and len(xh3) == 0 and len(xh4) == 0 and len(xh5) == 0 and len(xh6) == 0:
            c = cursor.execute('select * from student')
        else:
            c = "select * from student where"
            if len(xh0) != 0:
                if sign == 1:
                    c += " and"
                else:
                    sign += 1
                c += " studentid = '{}'".format(xh0)
            if len(xh1) != 0:
                if sign == 1:
                    c += " and"
                else:
                    sign += 1
                c += " cname = '{}'".format(xh1)
            if len(xh2) != 0:
                if sign == 1:
                    c += " and"
                else:
                    sign += 1
                c += " supervisor = '{}'".format(xh2)
            if len(xh3) != 0:
                if sign == 1:
                    c += " and"
                else:
                    sign += 1
                c += " category = '{}'".format(xh3)
            if len(xh4) != 0:
                if sign == 1:
                    c += " and"
                else:
                    sign += 1
                c += " grade = '{}'".format(xh4)
            if len(xh5) != 0:
                if sign == 1:
                    c += " and"
                else:
                    sign += 1
                c += " reading_information = '{}'".format(xh5)
            if len(xh6) != 0:
                if sign == 1:
                    c += " and"
                else:
                    sign += 1
                c += " notes = '{}'".format(xh6)
            #print('select * from student where studentid like "%' + xh0 + '%"')
            c0 = cursor.execute(c)

        list_re = cursor.fetchall()
        x = self.tree.get_children()
        for item in x:
            self.tree.delete(item)
        for i in range(len(list_re)):
            self.tree.insert('', i, text=i, values=(
                list_re[i][0], list_re[i][1], list_re[i][9], list_re[i][6], list_re[i][15], list_re[i][8], list_re[i][14]))
        conn.close()

    def insert(self):
        # 创建会话连接
        conn = psycopg2.connect(dbname="postgres",
                                user="jacky",
                                password="Bigdata@123",
                                host="123.249.29.62",
                                port="26000")
        # 编码
        conn.set_client_encoding('utf8')
        # 创建游标
        cursor = conn.cursor()
        cursor.execute("insert into student values ('{0}','{1}',null,null,null,null,'{2}',null,'{3}','{4}',null,null,null,null, '{5}', '{6}')".format(
            self.snoStringvar.get(), self.nameStringvar.get(), self.telephoneStringvar.get(),self.end_timeStringvar.get(),
            self.genderStringvar.get(),  self.beizhuStringvar.get(), self.addressStringvar.get()))
        conn.commit()
        self.tree.insert('', 'end',
                         value=[self.snoStringvar.get(), self.nameStringvar.get(), self.genderStringvar.get(), self.telephoneStringvar.get(),
            self.addressStringvar.get(), self.end_timeStringvar.get(), self.beizhuStringvar.get()])
        tk.messagebox.showinfo('提示', self.nameStringvar.get() + '信息插入成功！')
        conn.close()

    #还没弄好。。。
    def update(self):
        xh = [self.snoStringvar.get(), self.nameStringvar.get(), self.genderStringvar.get(), self.telephoneStringvar.get(),
            self.addressStringvar.get(), self.end_timeStringvar.get(), self.beizhuStringvar.get()]
        # 创建会话连接
        conn = psycopg2.connect(dbname="postgres",
                                user="jacky",
                                password="Bigdata@123",
                                host="123.249.29.62",
                                port="26000")
        # 编码
        conn.set_client_encoding('utf8')
        # 创建游标
        cursor = conn.cursor()
        cursor.execute(
            "update student set studentid='{0}',cname='{1}',supervisor='{2}',category='{3}',grade='{4}', reading_information='{5}', notes='{6}' where studentid = '{7}'".format(
                                                                                                                 xh[0],
                                                                                                                 xh[1],
                                                                                                                 xh[2],
                                                                                                                 xh[3],
                                                                                                                 xh[4],
                                                                                                                 xh[5],
                                                                                                                 xh[6],
                                                                                                                 xh[0]))
        conn.commit()
        tk.messagebox.showinfo('提示', xh[0] + '信息更新成功！')
        conn.close()

    def delete(self):
        xh0 = self.snoStringvar.get()
        xh1 = self.nameStringvar.get()
        xh2 = self.genderStringvar.get()
        xh3 = self.telephoneStringvar.get()
        xh4 = self.addressStringvar.get()
        xh5 = self.end_timeStringvar.get()
        xh6 = self.beizhuStringvar.get()
        # 创建会话连接
        conn = psycopg2.connect(dbname="postgres",
                                user="jacky",
                                password="Bigdata@123",
                                host="123.249.29.62",
                                port="26000")
        # 编码
        conn.set_client_encoding('utf8')
        # 创建游标
        cursor = conn.cursor()
        sign = 0
        c = "delete from student where"
        if len(xh0) != 0:
            if sign == 1:
                c += " and"
            else:
                sign += 1
            c += " studentid = '{}'".format(xh0)
        if len(xh1) != 0:
            if sign == 1:
                c += " and"
            else:
                sign += 1
            c += " cname = '{}'".format(xh1)
        if len(xh2) != 0:
            if sign == 1:
                c += " and"
            else:
                sign += 1
            c += " supervisor = '{}'".format(xh2)
        if len(xh3) != 0:
            if sign == 1:
                c += " and"
            else:
                sign += 1
            c += " category = '{}'".format(xh3)
        if len(xh4) != 0:
            if sign == 1:
                c += " and"
            else:
                sign += 1
            c += " grade = '{}'".format(xh4)
        if len(xh5) != 0:
            if sign == 1:
                c += " and"
            else:
                sign += 1
            c += " reading_information = '{}'".format(xh5)
        if len(xh6) != 0:
            if sign == 1:
                c += " and"
            else:
                sign += 1
            c += " notes = '{}'".format(xh6)
        # print('select * from student where studentid like "%' + xh0 + '%"')
        c0 = cursor.execute(c)
        #cursor.execute("delete from student where studentid = '{}'".format(xh))
        conn.commit()
        tk.messagebox.showinfo('提示', '信息删除成功！')
        conn.close()

    def clear(self):
        self.snoStringvar.set('')
        self.nameStringvar.set('')
        self.genderStringvar.set('')
        self.telephoneStringvar.set('')
        self.addressStringvar.set('')
        self.begin_timeStringvar.set('')
        self.end_timeStringvar.set('')
        self.beizhuStringvar.set('')


    def get_root(self):
        win = self.win
        tk.Button(win, text='清空', width=10, height=1, command=self.clear).grid(row=8, column=0, pady=5)
        tk.Button(win, text='查询', width=10, height=1, command=self.select).grid(row=8, column=1, pady=5)
        tk.Button(win, text='插入', width=10, height=1, command=self.insert).grid(row=8, column=2, pady=5)
        tk.Button(win, text='更新', width=10, height=1, command=self.update).grid(row=8, column=3, pady=5)
        tk.Button(win, text='删除', width=10, height=1, command=self.delete).grid(row=8, column=4, pady=5)


# win.mainloop()

其他部分同理。

我们可以看到，对于上述的每个实现方法的python脚本，都内置了插入，删除，更新的方法，方便用户对图书馆用户数据库进行管理。通过系统上方的输入框来进行SQL的条件筛选，从而实现对表中元组的修改。具体过程见代码描述。

最后，由于华为云不支持上传python文件，感兴趣的同学可以私聊我获取项目源码