#author = liuwei date = 2017-06-02
from datetime import *   #導(dǎo)入日期模塊
__metaclass__ = type
class Book:
 '''一個(gè)書(shū)本信息類(lèi)，包括書(shū)本名字，作者名字和書(shū)本簡(jiǎn)單信息'''
 def __init__(self, bookName = "", author = "", content = ""):
 self.bookName = bookName   #書(shū)本名字
 self.author = author   #作者名字
 self.content = content   #書(shū)本信息
 self.add_date = date.today()   #書(shū)本添加日期

 def setBookName(self, name):
 self.bookName = name

 def getBookName(self):
 return self.bookName

 def setAuthor(self, author):
 self.author = author

 def getAuthor(self):
 return self.author

 def setContent(self, content):
 self.content = content

 def getContent(self):
 return self.content

 def getAddDate(self):
 return self.add_date


if __name__ == "__main__":
 mybook = Book()
 print(mybook.date)

#author = liuwei date = 2017-06-02
#數(shù)據(jù)庫(kù)幫助類(lèi)
import pymysql
from book import *

__metaclass__ = type
class DBHelper:
 def getCon(self):
 '''獲取操作數(shù)據(jù)庫(kù)的curcor即游標(biāo)，首先的建立連接，需要服務(wù)器地址，端口號(hào)，用戶(hù)名，密碼和數(shù)據(jù)庫(kù)名'''
 #為了能用中文，得加上編碼方式
 conn = pymysql.connect(host = "localhost", port = 3306, user = "root", password = "201392260", db = "library", charset = "utf8")

 return conn

 def insertBook(self, book):
 '''向數(shù)據(jù)庫(kù)中book表插入書(shū)本信息，book為Book類(lèi)對(duì)象，包含書(shū)本基本信息'''
 sql = "insert into book(name, author, content, add_date) values(%s, %s, %s, %s)"

 conn = self.getCon();
 if conn ==None:
 return

 cursor = conn.cursor()
 cursor.execute(sql, (book.getBookName(), book.getAuthor(), book.getContent(), book.getAddDate()))

 conn.commit()
 cursor.close()
 conn.close()

 new_id = cursor.lastrowid
 print("新插入鍵值id為:", new_id)

 return new_id

 def getAllBook(self):
 '''返回?cái)?shù)據(jù)庫(kù)中，book表中所有的書(shū)本信息'''
 sql = "select *from book"

 conn = self.getCon()
 if conn == None:
 return

 cursor = conn.cursor()
 rownum = cursor.execute(sql) #執(zhí)行并返回找到的行數(shù)

 #獲取查詢(xún)結(jié)果
 rows = cursor.fetchall()
 list = []

 for item in rows:
 bitem = (item[0], item[1], str(item[4]))

 list.append(bitem)

 conn.commit()
 cursor.close()
 conn.close()

 return list

 def getBookById(self, bookid):
 '''根據(jù)書(shū)本id值來(lái)尋找書(shū)本信息'''

 sql = "select book.name, book.author, book.content from book where id=%s"

 conn = self.getCon()
 if conn == None:
 return

 cursor = conn.cursor()
 cursor.execute(sql, (bookid, ))  #參數(shù)以元組形式給出
 row = cursor.fetchone()  #取到第一個(gè)結(jié)果

 conn.commit()
 cursor.close()
 conn.close()

 return row   #返回該書(shū)本信息


 def saveUpdate(self, bookid, book):
 '''用book對(duì)象來(lái)修改id為bookid的書(shū)本信息'''
 sql = "update book set book.name=%s, book.author=%s, book.content=%s where book.id=%s"

 conn = self.getCon()
 if conn == None:
 return

 cursor = conn.cursor()
 cursor.execute(sql, (book.getBookName(), book.getAuthor(), book.getContent(), bookid))

 conn.commit()
 cursor.close()
 conn.close()

 def deleteBook(self, bookid):
 '''根據(jù)書(shū)本id來(lái)刪除書(shū)籍'''
 sql = "delete from book where book.id = %s"

 conn = self.getCon()
 if conn == None:
 return

 cursor = conn.cursor()
 cursor.execute(sql, (bookid, ))

 conn.commit()
 cursor.close()
 conn.close()

if __name__ == '__main__':
 db = DBHelper()
 #book = Book("秦腔", "賈凹平", "講的是大西北夏家和白家的事情，由引生口述。")
 #db.insertBook(book)
 list = db.getAllBook()
 for item in list:
 print(item)

'''一個(gè)圖書(shū)管理系統(tǒng)，能夠?qū)崿F(xiàn)增加書(shū)籍，刪除書(shū)籍，
修改書(shū)籍和查看圖書(shū)詳情，基于mysql數(shù)據(jù)庫(kù)和
wxPython'''

import wx
from book import *
from dbhelper import *

__metaclass__ = type

class AddFrame(wx.Frame):
 '''添加書(shū)籍彈出的小窗口'''

 def __init__(self, parent, title):
 '''初始化該小窗口的布局'''

 self.mainframe = parent
 #生成一個(gè)300*300的框
 wx.Frame.__init__(self, parent, title = title, size = (400, 250))

 self.panel = wx.Panel(self, pos = (0, 0), size = (400, 250))
 self.panel.SetBackgroundColour("#FFFFFF")  #背景為白色

 #三個(gè)編輯框，分別用來(lái)編輯書(shū)名，作者，書(shū)籍相關(guān)信息
 bookName_tip = wx.StaticText(self.panel, label = "書(shū)名:", pos = (5, 8), size = (35, 25))
 bookName_tip.SetBackgroundColour("#FFFFFF")
 bookName_text = wx.TextCtrl(self.panel, pos = (40, 5), size = (340, 25))
 self.name = bookName_text

 author_tip = wx.StaticText(self.panel, label = "作者:", pos = (5, 38), size = (35, 25))
 author_tip.SetBackgroundColour("#FFFFFF")
 author_text = wx.TextCtrl(self.panel, pos = (40, 35), size = (340, 25))
 self.author = author_text

 content_tip = wx.StaticText(self.panel, label = "內(nèi)容:", pos = (5, 68), size = (340, 25))
 content_tip.SetBackgroundColour("#FFFFFF")
 content_text = wx.TextCtrl(self.panel, pos = (40, 65), size = (340, 100), style = wx.TE_MULTILINE)
 self.content = content_text

 save_button = wx.Button(self.panel, label = "保存書(shū)籍", pos = (160, 170))
 self.Bind(wx.EVT_BUTTON, self.saveBook, save_button)

 #需要用到的數(shù)據(jù)庫(kù)接口
 self.dbhelper = DBHelper()


 def saveBook(self, evt):
 '''第一步：獲取text中文本；第二步，連接數(shù)據(jù)庫(kù)；第三步插入并獲得主鍵；第四步添加到ListCtrl中'''
 bookName = self.name.GetValue()
 author = self.author.GetValue()
 content = self.content.GetValue()

 print("書(shū)名:"+bookName)
 if bookName == "" or author == "" or content == "":
 print("進(jìn)來(lái)了")
 warn = wx.MessageDialog(self, message = "所有信息不能為空?。?！", caption = "錯(cuò)誤警告", style = wx.YES_DEFAULT | wx.ICON_ERROR)
 warn.ShowModal()    #提示錯(cuò)誤
 warn.Destroy()
 return
 else:
 print("開(kāi)始插入到數(shù)據(jù)庫(kù)中")
 book = Book(bookName, author, content)
 book_id = self.dbhelper.insertBook(book)
 self.mainframe.addToList(book_id, book)

 self.Destroy()


class UpdateFrame(wx.Frame):
 def __init__(self, parent, title, select_id):
 '''初始化更新圖書(shū)信息界面總布局'''

 wx.Frame(parent, title = title, size = (400, 250))

 #用來(lái)調(diào)用父frame,便于更新
 self.mainframe = parent
 #生成一個(gè)300*300的框
 wx.Frame.__init__(self, parent, title = title, size = (400, 250))

 self.panel = wx.Panel(self, pos = (0, 0), size = (400, 250))
 self.panel.SetBackgroundColour("#FFFFFF")  #背景為白色

 #三個(gè)編輯框，分別用來(lái)編輯書(shū)名，作者，書(shū)籍相關(guān)信息
 bookName_tip = wx.StaticText(self.panel, label = "書(shū)名:", pos = (5, 8), size = (35, 25))
 bookName_tip.SetBackgroundColour("#FFFFFF")
 bookName_text = wx.TextCtrl(self.panel, pos = (40, 5), size = (340, 25))
 self.name = bookName_text

 author_tip = wx.StaticText(self.panel, label = "作者:", pos = (5, 38), size = (35, 25))
 author_tip.SetBackgroundColour("#FFFFFF")
 author_text = wx.TextCtrl(self.panel, pos = (40, 35), size = (340, 25))
 self.author = author_text

 content_tip = wx.StaticText(self.panel, label = "內(nèi)容:", pos = (5, 68), size = (340, 25))
 content_tip.SetBackgroundColour("#FFFFFF")
 content_text = wx.TextCtrl(self.panel, pos = (40, 65), size = (340, 100), style = wx.TE_MULTILINE)
 self.content = content_text

 save_button = wx.Button(self.panel, label = "保存修改", pos = (160, 170))
 self.Bind(wx.EVT_BUTTON, self.saveUpdate, save_button)

 #選中的id和bookid
 self.select_id = select_id
 self.bookid = self.mainframe.list.GetItem(select_id, 0).Text #獲取第select_id行的第0列的值
 print(select_id, self.bookid)
 #需要用到的數(shù)據(jù)庫(kù)接口
 self.dbhelper = DBHelper()
 self.showAllText()  #展現(xiàn)所有的text原來(lái)取值


 def showAllText(self):
 '''顯示概述本原始信息'''
 data = self.dbhelper.getBookById(self.bookid)  #通過(guò)id獲取書(shū)本信息

 self.name.SetValue(data[0])   #設(shè)置值
 self.author.SetValue(data[1])
 self.content.SetValue(data[2])

 def saveUpdate(self, evt):
 '''保存修改后的值'''
 bookName = self.name.GetValue()   #獲得修改后的值
 author = self.author.GetValue()
 content = self.content.GetValue()

 print("書(shū)名:"+bookName)
 if bookName == "" or author == "" or content == "":
 print("進(jìn)來(lái)了")
 warn = wx.MessageDialog(self, message = "所有信息不能為空?。?！", caption = "錯(cuò)誤警告", style = wx.YES_DEFAULT | wx.ICON_ERROR)
 warn.ShowModal()    #提示錯(cuò)誤
 warn.Destroy()
 return
 else:
 print("開(kāi)始將修改后的數(shù)據(jù)保存到數(shù)據(jù)庫(kù)中")
 book = Book(bookName, author, content)  #將數(shù)據(jù)封裝到book對(duì)象中
 self.dbhelper.saveUpdate(self.bookid, book)
 self.mainframe.list.SetItem(self.select_id, 1, bookName)

 self.Destroy()    #修改完后自動(dòng)銷(xiāo)毀


class ShowFrame(wx.Frame):
 '''用來(lái)顯示書(shū)籍的信息'''

 def __init__(self, parent, title, select_id):
 '''初始化該小窗口的布局'''

 #便于調(diào)用父窗口
 self.mainframe = parent

 #生成一個(gè)300*300的框
 wx.Frame.__init__(self, parent, title = title, size = (400, 250))

 self.panel = wx.Panel(self, pos = (0, 0), size = (400, 250))
 self.panel.SetBackgroundColour("#FFFFFF")  #背景為白色

 #三個(gè)編輯框，分別用來(lái)編輯書(shū)名，作者，書(shū)籍相關(guān)信息
 bookName_tip = wx.StaticText(self.panel, label = "書(shū)名:", pos = (5, 8), size = (35, 25))
 bookName_tip.SetBackgroundColour("#FFFFFF")
 bookName_text = wx.TextCtrl(self.panel, pos = (40, 5), size = (340, 25))
 bookName_text.SetEditable(False)
 self.name = bookName_text

 author_tip = wx.StaticText(self.panel, label = "作者:", pos = (5, 38), size = (35, 25))
 author_tip.SetBackgroundColour("#FFFFFF")
 author_text = wx.TextCtrl(self.panel, pos = (40, 35), size = (340, 25))
 author_text.SetEditable(False)
 self.author = author_text

 content_tip = wx.StaticText(self.panel, label = "內(nèi)容:", pos = (5, 68), size = (340, 25))
 content_tip.SetBackgroundColour("#FFFFFF")
 content_text = wx.TextCtrl(self.panel, pos = (40, 65), size = (340, 100), style = wx.TE_MULTILINE)
 content_text.SetEditable(False)
 self.content = content_text

 #選中的id和bookid
 self.select_id = select_id
 self.bookid = self.mainframe.list.GetItem(select_id, 0).Text #獲取第select_id行的第0列的值

 #需要用到的數(shù)據(jù)庫(kù)接口
 self.dbhelper = DBHelper()
 self.showAllText()  #展現(xiàn)所有的text原來(lái)取值

 def showAllText(self):
 '''顯示概述本原始信息'''
 data = self.dbhelper.getBookById(self.bookid)  #通過(guò)id獲取書(shū)本信息

 self.name.SetValue(data[0])   #設(shè)置值
 self.author.SetValue(data[1])
 self.content.SetValue(data[2])



class LibraryFrame(wx.Frame):
 def __init__(self, parent, title):
 '''初始化系統(tǒng)總體布局，包括各種控件'''

 #生成一個(gè)寬為400，高為400的frame框
 wx.Frame.__init__(self, parent, title=title, size=(400, 400)) 

 #定一個(gè)網(wǎng)格布局,兩行一列
 self.main_layout = wx.BoxSizer(wx.VERTICAL)


 #生成一個(gè)列表
 self.list = wx.ListCtrl(self, -1, size = (400,300), style = wx.LC_REPORT | wx.LC_HRULES | wx.LC_VRULES) #| wx.LC_SINGLE_SEL
 #列表有散列，分別是書(shū)本ID,書(shū)名，添加日期
 self.list.InsertColumn(0, "ID")
 self.list.InsertColumn(1, "書(shū)名")
 self.list.InsertColumn(2, "添加日期")
 #設(shè)置各列的寬度
 self.list.SetColumnWidth(0, 60)   #設(shè)置每一列的寬度
 self.list.SetColumnWidth(1, 230)
 self.list.SetColumnWidth(2, 92)

 #添加一組按鈕，實(shí)現(xiàn)增刪改查,用一個(gè)panel來(lái)管理該組按鈕的布局
 self.panel = wx.Panel(self, pos = (0, 300), size = (400, 100))

 #定義一組按鈕
 add_button = wx.Button(self.panel, label = "添加", pos = (10, 15), size = (60, 30)) #, size = (75, 30)
 del_button = wx.Button(self.panel, label = "刪除", pos = (110, 15), size = (60, 30)) #, size = (75, 30)
 update_button = wx.Button(self.panel, label = "修改", pos = (210, 15), size = (60, 30)) #, size = (75, 30)
 query_button = wx.Button(self.panel, label = "查看", pos = (310, 15), size = (60, 30)) #, size = (75, 30)
 #w為按鈕綁定相應(yīng)事件函數(shù)，第一個(gè)參數(shù)為默認(rèn)參數(shù)，指明為按鈕類(lèi)事件，第二個(gè)為事件函數(shù)名，第三個(gè)為按鈕名
 self.Bind(wx.EVT_BUTTON, self.addBook, add_button)
 self.Bind(wx.EVT_BUTTON, self.delBook, del_button)
 self.Bind(wx.EVT_BUTTON, self.updateBook, update_button)
 self.Bind(wx.EVT_BUTTON, self.queryBook, query_button)

 #將列表和panel添加到主面板
 self.main_layout.Add(self.list, 3)
 self.main_layout.Add(self.panel, 1)

 self.SetSizer(self.main_layout)

 #添加數(shù)據(jù)庫(kù)操作對(duì)象
 self.dbhelper = DBHelper()
 datas = self.dbhelper.getAllBook()

 for data in datas:
 index = self.list.InsertItem(self.list.GetItemCount(), str(data[0]))
 self.list.SetItem(index, 1, data[1])
 self.list.SetItem(index, 2, data[2])


 def addBook(self, evt):
 '''添加書(shū)籍按鈕，彈出添加書(shū)籍框'''
 add_f = AddFrame(self, "添加書(shū)籍窗口")
 add_f.Show(True)


 def delBook(self, evt):
 '''刪除書(shū)籍按鈕，先選中,然后刪除'''
 selectId = self.list.GetFirstSelected()
 if selectId == -1:
 warn = wx.MessageDialog(self, message = "未選中任何條目?。?！", caption = "錯(cuò)誤警告", style = wx.YES_DEFAULT | wx.ICON_ERROR)
 warn.ShowModal()    #提示錯(cuò)誤
 warn.Destroy()
 return
 else:
 bookid = self.list.GetItem(selectId, 0).Text   #得到書(shū)本id
 self.list.DeleteItem(selectId)   #先在listctrl中刪除選中行
 self.dbhelper.deleteBook(bookid)



 def updateBook(self, evt):
 '''修改按鈕響應(yīng)事件，點(diǎn)擊修改按鈕，彈出修改框'''
 selectId = self.list.GetFirstSelected()
 if selectId == -1:
 warn = wx.MessageDialog(self, message = "未選中任何條目?。?！", caption = "錯(cuò)誤警告", style = wx.YES_DEFAULT | wx.ICON_ERROR)
 warn.ShowModal()    #提示錯(cuò)誤
 warn.Destroy()
 return
 else:
 update_f = UpdateFrame(self, "修改書(shū)籍窗口", selectId)
 update_f.Show(True)

 def queryBook(self, evt):
 '''查看按鈕響應(yīng)事件'''
 selectId = self.list.GetFirstSelected()
 if selectId == -1:
 warn = wx.MessageDialog(self, message = "未選中任何條目！??！", caption = "錯(cuò)誤警告", style = wx.YES_DEFAULT | wx.ICON_ERROR)
 warn.ShowModal()    #提示錯(cuò)誤
 warn.Destroy()
 return
 else:
 show_f = ShowFrame(self, "修改書(shū)籍窗口", selectId)
 show_f.Show(True)

 def addToList(self, id, book):
 index = self.list.InsertItem(self.list.GetItemCount(), str(id))
 self.list.SetItem(index, 1, book.getBookName())
 self.list.SetItem(index, 2, str(book.getAddDate()))



AppBaseClass = wx.App

class LibraryApp(AppBaseClass):
 def OnInit(self):
 frame = LibraryFrame(None, "library-system")
 frame.Show()

 return True


#類(lèi)似于c中的main函數(shù)，但被其他模塊導(dǎo)入時(shí)，__name__值不是"__main__"
if __name__ == "__main__":
 app = LibraryApp()
 app.MainLoop()