数据库大作业

import pymysql

db=pymysql.connect(host='localhost',user='root',password='123456',database='newst')

cursor=db.cursor()

from tkinter import *

from tkinter import ttk

root = Tk()

root.title('数据库管理程序')

root.geometry('1000x600')

tabs = ttk.Notebook()

tabs.place(relx=0.05,rely=0.05,relwidth=0.887,relheight=0.876)

tab1=Frame(tabs)

tab1.place(x=0,y=30)

tabs.add(tab1,text='录入')

label1=Label(tab1,width=18,height=2,text='请输入需要录入的表：')

label1.place(x=10,y=10)

label2=Label(tab1,width=18,height=2,text='请输入录入数据条数：')

label2.place(x=190,y=10)

def change(event): # 由于要满足多条录入，所以需要修改录入表格的条数

    num=numvar.get()

    table=var.get()

    if num.isdigit():

        if table=='s':

            stree.delete(*stree.get_children())

            for i in range(int(num)):

                stree.insert('','end',value=("","","","","",""))

            stree.update()

        elif table=='sc':

            sctree.delete(*sctree.get_children())

            for i in range(int(num)):

                sctree.insert('','end',value=("","","",""))

        else:

            ctree.delete(*ctree.get_children())

            for i in range(int(num)):

                ctree.insert('','end',value=("","","",""))

numvar=StringVar()

numsel = ttk.Combobox(tab1,textvariable=numvar,width=5)

numsel.bind('<<ComboboxSelected>>',change)

numval=("请选择数目",)

for i in range(30):

    numval+=(str(i+1),)

numsel["values"]=numval

numsel.current(0)

numsel.place(x=310,y=20)

numsel['state']='readonly'

var=StringVar()

comboxlist1=ttk.Combobox(tab1,textvariable=var,width=5)

comboxlist1["values"]=("请选择","s","sc","c")

tree=ttk.Treeview(tab1,show='headings')

stree=ttk.Treeview(tab1,show='headings',height=15)

cols = ["sclass","sno","sname","ssex","sage","sdept"]

stree["columns"]=("sclass","sno","sname","ssex","sage","sdept")

for i in range(6):

    stree.column(cols[i],width=60)

    stree.heading(cols[i],text=cols[i])

def editcell_s(event):  # 这个函数是用来满足双击表中的项就可以对录入表进行修改

    row = stree.identify_row(event.y)

    column = stree.identify_column(event.x)

    x,y,a,b= stree.bbox(row,column)

    entry=Entry(tab1)

    entry.place(x=x+20,y=y+50,width=a,height=b)

    value = stree.set(row,column)

    entry.insert(0,value)

    def apply_edit(event):

        new_value=entry.get()

        stree.set(row,column,new_value)

        entry.destroy()

    entry.bind("<Return>",apply_edit)

stree.bind("<Double-Button-1>",editcell_s)

sctree=ttk.Treeview(tab1,show='headings',height=15)

cols = ["sclass","sno","cno","grade"]

sctree["columns"]=("sclass","sno","cno","grade")

for i in range(4):

    sctree.column(cols[i],width=90)

    sctree.heading(cols[i],text=cols[i])

def editcell_sc(event):

    row = sctree.identify_row(event.y)

    column = sctree.identify_column(event.x)

    x,y,a,b= sctree.bbox(row,column)

    entry=Entry(tab1)

    entry.place(x=x+20,y=y+50,width=a,height=b)

    value = sctree.set(row,column)

    entry.insert(0,value)

    def apply_edit(event):

        new_value=entry.get()

        sctree.set(row,column,new_value)

        entry.destroy()

    entry.bind("<Return>",apply_edit)

sctree.bind("<Double-Button-1>",editcell_sc)

ctree=ttk.Treeview(tab1,show='headings',height=15)

cols = ["cno","cname","cpno","ccredit"]

ctree["columns"]=("cno","cname","cpno","ccredit")

for i in range(4):

    ctree.column(cols[i],width=90)

    ctree.heading(cols[i],text=cols[i])

def editcell_c(event):

    row = ctree.identify_row(event.y)

    column = ctree.identify_column(event.x)

    x,y,a,b= ctree.bbox(row,column)

    entry=Entry(tab1)

    entry.place(x=x+20,y=y+50,width=a,height=b)

    value = ctree.set(row,column)

    entry.insert(0,value)

    def apply_edit(event):

        new_value=entry.get()

        ctree.set(row,column,new_value)

        entry.destroy()

    entry.bind("<Return>",apply_edit)

ctree.bind("<Double-Button-1>",editcell_c)

def show(event): # 这个函数是为了显示插入后的表的数据

    table=var.get()

    numsel.current(0)

    stree.delete(*stree.get_children())

    sctree.delete(*sctree.get_children())

    ctree.delete(*ctree.get_children())

    tree.delete(*tree.get_children())

    if table=='s':

        tree.place_forget()

        cols = ["sclass","sno","sname","ssex","sage","sdept"]

        tree["columns"]=("sclass","sno","sname","ssex","sage","sdept")

        for i in range(6):

            tree.column(cols[i],width=60)

            tree.heading(cols[i],text=cols[i])

        strsql="SELECT * FROM S"

        try:

            cursor.execute(strsql)

            data=cursor.fetchall()

        except:

            db.rollback()

        for row in data:

            val = ()

            for i in range(len(row)):

                val+=(row[i],)

            tree.insert('','end',value=val)

        tree.place(x=400,y=50)

        sctree.place_forget()

        ctree.place_forget()

        stree.place(x=20,y=50)

    elif table=='sc':

        tree.place_forget()

        cols = ["sclass","sno","cno","grade"]

        tree["columns"]=("sclass","sno","cno","grade")

        for i in range(4):

            tree.column(cols[i],width=90)

            tree.heading(cols[i],text=cols[i])

        strsql="SELECT * FROM SC"

        try:

            cursor.execute(strsql)

            data=cursor.fetchall()

        except:

            db.rollback()

        for row in data:

            val = ()

            for i in range(len(row)):

                val+=(row[i],)

            tree.insert('','end',value=val)

        tree.place(x=400,y=50)

        stree.place_forget()

        ctree.place_forget()

        sctree.place(x=20,y=50)

    else:

        tree.place_forget()

        cols = ["cno","cname","cpno","ccredit"]

        tree["columns"]=("cno","cname","cpno","ccredit")

        for i in range(4):

            tree.column(cols[i],width=90)

            tree.heading(cols[i],text=cols[i])

        strsql="SELECT * FROM C"

        try:

            cursor.execute(strsql)

            data=cursor.fetchall()

        except:

            db.rollback()

        for row in data:

            val = ()

            for i in range(len(row)):

                val+=(row[i],)

            tree.insert('','end',value=val)

        tree.place(x=400,y=50)

        stree.place_forget()

        sctree.place_forget()

        ctree.place(x=20,y=50)

def insertsql():  # 点击插入按钮后，进行拼接录入 SQL 语句，并录入数据库

    inssql="INSERT INTO "

    table=var.get()

    if table=='s':

        inssql+=table

        inssql+="(sclass,sno,sname,ssex,sage,sdept) VALUES"

        for i in stree.get_children():

            val = stree.item(i)['values']

            value = '('

            for j in range(len(val)):

                if j != 0:

                    value+=','

                if str(val[j]).isdigit():

                    value+=str(val[j])

                else:

                    value=value+'\''+str(val[j])+'\''

            value+='),'

            inssql+=value

    elif table=='sc':

        inssql+=table

        inssql+="(sclass,sno,cno,grade) VALUES"

        for i in sctree.get_children():

            val = sctree.item(i)['values']

            value = '('

            for j in range(len(val)):

                if j != 0:

                    value+=','

                if str(val[j]).isdigit():

                    value+=str(val[j])

                else:

                    value=value+'\''+str(val[j])+'\''

            value+='),'

            inssql+=value

    else:

        inssql+=table

        inssql+="(cno,cname,cpno,ccredit) VALUES"

        for i in ctree.get_children():

            val = ctree.item(i)['values']

            value = '('

            for j in range(len(val)):

                if j != 0:

                    value+=','

                if val[j]=="":

                    value+="NULL"

                    continue

                if str(val[j]).isdigit():

                    value+=str(val[j])

                else:

                    value=value+'\''+str(val[j])+'\''

            value+='),'

            inssql+=value

    inssql=inssql[:-1]

    inssql+=';'

    print(inssql)

    try:

        cursor.execute(inssql)

        db.commit()

    except:

        db.rollback()

    tree.delete(*tree.get_children())

    tree.place_forget()

    if table=='s':

        cols = ["sclass","sno","sname","ssex","sage","sdept"]

        tree["columns"]=("sclass","sno","sname","ssex","sage","sdept")

        for i in range(6):

            tree.column(cols[i],width=60)

            tree.heading(cols[i],text=cols[i])

        strsql="SELECT * FROM S"

        try:

            cursor.execute(strsql)

            data=cursor.fetchall()

        except:

            db.rollback()

        for row in data:

            val = ()

            for i in range(len(row)):

                val+=(row[i],)

            tree.insert('','end',value=val)

        tree.place(x=400,y=50)

    elif table=='sc':

        cols = ["sclass","sno","cno","grade"]

        tree["columns"]=("sclass","sno","cno","grade")

        for i in range(4):

            tree.column(cols[i],width=90)

            tree.heading(cols[i],text=cols[i])

        strsql="SELECT * FROM SC"

        try:

            cursor.execute(strsql)

            data=cursor.fetchall()

        except:

            db.rollback()

        for row in data:

            val = ()

            for i in range(len(row)):

                val+=(row[i],)

            tree.insert('','end',value=val)

        tree.place(x=400,y=50)

    else:

        cols = ["cno","cname","cpno","ccredit"]

        tree["columns"]=("cno","cname","cpno","ccredit")

        for i in range(4):

            tree.column(cols[i],width=90)

            tree.heading(cols[i],text=cols[i])

        strsql="SELECT * FROM C"

        try:

            cursor.execute(strsql)

            data=cursor.fetchall()

        except:

            db.rollback()

        for row in data:

            val = ()

            for i in range(len(row)):

                val+=(row[i],)

            tree.insert('','end',value=val)

        tree.place(x=400,y=50)

comboxlist1.bind('<<ComboboxSelected>>',show)

comboxlist1.current(0)

comboxlist1['state']='readonly'

comboxlist1.place(x=130,y=20)

btn1 = Button(tab1,text="插入",command=insertsql)

btn1.place(x=400,y=450)

def itchange(event):

    table = tabVar.get()

    if table=='s':

        ilist["values"]=("请选择","sclass","sno","sname","ssex","sage","sdept")

        ilist.current(0)

        cols=["sclass","sno","sname","ssex","sage","sdept"]

        oritree.place_forget()

        oritree["columns"]=("sclass","sno","sname","ssex","sage","sdept")

        for i in range(6):

            oritree.column(cols[i],width=120)

            oritree.heading(cols[i],text=cols[i])

        strsql="SELECT * FROM S"

        try:

            cursor.execute(strsql)

            data=cursor.fetchall()

        except:

            db.rollback()

        for row in data:

            val = ()

            for i in range(len(row)):

                val+=(row[i],)

            oritree.insert('','end',value=val)

        oritree.place(x=20,y=50)

    elif table=='sc':

        ilist["values"]=("请选择","sclass","sno","cno","grade")

        ilist.current(0)

        cols=["sclass","sno","cno","grade"]

        oritree.place_forget()

        oritree["columns"]=("sclass","sno","cno","grade")

        for i in range(4):

            oritree.column(cols[i],width=180)

            oritree.heading(cols[i],text=cols[i])

        strsql="SELECT * FROM SC"

        try:

            cursor.execute(strsql)

            data=cursor.fetchall()

        except:

            db.rollback()

        for row in data:

            val = ()

            for i in range(len(row)):

                val+=(row[i],)

            oritree.insert('','end',value=val)

        oritree.place(x=20,y=50)

    else:

        ilist["values"]=("请选择","cno","cname","cpno","ccredit")

        ilist.current(0)

        cols=["cno","cname","cpno","ccredit"]

        oritree.place_forget()

        oritree["columns"]=("cno","cname","cpno","ccredit")

        for i in range(4):

            oritree.column(cols[i],width=180)

            oritree.heading(cols[i],text=cols[i])

        strsql="SELECT * FROM C"

        try:

            cursor.execute(strsql)

            data=cursor.fetchall()

        except:

            db.rollback()

        for row in data:

            val = ()

            for i in range(len(row)):

                val+=(row[i],)

            oritree.insert('','end',value=val)

        oritree.place(x=20,y=50)

def sqlchange(): # 拼接字符串

    table=tabVar.get()

    newval=newentry.get()

    it=itVar.get()

    val=oritree.item(oritree.focus())["values"]

    strsql="UPDATE "+table

    strsql+=" SET "

    strsql+=it

    strsql+="="

    if newval.isdigit():

        strsql+=newval

    else:

        strsql+='\''

        strsql+=newval

        strsql+='\''

    if table=='s':

        strsql+=" where sclass="

        strsql+=str(val[0])

        strsql+=" and sno="

        strsql+=str(val[1])

        strsql+=';'

    elif table=='sc':

        strsql+=" where sclass="

        strsql+=str(val[0])

        strsql+=" and sno="

        strsql+=str(val[1])

        strsql+=';'

    else:

        strsql+=" where cno="

        strsql+=str(val[0])

        strsql+=';'

    print(strsql)

    try:

        cursor.execute(strsql)

        db.commit()

    except:

        db.rollback()

    oritree.set(oritree.focus(),column=it,value=newval)

tab2=Frame(tabs)

tab2.place(x=100,y=30)

tabs.add(tab2,text='修改')

label3=Label(tab2,width=18,height=2,text='请输入需要修改的表：')

label3.place(x=10,y=10)

label4=Label(tab2,width=18,height=2,text='请输入需要修改的数据：')

label4.place(x=190,y=10)

label5=Label(tab2,width=19,height=2,text='请输入修改后的数据:')

label5.place(x=370,y=10)

tabVar = StringVar()

tlist=ttk.Combobox(tab2,textvariable=tabVar,width=5)

tlist["values"]=("请选择","s","sc","c")

tlist.current(0)

tlist['state']='readonly'

tlist.bind('<<ComboboxSelected>>',itchange)

tlist.place(x=130,y=20)

itVar = StringVar()

ilist=ttk.Combobox(tab2,textvariable=itVar,width=5)

ilist['state']='readonly'

ilist.place(x=320,y=20)

newVar = StringVar()

newentry=Entry(tab2,textvariable=newVar)

newentry.place(x=500,y=20)

oritree=ttk.Treeview(tab2,show='headings')

btn2=Button(tab2,text="修改",command=sqlchange)

btn2.place(x=400,y=450)

tab3=Frame(tabs)

tab3.place(x=200,y=30)

tabs.add(tab3,text='查询')

label6=Label(tab3,width=18,height=2,text='请选择所需查询的属性:')

label6.place(x=10,y=10)

label7=Label(tab3,width=18,height=2,text='请选择查询限制条件:')

label7.place(x=200,y=10)

chklist=["sclass","sno","sname","ssex","sage","Sdept","cno","grade","cname","cpno","ccredit"]

etylist=[]

btnlist=[]

varlist=[]

def showinput():

    for i in range(len(varlist)):

        val = varlist[i].get()

        index=int(val[:-1])

        val=val[-1]

        if val=='1':

            etylist[index].place(x=270,y=45+index*30)

        else:

            etylist[index].place_forget()

def search():

    choseitem=[]

    dct = {}

    for i in range(len(btnlist)):

        val=varlist[i].get()

        index=int(val[:-1])

        val=val[-1]

        if val=='1':

            dct[items[i]]=etylist[i].get()

    allitem=flist.curselection()

    for i in range(len(allitem)):

        choseitem.append(flist.get(allitem[i]))

    tree=ttk.Treeview(tab3,show='headings')

    tree["columns"]=choseitem

    for i in range(len(choseitem)):

        tree.column(choseitem[i],width=int(400/len(choseitem)))

        tree.heading(choseitem[i],text=choseitem[i])

    strsql="SELECT DISTINCT "

    for i in range(len(choseitem)):

        if i!=0:

            strsql+=','

        strsql+=choseitem[i]

    strsql+=" FROM "

    hasS=False

    hasSC=False

    hasC=False

    if ('sname' in choseitem) or ('ssex' in choseitem) or ('sage' in choseitem) or ('Sdept' in choseitem):

        hasS=True

    if 'grade' in choseitem:

        hasSC=True

    if ('cname' in choseitem) or ('cpno' in choseitem) or ('ccredit' in choseitem):

        hasC=True

    if hasS==False and hasSC==False and (('sclass' in choseitem) or ('sno' in choseitem)):

        hasSC=True

    if ('sname' in dct.keys()) or ('ssex' in dct.keys()) or ('sage' in dct.keys()) or ('Sdept' in dct.keys()):

        hasS=True

    if 'grade' in dct.keys():

        hasSC=True

    if ('cname' in dct.keys()) or ('cpno' in dct.keys()) or ('ccredit' in dct.keys()):

        hasC=True

    if hasS==False and hasSC==False and (('sclass' in dct.keys()) or ('sno' in dct.keys())):

        hasSC=True

    if hasS==True:

        strsql+="S"

    if hasSC==True:

        if strsql[-1]=='S':

            strsql+=','

        strsql+="SC"

    if hasC==True:

        if strsql[-1]=='C':

            strsql+=','

        strsql+="C"

    strsql+=" where "

    fi=True

    for key,value in dct.items():

        if fi:

            fi=False

        else:

            strsql+=' and '

        strsql+=str(key)

        strsql+='='

        if str(value).isdigit():

            strsql+=str(value)

        else:

            strsql+='\''

            strsql+=str(value)

            strsql+='\''

    strsql+=';'

    print(strsql)

    try:

        cursor.execute(strsql)

        data=cursor.fetchall()

    except:

        db.rollback()

    for row in data:

        val = ()

        for i in range(len(row)):

            val+=(row[i],)

        tree.insert('','end',value=val)

    tree.place(x=450,y=50)

for i in range(len(chklist)):

    chkvar=StringVar()

    chkbtn = Checkbutton(tab3,text=chklist[i],variable=chkvar,onvalue=str(i)+'1',offvalue=str(i)+'0',command=showinput)

    chkbtn.deselect()

    #chkbtn.place(x=200,y=40+i*30)

    ety=Entry(tab3)

    etylist.append(ety)

    btnlist.append(chkbtn)

    varlist.append(chkvar)

for i in range(len(btnlist)):

    btnlist[i].place(x=200,y=40+i*30)

items=("sclass","sno","sname","ssex","sage","Sdept","cno","grade","cname","cpno","ccredit")

strvar_items=StringVar(value=items)

flist=Listbox(tab3,listvariable=strvar_items,selectmode='multiple',selectforeground='red',selectbackground='white',activestyle='none')

scrollbar_flist=Scrollbar(tab3)

scrollbar_flist.config(command=flist.yview)

flist.config(yscrollcommand=scrollbar_flist.set)

flist.place(x=10,y=40)

scrollbar_flist.place(x=155,y=40)

btn3=Button(tab3,text="查询",command=search)

btn3.place(x=400,y=450)

from tkinter import messagebox

def tchange(event):

    table=tbvar.get()

    deltree.delete(*deltree.get_children())

    if table=="s":

        deltree.place_forget()

        cols=["sclass","sno","sname","ssex","sage","sdept"]

        deltree["columns"]=("sclass","sno","sname","ssex","sage","sdept")

        for i in range(len(cols)):

            deltree.column(cols[i],width=120)

            deltree.heading(cols[i],text=cols[i])

        strsql="SELECT * FROM S"

        try:

            cursor.execute(strsql)

            data=cursor.fetchall()

        except:

            db.rollback()

        for row in data:

            val = ()

            for i in range(len(row)):

                val+=(row[i],)

            deltree.insert('','end',value=val)

        deltree.place(x=20,y=50)

    elif table=="sc":

        deltree.place_forget()

        cols=["sclass","sno","cno","grade"]

        deltree["columns"]=("sclass","sno","cno","grade")

        for i in range(len(cols)):

            deltree.column(cols[i],width=180)

            deltree.heading(cols[i],text=cols[i])

        strsql="SELECT * FROM SC"

        try:

            cursor.execute(strsql)

            data=cursor.fetchall()

        except:

            db.rollback()

        for row in data:

            val = ()

            for i in range(len(row)):

                val+=(row[i],)

            deltree.insert('','end',value=val)

        deltree.place(x=20,y=50)

    else:

        deltree.place_forget()

        cols=["cno","cname","cpno","ccredit"]

        deltree["columns"]=("cno","cname","cpno","ccredit")

        for i in range(len(cols)):

            deltree.column(cols[i],width=180)

            deltree.heading(cols[i],text=cols[i])

        strsql="SELECT * FROM C"

        try:

            cursor.execute(strsql)

            data=cursor.fetchall()

        except:

            db.rollback()

        for row in data:

            val = ()

            for i in range(len(row)):

                val+=(row[i],)

            deltree.insert('','end',value=val)

        deltree.place(x=20,y=50)

def delete():

    config=messagebox.askyesno('提示','确定要删除吗？')

    if config == True:

        val = deltree.item(deltree.focus())["values"]

        strsql="DELETE FROM "

        table=tbvar.get()

        strsql+=table

        if table=='s':

            strsql+=" where sclass="

            strsql+=str(val[0])

            strsql+=" and sno="

            strsql+=str(val[1])

            strsql+=';'

        elif table=='sc':

            strsql+=" where sclass="

            strsql+=str(val[0])

            strsql+=" and sno="

            strsql+=str(val[1])

            strsql+=" and cno="

            strsql+=str(val[2])

            strsql+=';'

        else:

            strsql+=" where cno="

            strsql+=str(val[0])

            strsql+=';'

        print(strsql)

        try:

            cursor.execute(strsql)

            db.commit()

        except:

            db.rollback()

        deltree.delete(deltree.focus())

tab4=Frame(tabs)

tab4.place(x=300,y=30)

tabs.add(tab4,text='删除')

label8=Label(tab4,text="请选择需要删除的表:")

label8.place(x=20,y=10)

deltree=ttk.Treeview(tab4,show='headings')

tbvar=StringVar()

tblist=ttk.Combobox(tab4,textvariable=tbvar)

tblist["values"]=("请选择","s","sc","c")

tblist["state"]='readonly'

tblist.bind("<<ComboboxSelected>>",tchange)

tblist.place(x=145,y=10)

btn4=Button(tab4,text="删除",command=delete)

btn4.place(x=400,y=450)

root.mainloop()