Python implements employee Information table additions and deletions

Program Description: Simulation to implement the SQL statement additions and deletions
The key is how to achieve this, from where to start, online code quite a lot of, this is better, it is best to draw a flowchart, so that it is more convenient to write a code, the problem can be contacted, just began to learn python, learn together

The implementation functions are as follows

[x] Fuzzy query
[x] Create employee records
[x] Delete employee records
[x] Modify employee records
Next look at someone else's code: I just started without thinking, slowly watching some of the video is 1.1 points to complete a functional module,
Write a basic framework first:

The first is to write to read the file, because the data is generally stored in the file or database, so to write this, the use of with open () processing file

At this time, I contacted the reading of the file and processed the read data in a format.

To write a Python data object how to save to a file, as well as the processing of the contact file,

The new data can be implemented
Remember to write the function module must first Test
After writing the code remember to test:

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)

Play to write delete module, as finished remember test

#!/usr/bin/env python#_*_coding:utf-8_*_import osimport sysdef file_to_data (table): "" "#把文件转换为python对象:p Aram Ta             BLE:: 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_lis T,data_list# finished writing a function module remember to test #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]) # The largest staff in the table                 _id id = 0 # Initialize if os.path.exists (file): # The self-increment ID file exists 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 = ma X_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): WI            Th 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 (). St  RIP ("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 (t MP) Continue if flag = = 1:sql_list.append (l) return sql_listdef Delete (SQL, Struct_li St, data_list, table): Delete_flag = False input_info = sql_to_list (sql) staff_id = input_info[1] for d_list i N Data_list:if d_list[0] = = Staff_id:delete_flag = True Data_list.remove (d_list) If Delete_flag is not TRUE:PR Int ("The staff_id is not exist,can ' t delete") Else:data_to_file (struct_list,data_list,table) #写完代码记得测试 #struct_li St,data_list = File_to_data ("staff_table") #table = "staff_table" #sql = "Delete 5" #delete (Sql,struct_list,data_list, Table) def check_quotes (str): "" ":p Aram Str: string to be processed: return: Returns unsigned string" "" If ' "' in str:str = St    R.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): "" "# Gets the column position:p Aram column:  Column name, only implemented here supports one: return: "" "Column_number = Struct_list.index (column) # result is a digital return column_numberdef input_s QL (): # gets input SQL Exit_flag = False while Exit_flag is not True:print ("-". Center ("-") "Print" ("Ti        P:input "Help [Select/update/add/delete]" to get help. ")        Print ("-". Center ("-")) 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 ("#") Exit_flag = True return sqldef chec K_table (table, c_table): "" "# determines if the table exists:p Aram table: Table name: return:" "" if Table! = C_table:print (" Your Input Table \033[31m{}\033[0m is not a exists, "please check!". Format (c_table)) print ("#". Center ("#")) return True # tag to continue_flagdef check_quotes (str):D EF Analyze    (SQL):Input_info = sql_to_list (SQL) # return Input_info action = input_info[0] # check: select; increase: add; change: update; Delete: Delete Retu RN Actiondef Select (SQL, Struct_list, data_list, table): # Select SQL parsing input_info = sql_to_list (sql) Select_colu  MN = 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_colum        n = 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_lis T) column_numbers.append (S_number) if "where" in sql: # Because of the double quote problem, here is a double quote where_flag = Input_inf O[4] # where condition_column = input_info[5] # condition field condition_str = input_info[6] # constraint keyword, support "=", ">= ”，"Like" etc Condition_value = input_info[7] # condition parameter Condition_value = check_quotes (condition_value) # remove double quotes        Column_number = Get_column_number (Condition_column, struct_list) # column position match_data_list = [] # match out the result, list format,                # query line, there is like, >=, = etc if Where_flag = = "where": # there is a where if condition_str = = "as":  # Data_list: # line is also list if condition_value in Line[column_number]:  # match 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_nu                Mber] > Condition_value:match_data_list.append (line) elif condition_str = = ">=": For lineIn Data_list:if Line[column_number] >= Condition_value:match_data_list.appe nd (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: # No where, fetch all rows all_line = True match_data_list = data_list # Print the result print ("The Select Result:") print ("#". Center ("#")) print ("\033[32m{}\033[0m rows in Set". Format (Len (MATC h_data_list))) If All_column is True:print ("{: >8} {: >8} {: >8} {: >8} {: >8} {: >8}". Format (*stru ct_list))) for line in Match_data_list:print ("{: >8} {: >8} {: >8} {: >8} {: >8} {: >8}".    Mat (*line)) Else:    Len_num = Len (select_column) Format_str = ' {: >8} ' * Len_num print (Format_str.format (*select_column)) for match_data_list:line_list = [] for s in Column_numbers:line_lis T.append (Line[s]) print (Format_str.format (*line_list)) print ("#". Center ("#") def update (SQL, struct_list   , Data_list, table): # update staff_table Set dept = "Market" WHERE dept = "IT"; Only allow modification of 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] # Modified field equal_flag = input_info[4] # equals symbol Modify_v Alue = input_info[5] # modified Value Modify_value = Check_quotes (modify_value) # remove double quotes Where_flag = input_info[6] Condi  Tion_column = input_info[7] # condition field condition_str = input_info[8] # constraint keyword, only "=" Condition_value = input_info[9]  # condition Parameter Condition_value = check_quotes (condition_value)# Remove double Quotes modify_column_number = Get_column_number (Modify_column, struct_list) # column Position Condition_column_number = Get_co Lumn_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 cond Ition_str = = "=": Phone_exist = False phone = Modify_value for d_list in Data_list:if Pho NE = = d_list[3]: phone_exist = True if Phone_exist is True:print ("Thone phone is exist,c            An ' t update. ") Return True for D_list in Data_list: # Because of the double quote question, here is a double quote if d_list[condition_column_number] = = Condition_value:d_list[modify_column_number] = Modify_value Modify_flag = True if mo        Dify_flag is not true:print ("no 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 was 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 # re-cycle elif action = = "Add": Continue_flag = Add (sql, struct_list, data_list, table) If Continue_flag is true:continue # recycle elif action = = "Update": CONTINUE_FLA g = Update (SQL, struct_list, data_list, table) if Continue_flag is True:continue elif ac          tion = = "Delete": Delete (sql, struct_list, data_list, table) Else:  Print ("Your input error!") if __name__ = = ' __main__ ': Main ()

Original link

Python implements employee Information table additions and deletions