python图书管理系统

#!/usr/bin/python
# -*- coding: utf-8 -*-

import pygtk
pygtk.require('2.0')
import gtk
import sys
import gobject
import sqlite3
import time
import datetime

class LibManager:
 bookColumName = (u"书本号",u"书名",u"作者",u"出版社",u"价格",u"购入日期",u"分类",u"简介",u"馆藏数")

 readerColumName = (u"读者号",u"读者姓名",u"读者身份",u"备注")

 def __init__(self):
 self.dbconn = sqlite3.connect('sqlitefile.db')
 self.cursor = self.dbconn.cursor()
 
 self.nowDate = time.strftime("%Y-%m-%d").decode("utf-8")
 inputBookInitData = (u"书名",0.0,self.nowDate,0)

 try:
 builder = gtk.Builder()
 builder.add_from_file("ui.glade")
 except BaseException,e:
 self.errorMessage("Fail to load UI file.")
 print e
 sys.exit(1)

 builder.connect_signals(self)
 self.window = builder.get_object("mainWindow")

 self.borrowView = builder.get_object("borrowView")
 self.borrowViewModel = builder.get_object("borrowViewModel")
 self.borrowViewInit()

 self.returnView = builder.get_object("returnView")
 self.returnViewModel = builder.get_object("returnViewModel")
 self.returnEntryReaderID = builder.get_object("returnReaderID")
 self.returnEntryBookID = builder.get_object("returnBookID")
 self.returnViewInit()

 self.queryView = builder.get_object("queryView")
 self.queryViewReaderModel = builder.get_object("queryViewReaderModel")
 self.queryViewBookModel = builder.get_object("queryViewBookModel")


 self.inputView = builder.get_object("inputView")
 self.inputViewBookModel = builder.get_object("inputViewBookModel")
 self.inputViewReaderModel = builder.get_object("inputViewReaderModel")
 self.inputViewModel = None


 def borrowViewInit(self):
 columName = (u"读者号",u"书号",u"借出日期")
 columEditAttr = (True,True,False)
 for columnNum in range(len(columName)):
 renderer = gtk.CellRendererText()
 renderer.set_data("column",columnNum)
 renderer.set_property("editable",columEditAttr[columnNum])
 if columEditAttr[columnNum]:
 renderer.connect("edited",self.on_borrow_cell_edited)
 column = gtk.TreeViewColumn(columName[columnNum],renderer,text=columnNum)
 column.set_resizable(True)
 self.borrowView.append_column(column)

 def returnViewInit(self):
 columName = (u"借书记录",u"书本",u"读者资料",u"借出日期")
 for columnNum in range(len(columName)):
 renderer = gtk.CellRendererText()
 column = gtk.TreeViewColumn(columName[columnNum],text=columnNum)
 column.set_resizable(True)
 self.returnView.append_column(column)

 # QueryView
 def queryViewSetColumn(self,columName):
 self.queryRecord = 0
 columns = self.queryView.get_columns()
 for c in columns:
 self.queryView.remove_column(c)
 
 self.queryViewModel.clear()
 self.queryView.set_model(self.queryViewModel)
 for columnNum in range(len(columName)):
 renderer = gtk.CellRendererText()
 renderer.set_property("editable",True)
 column = gtk.TreeViewColumn(columName[columnNum],text=columnNum)
 column.set_resizable(True)
 self.queryView.append_column(column)


 def queryReader(self):
 self.queryViewModel = self.queryViewReaderModel
 self.queryViewSetColumn(self.readerColumName)
 self.cursor.execute("SELECT COUNT(*) FROM reader")
 self.maxRecords = self.cursor.fetchone()[0]
 print self.maxRecords

 def queryBook(self):
 self.queryViewModel = self.queryViewBookModel
 self.queryViewSetColumn(self.bookColumName)
 self.cursor.execute("SELECT COUNT(*) FROM books")
 self.maxRecords = self.cursor.fetchone()[0]
 print self.maxRecords

 #inputView
 def inputViewSetColumn(self,columName):
 columns = self.inputView.get_columns()
 for c in columns:
 self.inputView.remove_column(c)
 
 self.inputViewModel.clear()
 self.inputView.set_model(self.inputViewModel)
 for columnNum in range(len(columName)):
 renderer = gtk.CellRendererText()
 renderer.set_property("editable",True)
 renderer.set_data("column",columnNum)
 renderer.connect("edited",self.on_input_cell_edited)

 column = gtk.TreeViewColumn(columName[columnNum],text=columnNum)
 column.set_resizable(True)
 self.inputView.append_column(column)

 def inputReader(self):
 self.inputViewModel = self.inputViewReaderModel
 self.inputViewSetColumn(self.readerColumName[1:])

 def inputBook(self):
 self.inputViewModel = self.inputViewBookModel
 self.inputViewSetColumn(self.bookColumName[1:])

 #Callback Functions
 def on_mainWindow_delete_event(self,widget,data=None):
 print "destroy signal occurred"
 gtk.main_quit()

 #BorrowView
 def on_borrowAdd_clicked(self,button,data = None):
 self.borrowViewModel.append((1001,1,self.nowDate))

 def on_borrowDel_clicked(self,data = None):
 selection = self.borrowView.get_selection()
 model,iter = selection.get_selected()
 if iter:
 model.remove(iter)

 def on_borrowClear_clicked(self,data = None):
 self.borrowViewModel.clear()

 def on_borrowSubmit_clicked(self,data = None):
 for row in self.borrowViewModel:
 self.cursor.execute("INSERT INTO borrow VALUES (NULL,?,?)",row)
 self.dbconn.commit()
 self.infoMessage(u"借书记录已经成功提交。")
 self.borrowViewModel.clear()

 def on_borrow_cell_edited(self,cell,path_string,new_text):
 model = self.borrowViewModel
 iter = model.get_iter_from_string(path_string)
 column = cell.get_data("column")
 try:
 id = int(new_text)
 except ValueError,e:
 print e
 self.errorMessage(u"请输入纯数字记录。")
 return

 if column == 0:
 self.cursor.execute("SELECT * FROM reader where ReaderID = ?",[new_text])
 if self.cursor.fetchone():
 model.set(iter,column,id)
 else:
 self.errorMessage(u"不存在该读者号: %d。" % id)

 if column == 1:
 self.cursor.execute("SELECT * FROM books where BookID = ?",id)
 else:
 self.errorMessage(u"不存在该书本号: %d。" % id)

 #QueryView
 def on_queryBooks_clicked(self,data = None):
 self.queryBook()
 self.cursor.execute("SELECT * FROM books LIMIT ? OFFSET ?",[15,0])
 for row in self.cursor:
 self.queryViewModel.append(row)

 def on_queryReaders_clicked(self,data = None):
 self.queryReader()
 self.cursor.execute("SELECT * FROM reader LIMIT ? OFFSET ?",0])
 for row in self.cursor:
 self.queryViewModel.append(row)

 def on_queryPrev_clicked(self,data = None):
 if self.queryRecord == 0:
 self.errorMessage(u"已到最前页。")
 return
 self.queryRecord -= 15
 self.queryViewModel.clear()

 if self.queryViewModel.get_n_columns() == 9:
 self.cursor.execute("SELECT * FROM books LIMIT ? OFFSET ?",self.queryRecord])

 elif self.queryViewModel.get_n_columns() == 4:
 self.cursor.execute("SELECT * FROM reader LIMIT ? OFFSET ?",self.queryRecord])

 for row in self.cursor:
 self.queryViewModel.append(row)


 def on_queryNext_clicked(self,data = None):
 if self.queryRecord + 15 > self.maxRecords:
 self.errorMessage(u"已到最后页。")
 return

 self.queryRecord += 15
 self.queryViewModel.clear()

 if self.queryViewModel.get_n_columns() == 9:
 self.cursor.execute("SELECT * FROM books LIMIT ? OFFSET ?",self.queryRecord])

 for row in self.cursor:
 self.queryViewModel.append(row)


 def on_returnQuery_clicked(self,data = None):
 readerid = self.returnEntryReaderID.get_text()
 bookid = self.returnEntryBookID.get_text()

 if len(readerid) and len(bookid):
 self.cursor.execute("SELECT borrow.BorrowID,books.BookName,reader.ReaderName,

  reader.ReaderSchool,borrow.BorrowDate FROM borrow,books,reader

  WHERE books.BookID = borrow.BookID AND 

  reader.ReaderID = borrow.ReaderID AND

  borrow.ReaderID = ? AND borrow.BookID = ?",[readerid,bookid])
 elif len(readerid):
 self.cursor.execute("SELECT borrow.BorrowID,reader

  WHERE books.BookID = borrow.BookID AND 

  reader.ReaderID = borrow.ReaderID AND

  borrow.ReaderID = ?",[readerid])
 elif len(bookid):
 self.cursor.execute("SELECT borrow.BorrowID,reader

  WHERE books.BookID = borrow.BookID AND 

  reader.ReaderID = borrow.ReaderID AND

  borrow.BookID = ?",[bookid])
 else:
 self.errorMessage(u"请输入查询条件。")
 return

 self.returnViewModel.clear()

 for row in self.cursor:
 self.returnViewModel.append(row)
 

 def on_returnCommit_clicked(self,data = None):
 selection = self.returnView.get_selection()
 model,iter = selection.get_selected()
 if iter:
 borrowDate = time.strptime(model.get_value(iter,4),"%Y-%m-%d")
 timeDelta = datetime.date.today() - datetime.date(*borrowDate[0:3])
 borrowDays = timeDelta.days

 borrowID = model.get_value(iter,0)
 self.cursor.execute("DELETE FROM borrow WHERE BorrowID = ?",[borrowID])
 self.dbconn.commit()
 readerName = model.get_value(iter,2)
 bookName = model.get_value(iter,1)
 self.infoMessage(u"读者 %s 所借书《%s》归还成功。 借出%d天" % (readerName,bookName,borrowDays))
 model.remove(iter)

 #inputView
 def on_inputReader_clicked(self,data = None):
 self.inputReader()

 def on_inputBooks_clicked(self,data = None):
 self.inputBook()

 def on_inputAdd_clicked(self,data = None):
 if not self.inputViewModel:
 self.errorMessage(u"请先选择录入类型。")
 return
 count = self.inputViewModel.get_n_columns()
 if count == 3:
 inputReaderInitData = (u"姓名",u"身份",u"备注")
 self.inputViewModel.append(inputReaderInitData)
 elif count == 8:
 inputBookInitData = (u"书名",0)
 self.inputViewModel.append(inputBookInitData)

 def on_inputDel_clicked(self,data = None):
 selection = self.inputView.get_selection()
 model,iter = selection.get_selected()
 if iter:
 model.remove(iter)

 def on_inputSubmit_clicked(self,data = None):
 count = self.inputViewModel.get_n_columns()
 for row in self.inputViewModel:
 #录入读者
 if count == 3:
 self.cursor.execute(u"INSERT INTO reader VALUES (NULL,[s.decode("utf-8") for s in row])

 #录入书目
 elif count == 8:
 dataRow = []
 for obj in row:
  if isinstance(obj,str):
  dataRow.append(obj.decode("utf-8"))
  else:
  dataRow.append(obj)
 print dataRow
 self.cursor.execute(u"INSERT INTO books VALUES (NULL,

   dataRow)
 self.dbconn.commit()
 self.infoMessage(u"记录已经成功提交。")
 self.inputViewModel.clear()

 def on_input_cell_edited(self,new_text):
 iter = self.inputViewModel.get_iter_from_string(path_string)
 column = cell.get_data("column")
 columnCount = self.inputViewModel.get_n_columns()
 if columnCount == 3:
 self.inputViewModel.set(iter,new_text)
 elif columnCount == 8:
 if self.inputViewModel.get_column_type(column) == gobject.TYPE_FLOAT:
 try:
  self.inputViewModel.set(iter,float(new_text))
 except ValueError,e:
  self.errorMessage(u"请输入价格实数。")
  return
 elif self.inputViewModel.get_column_type(column) == gobject.TYPE_UINT:
 try:
  self.inputViewModel.set(iter,int(new_text))
 except ValueError,e:
  self.errorMessage(u"请输入整数。")
  return
 else:
 self.inputViewModel.set(iter,new_text)

 def errorMessage(self,message):
 print message
 dialog = gtk.MessageDialog(None,gtk.DIALOG_MODAL | gtk.DIALOG_DESTROY_WITH_PARENT,gtk.MESSAGE_ERROR,gtk.BUTTONS_OK,message)
 dialog.run()
 dialog.destroy()

 def infoMessage(self,gtk.MESSAGE_INFO,message)
 dialog.run()
 dialog.destroy()

 def main(self):
 self.window.show()
 gtk.main()

if __name__ == "__main__":
 app = LibManager()
 app.main()

#!/usr/bin/python
#encoding:utf-8

import sqlite3

def LibDbInit(con):
 c = con.cursor()

 c.execute("""
 CREATE TABLE `books` (
 `BookID` INTEGER PRIMARY KEY,`BookName` TEXT,`BookAuthor` TEXT,`BookPublish` TEXT,`BookPrice` FLOAT,`BookDate` TEXT,`BookType` TEXT,`BookContent` TEXT,`BookState` INTEGER
 )
 """)

 c.execute("""
 CREATE TABLE `reader` (
 `ReaderID` INTEGER PRIMARY KEY,`ReaderName` TEXT,`ReaderSchool` TEXT,`ReaderInfo` TEXT
 )
 """)

 c.execute("""
 CREATE TABLE `borrow` (
 `BorrowID` INTEGER PRIMARY KEY,`ReaderID` INTEGER,`BookID` INTEGER,`BorrowDate` TEXT
 )
 """)

 books = ((1,u"C语言程序设计",u"谭浩强",u"教育出版社",29.90,u"2009-04-30",u"计算机",u"",5),(2,u"数据结构",(3,u"数据库原理",(4,u"汇编语言",(5,u"大学国文",u"国立",19.90,u"文学",(6,u"古代诗词",(7,u"广州地图",u"工作",u"广州出版社",9.90,u"工具书",(8,u"牛津词典",u"词典",5))

 for r in books:
 c.execute("INSERT INTO books VALUES (?,r)

 readers = ((1001,u"陈一",u"广州大学计算机学院",u"B15"),(1002,u"王二",(1003,u"刘三",u"广州大学美术学院",u"B12"),(1004,u"陈一明",u"广州大学人文学院",u"B10"),(1005,u"张三",u"B5"),(1006,u"何明",u"广州大学土木工程学院",u"B22"),(1007,u"陈中",u"广州大学数学学院",u"B24"),(1008,u"黄六",u"广州大学体育学院",u"B25"))

 for r in readers:
 c.execute("INSERT INTO reader VALUES (?,r)

 borrows = ((1,1001,u"2009-04-20"),2,1002,3,1003,5,1004,4,7,1006,8,(9,1007,(10,1008,u"2009-04-20"))

 for r in borrows:
 c.execute("INSERT INTO borrow VALUES (?,r)

 # Save (commit) the changes
 con.commit()

if __name__ == "__main__":
 LibDbInit(sqlite3.connect('sqlitefile.db'))