建立新表
import pymysql
db = pymysql.connect(host = "localhost", user = "root", password = "*******", database = "*******", charset='utf8' )
# 使用cursor()方法获取操作游标
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS studentlist")
#使用预处理语句创建表
sql ="""
CREATE TABLE studentlist (
name varchar(20) NOT NULL,
number varchar(20) NOT NULL
)"""
#执行sql语句
cursor.execute(sql)
db.close()
建立GUI界面并对button键添加事件（添加和查询）
1 import pymysql 2 import wx 3 4 5 6 class MyFrame(wx.Frame): 7 def __init__(self,parent,id): 8 wx.Frame.__init__(self,parent,id,title="学生信息收集",size=(400,300)) 9 #创建面板 10 panel=wx.Panel(self) 11 #创建文本和密码输入框 12 self.title =wx.StaticText(panel ,label="请输入姓名和学号",pos=(140,20)) 13 self.label_user =wx.StaticText(panel,label="姓 名:",pos=(50,50)) 14 self.text_user =wx.TextCtrl(panel,pos=(100,50),size=(235,25),style=wx.TE_LEFT) 15 self.label_number =wx.StaticText(panel,pos=(50,90),label="学 号:") 16 self.text_number =wx.TextCtrl(panel,pos=(100,90),size=(235,25),style=wx.TE_LEFT) 17 # 创建“确定”和“取消”按钮及添加事件 18 self.bt_confirm =wx.Button(panel,label='添加',pos=(105,130)) 19 self.bt_confirm.Bind(wx.EVT_BUTTON,self.OnclickAdd) 20 self.bt_cancel =wx.Button(panel,label='查询',pos=(195,130)) 21 self.bt_cancel.Bind(wx.EVT_BUTTON,self.OnclickCheck) 22 23 def OnclickCheck(self,event): 24 message = "" 25 username = self.text_user.GetValue() 26 usernumber = self.text_number.GetValue() 27 28 29 #打开数据库 30 db = pymysql.connect(host = "localhost", user = "root", password = "*******", database = "********", charset='utf8' ) 31 32 # 使用cursor()方法获取操作游标 33 cursor = db.cursor() 34 35 36 37 if username == "" or usernumber == "": 38 message = '姓名或学号不能为空' 39 else: 40 try: 41 # SQL 插入语句 42 sql = "SELECT * FROM xinjistudent" 43 # 执行sql语句 44 cursor.execute(sql) 45 # 提交到数据库执行 46 db.commit() 47 # 获取所有命令行 48 a = cursor.fetchall() 49 for row in a: 50 sqlname = row[0] 51 sqlnumber = row[1] 52 53 if username == sqlname and usernumber == sqlnumber: 54 message = '学生信息正确' 55 break 56 if message !='学生信息正确': 57 message = '学号姓名不匹配' 58 except: 59 message = '学号不存在' 60 # 关闭数据库连接 61 db.close() 62 wx.MessageBox(message) 63 64 def OnclickAdd(self,event): 65 message = "" 66 user_name = self.text_user.GetValue() 67 user_number = self.text_number.GetValue() 68 print(user_name,user_number) 69 db = pymysql.connect(host = "localhost", user = "root", password = "******", database = "******", charset='utf8' ) 70 71 # 使用cursor()方法获取操作游标 72 cursor = db.cursor() 73 data = [(user_name,user_number)] 74 75 76 77 if user_name == "" or user_number == "": 78 message = '学号和姓名不能为空' 79 elif len(user_name) >=1 and len(user_number) == 13: 80 try: 81 # SQL 插入语句 82 sql = "INSERT INTO xinjistudent(name,number) VALUES (%s,%s)" 83 # 执行sql语句 84 cursor.executemany(sql,data) 85 # 提交到数据库执行 86 db.commit() 87 88 message = '学生添加成功' 89 90 except: 91 message = '添加没有成功' 92 93 # 关闭数据库连接 94 db.close() 95 else: 96 message = '填写格式不对' 97 98 wx.MessageBox(message) 99 100 101 if __name__ == '__main__': 102 app =wx.App() # 初始化 103 frame =MyFrame(parent=None,id=-1) #实例MyFrame类，并传递参数 104 frame.Show() #显示窗口 105 app.MainLoop() # 调用主循环方法 import wx
1、添加
1.1成功添加
1.2输入为空时
1.3当学号不足13位或者名字小于1位时
2、查询
2.1查询成功
2.2、学号姓名为空时
2.3、学号姓名不匹配时