python simulated sql statement to add delete change and check the employee table

This paper mainly introduces the contents related to the python simulation sql statement to add, delete, modify and check the employee table, and share them for your reference and study. Let's start from 1 to see the detailed introduction:

Specific requirements:

Employee information table program to realize the operation of adding, deleting, modifying and checking:

Can be fuzzy query, syntax support the following three:

select name,age from staff_data where age > 22 Multiple query parameters name,age split with ','

select * from staff_data where dept = 人事

select * from staff_data where enroll_date like 2013

After the information is printed, the number of items to be found is displayed at the end

New employee records can be created with phone as the only key. When phone exists, it will be prompted. staff_id needs to be added

insert into staff_data values record1/record2

You can delete the designated employee information record by entering employee id

delete from staff_data where staff_id>=5andstaff_id<=10

Employee information can be modified with the following syntax:

update staff_table set dept=Market,phone=13566677787  where dept = 运维 Multiple set values are split with ','

Use re module,os module, full use of functions to simplify code, skilled use str.split() To parse the formatted string

As a result, several key strings in the sql command have a definite pattern, appear only once, and in order!!

In accordance with the key_lis = ['select', 'insert', 'delete', 'update', 'from', 'into', 'set', 'values', 'where', 'limit'] Element order of sql.

The split element is put into the dictionary as key of the sql_dic dictionary. The split list is b if len(b) > 1, indicates that sql string contains segmentation element, and b[0] corresponds to the value of the previous segmentation element, and b[-1] is the next segmentation object!

This iterates until the sql is broken by all the split elements that appear, but notice that each loop is split first and then assigned!! The value of the current split element such as 'select' needs to wait until the next split element

For example, 'from' performs the split list b, where the value of b[0] is assigned to sql_dic['select'] , so the value of the last segmentation element cannot be completed through the above loop, must be processed first may be the last segmentation element, and then the normal loop!!

In this sql statement,' limit','values', 'where', which may be the last split element, process 'limit' first, then 'values' or 'where', in order of priority...

After processing to get sql_dic, that is, you according to different command execution, to the data file add, delete, change check, finally return processing results!!

The sample code

# _*_coding:utf-8_*_
# Author:Jaye He
import re
import os


def sql_parse(sql, key_lis):
 '''
  parsing sql Command string , In accordance with the key_lis The element split in the list sql Get the command in dictionary form sql_dic
 :param sql:
 :param key_lis:
 :return:
 '''
 sql_list = []
 sql_dic = {}
 for i in key_lis:
  b = [j.strip() for j in sql.split(i)]
  if len(b) > 1:
   if len(sql.split('limit')) > 1:
    sql_dic['limit'] = sql.split('limit')[-1]
   if i == 'where' or i == 'values':
    sql_dic[i] = b[-1]
   if sql_list:
    sql_dic[sql_list[-1]] = b[0]
   sql_list.append(i)
   sql = b[-1]
  else:
   sql = b[0]
  if sql_dic.get('select'):
   if not sql_dic.get('from') and not sql_dic.get('where'):
    sql_dic['from'] = b[-1]
 if sql_dic.get('select'):
  sql_dic['select'] = sql_dic.get('select').split(',')
 if sql_dic.get('where'):
  sql_dic['where'] = where_parse(sql_dic.get('where'))
 return sql_dic


def where_parse(where):
 '''
  formatting where String is a list where_list, with 'and', 'or', 'not' Split string 
 :param where:
 :return:
 '''
 casual_l = [where]
 logic_key = ['and', 'or', 'not']
 for j in logic_key:
  for i in casual_l:
   if i not in logic_key:
    if len(i.split(j)) > 1:
     ele = i.split(j)
     index = casual_l.index(i)
     casual_l.pop(index)
     casual_l.insert(index, ele[0])
     casual_l.insert(index+1, j)
     casual_l.insert(index+2, ele[1])
     casual_l = [k for k in casual_l if k]
 where_list = three_parse(casual_l, logic_key)
 return where_list


def three_parse(casual_l, logic_key):
 '''
  Working with temporary lists casual_l Specific conditions ,'staff_id>5'-->['staff_id','>','5']
 :param casual_l:
 :param logic_key:
 :return:
 '''
 where_list = []
 for i in casual_l:
  if i not in logic_key:
   b = i.split('like')
   if len(b) > 1:
    b.insert(1, 'like')
    where_list.append(b)
   else:
    key = ['<', '=', '>']
    new_lis = []
    opt = ''
    lis = [j for j in re.split('([=<>])', i) if j]
    for k in lis:
     if k in key:
      opt += k
     else:
      new_lis.append(k)
    new_lis.insert(1, opt)
    where_list.append(new_lis)
  else:
   where_list.append(i)
 return where_list


def sql_action(sql_dic, title):
 '''
  Parse it out sql_dic Distribute to the corresponding function for processing 
 :param sql_dic:
 :param title:
 :return:
 '''
 key = {'select': select,
   'insert': insert,
   'delete': delete,
   'update': update}
 res = []
 for i in sql_dic:
  if i in key:
   res = key[i](sql_dic, title)
 return res


def select(sql_dic, title):
 '''
  To deal with select Statement command 
 :param sql_dic:
 :param title:
 :return:
 '''
 with open('staff_data', 'r', encoding='utf-8') as fh:
  filter_res = where_action(fh, sql_dic.get('where'), title)
  limit_res = limit_action(filter_res, sql_dic.get('limit'))
  search_res = search_action(limit_res, sql_dic.get('select'), title)
 return search_res


def insert(sql_dic, title):
 '''
  To deal with insert Statement command 
 :param sql_dic:
 :param title:
 :return:
 '''
 with open('staff_data', 'r+', encoding='utf-8') as f:
  data = f.readlines()
  phone_list = [i.strip().split(',')[4] for i in data]
  ins_count = 0
  if not data:
   new_id = 1
  else:
   last = data[-1]
   last_id = int(last.split(',')[0])
   new_id = last_id+1
  record = sql_dic.get('values').split('/')
  for i in record:
   if i.split(',')[3] in phone_list:
    print('\033[1;31m%s  The cell phone number already exists \033[0m' % i)
   else:
    new_record = '%s,%s\n' % (str(new_id), i)
    f.write(new_record)
    new_id += 1
    ins_count += 1
  f.flush()
 return ['insert successful'], [str(ins_count)]


def delete(sql_dic, title):
 '''
  To deal with delete Statement command 
 :param sql_dic:
 :param title:
 :return:
 '''
 with open('staff_data', 'r', encoding='utf-8') as r_file,\
   open('staff_data_bak', 'w', encoding='utf-8') as w_file:
  del_count = 0
  for line in r_file:
   dic = dict(zip(title.split(','), line.split(',')))
   filter_res = logic_action(dic, sql_dic.get('where'))
   if not filter_res:
    w_file.write(line)
   else:
    del_count += 1
  w_file.flush()
 os.remove('staff_data')
 os.rename('staff_data_bak', 'staff_data')
 return ['delete successful'], [str(del_count)]


def update(sql_dic, title):
 '''
  To deal with update Statement command 
 :param sql_dic:
 :param title:
 :return:
 '''
 set_l = sql_dic.get('set').strip().split(',')
 set_list = [i.split('=') for i in set_l]
 update_count = 0
 with open('staff_data', 'r', encoding='utf-8') as r_file,\
   open('staff_data_bak', 'w', encoding='utf-8') as w_file:
  for line in r_file:
   dic = dict(zip(title.split(','), line.strip().split(',')))
   filter_res = logic_action(dic, sql_dic.get('where'))
   if filter_res:
    for i in set_list:
     k = i[0]
     v = i[-1]
     dic[k] = v
    line = [dic[i] for i in title.split(',')]
    update_count += 1
    line = ','.join(line)+'\n'
   w_file.write(line)
  w_file.flush()
 os.remove('staff_data')
 os.rename('staff_data_bak', 'staff_data')
 return ['update successful'], [str(update_count)]


def where_action(fh, where_list, title):
 '''
  The specific process where_list All of the conditions in 
 :param fh:
 :param where_list:
 :param title:
 :return:
 '''
 res = []
 if len(where_list) != 0:
  for line in fh:
   dic = dict(zip(title.split(','), line.strip().split(',')))
   if dic['name'] != 'name':
    logic_res = logic_action(dic, where_list)
    if logic_res:
     res.append(line.strip().split(','))
 else:
  res = [i.split(',') for i in fh.readlines()]
 return res
 pass


def logic_action(dic, where_list):
 '''
  Determine each in the data file 1 Does the bar match where_list conditions 
 :param dic:
 :param where_list:
 :return:
 '''
 logic = []
 for exp in where_list:
  if type(exp) is list:
   exp_k, opt, exp_v = exp
   if exp[1] == '=':
    opt = '=='
   logical_char = "'%s'%s'%s'" % (dic[exp_k], opt, exp_v)
   if opt != 'like':
    exp = str(eval(logical_char))
   else:
    if exp_v in dic[exp_k]:
     exp = 'True'
    else:
     exp = 'False'
  logic.append(exp)
 res = eval(' '.join(logic))
 return res


def limit_action(filter_res, limit_l):
 '''
  Use list sharding to show the number of eligible items 
 :param filter_res:
 :param limit_l:
 :return:
 '''
 if limit_l:
  index = int(limit_l[0])
  res = filter_res[:index]
 else:
  res = filter_res
 return res


def search_action(limit_res, select_list, title):
 '''
  Process what needs to be queried and displayed title And corresponding data 
 :param limit_res:
 :param select_list:
 :param title:
 :return:
 '''
 res = []
 fields_list = title.split(',')
 if select_list[0] == '*':
  res = limit_res
 else:
  fields_list = select_list
  for data in limit_res:
   dic = dict(zip(title.split(','), data))
   r_l = []
   for i in fields_list:
    r_l.append((dic[i].strip()))
   res.append(r_l)
 return fields_list, res


if __name__ == '__main__':
 with open('staff_data', 'r', encoding='utf-8') as f:
  title = f.readline().strip()
 key_lis = ['select', 'insert', 'delete', 'update', 'from', 'into', 'set', 'values', 'where', 'limit']
 while True:
  sql = input(' Please enter the sql The command , To exit, please enter exit:').strip()
  sql = re.sub(' ', '', sql)
  if len(sql) == 0:continue
  if sql == 'exit':break
  sql_dict = sql_parse(sql, key_lis)
  fields_list, fields_data = sql_action(sql_dict, title)
  print('\033[1;33m The results are as follows :\033[0m')
  print('-'.join(fields_list))
  for data in fields_data:
   print('-'.join(data))

conclusion