[Terry Notes] database operations on personnel list files, terry notes

[Terry Notes] database operations on personnel list files, terry notes

 

 

Original file (data has been desensitized ):

Staff_id, name, age, phone, dept, enroll_date
1. Ji Jianming, O & M
2. Liu Hailong, O & M
3. Yan peifei, 157016802, IT
4, Sun Haitao, 33,158206312, IT
5. Zhang yalei, 24, 137338110, IT
6. Zhang song, 26,136837094, IT, 2013-10-08
7. Liu wenjian, SA
8, Wei man, 18, 189108670, O & M
9, Song Xiaonan, test
10. Wu donghang, O & M
11, Li Jing, 18, 186127011, test
12, Wen Xin, Wu
13. Yu Honglin, 133232639, O & M
14. Wang Xinkai, O & M
15. gaishuang, 18,185003763, O & M
16. Zhang Zhenxing, 27,135214320, O & M
17. Hou Ziyun, 186819511, O & M
18, Zhang Yuxin, 27,101241184, O & M
19, Ji guanyu, 24, O & M
20. Li Qianlong, 24, 185105376, O & M
21, Yang, 188131922, O & M
22, Zhang zewei, 27,1029861, none, 2011-2-1

Requirements are as follows:

# The following employee information table is available: staff_table
#1, AlexLi, 22,136 51054608, IT, 2013-04-01
# Add, delete, modify, and query the employee information file
# Fuzzy query is supported. At least three types of syntax are supported:
# 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"
# Check the information. After printing the information, the number of records is displayed at the end.
# You can create new employee records and use phone as the unique key. The staff_id must be auto-incrementing.
# You can delete a specified employee information record and enter the employee id to delete it.
# The employee information can be modified. The syntax is as follows:
# UPDATE staff_table SET dept = "Market" where dept = "IT"
# Note: to fully use functions for the above requirements, please do your best to reduce repeated code!
# Select * from staff_table where age <25

Replace and delete the self-written code:

1 # query function 2 def select (column, term = [], symbol = [], option = []): 3 f = open ("staff_table", "r ", encoding = "UTF-8") 4 object = f. readline (). strip (). split (",") 5 6 # query column as * without conditions, such: select * from staff_table 7 if column = "*" and not term and not symbol and not option: 8 for line in f: 9 object = line. strip (). split (",") 10 print ("% s" % (object [0], object [1], object [2], object [3], object [4], obj Ect [5]) 11 exit () 12 13 # query column as * with conditions, such: select * from staff_table where age> 25 14 if column = "*" and term: 15 show_list = [] 16 position = object. index (term) 17 for line in f: 18 object = line. strip (). split (",") 19 if object [0]. isdigit: 20 if symbol = "=": 21 if object [position] = option. strip ('\ "'): 22 show_list.append (object) 23 elif symbol ="> ": 24 if object [position]> option. strip (' \ "'): 25 show_list.append (object) 26 elif symbol =" <": 27 if object [position] <option. strip ('\ "'): 28 show_list.append (object) 29 elif symbol =" >=": 30 if object [position]> = option. strip ('\ "'): 31 show_list.append (object) 32 elif symbol =" <= ": 33 if object [position] <= option. strip ('\ "'): 34 show_list.append (object) 35 elif symbol =" like ": 36 if option. strip ('\ "') in object [position]: 37 Show_list.append (object) 38 I = 0 39 for I, ele in enumerate (show_list ): 40 print ("% s" % (ele [0], ele [1], ele [2], ele [3], ele [4], ele [5]) 41 print ("% s bit member % s" % (I + 1, term, symbol, option )) 42 exit () 43 44 # the query column is optional without conditions, such as select name, age, dept from staff_table 45 if column! = "*" And not term and not symbol and not option: 46 column = column. split (",") 47 position_list = [] 48 show_list1 = [] 49 show_list2 = [] 50 for I in range (0, len (column): 51 position = object. index (column [I]) 52 position_list.append (position) 53 for line in f: 54 object = line. strip (). split (",") 55 for I in range (0, len (position_list): 56 show_list1.append (object [position_list [I]) 57 show_list2. Append (show_list1) 58 show_list1 = [] 59 for I in show_list2: 60 print (I) 61 exit () 62 63 # query column as your choice with conditions, such as select name, age, dept from staff_table where dept = "O & M" 64 if column! = "*" And term: 65 show_list = [] 66 position = object. index (term) 67 for line in f: 68 object_data = line. strip (). split (",") 69 if object_data [0]. isdigit: 70 if symbol = "=": 71 if object_data [position] = option. strip ('\ "'): 72 show_list.append (object_data) 73 elif symbol ="> ": 74 if object_data [position]> option. strip ('\ "'): 75 show_list.append (object_data) 76 elif symbol =" <": 77 if object_data [position] <option. strip ('\ "'): 78 show_list.append (object_data) 79 elif symbol =" >=": 80 if object_data [position]> = option. strip ('\ "'): 81 show_list.append (object_data) 82 elif symbol =" <= ": 83 if object_data [position] <= option. strip ('\ "'): 84 show_list.append (object_data) 85 elif symbol =" like ": 86 if option. strip ('\ "') in object_data [position]: 87 show_list.append (object_data) 88 column = column. split (",") 89 position_list = [] 90 show_list1 = [] 91 show_list2 = [] 92 for I in range (0, len (column): 93 position = object. index (column [I]) 94 position_list.append (position) 95 for j in range (0, len (show_list): 96 for I in range (0, len (position_list )): 97 show_list1.append (show_list [j] [position_list [I]) 98 show_list2.append (show_list1) 99 show_list1 = [] 100 for I in show_list2: 101 print (I) 102 exit () 103 104 # insert function: 105 def insert (name, age, phone, dept, enroll_date): 106 f = open ("staff_table", "r", encoding = "UTF-8 ") 107 num = 0108 for line in f: 109 object = line. strip (). split (",") 110 if phone = object [3]: 111 print ("Duplicate mobile phone number, unable to insert") 112 exit () 113 num = object [0] 114 f. close () 115 num = int (num) 116 num = num + 1117 num = str (num) 118 name = name. strip ('\ "') 119 age = str (age) 120 phone = str (phone) 121 dept = dept. strip ('\ "') 122 enroll_date = enroll_date.strip ('\"') 123 new_line = "\ n" + num + "," + name + ", "+ age +", "+ phone +", "+ dept +", "+ enroll_date124 f = open (" staff_table "," a ", encoding =" utf8 ") 125 f. write (maid) 126 f. close () 127 128 129 def update (): 130 pass131 132 def delete (): 133 pass134 135 msg = "136 select: 137 1. query 138 2. insert 139 3. update 140 4. delete 141 5. exit 142 "" 143 main_dic = {144 "1": select, 145 "2": insert, 146 "3": update, 147 "4": delete, 148 "5": exit149} 150 151 152 while True: 153 f = open ("staff_table", "r", encoding = "UTF-8 ") 154 print ("the member table is as follows ". center (40, "*") 155 for line in f: 156 object = line. strip (). split (",") 157 print ("% s" % (object [0], object [1], object [2], object [3], object [4], object [5]) 158 print (msg) 159 column = [] 160 term = [] 161 symbol = [] 162 option = [] 163 choice = input ("what do you want:") 164 if len (choice) = 0 or choice not in main_dic: continue165 if choice = "5": break166 if choice = "1": 167 select_ SQL = input ("query statement :") 168 select_ SQL _list = select_ SQL .split ("") 169 column = select_ SQL _list [1] 170 if len (select_ SQL _list)> 7: 171 term = select_ SQL _list [5] 172 symbol = select_ SQL _list [6] 173 option = select_ SQL _list [7] 174 main_dic ["1"] (column, term, symbol, option) 175 if choice = "2": 176 insert_ SQL = input ("insert statement:") 177 insert_ SQL _list = insert_ SQL .split (") 178 # insert into staff_table values (" terry ", 179 13333333, "it", "2013-1-1 1-1") 180 column_str = insert_ SQL _list [4] column_list = column_str.lstrip ("("). rstrip (")"). split (",") 181 name = column_list [0] 182 age = column_list [1] 183 phone = column_list [2] 184 dept = column_list [3] 185 enroll_date = column_list [4] 186 main_dic ["2 "] (name, age, phone, dept, enroll_date)