Python Operations Employee Information sheet
- Requirements:
For fuzzy queries, the syntax supports at least 3 of the following:
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"
Find the information, after printing, the last side to show the number of found
Can create a new employee record, with the phone to do unique keys, staff_id need to increase
Delete a specified employee information record, enter an employee ID, and delete
To modify employee information, the syntax is as follows:
UPDATE staff_table SET dept= "Market" where where dept = "IT"
Note: The above requirements, to fully use the function, please do your utmost to reduce duplication of code
- Reach the requirements:
- There can be any number of spaces between the words of the query command
- Do not set a condition when querying (where statement) can also be executed
- Error message highlighting
- Code:
#! /usr/bin/env python3# author:jaillyimport pickle,re# Find def search (search_condition): res = [] # Store Find results # Where condition saved In the IF search_condition: # three conditional formats M1 = Re.search (R ' age\s+ (>|<) \s+ (0| ( [1-9]\d*)] ', search_condition) m2 = Re.search (r "' (? P<category> (staff_id) | (name) | (age) | (phone) | (dept) | (enroll_date)) \s+=\s+ (? p<value>.*) ", Search_condition, re. X) m3 = Re.search (R "" (? P<category> (staff_id) | (name) | (age) | (phone) | (dept) | (enroll_date)) \s+like\s+ (? p<value>[1-9]\d*) ", Search_condition, re. X) # Age <|> \d Case if M1: # Filter entry for I in Staff_table:info_dict = Dict (Zip ([' staff_id ', ' name ', ' age ', ' phone ', ' dept ', ' Enroll_date '], i)) if Eval (M1.group (). replace (' AG E ', str (i[2])): Res.append (info_dict) # category = \w+ case elif m2: # Filter entries for I in staff_table: # Dictionary of each entry in the corresponding information table info_dict = dict (Zip ([' s taff_id ', ' name ', ' age ', ' phone ', ' dept ', ' Enroll_date '], i)) if Str (info_dict[m2.group (' category ')]) = = m2 . Group (' value '). Strip (): Res.append (info_dict) # category like \w+ case elif M3: For i in staff_table:info_dict = Dict (Zip ([' staff_id ', ' name ', ' age ', ' phone ', ' dept ', ' enroll_date '], i) # corresponds to the dictionary of each entry if Info_dict[m3.group (' category ')].count (M3.group (' value ')) or STR (info_dict[m3.group (' category ')]). Count (Str (m3.group (' value))): Res.append (info_dic T) # condition format is incorrect else:print (' \033[1;31mwhere statement format Error! \033[0m ') # no WHERE Condition else:for i in staff_table:info_dict = Dict (Zip ([' staff_id ', ' name ', ' age ', ' Phone ', ' dept ', ' Enroll_date '], i)) Res.append (info_dict) Return res# display Find Results def show (res, search_categories): If Res:for i in res: # query all Entries * I F Search_categories.strip () = = ' * ': print (', '. Join ([Str (i[' staff_id '), i[' name '), str (i[' age ']), i[' phone ' ], i[' dept '], i[' enroll_date '])) # Query specified entry name,age,... else:category_list = Search_ Categories.split (', ') for J in Category_list:print (I[j.strip ()], end= ', ') Print () print (' \ r \ n ' found%d records \ n '% len (res)) # Save File Def save (): With open (' Staff_table.pkl ', ' WB ') as F:pic Kle.dump (staff_table, f) if __name__ = = ' __main__ ': While 1:with open (' staff_table.pkl ', ' RB ') as F: Staff_table = Pickle.load (f) cmd = input (' command-and ') # query instruction Search_cmd = Re.search (R ' " ^\s*select\s+ (? p<categories>\*| ((staff_id) | (name) | (age) | (phone) | (dept) | (enroll_date)) (\S*,\s* ((staff_id) | ( Name) | (age) | (phone) | (dept) | (enroll_date)))? (\s*,\s* (staff_id) | ( Name) | (age) | (phone) | (dept) | (enroll_date)))? (\s*,\s* (staff_id) | ( Name) | (age) | (phone) | (dept) | (enroll_date)))? (\s*,\s* (staff_id) | ( Name) | (age) | (phone) | (dept) | (enroll_date)))?) ) \s+from\s+staff_table (\s+where\s+ (? p<condition>.*))? $ ' ', cmd,re. X) # add directive Insert_cmd = Re.search (r "' ^\s*[\w]+, # name [1-9]\d*, # Age \d{ 11}, # Mobile number [\w]+, # Department (20) | ( )) \d{2}-((10) | ( 11) | (12) | (0[1-9])) -(([0-2][1-9]) | (3[01])) $ # Entry Date ', Cmd,re. X) # Delete Instruction Delete_cmd = Re.search (R ' ^\d+$ ', cmd) # change directive Update_cmd = Re.search (R ' ^ Update\s+staff_table\s+set\s+ (? P<category_update> (name) | (age) | (phone) | (dept) | (enroll_date)) \s+ =\s+ (' | ') (? p<new_value>.*) (' | ') (\s+where\s+ (? p<condition>.*))? $ ' ', cmd,re. X) # query If search_cmd:search_categories = Search_cmd.group (' categories ') Search_condi tion = search_cmd.group (' condition ') res = search (search_condition) show (res,search_categories) # add Elif insert_cmd:insert_list = Insert_cmd.group (). Split (', ') Repeat_flag = 0 For i in staff_table:if insert_list[2] = = I[3]: print (' \033[1;31m mobile number already exists!\033[0m ') Repeat_flag = 1 Break else:insert_list.insert (0, (Staff_ta Ble[-1][0] + 1)) Staff_table.append (insert_list) Save () print (' Add success! ') # Delete elif delete_cmd:for i in Staff_table:if delete_cmd.group () = = str (i[0]): Staff_table.remove (i) Save () print (' DeleteExcept for success! ') Break Else:print (' \033[1;31m the staff_id does not exist \033[0m ') # Modify Elif Update_cmd:search_condition = Update_cmd.group (' condition ') res = search (search_condition) For I in range (len (staff_table)): For J in res:if staff_table[i][0] = = j[' staff_id ' ]: Category_update = Update_cmd.group (' category_update ') New_value = Update_ Cmd.group (' New_value '). Strip () j[category_update] = Int (new_value) If category_update = = ' age ' else New_value # Guaranteed order, cannot be used with list (J.values ()) staff_table[i] = list ([j[' staff_id '],j [' Name '],j[' age '],j[' phone '],j[' dept '],j[' Enroll_date ']] Save () print (' modified successfully! ') # exit elif cmd = = ' Q ': Break # Input Malformed case else:print (' \033[1;31m input format is incorrect False \033[0m ')
Python Practice _module02-1-Employee Information Sheet