数据库系统概论（实验）期末实验代码部分

在数据库已经构建完成的状态下结合Python进行编程，写一个高考信息查询系统

此处仅展示代码部分，数据库的结构和E-R图也附上，便于理解代码，代码很长（一千四百多行），我只对每个功能区进行了划分标注，具体的注释没有写，我相信代码不是无聊的，至少，可以加一些彩蛋。

希望有人能看完全部的代码，就算只有一个人也行，代码没有分区，因此想找到自己需要的部分，估计只能往下划了……不过可以简单说一下，进行表查询的代码是从第290行开始的换算过来差不多是五分之一的长度处，查询代码的顺序与我给的表上面是完全一样的。

需要用的知识，CSDN上都有，建议就事论事去查找学习。

大学(collage)表(主码为collage_no)
高校编号(collage_no)
高校名称(collage_name)
官方URL(official_URL)
省份名(province_name)(FK)
学校类型(school_type_name)(FK)
省份(province)表(主码为province_name)
省份名(province name)
省教育招生考试院(PEAEI_URL)
考生人数(student_num)表(主码为province_name与year)
省份名(provience_name)(FK)
年份(year)
参加考试人数(student_in_total)
最低分数线(min_score_line)表(主码为collage_no与province_name)
高校编号(collage_no)(FK)
省份名(province_name)(FK)
最低分数线(min_score)
用户(user)表(主码为user_name)
用户名(user_name)
用户密码(user_password)
用户电子邮箱(user_e_mail)
省份名(province_name)(FK)
高考分数(score)
学校等级(school_level)表(主码为school_level)
学校等级名(school_level_name)
学校等级介绍(school_level_intro)
招生类型(enrollment_type)表(主码为enrollment_type_name)
招生类型名(enrollment_type_name)
招生类型介绍(enrollment_type_intro)
招生人数(enrollment)表(主码为collage_no与province_name与year)
高校编号(collage_no)(FK)
省份名(province_name)(FK)
年份(year)
招生人数(enrollment_in_total)
学校类型(school_type)表(主码为school_type_name)
高校类型名(school_type_name)
高校类型介绍(school_type_intro)
各高校等级(collage_school_level)表(主码为collage_no)
高校编号(collage_no)(FK)
高校等级名(school_level_name)(FK)
各高校招生类型(collage_province_enrollment_type)(主码为collage_no与enrollment_type_name与province_name)
高校编号(collage_no)(FK)
招生类型名(enrollment_type_name)(FK)
省份名(province_name)(FK)

E-R图：

上代码

import tkinter as tk
from tkinter import ttk
from tkinter import messagebox
import pymysql as py
import re
import turtle
import random
import time


# 随时随地配置IP
ip = "127.0.0.1"


# 窗口
window = tk.Tk()
window.title("欢迎使用本系统")
window.geometry("450x300")


# 背景图片
canvas = tk.Canvas(window, height=1450, width=1300)
image0 = tk.PhotoImage(file="樱顶1.png")
image = canvas.create_image(0, 0, anchor="nw", image=image0)
canvas.pack(side="top")


# 标签
tk.Label(window, text="高校报考信息查询系统").place(x=170, y=60)
tk.Label(window, text="请切记务必在使用本系统前仔细阅读使用说明，不然保证无法使用").place(x=60, y=90)
tk.Label(window, text="用户名：").place(x=100, y=150)
tk.Label(window, text="密码:").place(x=100, y=190)


# 输入框
username_input = tk.StringVar()
frame_username_input = tk.Entry(window, textvariable=username_input)
frame_username_input.place(x=160, y=150)
password_input = tk.StringVar()
frame_password_input = tk.Entry(window, textvariable=password_input, show="*")
frame_password_input.place(x=160, y=190)


# 登录函数
def log_in():
    username = username_input.get()
    password = password_input.get()
    conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                      charset='utf8')
    cursor = conn.cursor()
    a = cursor.execute("select * from user")
    b = cursor.fetchall()
    list_usernames = []
    list_passwords = []
    for i in range(0, a):
        list_usernames.append(b[i][0])
        list_passwords.append(b[i][1])
    dict_users = dict(zip(list_usernames, list_passwords))
    if username in list_usernames:
        if password == dict_users[username]:
            tk.messagebox.showinfo(title="welcome", message=username+",欢迎您")
            choose()
        else:
            tk.messagebox.showerror(message="密码错误")
    elif username == "":
        tk.messagebox.showerror(message="用户名不能为空")
    elif password == "":
        tk.messagebox.showerror(message="密码不能为空")
    else:
        sign_up_ask = tk.messagebox.askyesno("提醒", "您尚未注册，是否注册？")
        if sign_up_ask is True:
            sign_up()
    conn.commit()
    conn.close()


# 注册函数
def sign_up():
    # 写入函数
    def save_user():
        try:
            conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                              charset='utf8')
            cursor = conn.cursor()
            save_username = str(new_username.get())
            save_password = str(new_password.get())
            save_password_confirm = str(new_password_confirm.get())
            save_user_e_mail = str(user_e_mail.get())
            save_province_name = str(province_name.get())
            save_score = int(score.get())
            # 密码等检查
            try:
                pattern_password = r"^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)[0-9a-zA-Z]{8,12}$"
                pattern_e_mail = r"^[0-9a-zA-Z]+(@)[0-9a-zA-Z]+\.(com)$"
                result_password = re.match(pattern_password, save_password)
                result_e_mail = re.match(pattern_e_mail, save_user_e_mail)
                if save_username == "" or save_password == "":
                    tk.messagebox.showerror("错误", "用户名或密码不能为空")
                elif not result_password:
                    tk.messagebox.showerror("错误", "请输入8到12位由数字、大小写字母构成的密码")
                elif not result_e_mail:
                    tk.messagebox.showerror("错误", "请输入正确的电子邮箱")
                elif save_password_confirm != save_password:
                    tk.messagebox.showerror("错误", "密码前后不一致")
                else:
                    sql = "insert into user(user_name,user_password,user_e_mail,province_name,score)\
                        values('%s','%s','%s','%s',%d)"\
                        % (save_username, save_password, save_user_e_mail, save_province_name, save_score)
                    cursor.execute(sql)
                    cursor.execute("select * from user")
                    list_sql2 = cursor.fetchall()
                    row_sql2 = len(list_sql2)
                    conn.commit()
                    conn.close()
                    tk.messagebox.showinfo("欢迎", "您是第%d位用户" % row_sql2)
                    window_sign_up.destroy()
            except py.err.IntegrityError:
                tk.messagebox.showerror("错误", "用户名已存在或省份输入格式不正确")
        except ValueError:
            tk.messagebox.showerror("错误", "成绩应为数字")
    # 注册界面
    window_sign_up = tk.Toplevel(window)
    window_sign_up.geometry("350x320")
    window_sign_up.title("注册")
    # 新建用户名
    new_username = tk.StringVar()
    tk.Label(window_sign_up, text="请输入用户名：").place(x=10, y=10)
    tk.Entry(window_sign_up, textvariable=new_username).place(x=150, y=10)
    # 新建密码
    new_password = tk.StringVar()
    tk.Label(window_sign_up, text="请输入8-12位包含\n大小写字母和数字的密码：").place(x=10, y=40)
    tk.Entry(window_sign_up, textvariable=new_password, show="*").place(x=150, y=50)
    # 再次输入密码
    new_password_confirm = tk.StringVar()
    tk.Label(window_sign_up, text="请再次输入密码：").place(x=10, y=90)
    tk.Entry(window_sign_up, textvariable=new_password_confirm, show="*").place(x=150, y=90)
    # 输入邮箱
    user_e_mail = tk.StringVar()
    tk.Label(window_sign_up, text="请输入邮箱：").place(x=10, y=130)
    tk.Entry(window_sign_up, textvariable=user_e_mail).place(x=150, y=130)
    # 输入省份
    province_name = tk.StringVar()
    tk.Label(window_sign_up, text="请输入省份：").place(x=10, y=170)
    tk.Entry(window_sign_up, textvariable=province_name).place(x=150, y=170)
    # 输入分数
    score = tk.StringVar()
    tk.Label(window_sign_up, text="请输入分数：").place(x=10, y=210)
    tk.Entry(window_sign_up, textvariable=score).place(x=150, y=210)
    # 确认注册按钮
    confirm_sign_up = tk.Button(window_sign_up, text="注册", command=save_user)
    confirm_sign_up.place(x=150, y=250)
    # 返回按钮


# 退出的函数
def quit_windows():
    window.destroy()
    main()


# 彩蛋函数三连
def trees(branch_len, t):
    time.sleep(0.00005)
    if branch_len > 3:
        if 8 <= branch_len <= 12:
            if random.randint(0, 2) == 0:
                t.color('snow')
            else:
                t.color('light coral')
            t.pensize(branch_len / 3)
        elif branch_len < 8:
            if random.randint(0, 1) == 0:
                t.color('snow')
            else:
                t.color('light coral')
            t.pensize(branch_len / 2)
        else:
            t.color('sienna')
            t.pensize(branch_len / 10)  # 6
        t.forward(branch_len)
        a = 1.5 * random.random()
        t.right(20 * a)
        b = 1.5 * random.random()
        trees(branch_len - 10 * b, t)
        t.left(40 * a)
        trees(branch_len - 10 * b, t)
        t.right(20 * a)
        t.up()
        t.backward(branch_len)
        t.down()


def petal(m, t):
    for i in range(m):
        a = 200 - 400 * random.random()
        b = 10 - 20 * random.random()
        t.up()
        t.forward(b)
        t.left(90)
        t.forward(a)
        t.down()
        t.color('light coral')
        t.circle(1)
        t.up()
        t.backward(a)
        t.right(90)
        t.backward(b)


def main():
    t = turtle.Turtle()
    w = turtle.Screen()
    t.hideturtle()
    t.getscreen().tracer(5, 0)
    w.screensize(bg='wheat')
    t.left(90)
    t.up()
    t.backward(150)
    t.down()
    t.color('sienna')
    trees(60, t)
    petal(200, t)
    w.exitonclick()


# 使用手册界面函数
def for_users():
    window_user_page = tk.Tk()
    window_user_page.geometry("450x300")
    window_user_page.title("使用指南")
    tk.Label(window_user_page, text="1.使用前请现将随程序附带的数据库dbms_report安装至MySQL中并保持原名").place(x=0, y=0)
    tk.Label(window_user_page, text="2.该系统默认的MySQL连接信息为IP：127.0.0.1，账户密码均为lzx，字符集为utf8").\
        place(x=0, y=30)
    tk.Label(window_user_page, text="2.本系统配置了查询、插入和修改数据三个主要功能，但未设置级联操作").place(x=0, y=60)
    tk.Label(window_user_page, text="3.插入和删除均受到外键限制，为保护数据库，每次删除仅能删除一条至数条数据").place(x=0, y=90)
    tk.Label(window_user_page, text="4.本系统基于DBMS MySQL搭建，使用MySQL自带的并发控制机制").place(x=0, y=120)
    tk.Label(window_user_page, text="5.标注有“关键词”字样的输入框是查询所需的关键词，如果不予限制将输出整表").place(x=0, y=150)
    tk.Label(window_user_page, text="6.当点击查询却无返回结果时，请检查您的关键词使用是否正确完整").place(x=0, y=180)
    tk.Label(window_user_page, text="7.大部分表格支持不完全关键字查询").place(x=0, y=210)
    tk.Label(window_user_page, text="8.请勿删库，已备份，删库无用，删库封号").place(x=0, y=240)


# 登录、注册、退出、使用指南按钮
login = tk.Button(window, text="登录", command=log_in)
login.place(x=140, y=230)
logup = tk.Button(window, text="注册", command=sign_up)
logup.place(x=210, y=230)
logdown = tk.Button(window, text="退出", command=quit_windows)
logdown.place(x=280, y=230)
user_page = tk.Button(window, text="使用指南", command=for_users)
user_page.place(x=199, y=270)


# 表格选择函数
def choose():
    window_choose = tk.Tk()
    window_choose.title("表格选择")
    window_choose.geometry("450x300")
    tk.Label(window_choose, text="高校报考信息查询系统").place(x=160, y=0)
    tk.Label(window_choose, text="请选择表格").place(x=190, y=20)
    collage = tk.Button(window_choose, text="大学", command=collage_use)
    collage.place(x=0, y=50)
    province = tk.Button(window_choose, text="省份", command=province_use)
    province.place(x=0, y=80)
    student_num = tk.Button(window_choose, text="考生人数", command=student_num_use)
    student_num.place(x=0, y=110)
    min_score_line = tk.Button(window_choose, text="最低分数线", command=min_score_line_use)
    min_score_line.place(x=0, y=140)
    school_level = tk.Button(window_choose, text="学校等级", command=school_level_use)
    school_level.place(x=0, y=170)
    enrollment_type = tk.Button(window_choose, text="招生类型", command=enrollment_type_use)
    enrollment_type.place(x=225, y=50)
    enrollment = tk.Button(window_choose, text="招生人数", command=enrollment_use)
    enrollment.place(x=225, y=80)
    school_type = tk.Button(window_choose, text="学校类型", command=school_type_use)
    school_type.place(x=225, y=110)
    collage_school_level = tk.Button(window_choose, text="各高校等级", command=collage_school_level_use)
    collage_school_level.place(x=225, y=140)
    collage_province_enrollment_type = tk.Button(window_choose, text="各高校招生类型",
                                                 command=collage_province_enrollment_type_use)
    collage_province_enrollment_type.place(x=225, y=170)

    # 返回函数
    def quit_window_choose():
        window_choose.destroy()
    quit_window_choose = tk.Button(window_choose, text="返回", command=quit_window_choose)
    quit_window_choose.place(x=280, y=230)


# collage 表增删改查——————————————————————————————————————————————————————————————————————————————————————————————————————
def collage_use():
    collage_window = tk.Tk()
    collage_window.title("collage表(高校表)操作界面")
    collage_window.geometry("450x500")
    tk.Label(collage_window, text="高校报考信息查询系统").place(x=300, y=20)

    tree = ttk.Treeview(collage_window)
    tree["column"] = ("collage_no", "collage_name", "official_URL", "province_name", "school_type_name")
    tree.column("collage_no", width=120)
    tree.heading("collage_no", text="高校招生代码")
    tree.column("collage_name", width=120)
    tree.heading("collage_name", text="高校全称")
    tree.column("official_URL", width=240)
    tree.heading("official_URL", text="官网链接")
    tree.column("province_name", width=120)
    tree.heading("province_name", text="省份全称")
    tree.column("school_type_name", width=120)
    tree.heading("school_type_name", text="高校类型全称")
    tree.grid(row=8, column=0, columnspan=60)

    collage_no = tk.Label(collage_window, text="高校招生代码")
    collage_no.grid(row=1, column=0)
    collage_no_str = tk.StringVar()
    collage_no_input = tk.Entry(collage_window, width=20, textvariable=collage_no_str)
    collage_no_input.grid(row=1, column=1)

    collage_name = tk.Label(collage_window, text="高校全称(关键字)")
    collage_name.grid(row=2, column=0)
    collage_name_str = tk.StringVar()
    collage_name_input = tk.Entry(collage_window, width=20, textvariable=collage_name_str)
    collage_name_input.grid(row=2, column=1)

    official_url = tk.Label(collage_window, text="官网链接")
    official_url.grid(row=3, column=0)
    official_url_str = tk.StringVar()
    official_url_input = tk.Entry(collage_window, width=20, textvariable=official_url_str)
    official_url_input.grid(row=3, column=1)

    province_name = tk.Label(collage_window, text="省份全称")
    province_name.grid(row=4, column=0)
    province_name_str = tk.StringVar()
    province_name_input = tk.Entry(collage_window, width=20, textvariable=province_name_str)
    province_name_input.grid(row=4, column=1)

    school_type_name = tk.Label(collage_window, text="高校类型全称")
    school_type_name.grid(row=5, column=0)
    school_type_name_str = tk.StringVar()
    school_type_name_input = tk.Entry(collage_window, width=20, textvariable=school_type_name_str)
    school_type_name_input.grid(row=5, column=1)

    def select():
        collage_name_info = collage_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        if len(collage_name_info) == 0:
            cursor.execute("select * from collage")
        else:
            cursor.execute("select * from collage where collage_name='%s'" % collage_name_info)
        list_result = cursor.fetchall()
        x = tree.get_children()
        for item in x:
            tree.delete(item)
        for i in range(len(list_result)):
            tree.insert("", i, text=str(i), values=(list_result[i][0], list_result[i][1],
                                                    list_result[i][2], list_result[i][3], list_result[i][4]))
        conn.commit()
        conn.close()

    def insert():
        collage_no_info = int(collage_no_input.get())
        collage_name_info = collage_name_input.get()
        official_url_info = official_url_input.get()
        province_name_info = province_name_input.get()
        school_type_name_info = school_type_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        try:
            cursor.execute("insert into collage values(%d,'%s','%s','%s','%s')"
                           % (collage_no_info, collage_name_info, official_url_info, province_name_info,
                              school_type_name_info))
            conn.commit()
            tk.messagebox.showinfo("正确", "数据插入成功")
            conn.close()
        except py.err.IntegrityError:
            tk.messagebox.showerror("错误", "受到完整性约束限制，数据插入失败，请重新检查数据，然后插入")

    def delete():
        collage_name_info = collage_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        try:
            if len(collage_name_info) == 0:
                tk.messagebox.showerror("错误", "请输入执行删除的筛选条件")
            else:
                cursor.execute("delete from collage where collage_name='%s'" % collage_name_info)
                conn.commit()
                tk.messagebox.showinfo("正确", "删除数据成功")
            conn.close()
        except py.err.IntegrityError:
            tk.messagebox.showerror("错误", "受到完整性约束限制，数据删除失败，请先删除外键数据")

    tk.Button(collage_window, text="查询", command=select).grid(row=6, column=0)
    tk.Button(collage_window, text="插入", command=insert).grid(row=6, column=1)
    tk.Button(collage_window, text="删除", command=delete).grid(row=6, column=2)

    def quit_collage_use():
        collage_window.destroy()
    tk.Button(collage_window, text="返回", command=quit_collage_use).grid(row=6, column=3)


# province 表增删改查—————————————————————————————————————————————————————————————————————————————————————————————————————
def province_use():
    province_window = tk.Tk()
    province_window.title("province表(省份表)操作界面")
    province_window.geometry("450x500")
    tk.Label(province_window, text="高校报考信息查询系统").place(x=300, y=20)

    tree = ttk.Treeview(province_window)
    tree["column"] = ("province_name", "province_intro")
    tree.column("province_name", width=240)
    tree.heading("province_name", text="省份名全称")
    tree.column("province_intro", width=240)
    tree.heading("province_intro", text="教育招生考试院链接")
    tree.grid(row=4, column=0, columnspan=20)

    province_name = tk.Label(province_window, text="省份名全称(关键字)")
    province_name.grid(row=1, column=0)
    province_name_str = tk.StringVar()
    province_name_input = tk.Entry(province_window, width=20, textvariable=province_name_str)
    province_name_input.grid(row=1, column=1)

    province_intro = tk.Label(province_window, text="教育招生考试院链接")
    province_intro.grid(row=2, column=0)
    province_intro_str = tk.StringVar()
    province_intro_input = tk.Entry(province_window, width=20, textvariable=province_intro_str)
    province_intro_input.grid(row=2, column=1)

    def select():
        province_name_info = province_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        if len(province_name_info) == 0:
            cursor.execute("select * from province")
        else:
            cursor.execute("select * from province where province_name='%s'" % province_name_info)
        list_result = cursor.fetchall()
        x = tree.get_children()
        for item in x:
            tree.delete(item)
        for i in range(len(list_result)):
            tree.insert("", i, text=str(i), values=(list_result[i][0], list_result[i][1]))
        conn.commit()
        conn.close()

    def insert():
        province_name_info = province_name_input.get()
        province_intro_info = province_intro_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        try:
            cursor.execute("insert into province values('%s','%s')" % (province_name_info, province_intro_info))
            conn.commit()
            tk.messagebox.showinfo("正确", "数据插入成功")
            conn.close()
        except py.err.IntegrityError:
            tk.messagebox.showerror("错误", "受到完整性约束限制，数据插入失败，请重新检查数据，然后插入")

    def delete():
        province_name_info = province_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        try:
            if len(province_name_info) == 0:
                tk.messagebox.showerror("错误", "请输入执行删除的筛选条件")
            else:
                cursor.execute("delete from province where province_name='%s'" % province_name_info)
                conn.commit()
                tk.messagebox.showinfo("正确", "删除数据成功")
            conn.close()
        except py.err.IntegrityError:
            tk.messagebox.showerror("错误", "受到完整性约束限制，数据删除失败，请先删除外键数据")

    tk.Button(province_window, text="查询", command=select).grid(row=3, column=0)
    tk.Button(province_window, text="插入", command=insert).grid(row=3, column=1)
    tk.Button(province_window, text="删除", command=delete).grid(row=3, column=2)

    def quit_province_use():
        province_window.destroy()
    tk.Button(province_window, text="返回", command=quit_province_use).grid(row=3, column=3)


# student_num_use 表增删改查——————————————————————————————————————————————————————————————————————————————————————————————
def student_num_use():
    student_num_window = tk.Tk()
    student_num_window.title("student_num表(考生人数表)操作界面")
    student_num_window.geometry("450x300")
    tk.Label(student_num_window, text="高校报考信息查询系统").place(x=300, y=20)

    tree = ttk.Treeview(student_num_window)
    tree["column"] = ("province_name", "year", "student_in_total")
    tree.column("province_name", width=120)
    tree.heading("province_name", text="省份名全称")
    tree.column("year", width=60)
    tree.heading("year", text="年份")
    tree.column("student_in_total", width=120)
    tree.heading("student_in_total", text="考生总人数")
    tree.grid(row=5, column=0, columnspan=20)

    province_name = tk.Label(student_num_window, text="省份名全称(关键字)")
    province_name.grid(row=1, column=0)
    province_name_str = tk.StringVar()
    province_name_input = tk.Entry(student_num_window, width=20, textvariable=province_name_str)
    province_name_input.grid(row=1, column=1)

    year = tk.Label(student_num_window, text="年份(关键字)")
    year.grid(row=2, column=0)
    year_str = tk.StringVar()
    year_input = tk.Entry(student_num_window, width=20, textvariable=year_str)
    year_input.grid(row=2, column=1)

    student_in_total = tk.Label(student_num_window, text="考生总人数")
    student_in_total.grid(row=3, column=0)
    student_in_total_str = tk.StringVar()
    student_in_total_input = tk.Entry(student_num_window, width=20, textvariable=student_in_total_str)
    student_in_total_input.grid(row=3, column=1)

    def select():
        province_name_info = province_name_input.get()
        year_info = year_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        try:
            if len(province_name_info) == 0 and len(year_info) != 0:
                cursor.execute("select * from student_num where year=%d" % int(year_info))
            elif len(province_name_info) != 0 and len(year_info) == 0:
                cursor.execute("select * from student_num where province_name='%s'" % province_name_info)
            elif len(province_name_info) == 0 and len(year_info) == 0:
                cursor.execute("select * from student_num")
            else:
                cursor.execute("select * from student_num where province_name='%s' and year=%d"
                               % (province_name_info, int(year_info)))
            list_result = cursor.fetchall()
            x = tree.get_children()
            for item in x:
                tree.delete(item)
            for i in range(len(list_result)):
                tree.insert("", i, text=str(i), values=(list_result[i][0], list_result[i][1], list_result[i][2]))
            conn.commit()
            conn.close()
        except ValueError:
            tk.messagebox.showerror("错误", "年份需为整数")

    def insert():
        try:
            province_name_info = province_name_input.get()
            year_info = int(year_input.get())
            student_in_total_info = student_in_total_input.get()
            conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                              charset='utf8')
            cursor = conn.cursor()
            try:
                cursor.execute("insert into student_num values('%s',%d,'%s')"
                               % (province_name_info, year_info, student_in_total_info))
                conn.commit()
                tk.messagebox.showinfo("正确", "数据插入成功")
                conn.close()
            except py.err.IntegrityError:
                tk.messagebox.showerror("错误", "受到完整性约束限制，数据插入失败，请重新检查数据，然后插入")
        except ValueError:
            tk.messagebox.showerror("错误", "年份需为整数")

    def delete():
        try:
            province_name_info = province_name_input.get()
            year_info = year_input.get()
            conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                              charset='utf8')
            cursor = conn.cursor()
            try:
                if len(province_name_info) == 0 or len(year_info) == 0:
                    tk.messagebox.showerror("错误", "请输入两个执行删除的筛选条件")
                else:
                    cursor.execute("delete from student_num where province_name='%s' and year=%d"
                                   % (province_name_info, int(year_info)))
                    conn.commit()
                    tk.messagebox.showinfo("正确", "删除数据成功")
                conn.close()
            except py.err.IntegrityError:
                tk.messagebox.showerror("错误", "受到完整性约束限制，数据删除失败，请先删除外键数据")
        except ValueError:
            tk.messagebox.showerror("错误", "年份需为整数")

    tk.Button(student_num_window, text="查询", command=select).grid(row=4, column=0)
    tk.Button(student_num_window, text="插入", command=insert).grid(row=4, column=1)
    tk.Button(student_num_window, text="删除", command=delete).grid(row=4, column=2)

    def quit_student_num_use():
        student_num_window.destroy()
    tk.Button(student_num_window, text="返回", command=quit_student_num_use).grid(row=4, column=3)


# min_score_line 表增删改查———————————————————————————————————————————————————————————————————————————————————————————————
def min_score_line_use():
    min_score_line_window = tk.Tk()
    min_score_line_window.title("min_score_line表(最低分数线表)操作界面")
    min_score_line_window.geometry("450x500")
    tk.Label(min_score_line_window, text="高校报考信息查询系统").place(x=325, y=20)

    tree = ttk.Treeview(min_score_line_window)
    tree["column"] = ("collage_no", "province_name", "min_score")
    tree.column("collage_no", width=240)
    tree.heading("collage_no", text="高校名全称(查询)或高校招生代码(插入删除)")
    tree.column("province_name", width=240)
    tree.heading("province_name", text="省份名全称")
    tree.column("min_score", width=120)
    tree.heading("min_score", text="最低分数线")
    tree.grid(row=5, column=0, columnspan=20)

    collage_no = tk.Label(min_score_line_window, text="高校名全称(关键字,查询)或\n高校招生代码(插入删除)")
    collage_no.grid(row=1, column=0)
    collage_no_str = tk.StringVar()
    collage_no_input = tk.Entry(min_score_line_window, width=20, textvariable=collage_no_str)
    collage_no_input.grid(row=1, column=1)

    province_name = tk.Label(min_score_line_window, text="省份名全称(关键字)")
    province_name.grid(row=2, column=0)
    province_name_str = tk.StringVar()
    province_name_input = tk.Entry(min_score_line_window, width=20, textvariable=province_name_str)
    province_name_input.grid(row=2, column=1)

    min_score = tk.Label(min_score_line_window, text="最低分数线")
    min_score.grid(row=3, column=0)
    min_score_str = tk.StringVar()
    min_score_input = tk.Entry(min_score_line_window, width=20, textvariable=min_score_str)
    min_score_input.grid(row=3, column=1)

    def select():
        collage_no_info = collage_no_input.get()
        province_name_info = province_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        if len(province_name_info) == 0 and len(collage_no_info) != 0:
            cursor.execute("select collage_name,min_score_line.province_name,min_score \
            from min_score_line,collage \
            where collage.collage_no=min_score_line.collage_no and collage_name='%s'"
                           % collage_no_info)
        elif len(province_name_info) != 0 and len(collage_no_info) == 0:
            cursor.execute("select collage_name,min_score_line.province_name,min_score \
                        from min_score_line,collage \
                        where collage.collage_no=min_score_line.collage_no and min_score_line.province_name='%s'"
                           % province_name_info)
        elif len(province_name_info) == 0 and len(collage_no_info) == 0:
            cursor.execute("select collage_name,min_score_line.province_name,min_score from min_score_line,collage \
            where collage.collage_no=min_score_line.collage_no")
        else:
            cursor.execute("select collage_name,min_score_line.province_name,min_score \
                        from min_score_line,collage \
                        where collage.collage_no=min_score_line.collage_no and collage_name='%s' \
                        and min_score_line.province_name='%s'"
                           % (collage_no_info, province_name_info))
        list_result = cursor.fetchall()
        x = tree.get_children()
        for item in x:
            tree.delete(item)
        for i in range(len(list_result)):
            tree.insert("", i, text=str(i), values=(list_result[i][0], list_result[i][1], list_result[i][2]))
        conn.commit()
        conn.close()

    def insert():
        try:
            collage_no_info = int(collage_no_input.get())
            province_name_info = province_name_input.get()
            min_score_info = int(min_score_input.get())
            conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                              charset='utf8')
            cursor = conn.cursor()
            try:
                cursor.execute("insert into min_score_line values(%d,'%s',%d)"
                               % (collage_no_info, province_name_info, min_score_info))
                conn.commit()
                tk.messagebox.showinfo("正确", "数据插入成功")
                conn.close()
            except py.err.IntegrityError:
                tk.messagebox.showerror("错误", "受到完整性约束限制，数据插入失败，请重新检查数据，然后插入")
        except ValueError:
            tk.messagebox.showerror("错误", "高校招生代码与最低分数线需为整数")

    def delete():
        try:
            collage_no_info = int(collage_no_input.get())
            province_name_info = province_name_input.get()
            conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                              charset='utf8')
            cursor = conn.cursor()
            try:
                if len(province_name_info) == 0:
                    tk.messagebox.showerror("错误", "请输入执行删除的筛选条件")
                else:
                    cursor.execute("delete from min_score_line where collage_no=%d and province_name='%s'"
                                   % (collage_no_info, province_name_info))
                    conn.commit()
                    tk.messagebox.showinfo("正确", "删除数据成功")
                conn.close()
            except py.err.IntegrityError:
                tk.messagebox.showerror("错误", "受到完整性约束限制，数据删除失败，请先删除外键数据")
        except ValueError:
            tk.messagebox.showerror("错误", "高校招生代码与最低分数线需为整数")

    tk.Button(min_score_line_window, text="查询", command=select).grid(row=4, column=0)
    tk.Button(min_score_line_window, text="插入", command=insert).grid(row=4, column=1)
    tk.Button(min_score_line_window, text="删除", command=delete).grid(row=4, column=2)

    def quit_min_score_line_use():
        min_score_line_window.destroy()
    tk.Button(min_score_line_window, text="返回", command=quit_min_score_line_use).grid(row=4, column=3)


# school_level 表增删改查—————————————————————————————————————————————————————————————————————————————————————————————————
def school_level_use():
    school_level_window = tk.Tk()
    school_level_window.title("school_level表(高校等级表)操作界面")
    school_level_window.geometry("450x500")
    tk.Label(school_level_window, text="高校报考信息查询系统").place(x=300, y=20)

    tree = ttk.Treeview(school_level_window)
    tree["column"] = ("school_level_name", "school_level_intro")
    tree.column("school_level_name", width=240)
    tree.heading("school_level_name", text="高校等级全称")
    tree.column("school_level_intro", width=1240)
    tree.heading("school_level_intro", text="高校等级介绍链接")
    tree.grid(row=4, column=0, columnspan=20)

    school_level_name = tk.Label(school_level_window, text="高校等级全称(关键字)")
    school_level_name.grid(row=1, column=0)
    school_level_name_str = tk.StringVar()
    school_level_name_input = tk.Entry(school_level_window, width=20, textvariable=school_level_name_str)
    school_level_name_input.grid(row=1, column=1)

    school_level_intro = tk.Label(school_level_window, text="高校等级介绍链接")
    school_level_intro.grid(row=2, column=0)
    school_level_intro_str = tk.StringVar()
    school_level_intro_input = tk.Entry(school_level_window, width=20, textvariable=school_level_intro_str)
    school_level_intro_input.grid(row=2, column=1)

    def select():
        school_level_name_info = school_level_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        if len(school_level_name_info) == 0:
            cursor.execute("select * from school_level")
        else:
            cursor.execute("select * from school_level where school_level_name='%s'" % school_level_name_info)
        list_result = cursor.fetchall()
        x = tree.get_children()
        for item in x:
            tree.delete(item)
        for i in range(len(list_result)):
            tree.insert("", i, text=str(i), values=(list_result[i][0], list_result[i][1]))
        conn.commit()
        conn.close()

    def insert():
        school_level_name_info = school_level_name_input.get()
        school_level_intro_info = school_level_intro_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        try:
            cursor.execute("insert into school_level values('%s','%s')"
                           % (school_level_name_info, school_level_intro_info))
            conn.commit()
            tk.messagebox.showinfo("正确", "数据插入成功")
            conn.close()
        except py.err.IntegrityError:
            tk.messagebox.showerror("错误", "受到完整性约束限制，数据插入失败，请重新检查数据，然后插入")

    def delete():
        school_level_name_info = school_level_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        try:
            if len(school_level_name_info) == 0:
                tk.messagebox.showerror("错误", "请输入执行删除的筛选条件")
            else:
                cursor.execute("delete from school_level where school_level_name='%s'" % school_level_name_info)
                conn.commit()
                tk.messagebox.showinfo("正确", "删除数据成功")
            conn.close()
        except py.err.IntegrityError:
            tk.messagebox.showerror("错误", "受到完整性约束限制，数据删除失败，请先删除外键数据")

    tk.Button(school_level_window, text="查询", command=select).grid(row=3, column=0)
    tk.Button(school_level_window, text="插入", command=insert).grid(row=3, column=1)
    tk.Button(school_level_window, text="删除", command=delete).grid(row=3, column=2)

    def quit_school_level_use():
        school_level_window.destroy()
    tk.Button(school_level_window, text="返回", command=quit_school_level_use).grid(row=3, column=3)


# enrollment_type 表增删改查——————————————————————————————————————————————————————————————————————————————————————————————
def enrollment_type_use():
    enrollment_type_window = tk.Tk()
    enrollment_type_window.title("enrollment_type表(招生类型表)操作界面")
    enrollment_type_window.geometry("450x500")
    tk.Label(enrollment_type_window, text="高校报考信息查询系统").place(x=300, y=20)

    tree = ttk.Treeview(enrollment_type_window)
    tree["column"] = ("enrollment_type_name", "enrollment_type_intro")
    tree.column("enrollment_type_name", width=240)
    tree.heading("enrollment_type_name", text="招生类型全称")
    tree.column("enrollment_type_intro", width=1240)
    tree.heading("enrollment_type_intro", text="招生类型介绍链接")
    tree.grid(row=4, column=0, columnspan=20)

    enrollment_type_name = tk.Label(enrollment_type_window, text="招生类型全称(关键字)")
    enrollment_type_name.grid(row=1, column=0)
    enrollment_type_name_str = tk.StringVar()
    enrollment_type_name_input = tk.Entry(enrollment_type_window, width=20, textvariable=enrollment_type_name_str)
    enrollment_type_name_input.grid(row=1, column=1)

    enrollment_type_intro = tk.Label(enrollment_type_window, text="招生类型介绍链接")
    enrollment_type_intro.grid(row=2, column=0)
    enrollment_type_intro_str = tk.StringVar()
    enrollment_type_intro_input = tk.Entry(enrollment_type_window, width=20, textvariable=enrollment_type_intro_str)
    enrollment_type_intro_input.grid(row=2, column=1)

    def select():
        enrollment_type_name_info = enrollment_type_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        if len(enrollment_type_name_info) == 0:
            cursor.execute("select * from enrollment_type")
        else:
            cursor.execute("select * from enrollment_type where enrollment_type_name='%s'" % enrollment_type_name_info)
        list_result = cursor.fetchall()
        x = tree.get_children()
        for item in x:
            tree.delete(item)
        for i in range(len(list_result)):
            tree.insert("", i, text=str(i), values=(list_result[i][0], list_result[i][1]))
        conn.commit()
        conn.close()

    def insert():
        enrollment_type_name_info = enrollment_type_name_input.get()
        enrollment_type_intro_info = enrollment_type_intro_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        try:
            cursor.execute("insert into enrollment_type values('%s','%s')"
                           % (enrollment_type_name_info, enrollment_type_intro_info))
            conn.commit()
            tk.messagebox.showinfo("正确", "数据插入成功")
            conn.close()
        except py.err.IntegrityError:
            tk.messagebox.showerror("错误", "受到完整性约束限制，数据插入失败，请重新检查数据，然后插入")

    def delete():
        enrollment_type_name_info = enrollment_type_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        try:
            if len(enrollment_type_name_info) == 0:
                tk.messagebox.showerror("错误", "请输入执行删除的筛选条件")
            else:
                cursor.execute("delete from enrollment_type where enrollment_type_name='%s'"
                               % enrollment_type_name_info)
                conn.commit()
                tk.messagebox.showinfo("正确", "删除数据成功")
            conn.close()
        except py.err.IntegrityError:
            tk.messagebox.showerror("错误", "受到完整性约束限制，数据删除失败，请先删除外键数据")

    tk.Button(enrollment_type_window, text="查询", command=select).grid(row=3, column=0)
    tk.Button(enrollment_type_window, text="插入", command=insert).grid(row=3, column=1)
    tk.Button(enrollment_type_window, text="删除", command=delete).grid(row=3, column=2)

    def quit_enrollment_type_use():
        enrollment_type_window.destroy()
    tk.Button(enrollment_type_window, text="返回", command=quit_enrollment_type_use).grid(row=3, column=3)


#  enrollment表增删改查—————————————————————————————————————————————————————————————————————————————————————————————————
def enrollment_use():
    enrollment_window = tk.Tk()
    enrollment_window.title("enrollment表(招生人数表)操作界面")
    enrollment_window.geometry("450x500")
    tk.Label(enrollment_window, text="高校报考信息查询系统").place(x=325, y=20)

    tree = ttk.Treeview(enrollment_window)
    tree["column"] = ("collage_no", "province_name", "year", "enrollment_in_total")
    tree.column("collage_no", width=240)
    tree.heading("collage_no", text="高校名全称(查询)或高校招生代码(插入删除)")
    tree.column("province_name", width=240)
    tree.heading("province_name", text="省份名全称")
    tree.column("year", width=120)
    tree.heading("year", text="年份")
    tree.column("enrollment_in_total", width=120)
    tree.heading("enrollment_in_total", text="招生人数")
    tree.grid(row=6, column=0, columnspan=20)

    collage_no = tk.Label(enrollment_window, text="高校名全称(关键字,查询)或\n高校招生代码(插入删除)")
    collage_no.grid(row=1, column=0)
    collage_no_str = tk.StringVar()
    collage_no_input = tk.Entry(enrollment_window, width=20, textvariable=collage_no_str)
    collage_no_input.grid(row=1, column=1)

    province_name = tk.Label(enrollment_window, text="省份名全称(关键字)")
    province_name.grid(row=2, column=0)
    province_name_str = tk.StringVar()
    province_name_input = tk.Entry(enrollment_window, width=20, textvariable=province_name_str)
    province_name_input.grid(row=2, column=1)

    year = tk.Label(enrollment_window, text="年份(关键字)")
    year.grid(row=3, column=0)
    year_str = tk.StringVar()
    year_input = tk.Entry(enrollment_window, width=20, textvariable=year_str)
    year_input.grid(row=3, column=1)

    enrollment_in_total = tk.Label(enrollment_window, text="招生人数")
    enrollment_in_total.grid(row=4, column=0)
    enrollment_in_total_str = tk.StringVar()
    enrollment_in_total_input = tk.Entry(enrollment_window, width=20, textvariable=enrollment_in_total_str)
    enrollment_in_total_input.grid(row=4, column=1)

    def select():
        collage_no_info = collage_no_input.get()
        province_name_info = province_name_input.get()
        year_info = year_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        try:
            if len(province_name_info) != 0 and len(collage_no_info) == 0 and len(year_info) == 0:
                cursor.execute("select collage_name,enrollment.province_name,year,enrollment_in_total \
                            from enrollment,collage \
                            where collage.collage_no=enrollment.collage_no \
                            and enrollment.province_name='%s'"
                               % province_name_info)
            elif len(province_name_info) == 0 and len(collage_no_info) != 0 and len(year_info) == 0:
                cursor.execute("select collage_name,enrollment.province_name,year,enrollment_in_total \
                            from enrollment,collage \
                            where collage.collage_no=enrollment.collage_no \
                            and collage_name='%s'"
                               % collage_no_info)
            elif len(province_name_info) == 0 and len(collage_no_info) == 0 and len(year_info) != 0:
                cursor.execute("select collage_name,enrollment.province_name,year,enrollment_in_total \
                            from enrollment,collage \
                            where collage.collage_no=enrollment.collage_no \
                            and year=%d"
                               % int(year_info))
            elif len(province_name_info) != 0 and len(collage_no_info) != 0 and len(year_info) == 0:
                cursor.execute("select collage_name,enrollment.province_name,year,enrollment_in_total \
                            from enrollment,collage \
                            where collage.collage_no=enrollment.collage_no \
                            and collage_name='%s' \
                            and enrollment.province_name='%s'"
                               % (collage_no_info, province_name_info))
            elif len(province_name_info) != 0 and len(collage_no_info) == 0 and len(year_info) != 0:
                cursor.execute("select collage_name,enrollment.province_name,year,enrollment_in_total \
                            from enrollment,collage \
                            where collage.collage_no=enrollment.collage_no \
                            and enrollment.province_name='%s' \
                            and year=%d"
                               % (province_name_info, int(year_info)))
            elif len(province_name_info) == 0 and len(collage_no_info) != 0 and len(year_info) != 0:
                cursor.execute("select collage_name,enrollment.province_name,year,enrollment_in_total \
                            from enrollment,collage \
                            where collage.collage_no=enrollment.collage_no \
                            and collage_name='%s' \
                            and year=%d"
                               % (collage_no_info, int(year_info)))
            elif len(province_name_info) == 0 and len(collage_no_info) == 0 and len(year_info):
                cursor.execute("select collage_name,enrollment.province_name,year,enrollment_in_total \
                from enrollment,collage \
                where collage.collage_no=enrollment.collage_no")
            else:
                cursor.execute("select collage_name,enrollment.province_name,year,enrollment_in_total \
                            from enrollment,collage \
                            where collage.collage_no=enrollment.collage_no \
                            and collage_name='%s' \
                            and enrollment.province_name='%s' \
                            and year=%d"
                               % (collage_no_info, province_name_info, int(year_info)))
            list_result = cursor.fetchall()
            x = tree.get_children()
            for item in x:
                tree.delete(item)
            for i in range(len(list_result)):
                tree.insert("", i, text=str(i),
                            values=(list_result[i][0], list_result[i][1], list_result[i][2], list_result[i][3]))
            conn.commit()
            conn.close()
        except ValueError:
            tk.messagebox.showerror("错误", "年份须为整数")

    def insert():
        try:
            collage_no_info = int(collage_no_input.get())
            province_name_info = province_name_input.get()
            year_info = int(year_input.get())
            enrollment_in_total_info = int(enrollment_in_total_input.get())
            conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                              charset='utf8')
            cursor = conn.cursor()
            try:
                cursor.execute("insert into enrollment values(%d,'%s',%d,%d)"
                               % (collage_no_info, province_name_info, year_info, enrollment_in_total_info))
                conn.commit()
                tk.messagebox.showinfo("正确", "数据插入成功")
                conn.close()
            except py.err.IntegrityError:
                tk.messagebox.showerror("错误", "受到完整性约束限制，数据插入失败，请重新检查数据，然后插入")
        except ValueError:
            tk.messagebox.showerror("错误", "高校招生代码、年份、与招生人数需为整数")

    def delete():
        try:
            collage_no_info = collage_no_input.get()
            province_name_info = province_name_input.get()
            year_info = year_input.get()
            conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                              charset='utf8')
            cursor = conn.cursor()
            try:
                if len(province_name_info) == 0 or len(collage_no_info) == 0 or len(year_info) == 0:
                    tk.messagebox.showerror("错误", "请输入三条执行删除的筛选条件")
                else:
                    cursor.execute("delete from enrollment where collage_no=%d and province_name='%s' and year=%d"
                                   % (int(collage_no_info), province_name_info, int(year_info)))
                    conn.commit()
                    tk.messagebox.showinfo("正确", "删除数据成功")
                conn.close()
            except py.err.IntegrityError:
                tk.messagebox.showerror("错误", "受到完整性约束限制，数据删除失败，请先删除外键数据")
        except ValueError:
            tk.messagebox.showerror("错误", "高校招生代码与年份需为整数")

    tk.Button(enrollment_window, text="查询", command=select).grid(row=5, column=0)
    tk.Button(enrollment_window, text="插入", command=insert).grid(row=5, column=1)
    tk.Button(enrollment_window, text="删除", command=delete).grid(row=5, column=2)

    def quit_enrollment_use():
        enrollment_window.destroy()
    tk.Button(enrollment_window, text="返回", command=quit_enrollment_use).grid(row=5, column=3)


# school_level 表增删改查—————————————————————————————————————————————————————————————————————————————————————————————————
def school_type_use():
    school_type_window = tk.Tk()
    school_type_window.title("school_type表(高校类型表)操作界面")
    school_type_window.geometry("450x500")
    tk.Label(school_type_window, text="高校报考信息查询系统").place(x=300, y=20)

    tree = ttk.Treeview(school_type_window)
    tree["column"] = ("school_type_name", "school_type_intro")
    tree.column("school_type_name", width=240)
    tree.heading("school_type_name", text="高校类型全称")
    tree.column("school_type_intro", width=1240)
    tree.heading("school_type_intro", text="高校类型介绍链接")
    tree.grid(row=4, column=0, columnspan=20)

    school_type_name = tk.Label(school_type_window, text="高校类型全称(关键字)")
    school_type_name.grid(row=1, column=0)
    school_type_name_str = tk.StringVar()
    school_type_name_input = tk.Entry(school_type_window, width=20, textvariable=school_type_name_str)
    school_type_name_input.grid(row=1, column=1)

    school_type_intro = tk.Label(school_type_window, text="高校类型介绍链接")
    school_type_intro.grid(row=2, column=0)
    school_type_intro_str = tk.StringVar()
    school_type_intro_input = tk.Entry(school_type_window, width=20, textvariable=school_type_intro_str)
    school_type_intro_input.grid(row=2, column=1)

    def select():
        school_type_name_info = school_type_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        if len(school_type_name_info) == 0:
            cursor.execute("select * from school_type")
        else:
            cursor.execute("select * from school_type where school_type_name='%s'" % school_type_name_info)
        list_result = cursor.fetchall()
        x = tree.get_children()
        for item in x:
            tree.delete(item)
        for i in range(len(list_result)):
            tree.insert("", i, text=str(i), values=(list_result[i][0], list_result[i][1]))
        conn.commit()
        conn.close()

    def insert():
        school_type_name_info = school_type_name_input.get()
        school_type_intro_info = school_type_intro_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        try:
            cursor.execute("insert into school_type values('%s','%s')"
                           % (school_type_name_info, school_type_intro_info))
            conn.commit()
            tk.messagebox.showinfo("正确", "数据插入成功")
            conn.close()
        except py.err.IntegrityError:
            tk.messagebox.showerror("错误", "受到完整性约束限制，数据插入失败，请重新检查数据，然后插入")

    def delete():
        school_type_name_info = school_type_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        try:
            if len(school_type_name_info) == 0:
                tk.messagebox.showerror("错误", "请输入执行删除的筛选条件")
            else:
                cursor.execute("delete from school_type where school_type_name='%s'"
                               % school_type_name_info)
                conn.commit()
                tk.messagebox.showinfo("正确", "删除数据成功")
            conn.close()
        except py.err.IntegrityError:
            tk.messagebox.showerror("错误", "受到完整性约束限制，数据删除失败，请先删除外键数据")

    tk.Button(school_type_window, text="查询", command=select).grid(row=3, column=0)
    tk.Button(school_type_window, text="插入", command=insert).grid(row=3, column=1)
    tk.Button(school_type_window, text="删除", command=delete).grid(row=3, column=2)

    def quit_school_type_use():
        school_type_window.destroy()
    tk.Button(school_type_window, text="返回", command=quit_school_type_use).grid(row=3, column=3)


# collage_school_level 表增删改查—————————————————————————————————————————————————————————————————————————————————————————
def collage_school_level_use():
    collage_school_level_window = tk.Tk()
    collage_school_level_window.title("collage_school_level表(各高校等级表)操作界面")
    collage_school_level_window.geometry("450x500")
    tk.Label(collage_school_level_window, text="高校报考信息查询系统").place(x=300, y=20)

    tree = ttk.Treeview(collage_school_level_window)
    tree["column"] = ("collage_no", "school_level_name")
    tree.column("collage_no", width=120)
    tree.heading("collage_no", text="高校名全称(查询)或高校招生代号(插入删除)")
    tree.column("school_level_name", width=120)
    tree.heading("school_level_name", text="高校等级全称")
    tree.grid(row=4, column=0, columnspan=20)

    collage_no = tk.Label(collage_school_level_window, text="高校名全称(关键字,查询)或高校招生代码(插入删除)")
    collage_no.grid(row=1, column=0)
    collage_no_str = tk.StringVar()
    collage_no_input = tk.Entry(collage_school_level_window, width=20, textvariable=collage_no_str)
    collage_no_input.grid(row=1, column=1)

    school_level_name = tk.Label(collage_school_level_window, text="高校等级全称")
    school_level_name.grid(row=2, column=0)
    school_level_name_str = tk.StringVar()
    school_level_name_input = tk.Entry(collage_school_level_window, width=20, textvariable=school_level_name_str)
    school_level_name_input.grid(row=2, column=1)

    def select():
        collage_no_info = collage_no_input.get()
        school_level_name_info = school_level_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        if len(collage_no_info) == 0 and len(school_level_name_info) != 0:
            cursor.execute("select collage_name,school_level_name \
                        from collage_school_level,collage \
                        where collage.collage_no=collage_school_level.collage_no and school_level_name='%s'"
                           % school_level_name_info)
        elif len(collage_no_info) != 0 and len(school_level_name_info) == 0:
            cursor.execute("select collage_name,school_level_name \
                        from collage_school_level,collage \
                        where collage.collage_no=collage_school_level.collage_no and collage_name='%s'"
                           % collage_no_info)
        elif len(collage_no_info) == 0 and len(school_level_name_info) == 0:
            cursor.execute("select collage_name,school_level_name \
                        from collage_school_level,collage \
                        where collage.collage_no=collage_school_level.collage_no")
        else:
            cursor.execute("select collage_name,school_level_name \
                        from collage_school_level,collage \
                        where collage.collage_no=collage_school_level.collage_no and collage_name='%s' \
                        and school_level_name='%s'"
                           % (collage_no_info, school_level_name_info))
        list_result = cursor.fetchall()
        x = tree.get_children()
        for item in x:
            tree.delete(item)
        for i in range(len(list_result)):
            tree.insert("", i, text=str(i), values=(list_result[i][0], list_result[i][1]))
        conn.commit()
        conn.close()

    def insert():
        try:
            collage_no_info = int(collage_no_input.get())
            school_level_name_info = school_level_name_input.get()
            conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                              charset='utf8')
            cursor = conn.cursor()
            try:
                cursor.execute("insert into collage_school_level values(%d,'%s')"
                               % (collage_no_info, school_level_name_info))
                conn.commit()
                tk.messagebox.showinfo("正确", "数据插入成功")
                conn.close()
            except py.err.IntegrityError:
                tk.messagebox.showerror("错误", "受到完整性约束限制，数据插入失败，请重新检查数据，然后插入")
        except ValueError:
            tk.messagebox.showerror("错误", "高校招生代码需为整数")

    def delete():
        collage_no_info = collage_no_input.get()
        school_level_name_info = school_level_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        try:
            if len(collage_no_info) != 0 and len(school_level_name_info) == 0:
                cursor.execute("delete from collage_school_level where collage_no=%d" % int(collage_no_info))
            elif len(collage_no_info) == 0 and len(school_level_name_info) != 0:
                cursor.execute("delete from collage_school_level where school_level_name='%s'" % school_level_name_info)
            elif len(collage_no_info) == 0 and len(school_level_name_info) == 0:
                tk.messagebox.showerror("错误", "请输入执行删除的筛选条件")
            else:
                cursor.execute("delete from collage_school_level where collage_no=%d and school_type_name='%s'"
                               % (int(collage_no_info), school_level_name_info))
                conn.commit()
                tk.messagebox.showinfo("正确", "删除数据成功")
            conn.close()
        except py.err.IntegrityError:
            tk.messagebox.showerror("错误", "受到完整性约束限制，数据删除失败，请先删除外键数据")

    tk.Button(collage_school_level_window, text="查询", command=select).grid(row=3, column=0)
    tk.Button(collage_school_level_window, text="插入", command=insert).grid(row=3, column=1)
    tk.Button(collage_school_level_window, text="删除", command=delete).grid(row=3, column=2)

    def quit_collage_school_level_use():
        collage_school_level_window.destroy()
    tk.Button(collage_school_level_window, text="返回", command=quit_collage_school_level_use).grid(row=3, column=3)


# collage_province_enrollment_type 表增删改查—————————————————————————————————————————————————————————————————————————————
def collage_province_enrollment_type_use():
    collage_province_enrollment_type_window = tk.Tk()
    collage_province_enrollment_type_window.title("collage_province_enrollment_type表(各高校招生类型表)操作界面")
    collage_province_enrollment_type_window.geometry("450x500")
    tk.Label(collage_province_enrollment_type_window, text="高校报考信息查询系统").place(x=320, y=20)

    tree = ttk.Treeview(collage_province_enrollment_type_window)
    tree["column"] = ("collage_no", "province_name", "enrollment_type_name")
    tree.column("collage_no", width=120)
    tree.heading("collage_no", text="高校名全称(查询)或高校招生代码(插入删除)")
    tree.column("province_name", width=120)
    tree.heading("province_name", text="省份名全称")
    tree.column("enrollment_type_name", width=120)
    tree.heading("enrollment_type_name", text="招生类型全称")
    tree.grid(row=5, column=0, columnspan=20)

    collage_no = tk.Label(collage_province_enrollment_type_window, text="高校名全称(关键字,查询)或\n高校招生代码(插入删除)")
    collage_no.grid(row=1, column=0)
    collage_no_str = tk.StringVar()
    collage_no_input = tk.Entry(collage_province_enrollment_type_window, width=20, textvariable=collage_no_str)
    collage_no_input.grid(row=1, column=1)

    province_name = tk.Label(collage_province_enrollment_type_window, text="省份名全称(关键字)")
    province_name.grid(row=2, column=0)
    province_name_str = tk.StringVar()
    province_name_input = tk.Entry(collage_province_enrollment_type_window, width=20, textvariable=province_name_str)
    province_name_input.grid(row=2, column=1)

    enrollment_type_name = tk.Label(collage_province_enrollment_type_window, text="招生类型全称")
    enrollment_type_name.grid(row=3, column=0)
    enrollment_type_name_str = tk.StringVar()
    enrollment_type_name_input = tk.Entry(collage_province_enrollment_type_window, width=20,
                                          textvariable=enrollment_type_name_str)
    enrollment_type_name_input.grid(row=3, column=1)

    def select():
        collage_no_info = collage_no_input.get()
        province_name_info = province_name_input.get()
        enrollment_type_name_info = enrollment_type_name_input.get()
        conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                          charset='utf8')
        cursor = conn.cursor()
        if len(province_name_info) != 0 and len(collage_no_info) == 0 and len(enrollment_type_name_info) == 0:
            cursor.execute("select collage_name,collage_province_enrollment_type.province_name,enrollment_type_name \
                        from collage_province_enrollment_type,collage \
                        where collage.collage_no=collage_province_enrollment_type.collage_no \
                        and collage_province_enrollment_type.province_name='%s'"
                           % province_name_info)
        elif len(province_name_info) == 0 and len(collage_no_info) != 0 and len(enrollment_type_name_info) == 0:
            cursor.execute("select collage_name,collage_province_enrollment_type.province_name,enrollment_type_name \
                        from collage_province_enrollment_type,collage \
                        where collage.collage_no=collage_province_enrollment_type.collage_no \
                        and collage_name='%s'"
                           % collage_no_info)
        elif len(province_name_info) == 0 and len(collage_no_info) == 0 and len(enrollment_type_name_info) != 0:
            cursor.execute("select collage_name,collage_province_enrollment_type.province_name,enrollment_type_name \
                        from collage_province_enrollment_type,collage \
                        where collage.collage_no=collage_province_enrollment_type.collage_no \
                        and enrollment_type_name='%s'"
                           % enrollment_type_name_info)
        elif len(province_name_info) != 0 and len(collage_no_info) != 0 and len(enrollment_type_name_info) == 0:
            cursor.execute("select collage_name,collage_province_enrollment_type.province_name,enrollment_type_name \
                        from collage_province_enrollment_type,collage \
                        where collage.collage_no=collage_province_enrollment_type.collage_no \
                        and collage_name='%s' \
                        and collage_province_enrollment_type.province_name='%s'"
                           % (collage_no_info, province_name_info))
        elif len(province_name_info) != 0 and len(collage_no_info) == 0 and len(enrollment_type_name_info) != 0:
            cursor.execute("select collage_name,collage_province_enrollment_type.province_name,enrollment_type_name \
                        from collage_province_enrollment_type,collage \
                        where collage.collage_no=collage_province_enrollment_type.collage_no \
                        and collage_province_enrollment_type.province_name='%s' \
                        and enrollment_type_name='%s'"
                           % (province_name_info, enrollment_type_name_info))
        elif len(province_name_info) == 0 and len(collage_no_info) != 0 and len(enrollment_type_name_info) != 0:
            cursor.execute("select collage_name,collage_province_enrollment_type.province_name,enrollment_type_name \
                        from collage_province_enrollment_type,collage \
                        where collage.collage_no=collage_province_enrollment_type.collage_no \
                        and collage_name='%s' \
                        and enrollment_type_name='%s'"
                           % (collage_no_info, enrollment_type_name_info))
        elif len(province_name_info) == 0 and len(collage_no_info) == 0 and len(enrollment_type_name_info):
            cursor.execute("select collage_name,collage_province_enrollment_type.province_name,enrollment_type_name \
                        from collage_province_enrollment_type,collage \
                        where collage.collage_no=collage_province_enrollment_type.collage_no")
        else:
            cursor.execute("select collage_name,collage_province_enrollment_type.province_name,enrollment_type_name \
                        from collage_province_enrollment_type,collage \
                        where collage.collage_no=collage_province_enrollment_type.collage_no \
                        and collage_name='%s' \
                        and collage_province_enrollment_type.province_name='%s' \
                        and enrollment_type_name='%s'"
                           % (collage_no_info, province_name_info, enrollment_type_name_info))
        list_result = cursor.fetchall()
        x = tree.get_children()
        for item in x:
            tree.delete(item)
        for i in range(len(list_result)):
            tree.insert("", i, text=str(i),
                        values=(list_result[i][0], list_result[i][1], list_result[i][2]))
        conn.commit()
        conn.close()

    def insert():
        try:
            collage_no_info = int(collage_no_input.get())
            province_name_info = province_name_input.get()
            enrollment_type_name_info = enrollment_type_name_input.get()
            conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                              charset='utf8')
            cursor = conn.cursor()
            try:
                cursor.execute("insert into collage_province_enrollment_type values(%d,'%s','%s')"
                               % (collage_no_info, province_name_info, enrollment_type_name_info))
                conn.commit()
                tk.messagebox.showinfo("正确", "数据插入成功")
                conn.close()
            except py.err.IntegrityError:
                tk.messagebox.showerror("错误", "受到完整性约束限制，数据插入失败，请重新检查数据，然后插入")
        except ValueError:
            tk.messagebox.showerror("错误", "高校招生代码需为整数")

    def delete():
        try:
            collage_no_info = collage_no_input.get()
            province_name_info = province_name_input.get()
            enrollment_type_name_info = enrollment_type_name_input.get()
            conn = py.connect(host='%s' % ip, user='lzx', password='lzx', port=3306, database='dbms_report',
                              charset='utf8')
            cursor = conn.cursor()
            try:
                if len(province_name_info) == 0 or len(collage_no_info) == 0 or len(enrollment_type_name_info) == 0:
                    tk.messagebox.showerror("错误", "请输入执行删除的筛选条件")
                else:
                    cursor.execute("delete from collage_province_enrollment_type \
                    where collage_no=%d and province_name='%s' and enrollment_type_name='%s'"
                                   % (int(collage_no_info), province_name_info, enrollment_type_name_info))
                    conn.commit()
                    tk.messagebox.showinfo("正确", "删除数据成功")
                conn.close()
            except py.err.IntegrityError:
                tk.messagebox.showerror("错误", "受到完整性约束限制，数据删除失败，请先删除外键数据")
        except ValueError:
            tk.messagebox.showerror("错误", "高校招生代码需为整数")

    tk.Button(collage_province_enrollment_type_window, text="查询", command=select).grid(row=4, column=0)
    tk.Button(collage_province_enrollment_type_window, text="插入", command=insert).grid(row=4, column=1)
    tk.Button(collage_province_enrollment_type_window, text="删除", command=delete).grid(row=4, column=2)

    def quit_collage_province_enrollment_type_use():
        collage_province_enrollment_type_window.destroy()
    tk.Button(collage_province_enrollment_type_window, text="返回",
              command=quit_collage_province_enrollment_type_use).grid(row=4, column=3)


window.mainloop()

这代码写了我30个小时，属实是不容易，每个想从相关体系上找到东西的人都可以找到自己需要的