图书数据库以及操作界面

期末空闲了，就花了点时间研究下自己感兴趣的东西：wxpython和python自带的轻量级数据库sqlite3
这次分享的包括数据库创建的creatSQL.py文件、插入数据的insert.py文件、删除数据的delete.py文件以及查询数据的find.py文件

creatSQL.py

#coding:gbk
import wx,sqlite3

def creat():
    conn=sqlite3.connect("library.db")  #创建数据库
    conn.cursor()
    try:
        conn.execute('''create table library  #创建table
                     (id integer primary key,
                     name text,
                     pub text,
                     price text,
                     type text)''')
        dial = wx.MessageDialog(None,'成功创建数据库!','结果',wx.YES_NO)
        dial.ShowModal()
    except:
        dial = wx.MessageDialog(None,'数据库已经创建过了!','结果',wx.YES_NO)
        dial.ShowModal()

if __name__ == "__main__":
    app = wx.App()
    creat()
    app.MainLoop()

insert.py

    

#coding:gbk
import wx,sqlite3

class insert(wx.Frame):
    def load(self,event):
        conn=sqlite3.connect("library.db")  #链接数据库
        conn.cursor()
        n1=self.t1.GetValue()
        n2=self.t2.GetValue()
        n3=self.t3.GetValue()
        n4=self.t4.GetValue()
        s=[n1,n2,n3,n4]
        conn.execute("insert into library(name,pub,price,type) values(?,?,?,?)",s)  #插入数据
        conn.commit()
        dial = wx.MessageDialog(None,'成功插入数据!','结果',wx.YES_NO)
        dial.ShowModal()

    def __init__(self):
        wx.Frame.__init__(self, None, -1, title='插入数据',
                pos=(400,200),size=(200, 300))
        panel = wx.Panel(self)

        st1 = wx.StaticText(panel, -1, "请输入书名:",(20,10))
        st2 = wx.StaticText(panel, -1, "请输入出版社:",(20, 60))
        st3 = wx.StaticText(panel, -1, "请输入价格:",(20, 110))
        st4 = wx.StaticText(panel, -1, "请选择分类:",(20, 160))

        self.t1 = wx.TextCtrl(panel,pos=(20,30),size=(100,20))
        self.t2 = wx.TextCtrl(panel,pos=(20,80),size=(100,20))
        self.t3 = wx.TextCtrl(panel,pos=(20,130),size=(100,20))
        self.t4 = wx.ComboBox(panel,value='未选择',
                choices=['文学','理工科','计算机','外语','其他'],pos=(20,180),size=(100,20))

        btn=wx.Button(parent=panel,label="插入",pos=(20,210),size=(100,30))
        btn.Bind(wx.EVT_BUTTON,self.load)

if __name__ == '__main__':
    app = wx.App()
    insert().Show()
    app.MainLoop()

delete.py

#coding:gbk
import wx,sqlite3

class delete(wx.Frame):
    def load(self,event):
        conn=sqlite3.connect("library.db")  #链接数据库
        conn.cursor()
        n1=self.t1.GetValue()
        s=[n1]
        conn.execute('DELETE FROM library where name=?',s)  #删除数据
        conn.commit()
        dial = wx.MessageDialog(None,'成功删除数据!','结果',wx.YES_NO)
        dial.ShowModal()

    def __init__(self):
        wx.Frame.__init__(self, None, -1, title='删除数据',
                pos=(400,200),size=(210, 180))
        panel = wx.Panel(self)

        st1 = wx.StaticText(panel, -1, "请输入要删除的书名:",(20,20))

        self.t1 = wx.TextCtrl(panel,pos=(20,40),size=(150,20))

        btn=wx.Button(parent=panel,label="删除",pos=(20,70),size=(100,30))
        btn.Bind(wx.EVT_BUTTON,self.load)

if __name__ == '__main__':
    app = wx.App()
    delete().Show()
    app.MainLoop()

find.py

#coding:gbk
import wx,sqlite3

class find(wx.Frame):
    def load(self,event):
        conn=sqlite3.connect("library.db")
        conn.cursor()
        rel = conn.execute('SELECT * FROM library')
        t = 0
        h = 40
        if self.x > 0:
            for i in range(self.x):
                h1 = 60 + 20*i
                wx.StaticText(self.panel, -1,'                           ',(20,h1))
                wx.StaticText(self.panel, -1,'                           ',(40,h1))
                wx.StaticText(self.panel, -1,'                           ',(120,h1))
                wx.StaticText(self.panel, -1,'                           ',(220,h1))
                wx.StaticText(self.panel, -1,'                           ',(260,h1))

        if self.s.GetValue().encode('gbk') == '按书名':
            for i in rel.fetchall():
                if i[1] == self.t.GetValue():
                    t = t + 1
                    h = h + 20
                    id = '%d' %i[0]
                    name = i[1].encode('gbk')
                    pub = i[2].encode('gbk')
                    price = i[3].encode('gbk')
                    type = i[4].encode('gbk')
                    wx.StaticText(self.panel, -1,id,(20,h))
                    wx.StaticText(self.panel, -1,name,(40,h))
                    wx.StaticText(self.panel, -1,pub,(120,h))
                    wx.StaticText(self.panel, -1,price,(220,h))
                    wx.StaticText(self.panel, -1,type,(260,h))
            dial = wx.MessageDialog(None,'共有%d个结果'%t,'结果',wx.YES_NO)
            dial.ShowModal()
            self.x = t

        if self.s.GetValue().encode('gbk') == '按出版社':
            for i in rel.fetchall():
                if i[2] == self.t.GetValue():
                    t = t + 1
                    h = h + 20
                    id = '%d' %i[0]
                    name = i[1].encode('gbk')
                    pub = i[2].encode('gbk')
                    price = i[3].encode('gbk')
                    type = i[4].encode('gbk')
                    wx.StaticText(self.panel, -1,id,(20,h))
                    wx.StaticText(self.panel, -1,name,(40,h))
                    wx.StaticText(self.panel, -1,pub,(120,h))
                    wx.StaticText(self.panel, -1,price,(220,h))
                    wx.StaticText(self.panel, -1,type,(260,h))
            dial = wx.MessageDialog(None,'共有%d个结果'%t,'结果',wx.YES_NO)
            dial.ShowModal()
            self.x = t

        if self.s.GetValue().encode('gbk') == '按价格':
            for i in rel.fetchall():
                if i[3] == self.t.GetValue():
                    t = t + 1
                    h = h + 20
                    id = '%d' %i[0]
                    name = i[1].encode('gbk')
                    pub = i[2].encode('gbk')
                    price = i[3].encode('gbk')
                    type = i[4].encode('gbk')
                    wx.StaticText(self.panel, -1,id,(20,h))
                    wx.StaticText(self.panel, -1,name,(40,h))
                    wx.StaticText(self.panel, -1,pub,(120,h))
                    wx.StaticText(self.panel, -1,price,(220,h))
                    wx.StaticText(self.panel, -1,type,(260,h))
            dial = wx.MessageDialog(None,'共有%d个结果'%t,'结果',wx.YES_NO)
            dial.ShowModal()
            self.x = t

        if self.s.GetValue().encode('gbk') == '按分类':
            for i in rel.fetchall():
                if i[4] == self.t.GetValue():
                    t = t + 1
                    h = h + 20
                    id = '%d' %i[0]
                    name = i[1].encode('gbk')
                    pub = i[2].encode('gbk')
                    price = i[3].encode('gbk')
                    type = i[4].encode('gbk')
                    wx.StaticText(self.panel, -1,id,(20,h))
                    wx.StaticText(self.panel, -1,name,(40,h))
                    wx.StaticText(self.panel, -1,pub,(120,h))
                    wx.StaticText(self.panel, -1,price,(220,h))
                    wx.StaticText(self.panel, -1,type,(260,h))
            dial = wx.MessageDialog(None,'共有%d个结果'%t,'结果',wx.YES_NO)
            dial.ShowModal()
            self.x = t

        if self.s.GetValue().encode('gbk') == '查询全部':
            for i in rel.fetchall():
                t = t + 1
                h = h + 20
                id = '%d' %i[0]
                name = i[1].encode('gbk')
                pub = i[2].encode('gbk')
                price = i[3].encode('gbk')
                type = i[4].encode('gbk')
                wx.StaticText(self.panel, -1,id,(20,h))
                wx.StaticText(self.panel, -1,name,(40,h))
                wx.StaticText(self.panel, -1,pub,(120,h))
                wx.StaticText(self.panel, -1,price,(220,h))
                wx.StaticText(self.panel, -1,type,(260,h))
            dial = wx.MessageDialog(None,'共有%d个结果'%t,'结果',wx.YES_NO)
            dial.ShowModal()
            self.x = t

        if self.s.GetValue().encode('gbk') == '选择查询项':
            dial = wx.MessageDialog(None,'请选择查询项','结果',wx.YES_NO)
            dial.ShowModal()

    def __init__(self):
        wx.Frame.__init__(self, None, -1, title='查询数据',
                pos=(390,200),size=(340, 200))
        self.panel = wx.Panel(self)

        self.s = wx.ComboBox(self.panel,value='选择查询项',
                choices=['按书名','按出版社','按价格','按分类','查询全部'],pos=(20,10),size=(100,20))
        self.t = wx.TextCtrl(self.panel,pos=(130,10),size=(100,20))
        self.x = 0

        wx.StaticText(self.panel, -1, "id",(20,40))
        wx.StaticText(self.panel, -1, "书名",(40,40))
        wx.StaticText(self.panel, -1, "出版社",(120,40))
        wx.StaticText(self.panel, -1, "价格",(220,40))
        wx.StaticText(self.panel, -1, "类别",(260,40))

        btn=wx.Button(self.panel,label="查询",pos=(240,10),size=(50,20))
        btn.Bind(wx.EVT_BUTTON,self.load)

if __name__ == '__main__':
    app = wx.App()
    find().Show()
    app.MainLoop()

学python没多久，代码写的不好的地方大家多多包涵！