模拟MySQL命令

staff_table

1,Alex Li,22,13651054608,IT,2013-04-01
2,Jack Wang,30,13304320533,HR,2015-05-03
3,Rain Liu,25,1383235322,Saies,2016-04-22
4,Mmck Cao,40,1356145343,HR,2009-03-01

+---------------------------------------READ ME-------------------------------------------------+
|   注：命令及关键字请使用小写
|       本代码为模拟简单的 MySQL 增删改查的功能，可参考给的例子，自行输入
+-----------------------------------------------------------------------------------------------+
|   * 的位置表示显示范围，也可以输入表头名字，只打印这几列
|  范围 的位置，表示查找（修改）的条件，可以用and连接多个参数
|   (field1,field2) 表示你增加时需要写入的列的名字， phone 必选
|   values(value1,value2) 表示你增加时需要写入的列的数值
+-----------------------------------------------------------------------------------------------+
|   查找：select * from staff_table where 范围
|        :select * from staff_table where age > 22 and dept = "HR" and enroll_date like "2009"
|        :select name,age from staff_table where age > 22
+-----------------------------------------------------------------------------------------------+
|   插入：insert into staff_table (field1,field2) values(value1,value2)
|        :insert into staff_table (name,age,phone,dept) values ('jia',22,15246512541,'IT')
+-----------------------------------------------------------------------------------------------+
|   删除：delete from staff_table where 范围
|        :delete from staff_table where staff_id = 3
+-----------------------------------------------------------------------------------------------+
|   更新：update staff_table set field1=value1 where 范围
|        :update staff_table set age = 23 where staff_id = 1
+-----------------------------------------------------------------------------------------------+
  退出：q    再看一遍说明：r

low_B的代码

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "Always"
# Date: 2017/6/16
import time

file_path = r'staff_table'
readme_path = r'readme.txt'
all_tittle = ['staff_id', 'name', 'age','phone', 'dept', 'enroll_date']
func_dic = {}

def add_func_dic(keys):
    def deco(func):
        func_dic[keys] = func
    return deco

def resolve_cmd(cmd):
    """
    分解命令
    这个函数，主要是用来处理接收的 select 命令，将各个参数提取出来，放到一个字典中
    :param cmd:  这个参数是用户输入的，命令，格式为字符串形式
    :return:
    """
    cmd_dict = {'show_tittle':None, 'file_name':None, 'select_condetion':None, 'cmd':None}
    cmd_deal = cmd.split('from')[0].split()
    cmd_dict['cmd'] = cmd_deal.pop(0).strip()
    cmd_deal = ''.join(cmd_deal).split(',')
    cmd_dict['show_tittle'] = all_tittle if cmd_deal[0].strip() == "*" else cmd_deal
    filename_select = cmd.split('from')[1].strip().split('where')
    cmd_dict['file_name'] = filename_select[0].strip()
    if len(filename_select) > 1:
        cmd_dict['select_condetion'] = filename_select[1].strip()
    return cmd_dict

def deal_select_condetion(select_condetion):
    """
    主要用来处理后面的条件有 like 的情况，为筛选的条件重新排序
    :param select_condetion:
    :return:
    """
    select_condetion = select_condetion.split('and')
    for i in range(len(select_condetion)):
        if 'like' in select_condetion[i]:
            select_condetion[i] = select_condetion[i].split()
            select_condetion[i].reverse()
            select_condetion[i] = ' '.join(select_condetion[i])
    return ' and '.join(select_condetion).replace('like', 'in')


def return_select_result(total_count, all_tittle, show_tittle, d):
    """
    这个函数主要用来处理打印满足条件的行数和列数
    :param total_count: 用来计算一共打印了多少行
    :param all_tittle: 所有的表头，用来固定打印顺序
    :param show_tittle: 代表需要打印的列
    :return: 用来计算一共打印了多少行
    """
    for tittle in all_tittle:
        if tittle in show_tittle:
            print('%-14s' % d[tittle], end='')
    print()
    total_count += 1
    return total_count

@add_func_dic('select')
def what_a_funck(cmd):
    cmd_dict = resolve_cmd(cmd)
    select(cmd_dict['file_name'], cmd_dict['select_condetion'], cmd_dict['show_tittle'])

def select(file_name, select_condetion, show_tittle):
    """
    这个功能用来查找，并打印符合要求的字段
    :param file_name
    :param select_condetion
    :param show_tittle
    :return:
    """
    file_info_list = get_file_info_to_list(file_name)

    for tittle in all_tittle:                         # 打印表头
        if tittle in show_tittle:
            print('33[44;1m%-14s' % tittle, end='')
    print('33[0m')
    total_count = 0

    if select_condetion:                   # 如果有筛选条件的话，按条件打印，没有的话，全部打印
        select_condetion = select_condetion.replace('=', '==')  # 如果判断条件有一个 ’=‘ 就把他变成两个，方便后面 eval 计算
        if 'like' in select_condetion:  # 如果判断条件有 like 就把   enroll_date like "2013"  改成 “2013” in enroll_date ，方便后面 eval 计算
            select_condetion = deal_select_condetion(select_condetion)


        for d in file_info_list:                          # 遍历 用户信息 列表
            globals().update(d)                           # 将字典中所有内容变为变量，用来处理条件
            show_table_tittle = False
            if eval(select_condetion):                    # 如果满足输入的条件，那么打印信息

                total_count = return_select_result(total_count,all_tittle,show_tittle, d)
    else:                                                 # 如果用户没有输入筛选条件，执行这里
        for d in file_info_list:
            total_count = return_select_result(total_count, all_tittle, show_tittle, d)
    print('33[1mtotal count: %s33[0m'%total_count)

def get_file_info_to_list(file_path):               # 将文件内容取出来，放入 [{},{}] 中
    with open (file_path,encoding='utf-8') as f:
        return [{'staff_id':int(line.strip().split(',')[0]), 'name':line.strip().split(',')[1], 'age':int(line.strip().split(',')[2]), 'phone':line.strip().split(',')[3], 'dept':line.strip().split(',')[4], 'enroll_date':line.strip().split(',')[5]} for line in f]


def insert_split(cmd):                                 # 插入功能的前戏
    """
    插入功能的命令分解。。。
    :param cmd: 用户输入的命令
    :return: 包含分解的命令的字典
    """
    cmd_dict = {}
    cmd_split = cmd.split()
    if cmd_split[1] != 'into':
        return
    cmd_dict['file_name'] = cmd_split[2]
    cmd_split = ''.join(cmd_split[3:]).split('values')
    cmd_dict['keys'] = cmd_split[0].strip('(').strip(')').split(',')
    cmd_dict['values'] = cmd_split[1].strip('(').strip(')').split(',')
    return cmd_dict

@add_func_dic('insert')
def insert(cmd, all_tittle = all_tittle, file_path = file_path):               # 插入功能的开始
    """
    插入功能，新增一个用户
    :param cmd: 传入命令行的输入，进行分解调用
    :param all_tittle: 默认为全局变量，用来判断需要写入那些内容，以及位置排序
    :param file_path: 默认为全局变量，用来标记需要修改的文件名
    :return:
    """
    if not insert_split(cmd):
        print('输入错误。。。')
        return
    cmd_dict = insert_split(cmd)
    globals().update(cmd_dict)
    file_info_list = get_file_info_to_list(file_path)
    new_people_info = {'staff_id':file_info_list[-1]['staff_id'] + 1}
    if 'phone' not in keys:
        print('哥，我也不想的，老师指定 phone 为唯一键，不输入不行啊。。。。')
        return

    for i in file_info_list:                                            # 如果号码重复，重新输入
        if i['phone'] == values[keys.index('phone')]:
            print('那啥，号码已注册，换一个吧。。亲。。。。')
            return
    if 'age' in keys and not values[keys.index('age')].isdigit():               # 判断年龄是不是数字
        print('年龄需要为数字。。。')
        return

    for i in all_tittle[1:]:                  # 准备加入 新人物 的信息
        if i in keys:
            new_people_info[i] = values[keys.index(i)].strip("'").strip('"')
        else:
            new_people_info[i] = None
    new_people_info = [new_people_info]
    add_new_people(all_tittle, new_people_info, file_path, open_with='a')
    print('33[1m加入成功。。。33[0m')


def add_new_people(all_tittle,new_people_info, file_path, open_with = 'r'):
    """
    主要用来写入或增加 staff_table 的内容
    :param all_tittle: 存放表头信息
    :param new_people_info: 需要加入，或重新写入的任务信息
    :param file_path: 文件路径
    :param open_with: 打开方式
    :return: 1
    """
    with open(file_path, open_with, encoding='utf-8') as f:
        new_people_info_str = ''
        if new_people_info[0]['enroll_date'] == None:
            new_people_info[0]['enroll_date'] = time.strftime("%Y-%m-%d")

        for d in new_people_info:
            for i in all_tittle:
                new_people_info_str += '%s,'%d[i]
            new_people_info_str = new_people_info_str.strip(',') + '
'
        f.write(new_people_info_str)
        return 1


@add_func_dic('delete')
def delete(cmd1):
    """
    处理删除命令，通过匹配符合条件的方法，不符合的加入新的列表中，等待重新写入
    :param cmd1: 用户输入的命令
    :return: 1
    """
    cmd_dic = resolve_cmd(cmd1)
    cmd, file_name, select_condetion = cmd_dic['cmd'], cmd_dic['file_name'], cmd_dic['select_condetion']

    if select_condetion:
        select_condetion = select_condetion.replace('=', '==')
        if 'like' in select_condetion:               # 如果判断条件有 like 就把   enroll_date like "2013"  改成 “2013” in enroll_date ，方便后面 eval 计算
            select_condetion = deal_select_condetion(select_condetion)
    else:
        print("请输入条件。。。。")
        return

    file_info_list = get_file_info_to_list(file_name)   # 获取文件相关参数
    new_file_list = []            # 筛选出新信息后重新写入
    total_count = 0
    for d in file_info_list:
        globals().update(d)
        if not eval(select_condetion):
            new_file_list.append(d)
        else:
            total_count += 1
    add_new_people(all_tittle,new_file_list,file_name,open_with='w')
    print('33[1m删除了%s条内容。。。33[0m' % total_count)
    return 1


def update_split(cmd):
    """
    处理用户更新信息时候的命令，将它分割开
    :param cmd: 用户输入的信息
    :return: 分割好的内容 file_name, change_thing, select_condetion
    """
    cmd_dic={}
    cmd_dic['file_name'] = cmd.split('set')[0].split('update')[1].strip()
    cmd_dic['change_thing'] = cmd.split('where')[0].split('set')[1].strip().split('=')
    if 'staff_id' in cmd_dic['change_thing'][1]:
        print('哥，为了方便程序运行，我禁止了修改 ID 。。。。')
        return
    if 'where' not in cmd:
        print('没有输入范围(where)。。。')
        return
    cmd_dic['select_condetion'] = cmd.split('where')[1].strip()
    if 'age' in cmd_dic['change_thing']:
        if not cmd_dic['change_thing'][1].isdigit():
            print('年龄要是个数字。。。。')
            return
    return cmd_dic['file_name'], cmd_dic['change_thing'], cmd_dic['select_condetion']

@add_func_dic('update')
def update(cmd):
    """
    这个程序用来修改数据库信息
    :param cmd: 用户输入的命令
    :return: 1
    """
    cmd_dic = update_split(cmd)
    if not cmd_dic:
        return
    file_name, change_thing, select_condetion = update_split(cmd)
    select_condetion = select_condetion.replace('=','==')
    if 'like' in select_condetion:  # 如果判断条件有 like 就把   enroll_date like "2013"  改成 “2013” in enroll_date ，方便后面 eval 计算
        select_condetion = deal_select_condetion(select_condetion)
    file_info_list = get_file_info_to_list(file_name)
    total_count = 0
    for i,d in enumerate(file_info_list):
        globals().update(d)
        if eval(select_condetion):
            file_info_list[i][change_thing[0].strip()] = change_thing[1].strip()
            total_count += 1

    add_new_people(all_tittle,file_info_list, file_name, open_with = 'w')
    print('33[1m修改了%s条内容。。。33[0m' % total_count)


def main():
    while True:
        with open(readme_path,encoding='utf-8') as f:
            for i in f:
                print(i.strip())
        while True:
            cmd = input("33[31;1m>>>33[0m").strip()
            if cmd.upper() == 'Q':
                exit('谢谢使用')
            elif cmd.upper() == 'R':
                break
            try:
                func_dic[cmd.strip().split()[0]](cmd)
            except Exception:
                print('输入错误。。。')
            time.sleep(0.3)

if __name__ == "__main__":
    main()