本文只是为了做记录,此程序是本人第一次使用python连数据库、做gui,只是因为好奇而写。
不管是性能、还是事务管理逻辑判断都非常不足。
敬请指出不足,但不喜勿喷。
前提:下载好pymysql & tkinter
在MySQL建好数据库sysUser 、表sysUser:
MySQL部分
create database sysUser;
use sysUser;
create table if not exists sysUser(
uid varchar(20) not null,
passwd varchar(100) not null,
limits varchar(20) not null,
uname varchar(20) not null,
num varchar(20) not null,
primary key(id)
);
insert into sysUser values('1','111111','read','ZhangSan','123123');
insert into sysUser values('2','222222','write','Lisi','789789');
程序部分(杂乱、粗糙、只是为了做测试,有兴趣再优化了)
ps:要运行记得修改连接数据库时的用户名和密码。
# -*- coding: utf-8 -*-
"""
Created on Fri May 15 17:01:32 2020
@author: young
本文使用uid、uname变量名是因为
id是python中的一个函数,不建议用作变量名;name是MySQL中较为特殊的一个字段。
"""
#导入用于连接数据库的库
import pymysql
#导入用于可视化的库
from tkinter import *
#升级的组合控件包
from tkinter.tix import Tk, Control, ComboBox
#各种类型的提示框
from tkinter.messagebox import showinfo, showwarning, showerror, askokcancel
#一、DAO
#定义一个数据库类来封装增删改查的动作
class db:
def __init__(self):
#获取数据库连接
#此处直接在构造器中建立连接,因个人觉得每次增删改查都建立和释放过于消耗资源
#但网上也有人那么做,也可能有使用连接池的方式(不了解),请自行了解
self.conn = pymysql.connect(host = 'localhost',
user = 'root',
password = '888888',
db = 'sysUser'
)
#通过连接获取游标(游标可以用于操作数据库)
self.curson = self.conn.cursor()
#插入用户
#要把self传入,就算不写也会占用一个位置,导致方法调用时传参个数出错
def insert(self, table, uid, passwd, limits, uname, num):
sql = "insert into "+ table +" values('"+ uid +"','"+ passwd +"','"+ limits +"','"+ uname +"','"+ num +"')"
try:
self.curson.execute(sql)
self.conn.commit()
showinfo('OK', 'success!')
except:
self.conn.rollback()
showinfo('!', 'fail!')
#删除用户
def delete(self, table, uid):
#拼接不需加’的变量时要加空格,拼接字符型变量指定条件时‘后方不要加空格
#为区分,sql应尽量使用“”嵌套;以及删除指定的字符型数据条件应该用模糊查找而非=
sql = "delete from "+ table +" where uid like '%"+ uid +"%'"
try:
self.curson.execute(sql)
self.conn.commit()
showinfo('OK', 'success!')
except:
self.conn.rollback()
showinfo('!', 'fail!')
#修改用户信息(按照逻辑,只能修改密码、号码)
def update(self, table, uid, passwd, num):
if(''!=passwd and ''!=num):
sql = "update "+ table +" set passwd='"+ passwd +\
"', num='"+ num +"' where uid ='"+ uid +"'"
elif(''!=passwd and ''==num):
sql = "update "+ table +" set passwd='"+ passwd +\
"' where uid ='"+ uid +"'"
else:
sql = "update "+ table +" set num = '"+ num +\
"' where uid ='"+ uid +"'"
try:
self.curson.execute(sql)
self.conn.commit()
showinfo('OK', 'success!')
except:
self.conn.rollback()
showinfo('!', 'fail!')
#查找table表中所有用户姓名
def findAllName(self, table):
sql = "select uname from " + table
#利用游标执行sql语句
self.curson.execute(sql)
#fetchone()返回一个对象,fetchall()返回多个
data = self.curson.fetchall()
return data
#查找table中所有用户信息
def findAll(self, table):
sql = "select * from " + table
#利用游标执行sql语句
self.curson.execute(sql)
#fetchone()返回一个对象,fetchall()返回多个
data = self.curson.fetchall()
return data
#根据姓名查找table表中某个用户所有信息(未考虑同名情况)
def findOneByName(self, table, uname):
#sql要注意查找时字段名要加‘’
sql = "select * from " + table + " where uname = '" + uname + "'"
self.curson.execute(sql)
data = self.curson.fetchall()
return data
#根据id查找table表中某个用户所有信息
def findOneById(self, table, uid):
#sql要注意查找时字段名要加‘’
sql = "select * from " + table + " where uid = '" + uid + "'"
self.curson.execute(sql)
data = self.curson.fetchall()
return data
#二、Service(其实应该用一个类封装起来的)
#真正的执行插入
# 其实方法中的五个属性可以用一个类封装起来,参数只需传一个对象
def execInsert(root, table,uid,passwd,limits,uname,num):
if('' == uid or '' == passwd or '' == limits or '' == uname or '' == num):
showinfo('注意', '输入信息不完整')
#先把原插入窗口销毁再开启新窗口,否则会报错,原因未查
#优化:其实这里还可以利用Entry的textvariable属性把原本填的值传过来了
root.destroy()
addUserWin()
elif(len(db.findOneById(table, uid)) != 0):
showinfo('注意', '该id已存在,请重新输入')
root.destroy()
addUserWin()
else:
db.insert(table, uid, passwd, limits, uname, num)
root.destroy()
#真正删除信息前的提示
def execDelete(table, uid):
choice = askokcancel('?', '确定要删除该用户吗')
if(choice):
db.delete(table, uid)
#真正地执行修改
def execAlter(root, table, uid, passwd, num):
passwd = str(passwd)
num = str(num)
if('' == passwd and '' == num):
showinfo('error', '你没有输入任何值')
root.destroy() #直接销毁当前页面、也可以不销毁、但是会乱
else:
db.update(table, uid, passwd, num)
root.destroy()
#三、GUI
#名为name的用户信息详情的新窗口
def moreImfWin(name):
root = Tk()
root.title('用户信息详情')
root.geometry('800x600')
root.resizable(width = True, height = True)
# 找到sysUser表中姓名为name的用户信息
imf = db.findOneByName('sysUser',name)
# 保存游标的描述信息,目的是了获取字段名
desc = db.curson.description
#其实imf是一个二维元组,因为只查找了一行数据,所有只有imf[0]有值
for i in range(len(imf[0])):
label1 = Label(root, text = desc[i][0], width = 20) #显示字段名
label1.grid(row = i, column = 0, padx = 80, pady = 10)
label2 = Label(root, text = imf[0][i], width = 20) #遍历显示值
label2.grid(row = i, column = 1, ipadx = 10)
#从desc中找到字段uid所在列,然后从imf中拿出来存到变量id中,用于后面的删除修改动作
if(desc[i][0] == 'uid'):
uid = imf[0][i]
#定义一个修改信息的按钮
alterButton = Button(root, text = '修改信息',
command = lambda: alterImfWin(uid))
alterButton.grid(columnspan = 2, pady = 20)
#定义一个删除信息的按钮
alterButton = Button(root, text = '删除该用户',
command = lambda: execDelete('sysUser', uid))
alterButton.grid(columnspan = 2)
#进入消息循环(必需组件)
root.mainloop()
#插入新用户的窗口
def addUserWin():
root = Tk()
root.title('添加用户')
root.geometry('800x600')
root.resizable(width = True, height = True)
label = Label(root, text = '请务必填写各项信息')
label.grid(row = 0, columnspan = 2, padx = 330, pady = 55)
uidLabel = Label(root, text = 'id')
uidLabel.grid(row = 1, column = 0, padx = 200)
uidEntry = Entry(root)
uidEntry.grid(row = 1, column = 1)
passwdLabel = Label(root, text = 'passwd')
passwdLabel.grid(row = 2, column = 0)
passwdEntry = Entry(root)
passwdEntry.grid(row = 2, column = 1)
limitsLabel = Label(root, text = 'limits')
limitsLabel.grid(row = 3, column = 0)
limitsEntry = Entry(root)
limitsEntry.grid(row = 3, column = 1)
unameLabel = Label(root, text = 'name')
unameLabel.grid(row = 4, column = 0)
unameEntry = Entry(root)
unameEntry.grid(row = 4, column = 1)
numLabel = Label(root, text = 'num')
numLabel.grid(row = 5, column = 0)
numEntry = Entry(root)
numEntry.grid(row = 5, column = 1)
execButton = Button(root, text = '确认提交',
command = lambda: execInsert(root,
'sysUser',
uidEntry.get(),
passwdEntry.get(),
limitsEntry.get(),
unameEntry.get(),
numEntry.get()))
execButton.grid(row = 6, columnspan = 2, pady = 30)
#修改用户信息信息窗口
def alterImfWin(uid):
root = Tk()
root.title('修改用户信息')
root.geometry('800x600')
root.resizable(width = True, height = True)
#这里的Entry其实可以使其参数textvariable设为一个StringVar对象,
#可以达到动态改变文本框内的值的效果,但此处不需要这个功能,故不使用
passwdLabel = Label(root, text = 'passwd')
passwdLabel.grid(row = 0, column = 0, padx = 100, pady = 44)
passwdEntry = Entry(root)
passwdEntry.grid(row = 0, column = 1)
numLabel = Label(root, text = 'num')
numLabel.grid(row = 1, column = 0)
numEntry = Entry(root)
numEntry.grid(row = 1, column = 1)
#注意文本框的值应该在传参数再get()获取,否则会获取不到
execButton = Button(root, text = '提交',
command = lambda: execAlter(root,'sysUser',uid,
passwdEntry.get(),
numEntry.get()))
execButton.grid(row = 3, columnspan = 2, pady = 20)
#关闭窗口按钮的事件监听,目的是为了释放数据库连接
def callbackClose():
if(None != db.conn):
db.conn.close()
root.destroy()
#主窗口
if __name__ == '__main__':
#初始化一个布局的根节点
root = Tk()
#引入升级包,才可以使用升级的组合控件
root.tk.eval('package require Tix')
#设置标题
root.title('系统用户表')
sw = root.winfo_screenwidth() #得到屏幕宽度
sh = root.winfo_screenheight() #得到屏幕高度
ww = 800 #窗口宽为800
wh = 600 #窗口高为600
x = (sw-ww) / 2 #获得边距
y = (sh-wh) / 2
#设置窗口大小
root.geometry("%dx%d+%d+%d" %(ww,wh,x,y))
#设置窗口大小为可变
root.resizable(width = True, height = True)
#获取数据库类对象
db = db()
# 找到sysUser表中所有用户名(此处不用进行判断找不到的情况是因为原窗口中的name就是从数据库拿的)
allName = db.findAllName('sysUser')
label = Label(root, text = '所有用户', width = 9)
label.grid(row = 0, column = 0, columnspan = 2, padx = 395, pady = 55)
# 循环打印出每一个用户名,并在其后方添加‘see more’按钮
for i in range(len(allName)):
nameLabel = Label(root, width = 9, text=allName[i])
nameLabel.grid(row = i+1, column = 0)
name = "".join(allName[i]) #将name从元组转为str类型,后方sql语句会用到
button = Button(root, width = 9, text='see more..',
command=lambda name=name: moreImfWin(name))
#此处lambda表达式要加name=name是因为在循环中,不加会导致每一个传入的遍历都是最后一个值
button.grid(row = i+1, column = 1)
#添加用户按钮
insertButton = Button(root, text = '添加用户', command = addUserWin)
insertButton.grid(columnspan = 2, pady = 55)
#关闭窗口按钮监听,目的是为了释放数据库连接
root.protocol("WM_DELETE_WINDOW", callbackClose)
#进入消息循环(必需组件)
root.mainloop()
效果图不贴了、很丑哈哈哈,毕竟咱目的是功能嘛…