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

Source: Internet
Author: User

[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)

 




Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.