Python3如何界面操作数据库

你的名字 2023-07-03 04:47 70阅读 0赞

# 写在开始 #

Python作为一个目前比较热门的新生语言，很值得小伙伴们一学。之前博主讲解了JDBC技术、Hibernate框架、Mybatis框架三种Java操作数据库的方法，但是Java需要找jar包下载，有些小伙伴可能会出现jar包问题导致程序出现报错的情况，因此，今天博主来讲下Python操作数据库的方法，同时制作一个简易界面，可视化地操作数据库！

# 前期准备 #

既然是要操作数据库，那么首先就要建一个数据库。博主使用的数据库工具为MySQL。

MySQL的安装后的配置步骤比较多，如果没配置的小伙伴建议自行百度下，博主这里就不多赘述了。

如果安装并且配置好MySQL的小伙伴，接下来就按照博主的语句进行建数据库与建表的操作。

**1.win+r打开cmd命令窗口**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAxODU0MA_size_16_color_FFFFFF_t_70]  
**2.登陆MySQL**

mysql -u <用户名> -p

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAxODU0MA_size_16_color_FFFFFF_t_70 1]

**3.建数据库(<数据库名>自行决定)**

create database <数据库名>;

**4.选中上面创建的数据库**

use <数据库名>

**5.建数据表(输入时可以根据此换行输入)**

create table <数据库名>(
    id int auto_increment,
    title varchar(30),
    author varchar(30),
    price varchar(30),
    primary key(id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

这样一个数据表就建好啦，这里最后一步的列名是根据我下面的程序来定的，如果你有自己的想法可以自己定。

博主的编辑器为pyCharm，主要是下载包比较方便，一键搞定，当然如果没有pyCharm，用最原始的编辑器IDLE，也可以直接在cmd界面下使用pip install <包名>来安装。

# 代码实现 #

#导入包
    #pymysql是python3连接数据库的包
    #tkinter是python的GUI界面包
    #tkinter.messagebox是消息提示框包
    import pymysql
    import tkinter as tk
    import tkinter.messagebox
    
    #数据库添加操作
    def db_add():
        #连接数据库
        db=pymysql.connect(host="localhost",port=3306,user="root",password="123a123a",db="test")
        #创建光标
        cursor=db.cursor()
        #编写SQL语句
        sql = "insert into book(title,author,price) values('%s','%s',%s)"%(v1.get(),v2.get(),v3.get())
        #执行SQL语句，并且输出完成提示信息，否则回滚
        try:
            cursor.execute(sql)
            db.commit()
            tkinter.messagebox.showinfo("提示","数据添加成功")
        except:
            db.rollback()
        #关闭数据库连接，防止泄露
        db.close()
    
    #数据库删除操作
    def db_delete():
        db=pymysql.connect(host="localhost",port=3306,user="root",password="123a123a",db="test")
        cursor=db.cursor()
        sql = "delete from book where title='%s'" % (v4.get())
        try:
            cursor.execute(sql)
            db.commit()
            tkinter.messagebox.showinfo("提示","数据删除成功")
        except:
            db.rollback()
        db.close()
    
    #数据库更新操作
    def db_update():
        db = pymysql.connect(host="localhost", port=3306, user="root", password="123a123a", db="test")
        cursor = db.cursor()
        sql="update book set price='%s' where title='%s'"%(v6.get(),v5.get())
        try:
            cursor.execute(sql)
            db.commit()
            tkinter.messagebox.showinfo("提示","数据更新成功！")
        except:
            db.rollback()
        db.close()
    
    #数据库输出所有记录
    def db_select_all():
        db = pymysql.connect(host="localhost", port=3306, user="root", password="123a123a", db="test")
        cursor = db.cursor()
        sql = "select * from book"
        try:
            #MySQL删除只删除数据不删除空间，导致id列自增会出现断层，需要重新生成后输出
            db_initiate()
            print("序号排列完成！")
            cursor.execute(sql)
            #输出全部结果
            results=cursor.fetchall()
            #输出对应列的数据
            for row in results:
                id = row[0]
                title = row[1]
                author = row[2]
                price = row[3]
                print("id=%d,title=%s,author=%s,price=%s" % (id,title, author, price))
        except:
            return
        db.close()
    
    #数据库模糊条件查询
    def db_select():
        db = pymysql.connect(host="localhost", port=3306, user="root", password="123a123a", db="test")
        cursor = db.cursor()
        sql = "select title,author,price from book where title like'%s'"%('%'+v7.get()+'%')
        try:
            cursor.execute(sql)
            results = cursor.fetchall()
            for row in results:
                title = row[0]
                author = row[1]
                price = row[2]
                tkinter.messagebox.showinfo("提示","title=%s,author=%s,price=%s" % (title, author, price))
        except:
            return
    
    #重新生成自增id列
    def db_initiate():
        db = pymysql.connect(host="localhost", port=3306, user="root", password="123a123a", db="test")
        cursor = db.cursor()
        #原理是删除原先的id列，然后重新生成一个自增id列
        sql = "alter table book drop id;"
        sql2= "alter table book add id int primary key not null auto_increment first;"
        try:
            cursor.execute(sql)
            db.commit()
            cursor.execute(sql2)
            db.commit()
        except:
            return
        db.close()
    
    #添加书籍界面
    def menu_add():
        #构建全集变量，方便上面的函数调用
        global window_function
        global v1, v2, v3
        #生成窗口
        window_function=tk.Tk()
        #窗口标题
        window_function.title("图书数据库系统")
        #窗口大小
        window_function.geometry('400x300')
        #生成标签
        tk.Label(window_function, text="添加新书", font=("黑体", 20)).grid(row=0,column=1,pady=10)
        tk.Label(window_function,text="请输入书名：").grid(row = 1,column =0,padx=20,pady=20)
        tk.Label(window_function,text="请输入作者名：").grid(row = 2,column =0,padx=20,pady=20)
        tk.Label(window_function,text="请输入价格：").grid(row = 3,column =0,padx=20,pady=20)
        #定义变量记录输入信息
        v1=tk.StringVar()
        v2=tk.StringVar()
        v3=tk.StringVar()
        #生成输入框
        entry1=tk.Entry(window_function,show=None,textvariable=v1).grid(row = 1,column =1)
        entry2=tk.Entry(window_function,show=None,textvariable=v2).grid(row = 2,column =1)
        entry3=tk.Entry(window_function,show=None,textvariable=v3).grid(row = 3,column =1)
        #生成按钮
        button = tk.Button(window_function, text="添加", command=db_add).grid(row = 4,column =1)
        button2 = tk.Button(window_function, text="返回", command=pagechaneg_main).grid(row = 4,column =2,padx=50)
        #显示窗口
        window_function.mainloop()
    
    #删除书籍界面
    def menu_delete():
        global window_function
        global v4
        window_function=tk.Tk()
        window_function.title("图书数据库系统")
        window_function.geometry('400x300')
        tk.Label(window_function, text="删除书籍", font=("黑体", 20)).grid(row=0,column=1,pady=20)
        tk.Label(window_function,text="请输入书名：").grid(row = 1,column =0,padx=20)
        v4 =tk.StringVar()
        entry1=tk.Entry(window_function,show=None,textvariable=v4).grid(row = 1,column =1,pady=40)
        button = tk.Button(window_function, text="删除", command=db_delete).grid(row = 2,column =1)
        button2 = tk.Button(window_function, text="返回", command=pagechaneg_main).grid(row = 2,column =2,padx=50)
        window_function.mainloop()
    
    #更新书籍界面
    def menu_update():
        global window_function
        global v5,v6
        window_function=tk.Tk()
        window_function.title("图书数据库系统")
        window_function.geometry('400x300')
        tk.Label(window_function, text="更新书籍", font=("黑体", 20)).grid(row=0,column=1,pady=20)
        tk.Label(window_function,text="请输入书名：").grid(row = 1,column =0,padx=20,pady=20)
        tk.Label(window_function,text="请输入价格：").grid(row = 2,column =0,padx=20,pady=20)
        v5=tk.StringVar()
        v6=tk.StringVar()
        entry1=tk.Entry(window_function,show=None,textvariable=v5).grid(row = 1,column =1)
        entry2=tk.Entry(window_function,show=None,textvariable=v6).grid(row = 2,column =1)
        button = tk.Button(window_function, text="更新", command=db_update).grid(row = 3,column =1)
        button2 = tk.Button(window_function, text="返回", command=pagechaneg_main).grid(row = 3,column =2,padx=50)
        window_function.mainloop()
    
    #条件查找书籍界面
    def menu_select():
        global window_function
        global v7
        window_function=tk.Tk()
        window_function.title("图书数据库系统")
        window_function.geometry('400x300')
        tk.Label(window_function, text="查找书籍", font=("黑体", 20)).grid(row=0,column=1,pady=20)
        tk.Label(window_function,text="请输入书名：").grid(row = 1,column =0,padx=20)
        v7 =tk.StringVar()
        entry1=tk.Entry(window_function,show=None,textvariable=v7).grid(row = 1,column =1,pady=40)
        button = tk.Button(window_function, text="查找", command=db_select).grid(row = 2,column =1)
        button2 = tk.Button(window_function, text="返回", command=pagechaneg_main).grid(row = 2,column =2,padx=50)
        window_function.mainloop()
    
    #添加书籍界面跳转
    def pagechange_add():
        #销毁画布
        window.destroy()
        #生成新界面
        menu_add()
    
    #删除书籍界面跳转
    def pagechange_delete():
        window.destroy()
        menu_delete()
    
    #更新书籍界面跳转
    def pagechange_update():
        window.destroy()
        menu_update()
    
    #条件查询书籍界面跳转
    def pagechange_select():
        window.destroy()
        menu_select()
    
    #主界面跳转
    def pagechaneg_main():
        window_function.destroy()
        mainpage()
    
    #主界面
    def mainpage():
        global window
        window = tk.Tk()
        window.title("图书数据库系统")
        window.geometry('400x300')
        #生成画布，销毁后生成新的画布实现跳转
        page = tk.Frame(window)
        page.pack()
        tk.Label(window, text="欢迎使用图书数据库系统", font=("黑体", 20)).pack(pady=10)
        button1 = tk.Button(window, text="添加书籍", command=pagechange_add).pack(pady=10)
        button2 = tk.Button(window, text="删除书籍", command=pagechange_delete).pack(pady=10)
        button3 = tk.Button(window, text="修改书籍", command=pagechange_update).pack(pady=10)
        button4 = tk.Button(window, text="查找书籍", command=pagechange_select).pack(pady=10)
        window.mainloop()
    
    #主函数生成主界面
    if __name__ == '__main__':
        mainpage()

# 总结 #

这里的源码分成了两部分，第一部分是对于数据库的操作，第二部分是使用tkinter对于界面的创建，能够使用户更好地进行交互。

源码会上传至github，有需要的小伙伴可以自取。链接为：[github仓库][github]，项目名为MySQL

# 结语 #

如果说这篇文章有让你学到一定的知识的话，不妨点个赞和关注，让博主能够看到，如果讲解中有什么错误和疏忽，也劳烦在评论中指出或提问，博主会第一时间进行更新和答复，谢谢！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAxODU0MA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200204124749113.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAxODU0MA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAxODU0MA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200203134007412.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAxODU0MA==,size_16,color_FFFFFF,t_70
[github]: https://github.com/Edith-finch/CSDN