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的博客地址

1.程序说明

实现功能如下

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

2.基本流程图

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]                         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