Python学习day4作业-员工信息表

缺乏、安全感 2022-07-13 01:55 158阅读 0赞

# Python学习day4作业-员工信息表 #

@(学习)\[python\]

*  Python学习day4作业-员工信息表
    
     *  day4作业员工信息表
     *  程序说明
     *  基本流程图
     *  程序代码
     *  程序测试

## day4作业：员工信息表 ##

> 作业需求  
> 1.模糊查询，查询结果可显示条数，支持例如以下语法：  
> select name,age from staff\_table where age > 22  
> select \* from staff\_table where dept = “IT”  
> select \* from staff\_table where enroll\_date like “2013”  
> 2.可创建新员工纪录，以phone做唯一键，staff\_id需自增；  
> 3.可删除指定员工信息纪录，输入员工id，即可删除；  
> 4.可修改员工信息，语法如下:  
> `update staff_table set dept = "Market" where dept = "IT";`  
> 5.充分使用函数，请尽你的最大限度来减少重复代码；

*  \[x\] 博客
 *  \[x\] 模糊查询
 *  \[x\] 创建员工纪录
 *  \[x\] 删除员工纪录
 *  \[x\] 修改员工纪录

[ygqygq2的博客地址][ygqygq2]

## 1.程序说明 ##

实现功能如下

*  \[x\] 博客
 *  \[x\] 模糊查询
 *  \[x\] 创建员工纪录
 *  \[x\] 删除员工纪录
 *  \[x\] 修改员工纪录

## 2.基本流程图 ##

![这里写图片描述][SouthEast]

## 3.程序代码 ##

#!/usr/bin/env python
    # _*_coding:utf-8_*_
    ''' * Created on 2016/12/5 22:31. * @author: Chinge_Yang. '''
    
    import os
    import sys
    
    
    def file_to_data(table):
        """ # 读文件成数据 :param :return: """
        n = 0
        data_list = []
        with open(table, "r", encoding="utf-8") as f:
            for line in f:
                line = line.strip()  # 去除换行符
                if n == 0:
                    # ["staff_id","name","age","phone","dept","enroll_date"]
                    struct_list = line.split(",")  # 第一行
                else:
                    line_list = line.split(",")
                    data_list.append(line_list)  # 其它行
                n += 1
        return struct_list, data_list
    
    
    def data_to_file(struct_list, data_list, table):
        """ # 把数据写入文件 :return: """
        with open(table, "w", encoding="utf-8") as f:
            f.write(','.join(struct_list) + "\n")
            for sub_list in data_list:
                f.write(",".join(sub_list) + "\n")
        print("Done!")
    
    
    def print_help(action):
        print("The grammar of the {} support likes:\n".format(action))
        if action == "select":
            print("\tselect * from staff_table;")
            print("\tselect name,age from staff_table where age > 22;")
            print("\tselect * from staff_table where dept = \"IT\";")
            print("\tselect * from staff_table where enroll_date like \"2013\";")
        elif action == "add":
            print("\tadd [Alex Li,22,13651054608,IT,2013-04-01];")
        elif action == "update":
            print("\tupdate staff_table set dept = \"Market\" where dept = \"IT\";")
        elif action == "delete":
            print("\tdelete 5;")
    
    
    def get_column_number(column, struct_list):
        """ # 获取列位置 :param column: 列名，此处只实现支持一个 :return: """
        column_number = struct_list.index(column)  # 结果为数字
        return column_number
    
    
    def input_sql():
        # 获取输入SQL
        exit_flag = False
        while exit_flag is not True:
            print("-".center(60, "-"))
            print("Tip: Input 【help [select/update/add/delete]】 to get help.")
            print("-".center(60, "-"))
            sql = input("Please input SQL:").strip().strip(";")
            if sql.startswith('help'):
                action = sql.split(" ")[1]
                print_help(action)
                continue
            if sql == "q" or sql == "quit":
                exit(" Bye Bye ".center(60, "#"))
            exit_flag = True
        return sql
    
    
    def sql_to_list(sql):
        tmp_sql = sql.split(' ')
        sql_list = []
        tmp = ''
        flag = 1    # 列表添加元素标识
        for l in tmp_sql:
            if l.startswith('"') and l.endswith('"'):
                flag = 1
            elif l.startswith('"') and (not l.endswith('"')):
                flag = 0
                tmp = l + ' '
            elif (not l.startswith('"')) and (not l.endswith('"')):
                if flag == 0:
                    l += ' '
                    tmp += l
                else:
                    flag = 1
            elif (not l.startswith('"')) and l.endswith('"'):
                if flag == 0:
                    tmp += l
                    flag = 1
                    sql_list.append(tmp)
                    continue
    
            if flag == 1:
                sql_list.append(l)
        return sql_list
    
    
    def check_table(table, c_table):
        """ # 判断表是否存在 :param table: 表名 :return: """
        if table != c_table:
            print("Your input table \033[31m{}\033[0m is not exists,"
                  "please check!".format(c_table))
            print("#".center(60, "#"))
            return True  # 标记给continue_flag
    
    
    def check_quotes(str):
        """ :param str: 需要处理的字符串 :return: 返回无符号的字符串 """
        if '"' in str:
            str = str.strip('"')
        return str
    
    
    def auto_increment_id(data_list):
        file = "auto_increment_id"
        max_staff_id = int(data_list[-1][0])    # 表中最大的staff_id
        id = 0  # 初始化
        if os.path.exists(file):    # 自增id文件存在时
            with open(file, "r+") as f:
                for line in f:
                    id = int(line)
        if max_staff_id <= id:
            new_staff_id = id + 1
        else:
            new_staff_id = max_staff_id + 1
        with open(file, "w+") as f:
            f.write(str(new_staff_id))
        return new_staff_id
    
    
    def analyze(sql):
        input_info = sql_to_list(sql)
        # return input_info
        action = input_info[0]  # 查：select；增：add；改：update；删：delete
        return action
    
    
    def select(sql, struct_list, data_list, table):  # select sql语法分析
        input_info = sql_to_list(sql)
        select_column = input_info[1].split(",")  # 可能有“,”号
        try:
            table_name = input_info[3]
        except Exception as e:
            print("Your input is error!")
            return True
        continue_flag = check_table(table, table_name)
        if continue_flag is True:
            return True
    
        all_column = False
        all_line = False
        # 查询列，* 或者指定列
        if "*" in select_column:  # 打印所有列
            all_column = True
        else:
            column_numbers = []  # 输出列的数字列表
            for s_column in select_column:
                s_number = get_column_number(s_column, struct_list)
                column_numbers.append(s_number)
    
        if "where" in sql:
            # 由于双引号问题，此处加上双引号
            where_flag = input_info[4]  # where
            condition_column = input_info[5]  # 条件字段
            condition_str = input_info[6]  # 限制条件关键字，支持“=”，“>=”，“like”等
            condition_value = input_info[7]  # 条件参数
            condition_value = check_quotes(condition_value) # 去除双引号
            column_number = get_column_number(condition_column, struct_list)  # 列位置
    
            match_data_list = []  # 匹配出来的结果，列表格式，
            # 查询行,有like、>=、= 等
            if where_flag == "where":  # 有where
                if condition_str == "like":
                    # like
                    for line in data_list:  # line也是列表
                        if condition_value in line[column_number]:  # 匹配like
                            match_data_list.append(line)
                elif condition_str == "=":
                    for line in data_list:
                        if line[column_number] == condition_value:
                            match_data_list.append(line)
                elif condition_str == ">":
                    for line in data_list:
                        if line[column_number] > condition_value:
                            match_data_list.append(line)
                elif condition_str == ">=":
                    for line in data_list:
                        if line[column_number] >= condition_value:
                            match_data_list.append(line)
                elif condition_str == "<":
                    for line in data_list:
                        if line[column_number] < condition_value:
                            match_data_list.append(line)
                elif condition_str == "<=":
                    for line in data_list:
                        if line[column_number] <= condition_value:
                            match_data_list.append(line)
        else:  # 无where，取所有行
            all_line = True
            match_data_list = data_list
    
        # 打印结果
        print("The select result:")
        print("#".center(60, "#"))
        print("\033[32m{}\033[0m rows in set".format(len(match_data_list)))
        if all_column is True:
            print("{:>8} {:>8} {:>8} {:>8} {:>8} {:>8}".format(*struct_list))
            for line in match_data_list:
                print("{:>8} {:>8} {:>8} {:>8} {:>8} {:>8}".format(*line))
        else:
            len_num = len(select_column)
            format_str = '{:>8} ' * len_num
            print(format_str.format(*select_column))
            for line in match_data_list:
                line_list = []
                for s in column_numbers:
                    line_list.append(line[s])
                print(format_str.format(*line_list))
        print("#".center(60, "#"))
    
    def add(sql, struct_list, data_list, table):
        # sql: Alex Li,22,13651054608,IT,2013-04-01
        input_info = sql.strip().strip("add [").strip("]")
        add_list = input_info.split(",")
        phone = add_list[2]
        phone_exist = False
        for d_list in data_list:
            if phone == d_list[3]:
                phone_exist = True
        if phone_exist is True:
            print("Thone phone is exist,can't add.")
            return True
        else:
            new_staff_id = auto_increment_id(data_list)
            add_list.insert(0, str(new_staff_id))
            data_list.append(add_list)
        data_to_file(struct_list, data_list, table)
    
    
    def delete(sql, struct_list, data_list, table):
        # delete 5;
        delete_flag = False
        input_info = sql_to_list(sql)
        staff_id = input_info[1]
        for d_list in data_list:
            if staff_id == d_list[0]:
                delete_flag = True
                data_list.remove(d_list)
    
        if delete_flag is not True:
            print("The staff_id is not exist,can't delete.")
        else:
            data_to_file(struct_list, data_list, table)
    
    
    def update(sql, struct_list, data_list, table):
        # update staff_table set dept = "Market" where dept = "IT"; 只允许修改age,phone,dept,enroll_date
        input_info = sql_to_list(sql)
        table_name = input_info[1]
        set_flag = input_info[2]
        modify_column = input_info[3]  # 修改的字段
        equal_flag = input_info[4]  # 等于符号
        modify_value = input_info[5]  # 修改后的值
        modify_value = check_quotes(modify_value) # 去除双引号
        where_flag = input_info[6]
        condition_column = input_info[7]  # 条件字段
        condition_str = input_info[8]  # 限制条件关键字，只支持“=”
        condition_value = input_info[9]  # 条件参数
        condition_value = check_quotes(condition_value) # 去除双引号
        modify_column_number = get_column_number(modify_column, struct_list)  # 列位置
        condition_column_number = get_column_number(condition_column, struct_list)  # 列位置
    
        modify_flag = False
        continue_flag = check_table(table, table_name)
        if continue_flag is True:
            return True
        if set_flag == "set" and equal_flag == "=" and where_flag == "where" and condition_str == "=":
            phone_exist = False
            phone = modify_value
            for d_list in data_list:
                if phone == d_list[3]:
                    phone_exist = True
            if phone_exist is True:
                print("Thone phone is exist,can't update.")
                return True
            for d_list in data_list:
                # 由于双引号问题，此处加上双引号
                if d_list[condition_column_number] == condition_value:
                    d_list[modify_column_number] = modify_value
                    modify_flag = True
            if modify_flag is not True:
                print("Not match any record!")
            else:
                data_to_file(struct_list, data_list, table)
        else:
            print("Your input is error!")
    
    
    def main():
        exit_flag = False
        table = "staff_table"
        while exit_flag is not True:
            sql = input_sql()
            action = analyze(sql)
            struct_list, data_list = file_to_data(table)
            if action == "select":
                continue_flag = select(sql, struct_list, data_list, table)
                if continue_flag is True:
                    continue    # 重新循环
            elif action == "add":
                continue_flag = add(sql, struct_list, data_list, table)
                if continue_flag is True:
                    continue    # 重新循环
            elif action == "update":
                continue_flag = update(sql, struct_list, data_list, table)
                if continue_flag is True:
                    continue
            elif action == "delete":
                delete(sql, struct_list, data_list, table)
            else:
                print("Your input error!")
    
    
    if __name__ == '__main__':
        main()

## 4.程序测试 ##

`cat staff_table`

staff_id,name,age,phone,dept,enroll_date
    1,Alex Li,22,13651054608,IT,2013-04-01
    2,Jim,25,13651058808,IT,2011-09-01
    3,Tom,29,13761054698,IT,2010-03-01
    4,Suzen,40,13957057707,Manager,2003-01-01
    5,Mark,32,13351959999,CTO,2014-08-08

`python staff_table_manager.py`

------------------------------------------------------------
    Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:help select The grammar of the select support likes: select * from staff_table;
        select name,age from staff_table where age > 22;
        select * from staff_table where dept = "IT";
        select * from staff_table where enroll_date like "2013";
    ------------------------------------------------------------
    Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:select * from staff_table;
    The select result: ############################################################ 5 rows in set staff_id name age phone dept enroll_date 1 Alex Li 22 13651054608 IT 2013-04-01 2 Jim 25 13651058808 IT 2011-09-01 3 Tom 29 13761054698 IT 2010-03-01 4 Suzen 40 13957057707 Manager 2003-01-01 5 Mark 32 13351959999 CTO 2014-08-08 ############################################################ ------------------------------------------------------------ Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:select name,age from staff_table where age > 22;
    The select result: ############################################################ 4 rows in set name age Jim 25 Tom 29 Suzen 40 Mark 32 ############################################################ ------------------------------------------------------------ Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:select * from staff_table where dept = "IT";
    The select result: ############################################################ 3 rows in set staff_id name age phone dept enroll_date 1 Alex Li 22 13651054608 IT 2013-04-01 2 Jim 25 13651058808 IT 2011-09-01 3 Tom 29 13761054698 IT 2010-03-01 ############################################################ ------------------------------------------------------------ Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:select * from staff_table where enroll_date like "2013";
    The select result: ############################################################ 1 rows in set staff_id name age phone dept enroll_date 1 Alex Li 22 13651054608 IT 2013-04-01 ############################################################ ------------------------------------------------------------ Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:help update The grammar of the update support likes: update staff_table set dept = "Market" where dept = "IT";
    ------------------------------------------------------------
    Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:update staff_table set dept = "Market" where dept = "IT";
    Done!
    ------------------------------------------------------------
    Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:select * from staff_table;
    The select result: ############################################################ 5 rows in set staff_id name age phone dept enroll_date 1 Alex Li 22 13651054608 Market 2013-04-01 2 Jim 25 13651058808 Market 2011-09-01 3 Tom 29 13761054698 Market 2010-03-01 4 Suzen 40 13957057707 Manager 2003-01-01 5 Mark 32 13351959999 CTO 2014-08-08 ############################################################ ------------------------------------------------------------ Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:update staff_table set phone = 13651054608 where name = "Mark";
    Thone phone is exist,can't update. ------------------------------------------------------------ Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:help add The grammar of the add support likes: add [Alex Li,22,13651054608,IT,2013-04-01];
    ------------------------------------------------------------
    Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:add [Alex Li,22,13651054608,IT,2013-04-01];
    Thone phone is exist,can't add.
    ------------------------------------------------------------
    Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:add [Alex Li,22,13651054666,IT,2013-04-01];
    Done!
    ------------------------------------------------------------
    Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:select * from staff_table where name = "Alex Li";
    The select result: ############################################################ 2 rows in set staff_id name age phone dept enroll_date 1 Alex Li 22 13651054608 Market 2013-04-01 6 Alex Li 22 13651054666 IT 2013-04-01 ############################################################ ------------------------------------------------------------ Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:help delete The grammar of the delete support likes: delete 5;
    ------------------------------------------------------------
    Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:delete 6 Done! ------------------------------------------------------------ Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:delete 5 Done! ------------------------------------------------------------ Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:select * from staff_table;
    The select result: ############################################################ 4 rows in set staff_id name age phone dept enroll_date 1 Alex Li 22 13651054608 Market 2013-04-01 2 Jim 25 13651058808 Market 2011-09-01 3 Tom 29 13761054698 Market 2010-03-01 4 Suzen 40 13957057707 Manager 2003-01-01 ############################################################ ------------------------------------------------------------ Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:add [Alex Li,22,13651054666,IT,2013-04-01];
    Done!
    ------------------------------------------------------------
    Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:select * from staff_table;
    The select result: ############################################################ 5 rows in set staff_id name age phone dept enroll_date 1 Alex Li 22 13651054608 Market 2013-04-01 2 Jim 25 13651058808 Market 2011-09-01 3 Tom 29 13761054698 Market 2010-03-01 4 Suzen 40 13957057707 Manager 2003-01-01 7 Alex Li 22 13651054666 IT 2013-04-01 ############################################################ ------------------------------------------------------------ Tip: Input 【help [select/update/add/delete]】 to get help. ------------------------------------------------------------ Please input SQL:quit ######################### Bye Bye ########################## Process finished with exit code 1

[ygqygq2]: http://blog.csdn.net/ygqygq2
[SouthEast]: /images/20220713/cac53dc0f3a24db2abacf2320446fca3.png