`#建立資料庫
import pymysql
db =pymysql.connect(host='localhost',user='root',password='Lwq020330',database="xinji_inf",charset='utf8')
使用cursor()方法建立一個遊標物件
cursor = db.cursor()
如果存在則刪除
cursor.execute("DROP TABLE IF EXISTS AccountPassword")
sql ="""
CREATE TABLE AccountPassword(
class VARCHAR(50) NOT NULL,
name VARCHAR(50) NOT NULL,
number VARCHAR(50) NOT NULL
)
"""
執行sql語句
cursor.execut
import pymysql
import wx
class MyFrame(wx.Frame):
def init(self,parent,id):
wx.Frame.init(self,parent,id,'班級資訊收集',size=(400,300))
#建立皮膚
panel = wx.Panel(self)
#建立“儲存”和“查詢”按鈕，並繫結事件
self.bt_storage = wx.Button(panel,label="儲存")
self.bt_storage.Bind(wx.EVT_BUTTON,self.OnclickStorage)
self.bt_inquire = wx.Button(panel,label ='查詢')
self.bt_inquire.Bind(wx.EVT_BUTTON,self.OnclickInquire)
#建立文字，左對齊
self.title =wx.StaticText(panel,label ="儲存資訊請輸入使用者的學號班級和姓名\n\t查詢請輸入姓名或學號")
self.label_class =wx.StaticText(panel,label ="班級：")
self.text_class =wx.TextCtrl(panel,style =wx.TE_LEFT)
self.label_user =wx.StaticText(panel,label ="姓名：")
self.text_user =wx.TextCtrl(panel,style =wx.TE_LEFT)
self.label_number = wx.StaticText(panel,label ="學號：")
self.text_number = wx.TextCtrl(panel,style =wx.TE_LEFT)
#新增容器，容器中控制元件橫向排列
hsizer_class =wx.BoxSizer(wx.HORIZONTAL)
hsizer_class.Add(self.label_class,proportion=0,flag=wx.ALL,border=5)
hsizer_class.Add(self.text_class,proportion=1,flag=wx.ALL,border=5)
hsizer_user = wx.BoxSizer(wx.HORIZONTAL)
hsizer_user.Add(self.label_user,proportion=0,flag=wx.ALL,border=5)
hsizer_user.Add(self.text_user,proportion=1,flag =wx.ALL,border=5)
hsizer_number =wx.BoxSizer(wx.HORIZONTAL)
hsizer_number.Add(self.label_number,proportion=0,flag=wx.ALL,border=5)
hsizer_number.Add(self.text_number,proportion=1,flag=wx.ALL,border=5)
hsizer_button =wx.BoxSizer(wx.HORIZONTAL)
hsizer_button.Add(self.bt_storage,proportion=0,flag=wx.ALIGN_CENTER,border=5)
hsizer_button.Add(self.bt_inquire,proportion=0,flag=wx.ALIGN_CENTER,border=5)
#新增容器，容器中的控制元件縱向排列
vsizer_all = wx.BoxSizer(wx.VERTICAL)
vsizer_all.Add(self.title,proportion=0,flag=wx.BOTTOM |wx.TOP |wx.ALIGN_CENTER,border=15)
vsizer_all.Add(hsizer_class,proportion=0,flag=wx.EXPAND |wx.LEFT |wx.RIGHT,border=45)
vsizer_all.Add(hsizer_user,proportion=0,flag=wx.EXPAND |wx.LEFT |wx.RIGHT,border=45)
vsizer_all.Add(hsizer_number,proportion=0,flag=wx.EXPAND |wx.LEFT |wx.RIGHT,border=45)
vsizer_all.Add(hsizer_button,proportion=0,flag=wx.ALIGN_CENTER |wx.TOP,border=15)
panel.SetSizer(vsizer_all)
def OnclickStorage(self,event):
"單擊儲存按鈕"
#連線資料庫
db =pymysql.connect(host='localhost',user='root',password='Lwq020330',database="xinji_inf",charset='utf8')
message =""
classname =self.text_class.GetValue() #獲取輸入的班級
username =self.text_user.GetValue() #獲取輸入的使用者名稱
number =self.text_number.GetValue() #獲取輸入的學號
#使用cursor()方法建立一個遊標物件
cursor = db.cursor()
#判斷是否為空
if classname =="" or username =="" or number =="":
message ='班級或名字或密碼不能為空'
else:
#資料列表
data = [(classname,username,number)]
try:
#執行sql語句，插入資料
sql="insert into accountpassword(class, name, number) values(%s,%s,%s)"
cursor.executemany(sql,data)
#提交資料
db.commit()
except:
#發生錯誤時回滾
db.rollback()
#關閉連線
db.close()
message ='儲存成功' #使用者名稱或密碼錯誤
wx.MessageBox(message) #彈出提示框
def OnclickInquire(self,event):
"單擊查詢按鈕"
username =self.text_user.GetValue() #獲取輸入的使用者名稱
number =self.text_number.GetValue() #獲取輸入的學號
db =pymysql.connect(host='localhost',user='root',password='Lwq020330',database="xinji_inf",charset='utf8')
#使用cursor()方法建立一個遊標物件
cursor = db.cursor()
sql =""
message =""
if username and number:
sql ="SELECT * FROM ACCOUNTPASSWORD \
WHERE NUMBER = %s" #查詢學號
try:
#執行sql語句
cursor.execute(sql,number)
#獲取記錄
results =cursor.fetchall()
if results:
for row in results:
classname = row[0]
username_sql =row[1]
number_sql =row[2]
except:
print("Error: unable to fecth data")
if username_sql == username:
message ="班級：{}\n姓名：{}\n學號：{}\n".format(classname,username_sql,number_sql)
else:
message ="姓名與學號不匹配"
elif username:
sql ="SELECT * FROM ACCOUNTPASSWORD \
WHERE NAME = %s" #找到該名字
try:
#執行sql語句
cursor.execute(sql,username)
#獲取記錄
results =cursor.fetchall()
if results: #若找到不為空，輸出對應的資訊
for row in results:
classname = row[0]
username =row[1]
number =row[2]
message ="班級：{}\n姓名：{}\n學號：{}\n".format(classname,username,number)
else:
message ="沒有這個名字"
except:
print("Error: unable to fecth data") #資料錯誤--
elif number:
sql ="SELECT * FROM ACCOUNTPASSWORD \
WHERE NUMBER = %s" #查詢學號，同上
try:
#執行sql語句
cursor.execute(sql,number)
#獲取記錄
results =cursor.fetchall()
if results:
for row in results:
classname = row[0]
username =row[1]
number =row[2]
message ="班級：{}\n姓名：{}\n學號：{}\n".format(classname,username,number)
else:
message ="沒有這個學號"
except:
print("Error: unable to fecth data")
else:
message ="班級或名字或密碼不能為空"
#關閉資料庫
db.close()
wx.MessageBox(message)
if name =='main':
app = wx.App() #初始化應用
frame = MyFrame(parent=None, id=-1) #例項MyFrame類，並傳遞引數
frame.Show() #顯示視窗
app.MainLoop() #呼叫主迴圈方法
`