需求：

 

自己写的：

 

"""除了支持作业中要求的语法，模糊查询还支持 >= <= 。严格按照作业中的语法，如果不按照作业中的语法会提示相应的错误。增:电话号码强制输入11位手机号而且里面不能有字母，否则会提示电话号码格式不对add staff_table Alex Li,25,13443534411,IT,2015-10-29删:del from staff_table where id=12改:UPDATE staff_table SET dept="Market" WHERE dept = "IT"UPDATE staff_table SET age=26 WHERE name = "Alex Li"UPDATE staff_table SET age=25 WHERE phone = 13888888888查:find name,age from staff_table where age > 22find name,age from staff_table where age < 22find name,age from staff_table where age >= 22find name,age from staff_table where age <= 22find * from staff_table where dept = "IT"find name,age from staff_table where age > 22find * from staff_table where enroll_date like "2013""""def add_staff(sql_cmd, matched_data):    staff_id = []    staff_phone = []    filename = ''.join(sql_cmd[1]) + '.txt'    try:        with open(filename, 'r', encoding='utf-8') as f:  # 把文件里的id写到staff_id = []里            for line in f:                if len(line) == 1:  # 如果有空行的话就跳过                    continue                staff_db_list = line.strip().split(',')                staff_id.append(staff_db_list[0])                staff_phone.append(staff_db_list[3])    except FileNotFoundError:        print('文件不存在,请查看输入顺序是否有误')    else:        for data in matched_data:            if data in staff_phone:                return print('号码已存在')        new_id = str(int(staff_id[-1]) + 1)  # 把最后一个id加1作为新id        with open(filename, 'a', encoding='utf-8') as f2:            f2.write(                '\n' + new_id + ',' + matched_data[0] + ',' + matched_data[1] + ',' + matched_data[2] + ',' +                matched_data[                    3] + ',' + matched_data[4])        print('添加成功')def search_staff(sql_cmd, matched_data):    import re    staff_info = {}    filename = ''.join(matched_data[1]) + '.txt'    try:        with open(filename, 'r', encoding='utf-8') as f:            for line in f:                if len(line) == 1:                    continue                staff_list = line.strip().split(',')                staff_info[staff_list[0]] = {                    'name': "\"" + staff_list[1] + "\"",                    'age': int(staff_list[2]),                    'phone': "\"" + staff_list[3] + "\"",                    'dept': "\"" + staff_list[4] + "\"",                    'enroll_date': "\"" + staff_list[5] + "\"",                }    except FileNotFoundError:        print('文件不存在,请查看输入顺序是否有误')    else:        show_content = ''.join(matched_data[0]).split(',')  # 查找内容        by_where = sql_cmd[0]  # 在哪搜索        by_which = sql_cmd[1]  # 通过哪种方法匹配        by_what = sql_cmd[2]  # 匹配内容是什么        mached = {}        tmp = 0  # 为了让by_what = by_what[1:-1]只执行一次        for key in staff_info:            if by_which.lower() == 'like':                if tmp == 0:                    by_what = by_what[1:-1]  # 去掉开头和结尾的双引号,如何用户不按要求输入双引号，就搜索不到想要的结果                tmp += 1                if re.findall(r'^"\w*(%s)+-\d+-\d+"$' % by_what, staff_info[key][by_where]):                    mached[key] = staff_info[key]            elif by_which == '=':                if by_where == 'age':                    if staff_info[key][by_where] == int(by_what):                        mached[key] = staff_info[key]                if staff_info[key][by_where] == by_what:                    mached[key] = staff_info[key]            else:                if by_where != 'age':                    return print('只有年龄可以比较大小')                if by_which == '>':                    if staff_info[key][by_where] > int(by_what):                        mached[key] = staff_info[key]                if by_which == '<':                    if staff_info[key][by_where] < int(by_what):                        mached[key] = staff_info[key]                if by_which == '>=':                    if staff_info[key][by_where] >= int(by_what):                        mached[key] = staff_info[key]                if by_which == '<=':                    if staff_info[key][by_where] <= int(by_what):                        mached[key] = staff_info[key]        mached_num = len(mached.keys())  # 查到几条信息        display_all_data = {}        display_data = {}        count = 0        if '*' in show_content:            display_all_data = mached        else:            for key in mached:                for show in show_content:                    if show in mached[key]:                        count += 1                        display_data[show + str(count)] = mached[key][show]        print('共查询出了%s条记录' % mached_num)        show_content_len = len(show_content)        count2 = 0        count3 = 0        if display_all_data == {}:            for key in display_data:                if count2 % show_content_len == 0:  # 每len(show_content)个信息，打印一个分割线                    count3 += 1                    print('--------------第%s条---------------' % count3)                count2 += 1                print(display_data[key])        if display_data == {}:            for key in display_all_data:                count3 += 1                print('--------------第%s条---------------' % count3)                print(mached[key]['name'])                print(mached[key]['age'])                print(mached[key]['phone'])                print(mached[key]['dept'])                print(mached[key]['enroll_date'])        if display_all_data == {} and display_data == {}:            return print('没有匹配的信息')def delete_staff(filename, user_id):    filename = filename + '.txt'    staff_info = {}    try:        with open(filename, 'r', encoding='utf-8') as f:            for line in f:                if len(line) == 1:                    continue                staff_list = line.strip().split(',')                staff_info[staff_list[0]] = {                    'name': staff_list[1],                    'age': staff_list[2],                    'phone': staff_list[3],                    'dept': staff_list[4],                    'enroll_date': staff_list[5].strip(),                }    except FileNotFoundError:        print('文件不存在')    else:        id_exist = False        for key in list(staff_info.keys()):            if user_id == key:                staff_info.pop(key)                id_exist = True        if id_exist == True:            with open(filename, 'w', encoding='utf-8') as f2:                for key in staff_info:                    f2.write(                        key + ',' + staff_info[key]['name'] + ',' + staff_info[key]['age'] + ',' + staff_info[key][                            'phone'] + ',' + staff_info[key]['dept'] + ',' + staff_info[key]['enroll_date'] + '\n')            print('删除成功')        else:            print('id不存在')def update_staff(filename, alter_where, where_data, original_keyword, alter_keyword):    filename = filename + '.txt'    if alter_where.lower() != 'age' and alter_where.lower() != 'phone':        alter_keyword = alter_keyword[1:-1]  # 去掉开头和结尾的双引号,如何用户不按要求输入双引号，就搜索不到想要的结果    if where_data.lower() != 'age' and where_data.lower() != 'phone':        original_keyword = original_keyword[1:-1]    staff_info = {}    with open(filename, 'r', encoding='utf-8') as f:        for line in f:            if len(line) == 1:                continue            staff_list = line.strip().split(',')            staff_info[staff_list[0]] = {                'name': staff_list[1],                'age': staff_list[2],                'phone': staff_list[3],                'dept': staff_list[4],                'enroll_date': staff_list[5],            }    count = 0    for key in staff_info:        if staff_info[key][where_data] == original_keyword:            count += 1            staff_info[key][alter_where] = alter_keyword    with open(filename, 'w', encoding='utf-8') as f2:        for key in staff_info:            f2.write(                key + ',' + staff_info[key]['name'] + ',' + staff_info[key]['age'] + ',' + staff_info[key][                    'phone'] + ',' + staff_info[key]['dept'] + ',' + staff_info[key]['enroll_date'] + '\n')    if count == 0:        print('没有符合条件的数据')    else:        print('修改成功，你共修改了%s条记录' % count)def staff_parse(cmd):    cmd = cmd.split(' ')  # 先用空格分开    if cmd[0].lower() == 'add':        try:            matched_data = cmd[2] + ' ' + cmd[3]  # 从姓名开始把增加的数据提取成一个字符串,名和姓之间有空格,如果只输入名，会没有cmd[3]就到异常处理了            matched_data = matched_data.split(',')  # 在把数据转换成列表            # 强制用户按顺序输入，否则从db拿电话号码查询是否重复的时候可能拿到别的数据            if len(matched_data[2]) != 11 or not matched_data[2].isdigit():                return print('没按顺序输入，或电话号码格式不对(请输入11位电话号码)\n'                             '请按顺序输入add 文件名 名、姓、年龄、手机号、部门、入职日期，并用空格隔开')            # 强制年龄按顺序输入，防止提取年龄数据查询的时候出现错误            if len(matched_data[1]) != 2:                return print('没按顺序输入，或年龄格式不对\n'                             '请按顺序输入add 文件名 名、姓、年龄、手机号、部门、入职日期，并用空格隔开')            print(                '你要增加的是:{0},{1},{2},{3},{4}'.format(matched_data[0], matched_data[1], matched_data[2], matched_data[3],                                                    matched_data[4], ))  # 如果输入的数据不够，就到异常处理了        except IndexError:            print('指令有误，请按顺序输入add 文件名 名、姓、年龄、手机号、部门、入职日期，并用空格隔开')        else:            sql_cmd = cmd[0:2]            add_staff(sql_cmd, matched_data)    elif cmd[0].lower() == 'find':        keyword1 = ['from', 'where']        keyword2 = ['=', '>', '<', '>=', '<=', 'like']        try:            # 强制用户按照正确的语法输入            if cmd[2].lower() not in keyword1 or cmd[4] not in keyword1:                return print('指令有误，请按顺序输入 find 查找内容 from 文件名 where 查找条件，并用空格隔开')            if cmd[6].lower() not in keyword2:                return print('指令有误，匹配关键词为like,>,<,=,>=,<=')            if len(cmd) > 8:                return print('指定有误，长度超出')            if cmd[5].lower() == 'age' and not cmd[7].isdigit():  # 如果输入年龄的话，查找条件就必须是数字                return print('年龄只接收整数')            sql_cmd = cmd[5] + ' ' + cmd[6] + ' ' + cmd[7]  # 最后三个匹配条件            matched_data = cmd[1] + ' ' + cmd[3]  # 查找内容和文件名        except IndexError:            print('指令有误，请按顺序输入 find 查找内容 from 文件名 where 查找条件，并用空格隔开')        else:            sql_cmd = sql_cmd.split(' ')            matched_data = matched_data.split(' ')            search_staff(sql_cmd, matched_data)    elif cmd[0].lower() == 'del':        keyword = ['from', 'where']        str_id = cmd[4][0:2]  # 检验用户输入的是不是id这个关键字        user_id = cmd[4][3:]  # 检验用户输入的是不是数字        equal_sign = cmd[4][2]  # 检验是不是等号        if cmd[1] not in keyword or cmd[3] not in keyword:            return print('指令有误，请按顺序输入 del from 文件名 where id=整数,并用空格隔开')        if str_id.lower() != 'id':            return print('请输入关键字:id')        if not user_id.isdigit():            return print('请输入整数')        if equal_sign != '=':            return print('只支持等号')        filename = ''.join(cmd[2])        delete_staff(filename, user_id)    elif cmd[0].lower() == 'update':        keyword = ['set', 'where']        try:            if cmd[2].lower() not in keyword or cmd[4].lower() not in keyword:                # 1.防止用户没输入关键词 2.如果用户没有用空格分开要修改的内容，cmd[4]就不是where，也会报错                return print('指令有误，请按顺序输入UPDATE 文件名 SET 要修改的内容(中间不能有空格) WHERE 原内容(用空格分开),并用空格隔开')            if cmd[6] != '=' or '=' not in cmd[3]:  # 1.防止输入其他符号 2. 如果用户如输入的最后三条内容没有空格也会报这个错误                return print('只支持等号，或指令输入有误，请按顺序输入UPDATE 文件名 SET 要修改的内容(中间不能有空格) WHERE 原内容(用空格分开),并用空格隔开')        except IndexError:            return print('指令有误，请按顺序输入UPDATE 文件名 SET 要修改的内容(中间不能有空格) WHERE 原内容(用空格分开),并用空格隔开')        else:            search_keyword = ['name', 'age', 'phone', 'dept', 'enroll_date']            where_data = cmd[5]  # 修改哪里的内容 关键词WHERE后面那个指定在哪符合什么条件，没有等号和原始内容            where1_equal_index = cmd[3].index('=')            alter_where = cmd[3][0:where1_equal_index]  # 关键词set后面指定要在哪里修改,去除掉等号和后面的修改内容            if alter_where in search_keyword and where_data in search_keyword:                filename = cmd[1]                original_keyword = ' '.join(cmd[7:])  # 数据库里是这个的将被修改                alter_keyword = cmd[3][where1_equal_index + 1:]  # 修改成什么                update_staff(filename, alter_where, where_data, original_keyword, alter_keyword)            else:                return print('修改的内容不存在')    else:        print('指令不存在')if __name__ == '__main__':    def main():        '''        主程序入口        :return:        '''        while True:            cmd = input('please enter sql:').strip()            if not cmd:                continue            if cmd.lower() == 'exit':                return None            else:                staff_parse(cmd)main()

 

讲解版本：

import osimport refrom tabulate import tabulateDB_FILE = 'staff_table.txt'COLUMNS = ['id', 'name', 'age', 'phone', 'dept', 'enrolled_date']def load_db(db_file):    data = {}    for i in COLUMNS:        data[i] = []    with open(db_file, 'r', encoding='utf-8') as f:        for line in f:            staff_id, name, age, phone, dept, enrolled_date = line.split(',')            data['id'].append(staff_id)            data['name'].append(name)            data['age'].append(age)            data['phone'].append(phone)            data['dept'].append(dept)            data['enrolled_date'].append(enrolled_date)        return dataSTAFF_DATE = load_db(DB_FILE)def save_db():    with open('%s.new' % DB_FILE, 'w', encoding='utf-8') as f:        for index, staff_id in enumerate(STAFF_DATE['id']):            row = []            for col in COLUMNS:                row.append(STAFF_DATE[col][index])            f.write(','.join(row))    os.rename("%s.new" % DB_FILE, DB_FILE)def print_log(msg, log_type='info'):    if log_type == 'info':        print('\033[32;1m%s\033[0m' % msg)    elif log_type == 'error':        print('\033[31;1m%s\033[0m' % msg)def op_gt(column, condition):    matched_date = []    for index, val in enumerate(STAFF_DATE[column]):        if float(val) > float(condition):            record = []            for col in COLUMNS:                record.append(STAFF_DATE[col][index])            matched_date.append(record)    if matched_date == []:        print_log('没有匹配到', 'error')        return None    else:        return matched_datedef op_lt(column, condition):    matched_date = []    for index, val in enumerate(STAFF_DATE[column]):        if float(val) < float(condition):            record = []            for col in COLUMNS:                record.append(STAFF_DATE[col][index])            matched_date.append(record)    if matched_date == []:        print_log('没有匹配到', 'error')        return None    else:        return matched_datedef op_eq(column, condition):    matched_date = []    for index, val in enumerate(STAFF_DATE[column]):        if val == condition:            record = []            for col in COLUMNS:                record.append(STAFF_DATE[col][index])            matched_date.append(record)    if matched_date == []:        print_log('没有匹配到', 'error')        return None    else:        return matched_datedef op_like(column, condition):    matched_date = []    for index, val in enumerate(STAFF_DATE[column]):        if condition in val:            record = []            for col in COLUMNS:                record.append(STAFF_DATE[col][index])            matched_date.append(record)    if matched_date == []:        print_log('没有匹配到', 'error')        return None    else:        print(matched_date)        return matched_datedef syntax_where(clause):    operator = {        '>': op_gt,        '<': op_lt,        '=': op_eq,        'like': op_like,    }    parttern = re.compile('(?P
     
      .+)(?P
      
       >|<|=|like+)(?P
       
        .+)')    data = re.search(parttern, clause)    try:        column = data.groupdict()['column'].strip()        operation = data.groupdict()['operation']        condition = data.groupdict()['condition'].strip()    except Exception:        print_log('语法错误:where条件只能支持[>,<,=,like]', 'error')    else:        matched_data = operator[operation](column, condition)        return matched_datadef syntax_find(data_set, query_clause):    filter_cols = query_clause.split('from')[0][4:].split(',')    filter_cols = [i.strip() for i in filter_cols]  # 干净的columns    if '*' in filter_cols[0]:        print(tabulate(data_set, headers=COLUMNS, tablefmt='grid'))    else:        reformat_data_set = []        for row in data_set:            filtered_vals = []            for col in filter_cols:                col_index = COLUMNS.index(col)                filtered_vals.append(row[col_index])            reformat_data_set.append(filtered_vals)        print(tabulate(reformat_data_set, headers=filter_cols, tablefmt='grid'))        print_log('匹配到%s条数据' % len(data_set))def syntax_delete(data_set):    staff_id = int(data_set[0][0]) - 1  # 获取id ,列表是从0开始数，所以减1    for data in list(STAFF_DATE.keys()):        STAFF_DATE[data].pop(staff_id)    save_db()    print_log('删除成功')def syntax_update(data_set, query_clause):    need_set_row = query_clause.split('set')    if len(need_set_row) > 1:        col_name, new_val = need_set_row[1].strip().split('=')  # age = 25        col_index = COLUMNS.index(col_name)        # 循环data_set,取到每条记录的id,拿着这个id到STAFF_DATE['id]里找对应的id的索引，找到之后,再拿这个索引，去STAFF_DATA['age']列表里，改对应索引的值        for mached_row in data_set:            staff_id = mached_row[0]            staff_id_index = STAFF_DATE['id'].index(staff_id)            STAFF_DATE[col_name][staff_id_index] = new_val        print(STAFF_DATE)        save_db()        print_log('成功修改了%s条数据' % len(data_set))    else:        print_log('语法错误:未检测到set关键字!', 'error')def syntax_add(query_clause):  # add staff_table Alex Bai,25,13451054622,Market,2016‐01-11    query_clause = query_clause.split(',')  # 转成列表    name = ' '.join(query_clause[0].split(' ')[2:])  # 提取出名字    query_clause[0] = name  # 把 add staff_table 换成名字    new_id = len(STAFF_DATE['id']) + 1    query_clause.insert(0, str(new_id))  # 加上id 只剩添加内容了    last_data = query_clause[-1]    last_data = last_data + '\n'  # 给最后一个元素加上换行符    query_clause[-1] = last_data    for index, cow in enumerate(COLUMNS):        if len(query_clause[index]) == 11 and query_clause[index] in STAFF_DATE[cow]:            print_log('电话号码不能重复', 'error')            return None        STAFF_DATE[cow].append(query_clause[index])    save_db()    print_log('添加成功')def syntax_parser(cmd):    # find name,age from staff_table where age > 22    syntax_list = {        'find': syntax_find,        'del': syntax_delete,        'update': syntax_update,        'add': syntax_add,    }    if cmd.split()[0].lower() in ('find', 'add', 'del', 'update'):        if 'where' in cmd:            query_clause, where_clause = cmd.split('where')            matched_record = syntax_where(where_clause)        elif 'WHERE' in cmd:            query_clause, where_clause = cmd.split('WHERE')            matched_record = syntax_where(where_clause)        else:            matched_record = []  # add staff_table Alex Li,25,134435344,IT,2015‐10‐29            for index, staff_id in enumerate(STAFF_DATE['id']):                record = []                for cow in COLUMNS:                    record.append(STAFF_DATE[cow][index])                matched_record.append(record)            query_clause = cmd        cmd_action = cmd.split()[0].lower()        if cmd_action in syntax_list:            if cmd_action == 'add':                syntax_list[cmd_action](query_clause)            elif cmd_action == 'del':                syntax_list[cmd_action](matched_record)            else:                syntax_list[cmd_action](matched_record, query_clause)    else:        print_log('语法错误:[find\\add\del\\update] [column1,...] from [staff_table] [where] [column] [>,..] [condition]',                  'error')def main():    while True:        cmd = input('[staff_db]:').strip()        if not cmd: continue        syntax_parser(cmd)main()# FIND name,age from staff_table WHERE age > 22# find * from staff_table where age > 22# update staff_table set dept=Market where dept = IT# UPDATE staff_table set dept=技术部 where name=Alex Li# UPDATE staff_table set dept=开发部 where dept=技术部