数据库实验 用pymysql+easygui制作的员工管理系统

import pymysql
import easygui as a

if __name__ == '__main__':
    conn = pymysql.connect(host='localhost',
                           user='root',
                           password='password',
                           database='company')
    cursor = conn.cursor()
    ret = a.choicebox(msg='输入您选择的功能', choices=['01', '02', '03', '04', '05', '06', '07', '08', '09'
        , '10', '11', '12'])
    print(ret)
    if ret == '01':
        sql = "select essn from employee where superssn in (select essn from employee where ename = %s);"
        name = a.enterbox(msg='请输入名字：', title='查询直接领导为%ENAME%的员工编号', strip=True)
        print(sql+name)
        cursor.execute(sql, [name])
        results = cursor.fetchall()
        list = []
        for row in results:
            id = row[0]
            print(id)
            list.append(id)
            list.append('\n')
        a.textbox(msg='这是你要的编号', title=' ', text=list, codebox=False, callback=None, run=True)
    elif ret == '02':
        sql = "select dname from department where dno in (select dno from project where plocation = %s);"
        location = a.enterbox(msg='请输入地点：', title='查询项目所在地为%PLOCATION%的部门名称', strip=True)
        print(sql+location)
        cursor.execute(sql, [location])
        results = cursor.fetchall()
        list = []
        for row in results:
            dname = row[0]
            print(dname)
            list.append(dname)
            list.append('\n')
        a.textbox(msg='这是你要的部门', title=' ', text=list, codebox=False, callback=None, run=True)
    elif ret == '03':
        sql = "select ename,address from employee where dno in (select dno from project where pname = %s);"
        project_name = a.enterbox(msg='请输入项目：', title='查询参与%PNAME%项目的所有工作人员的名字和居住地址', strip=True)
        print(sql+project_name)
        cursor.execute(sql, [project_name])
        results = cursor.fetchall()
        list = []
        for row in results:
            ename = row[0]
            address = row[1]
            print(ename, address)
            list.append(ename)
            list.append(' ')
            list.append(address)
            list.append('\n')
        a.textbox(msg='这是你要的姓名地址', title=' ', text=list, codebox=False, callback=None, run=True)
    elif ret == '04':
        sql = "select ename ,address from employee where superssn in (select essn from employee where address =%s) and salary >= %s;"
        address = a.enterbox(msg='请输入领导地址：', title=' 查询部门领导居住地在%ADDRESS%且工资不低于%SALARY%元的员工姓名和居住地', strip=True)
        salary = a.enterbox(msg='请输入工资底线：', title=' 查询部门领导居住地在%ADDRESS%且工资不低于%SALARY%元的员工姓名和居住地', strip=True)
        print(sql+salary+address)
        # print(address)
        cursor.execute(sql, [address, salary])
        results = cursor.fetchall()
        list = []
        for row in results:
            ename = row[0]
            address = row[1]
            print(ename, address)
            list.append(ename)
            list.append(' ')
            list.append(address)
            list.append('\n')
        a.textbox(msg='这是你要的姓名地址', title=' ', text=list, codebox=False, callback=None, run=True)
    elif ret == '05':
        sql = "select ename from employee where dno not in (select dno from project where pno = %s);"
        pno = a.enterbox(msg='请输入没有参加的项目编号：', title=' 查询没有参加项目编号为%PNO%的项目的员工姓名', strip=True)
        print(sql+pno)
        cursor.execute(sql, [pno])
        results = cursor.fetchall()
        list = []
        for row in results:
            ename = row[0]
            print(ename)
            list.append(ename)
            list.append('\n')
        a.textbox(msg='这是你要的姓名', title=' ', text=list, codebox=False, callback=None, run=True)
    elif ret == '06':
        sql = "select dname from department where mgrstartdate > %s;"
        startdate = a.enterbox(msg='请输入工作日期：（格式：xxxx-xx-xx)', title='  查询部门领导工作日期在%MGRSTARTDATE%之后的部门名', strip=True)
        print(sql,[startdate])
        cursor.execute(sql, [startdate])
        results = cursor.fetchall()
        list = []
        for row in results:
            dname = row[0]
            print(dname)
            list.append(dname)
            list.append('\n')
        a.textbox(msg='这是你要的部门', title=' ', text=list, codebox=False, callback=None, run=True)
    elif ret == '07':
        sql = "select pname from project inner join (select pno,sum(hours) as phours from works_on group by pno)" \
              "as dh on dh.pno = project.pno where phours > %s;"
        phours = a.enterbox(msg='请输入总时长：', title=' 查询总工作量大于%HOURS%小时的项目名称', strip=True)
        print(sql+phours)
        cursor.execute(sql, [phours])
        results = cursor.fetchall()
        list = []
        for row in results:
            pname = row[0]
            print(pname)
            list.append(pname)
            list.append('\n')
        a.textbox(msg='这是你要的项目', title=' ', text=list, codebox=False, callback=None, run=True)
    elif ret == '08':
        sql = "select pname from project inner join (select pno,avg(hours) as avghours from works_on group by pno)" \
              "as dh on dh.pno =project.pno where avghours < %s ;"
        avghours = a.enterbox(msg='请输入平均时长：', title=' 查询员工平均工作时间低于%HOURS%的项目名称', strip=True)
        print(sql+avghours)
        cursor.execute(sql, [avghours])
        results = cursor.fetchall()
        list = []
        for row in results:
            pname = row[0]
            print(pname)
            list.append(pname)
            list.append('\n')
        a.textbox(msg='这是你要的项目', title=' ', text=list, codebox=False, callback=None, run=True)
    elif ret == '09':
        sql = "select ename from employee inner join (select count(pno) as count_pno,sum(hours)  as hours_sum ,essn from works_on group by essn)" \
              "as dh on dh.essn = employee.essn where count_pno >= %s and hours_sum > %s;"
        count_pno = a.enterbox(msg='请输入至少参加项目数：', title=' 查询至少参与了%N%个项目并且工作总时间超过%HOURS%小时的员工名字', strip=True)
        hours_sum = a.enterbox(msg='请输入工作时间底线：', title=' 查询至少参与了%N%个项目并且工作总时间超过%HOURS%小时的员工名字', strip=True)
        print(sql)
        print('count_pno:'+count_pno+'hours_sum:'+hours_sum)
        cursor.execute(sql, [count_pno, hours_sum])
        results = cursor.fetchall()
        list = []
        for row in results:
            ename = row[0]
            print(ename)
            list.append(ename)
            list.append('\n')
        a.textbox(msg='这是你要的名字', title=' ', text=list, codebox=False, callback=None, run=True)
    elif ret == '10':
        sql = "insert into employee (ename,essn,address,salary,superssn,dno) values (%s,%s,%s,%s,%s,%s);"
        Msg = '要填的信息'
        Title = '在 employee 表新增记录'
        Fields = ['姓名', '编号', '地址', '工资', '上级编号', '部门号']
        ret = a.multenterbox(Msg, Title, Fields)
        print(sql)
        print(ret)
        cursor.execute(sql, ret)
    elif ret == '11':
        sql = "update employee set address = %s where ename = %s;"
        new_add = a.enterbox(msg='请输入要更改的地址：', title='employee表更改地址', strip=True)
        ename=a.enterbox(msg='请输入要更改地址的人名：', title='employee表更改地址', strip=True)
        print(sql)
        cursor.execute(sql,[new_add,ename])
    elif ret == '12':
       sql="delete from employee where ename =%s;"
       phours = a.enterbox(msg='请输入要删除的人名：', title='employee表删除条目', strip=True)
       print(sql+ename)
       cursor.execute(sql,[ename])
 conn.commit()
 cursor.close()
 conn.close()