python實現員工資訊表增刪改查

標籤：python實現員工資訊表增刪改查   python實現員工資訊表   員工資訊表增刪改查   增刪改查   

程式說明：類比實現sql語句的增刪改查
關鍵是怎麼去實現這個事情，從哪兒下手，網上的代碼挺多的，這個比較好，最好自己畫一個流程圖，這樣寫起來就比較方便，自己寫了一遍代碼，有問題的可以聯絡，剛開始學習python，共同學習

實現功能如下

[x] 模糊查詢
[x] 建立員工紀錄
[x] 刪除員工紀錄
[x] 修改員工紀錄
說下看別人寫的代碼：自己剛開始也沒有思路，慢慢的看一些視頻就是一點 一點的完成一個功能模組，
先寫一個基礎的架構：

最開始就是寫的讀檔案，因為資料一般都存放在檔案或者資料庫，所以要先寫這個，就用到了with open()處理檔案

這個時候寫完了就聯絡了檔案的讀及把讀取的資料按格式處理，

玩了就寫python的資料對象如何儲存到檔案，一樣也是聯絡檔案的處理，

這樣新增資料就可以實現了
記得寫完功能模組一定要先測試
寫完代碼記得測試:

test_list = "add [Alex Li,22,13651054666,IT,2013-04-01]"struct_list,data_list = file_to_data("staff_table")table = "staff_table"add(test_list,struct_list,data_list,table)

玩了就寫刪除模組，一樣寫完了記得測試

#!/usr/bin/env python#_*_coding:utf-8_*_import osimport sysdef file_to_data(table):    """    #把檔案轉換為python對象    :param table:    :return:    """    num = 0    data_list = []    with open(table,"r+",encoding="utf-8") as f:        for line in f:            line = line.strip()            line_list = line.split(",")            if num == 0:                struct_list = line_list            else:                data_list.append(line_list)            num = num + 1        return  struct_list,data_list#寫完一個功能模組記得可以先測試一下#struct_list,data_list = file_to_data("staff_table")#print(struct_list)#print(data_list)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_iddef data_to_file(struct_list, data_list, table):    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 add(sql, struct_list, data_list, table):    # sql: Alex Li,22,13651054608,IT,2013-04-01    input_info = sql.strip().strip("add [").strip("]")    new_list = input_info.split(",")    phone = new_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("The phone is exists ,can‘t Add !!!")        return True    else:        new_staff_id = auto_increment_id(data_list)        new_list.insert(0,str(new_staff_id))        data_list.append(new_list)    data_to_file(struct_list,data_list,table)#寫完代碼記得測試#test_list = "add [Alex Li,22,13651054666,IT,2013-04-01]"#struct_list,data_list = file_to_data("staff_table")#table = "staff_table"#add(test_list,struct_list,data_list,table)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_listdef delete(sql, struct_list, data_list, table):    delete_flag = False    input_info = sql_to_list(sql)    staff_id = input_info[1]    for d_list in data_list:        if d_list[0] == staff_id:            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)#寫完代碼記得測試#struct_list,data_list = file_to_data("staff_table")#table = "staff_table"#sql="delete 5"#delete(sql,struct_list,data_list,table)def check_quotes(str):    """    :param str: 需要處理的字串    :return: 返回無符號的字串    """    if ‘"‘ in str:        str = str.strip(‘"‘)    return strdef print_help():    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\";")    print("\tadd [Alex Li,22,13651054608,IT,2013-04-01];")    print("\tupdate staff_table set dept = \"Market\" where dept = \"IT\";")    print("\tdelete 5;")def get_column_number(column, struct_list):    """    # 擷取列位置    :param column:  列名，此處只實現支援一個    :return:    """    column_number = struct_list.index(column)  # 結果為數字    return column_numberdef 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, "-"))        print_help()        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 sqldef 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_flagdef check_quotes(str):def analyze(sql):    input_info = sql_to_list(sql)    # return input_info    action = input_info[0]  # 查：select；增：add；改：update；刪：delete    return actiondef 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 = []        print(select_column)        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 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]    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!")#寫完代碼記得測試#sql = input()#struct_list,data_list = file_to_data("staff_table")#table = "staff_table"#update(sql,struct_list,data_list,table)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()

原文連結

python實現員工資訊表增刪改查