Regular Expression practice-employee information table and regular expression information table

Regular Expression practice-employee information table and regular expression information table

Import re
Import json
'''
Only staff_id, staff_name, age, and phone fields are set in the employee table.
Phone is unique.
1. query supports select * from staff_table where age <100; select staff_name, age from staff_table where staff_name = "diaochan ";

select staff_name,age from staff_table where age > 10;select * from staff_table where staff_name like "xiang";

2. update staff_table set staff_name = "zhangsan" where staff_name = "xiangyu)
3. Due to cutting, age> 22 must be separated by Spaces
'''
Select = re. compile (R' (select. + from staff_table where. + \ s * \ =? \>? \ <? (Like )? \ S *. +) ') # SQL regular expression to check whether the query SQL meets the rules
Modify = re. compile (r'update staff_table set. + \ s * = \ s *\"*. + \ "* where. + \ s * = \ s *\"*. + \ "* ', re. i) # determine whether the updated SQL statement meets the rules
Seek = re. compile (R' [^ select] \ s *(? P <key> \ * {1}) | [^ select] \ s *(? P <key1> (\ w + \, {1} \ w +) {1}) ') # match the content * or field after select
Sn = re. compile (R' (\ w + \ s + \> + \ s + \"? \'? \ W + \"? \'?) | (\ W + \ s + \=+ \ s + \"? \'? \ W + \"? \'?) | (\ W + \ s + \ <+ \ s + \"? \'? \ W + \"? \'?) | (\ W + \ s * (like) + \ s *\"? \'? \ W + \"? \'?) ') # Match <,>, =, and like before and after the content name like "zhang"
Up = re. compile (R '(? P <sn> (\ w + \ s * \ = \ s * \ w +) ') # matching = rule
Rm = re. compile (R' [^ "] \ w * ') # rules for double quotation marks Removal

Def re_ SQL (SQL ):
'''
This function is used to determine the Regular Expression of SQL statements.
: Param SQL:
: Return:
'''
Tf = select. search (SQL)
Tf1 = modify. search (SQL)
If tf or tf1:
Return True
Else:
Return False

Def find_staff (SQL ):
'''
This function allows you to query employees and supports select name, age from staff_table where age <100
Select * from staff_table where name = "yuji"
Select * from staff_table where name like xiang
: Param SQL:
: Return:
'''
Staff = load_staff () # Load all user information in the file
Count = 0
You_need = seek. search (SQL). groupdict ()
If you_need ["key"]! = None: # If it matches *
You_sn = sn. search (SQL). group ()
Res = re. split (R' \ s', you_sn)
For temp in res:
If "like" in temp: # if the match is like
For st in staff:
If rm. search (res [2]). group () in st [res [0]:
Print (st)
Count + = 1
Elif "=" in temp: # If the match is =
For st in staff:
If rm. search (res [2]). group () = st [res [0]:
Print (st)
Count + = 1
Elif ">" in temp: # If the match is>
For st in staff:
# If int (re. search (R' [^ \ "?] \ W + [^ \ "?] ', Res [2]). group ()> int (st [res [0]):
If int (re. search (R' [^ \ "?] \ W * ', res [2]). group () <int (st [res [0]):
Print (st)
Count + = 1
Elif "<" in temp: # If it matches <
For st in staff:
If rm. search (res [2]). group ()> st [res [0]:
Print (st)
Count + = 1
Elif you_need ["key1"]! = None: # If the matching fields are age, staff_name
You_sn = sn. search (SQL). group ()
Res = re. split (R' \ s', you_sn)
U_key = re. split (R' \, ', you_need ["key1"])
For temp in res:
If "like" in temp:
For st in staff:
If rm. search (res [2]). group () in st [res [0]:
Print (st [u_key [1], st [u_key [0])
Count + = 1
Elif "=" in temp:
For st in staff:
If rm. search (res [2]). group () = st [res [0]:
Print (st [u_key [1], st [u_key [0])
Count + = 1
Elif ">" in temp:
For st in staff:
If int (re. search (R' [^ \ "?] \ W * ', res [2]). group ()> int (st [res [0]):
Print (st [u_key [1], st [u_key [0])
Count + = 1
Elif "<" in temp:
For st in staff:
If rm. search (res [2]). group () <st [res [0]:
Print (st [u_key [1], st [u_key [0])
Count + = 1

Print ("% d items of data in total" % count) # print the number of items queried

Def add_staff ():
'''
This function allows you to add an employee. You need to enter the employee name, age, and mobile phone number. The mobile phone number is unique and the name is entered in English.
: Return:
'''
Staff_list = []
Staff_dict = {}
Name = input ("Enter employee name >>> ")
Age = input ("Enter employee age >>> ")
Phone = input ("Enter the employee's mobile phone number >>>> ")
Staff = load_staff ()
For I in range (len (staff )):
If staff [I] ["phone"] = phone: # Check whether the number is repeated.
Print ("mobile phone numbers cannot be repeated ")
Exit ()
Staff_id = staff [I] ["staff_id"]
Staff_list.append (staff [I])
Staff_id = str (int (staff_id) + 1) # staff_id auto-Increment
Staff_dict ["staff_id"] = staff_id
Staff_dict ["staff_name"] = name
Staff_dict ["age"] = age
Staff_dict ["phone"] = phone
Staff_list.append (staff_dict) # Only one row is allowed during json serialization, so information is saved in the list.
Dump_staff (staff_list)
Print ("added successfully ")

Def remove_staff (id ):
'''
This function deletes an employee and uses the employee id
: Param id:
: Return:
'''
Staff_list = []
Staff = load_staff ()
Flag = 0
For I in range (len (staff )):
If staff [I] ["staff_id"] = id: # if the input id is found, the cycle is exceeded and the list is not added.
Flag = 1
Continue
Staff_list.append (staff [I])
If flag = 0: # if the output does not match the employee whose id does not exist
Print ("employees without id: % s" % id)
Else:
Dump_staff (staff_list) # Otherwise, save the information in json.

Def modify_staff (SQL ):
'''
Update staff_table set staff_name = "xiangyu" where staff_name = "jack"
: Param SQL:
: Return:
'''
Staff_list = []
Staff = load_staff ()
S = re. findall (R' \ w + \ s * \ = \ s *\"? \ W + \"? ', SQL)
If s! = None:
Ps = re. split (R' \ s * \ =? \ S * ', s [0])
Rs = re. split (R' \ s * \ =? \ S * ', s [1])
For m in range (len (staff )):
If staff [m] [ps [0] = rm. search (rs [1]). group ():
Staff [m] [ps [0] = rm. search (ps [1]). group ()
Staff_list.append (staff [m])
Dump_staff (staff_list)
Print ("employee information modified ")

Def dump_staff (s ):
With open ("employee info table", "w", encoding = 'utf-8') as f:
Json. dump (s, f)

Def load_staff ():
With open ("employee info table", encoding = 'utf-8') as f:
Return json. load (f)

Def print_info ():
Info = '''
* ********* Welcome to the employee information center ***********
1. query employees
2. New Employees
3. Modify employees
4. Delete employees
5. Exit
'''
Print (info)

Def main ():
Print_info ()
Choise = input ("Enter the operation >>> ")
If choise = "1 ":
SQL = input ("Enter the corresponding SQL >>> ")
Result = re_ SQL (SQL)
If result:
Find_staff (SQL)

Else:
Print ("SQL syntax error ")
Elif choise = "2 ":
Add_staff ()
Elif choise = "3 ":
SQL = input ("Enter the corresponding SQL >>> ")
Result = re_ SQL (SQL)
If result:
Modify_staff (SQL)
Else:
Print ("SQL syntax error ")
Elif choise = "4 ":
Id = input ("Enter the employee id to be deleted >>> ")
Remove_staff (id)
Elif choise = "5 ":
Exit ("exit system ")
Else:
Print ("incorrect input. Please enter again ")



While True:
Main ()