Python实现SQL模拟功能

博客核心内容


  1. Python实现insert插入功能
  2. Python实现delete删除功能(remove、rename)
  3. Python实现update更新功能(remove、rename)
  4. Python实现:将列表中的内容对号入座的放到相应的字典当中(警报场景1)
  5. Python实现:where列表中的内容进行再处理(警报场景2)
  6. Python实现:用where条件去过滤单行记录
  7. Python实现:如何获取单行记录中指定的字段数据(select id,name)
  8. Python实现select查询功能
  9. Python实现select查询功能(我自己实现的:带截图)

1、Python实现insert插入功能

插入之前db1.emp文件中的内容示例:

1,张德,25,18330267967,IT,2017-5-27
2,李四,25,19230267967,运维,2016-5-27
3,王五,35,15673026796,IT,2017-6-27
4,赵六,29,18122263467,测试,2017-5-28
5,董七,25,18556267967,运维6,2014-5-27
6,李张明,26,18122677967,测试,2017-5-21
7,赵四,28,18523267967,运维6,2014-5-22
8,董七,25,18556267967,运维6,2014-5-27

核心代码示例:

#!/usr/bin/python
# -*- coding:utf-8 -*-


def insert(sql):
    """
    function:该函数的功能是进行文本插入操作
    :param sql: 用户传进来的sql语句
    :return: 无
    """
    sql_list = sql.split()
    db_name,table_name = sql_list[2].split(".")
    #获取最后一行代码的ID
    last_id = ""
    with open("%s/%s"%(db_name,table_name),mode="r",encoding="utf-8") as fr:
        content_list = fr.readlines()
        last_record = content_list[-1]
        last_id = last_record.split(",")[0]

    #拼出最后一行记录,在操作的过程中注意insert的返回值是None
    record_list = sql_list[-1].split(",")
    record_list.insert(0,str(int(last_id)+1))
    record_line = ",".join(record_list)

    #向列表中写入记录
    with open("%s/%s"%(db_name, table_name), mode="a", encoding="utf-8") as fw:
        fw.write("\n"+record_line)
        #记住要刷新纪录
        fw.flush()

if __name__ == '__main__':
    while True:
        sql = input("sql> ").strip()
        insert(sql)

运行代码:

sql> insert into db1.emp values 张东,25,18556267367,运维6,2014-5-28
sql> insert into db1.emp values 刘恩,25,18556227967,运维6,2014-4-27

插入之后db1.emp文件的内容示例:

1,张德,25,18330267967,IT,2017-5-27
2,李四,25,19230267967,运维,2016-5-27
3,王五,35,15673026796,IT,2017-6-27
4,赵六,29,18122263467,测试,2017-5-28
5,董七,25,18556267967,运维6,2014-5-27
6,李张明,26,18122677967,测试,2017-5-21
7,赵四,28,18523267967,运维6,2014-5-22
8,董七,25,18556267967,运维6,2014-5-27
9,张东,25,18556267367,运维6,2014-5-28
10,刘恩,25,18556227967,运维6,2014-4-27

后续可以在拼接最后一行记录的时候进行优化,从而替换掉21/22/23三行代码.

    #拼出最后一条记录
    last_record = last_id+","+sql_list[-1]
2、Python实现delete删除功能

文件修改的思路:
1、读源文件的内容,然后一行一行的向新文件中写.
2、通过os模块将源文件给删除.
3、将新文件重命名为源文件的名字.
源文件删除之前的情况:

1,张德,25,18330267967,IT,2017-5-27
2,李四,25,19230267967,运维,2016-5-27
3,王五,35,15673026796,IT,2017-6-27
4,赵六,29,18122263467,测试,2017-5-28
5,董七,25,18556267967,运维6,2014-5-27
6,李张明,26,18122677967,测试,2017-5-21
7,赵四,28,18523267967,运维6,2014-5-22
8,董七,25,18556267967,运维6,2014-5-27

代码示例:

#!/usr/bin/python
# -*- coding:utf-8 -*-

import os

def delete(sql):
    """
    function:该函数的功能是删除表中指定的行,此处我的where条件就是=号的方式
    :param sql:用户传进来的SQL语句 
    :return: 返回值是删除的记录条数和删除是否成功
    """
    sql_list = sql.split()

    db_name,table_name = sql_list[2].split(".")
    #生成备份文件的名字
    back_name = table_name+"_bak"
    #获取判断字段
    field,value = sql_list[4],sql_list[6]
    title = "id,name,age,phone,dept,enroll_date"
    del_count = 0
    with open("%s/%s"%(db_name,table_name),"r",encoding="utf-8") as fr,\
        open("%s/%s"%(db_name,back_name),"w",encoding="utf-8") as fw:
        #只有在第一次打开文件的时候才会清空整个文件的内容
        for line in fr.readlines():
            user_dict = dict(zip(title.split(","),line.strip().split(",")))
            if user_dict.get(field) == value:
                del_count += 1
            else:
                fw.write(line)
                fw.flush()
    #在整个操作结束之后,删除源文件,并对新文件进行重命名
    os.remove("%s/%s"%(db_name,table_name))
    os.rename("%s/%s"%(db_name,back_name),"%s/%s"%(db_name,table_name))

    return [[del_count],["delete successful"]]


if __name__ == '__main__':
    while True:
        sql = input(">").strip()
        log_list = delete(sql)
        print(log_list)

运行示例:

>delete from db1.emp where id = 2
[[1], ['delete successful']]
>delete from db1.emp where name = 李四
[[0], ['delete successful']]
>delete from db1.emp where name = 赵四
[[1], ['delete successful']]

删除文件之后的文件内容:

1,张德,25,18330267967,IT,2017-5-27
3,王五,35,15673026796,IT,2017-6-27
4,赵六,29,18122263467,测试,2017-5-28
5,董七,25,18556267967,运维6,2014-5-27
6,李张明,26,18122677967,测试,2017-5-21
8,董七,25,18556267967,运维6,2014-5-27
3、Python实现update更新功能

update更新之前文件中的内容:

1,张德,25,18330267967,IT,2017-5-27
2,李四,25,19230267967,运维,2016-5-27
3,王五,35,15673026796,IT,2017-6-27
4,赵六,29,18122263467,测试,2017-5-28
6,李张明,26,18122677967,测试,2017-5-21
7,赵六,28,18523267967,运维6,2014-5-22
8,Alex,25,18556267967,运维6,2014-5-27

代码示例:

#!/usr/bin/python
# -*- coding:utf-8 -*-

import os

def update(sql):
    """
    function:用于进行记录插入操作
    :param sql: 用户传进来的sql语句
    :return: 日志列表
    """
    sql_list = sql.split()
    db_name,table_name = sql_list[1].split(".")
    #创建备份文件的名字
    back_name = table_name+"_bak"

    print("sql_list is \033[41;1m%s\033[0m"%sql_list)
    change_field = sql_list[3]
    change_value = sql_list[5]
    where_field = sql.partition("where")[2].split("=")[0].strip()
    where_value = sql.partition("where")[2].split("=")[1].strip()

    title = "id,name,age,phone,dept,enroll_date"
    update_count = 0
    with open("%s/%s"%(db_name,table_name),"r",encoding="utf-8") as fr, \
        open("%s/%s"%(db_name,back_name),"w",encoding="utf-8") as fw:
        for line in fr.readlines():
            #拼出用户的相关信息
            user_info = dict(zip(title.split(","),line.strip().split(",")))
            if user_info[where_field] == where_value:
                user_info[change_field] = change_value.strip("'")
                #构建插入的记录
                line_list = []
                #下面这一步骤值得学习:先转化成list,在拼接成字符串
                for i in title.split(","):
                    line_list.append(user_info.get(i))
                line_record = ",".join(line_list)
                fw.write(line_record+"\n")
                update_count += 1
            else:
                fw.write(line)
        #所有操作完之后之后再刷新一下缓冲区
        fw.flush()

    os.remove("%s/%s" % (db_name, table_name))
    os.rename("%s/%s" % (db_name, back_name), "%s/%s" % (db_name, table_name))

    return [[update_count], ["update successful"]]

if __name__ == '__main__':
    sql = input(">").strip()
    log_list = update(sql)
    print(log_list)

运行代码:

>update db1.emp set name = 'sb'   where name = Alex
sql_list is ['update', 'db1.emp', 'set', 'name', '=', "'sb'", 'where', 'name', '=', 'Alex']
[[0], ['update successful']]

运行后文件的内容:

1,张德,25,18330267967,IT,2017-5-27
2,李四,25,19230267967,运维,2016-5-27
3,王五,35,15673026796,IT,2017-6-27
4,赵六,29,18122263467,测试,2017-5-28
6,李张明,26,18122677967,测试,2017-5-21
7,赵六,28,18523267967,运维6,2014-5-22
8,sb,25,18556267967,运维6,2014-5-27
4、Python实现:将列表中的内容对号入座的放到相应的字典当中(警报场景1)

代码示例:

#!/usr/bin/python
# -*- coding:utf-8 -*-


def handle_parse(sql_list):
    """
    function:该函数的功能是将列表中的内容对号入座的放到相应的字典当中
    :param sql: 用户传进来的列表形式的数据
    :return: sql_dict数据
    """
    print("sql_list is %s"%sql_list)
    sql_dict = {
        "select":[],
        "from":[],
        "where":[],
        "limit":[]
    }
    tag = False
    key = ""
    for item in sql_list:
        if tag and item in sql_dict:
            tag = False
        if not tag and item in sql_dict:
            tag = True
            key = item
            continue
        if tag:
            sql_dict[key].append(item)

    return sql_dict

if __name__ == '__main__':
    sql_list = input(">").strip().split()
    sql_dict = handle_parse(sql_list)
    print("sql_dict is %s"%sql_dict)

运行结果:

>select id,name  from db1.emp where not id >= 3 limit 2
sql_list is ['select', 'id,name', 'from', 'db1.emp', 'where', 'not', 'id', '>=', '3', 'limit', '2']
sql_dict is {'from': ['db1.emp'], 'where': ['not', 'id', '>=', '3'], 'limit': ['2'], 'select': ['id,name']}

Process finished with exit code 0

除了select之外,insert、update、delete等操作都可以使用这种方式对号入座。

5、Python实现:where列表中的内容进行再处理(警报场景2)

代码示例:

#!/usr/bin/python
# -*- coding:utf-8 -*-

def handle_parse(where_list):
    """
    function:本函数的功能是对sql_list中的数据进行进一步的处理
    :param sql_list: ['id', '>', '10', 'and', 'id', '<', '14', 'or', 'name', 'like', '李']
    :return: [['id', '>', '10'], 'and', ['id', '<', '14'], 'or', ['name', 'like', '李']]
    """
    sens_list = ['and','or','not']
    where_result = []
    temp = []

    tag = False
    sens_word = ""
    for item in where_list:
        if item in sens_list:
            tag = True
            if temp:where_result.append(temp)
            temp = []
            sens_word = item
        if not tag and item not in sens_list:
            temp.append(item)
        if tag and item not in sens_list:
            tag = False
            where_result.append(sens_word)
            sens_word = ""
            temp.append(item)
    else:
        where_result.append(temp)

    return where_result

if __name__ == '__main__':
    where_list = ['id', '>', '10', 'and', 'id', '<', '14', 'or', 'name', 'like', '李']
    print(where_list)
    where_list = handle_parse(where_list)
    print("%s"%where_list)

运行结果:

['id', '>', '10', 'and', 'id', '<', '14', 'or', 'name', 'like', '李']
[['id', '>', '10'], 'and', ['id', '<', '14'], 'or', ['name', 'like', '李']]

问题注意:temp = []不要写成list.clear(temp)

6、Python实现用where条件去过滤单行记录
#!/usr/bin/python
# -*- coding:utf-8 -*-



def logic_action(user_dict,where_list):
    """
    function:通过where列表中的条件对文本进行过滤操作
    :param user_dict: 用户的记录字典形式:单行文本
    :param where_list: where条件列表形式[['id', '>', '10'], 'and', ['id', '<', '14'], 'or', ['name', 'like', '李']]
    :return: 返回逻辑值真或假
    """
    logic_result = []
    for item in where_list:
        if (type(item) == list):
            field,label,value = item
            line_value = user_dict[field]
            if label == "=":
                label = "=="
            if value.isdigit():
                value = int(value)
                line_value = int(line_value)
            if label != 'like':
                item = str(eval("%s%s%s" %(line_value,label,value)))
            else:
                if value in line_value:
                    item = 'True'
                else:
                    item = 'False'
        logic_result.append(item)

    logic_str = ' '.join(logic_result)
    print(logic_str)
    logic_res = eval("%s"%logic_str)

    return logic_res

if __name__ == '__main__':
    user_dict = {'enroll_date': '2014-5-27', 'dept': '运维6', 'name': '张明', 'age': '25', 'id': '1', 'phone': '18556267967'}
    where_list =  [['age', '=', '24'], 'and', ['id', '<', '14'], 'and', ['name', 'like', '张']]
    logic_res = logic_action(user_dict, where_list)
    print("logic_res is %s"%logic_res)

运行结果:

False and True and True
logic_res is False

Process finished with exit code
7、如何获取当行记录中指定的字段数据

代码示例:

#!/usr/bin/python
# -*- coding:utf-8 -*-



"""
function:获取文本当中指定的字段数值
"""
if __name__ == '__main__':
    db_name = "db1"
    table_name = "emp"
    title = "id,name,age,phone,dept,enroll_data"
    fields_list = ["age","phone","dept"]

    final_result = []
    with open("%s/%s"%(db_name,table_name),"r",encoding="utf-8") as fr:
        for line in fr.readlines():
            #通过zip形式处理单行记录
            user_info = dict(zip(title.split(","),line.strip().split(",")))
            temp = []
            for field in fields_list:
                temp.append(user_info[field])
            final_result.append(temp)
    for record in final_result:
        print(record)

运行结果:

['25', '18330267967', 'IT']
['25', '19230267967', '运维']
['35', '15673026796', 'IT']
['29', '18122263467', '测试']
['25', '18556267967', '运维6']
['26', '18122677967', '测试']
['28', '18523267967', '运维6']
['25', '18556267967', '运维6']

Process finished with exit code 0
8、Python实现select查询功能

代码示例:

#!/usr/bin/python
# -*- coding:utf-8 -*-

import os

def select_parse(sql):
    """
    function:该函数的功能是进行select查询操作
    :param sql: 用户传进来的sql语句
    :return: 返回操作日志
    """
    #定义一个存储结构,以字典的形式进行存储
    sql_list = sql.split()
    #用一个字典结构去存储相应的数据,方便处理数据
    sql_dict = handle_parse(sql_list)

    return sql_dict

def handle_parse(sql_list):
    """
    function:用字典的形式去存储数据
    :param sql_list: 用户传进来的sql_list列表
    :return: 返回字典形式的数据
    """
    sql_dict = {
        "select":[],
        "from":[],
        "where":[],
        "limit":[]
    }

    # 接下来就是如何将列表中的内容对号入座的放到相应的字典当中,利用到了警报
    tag = False
    key = ""
    for item in sql_list:
        if tag and item in sql_dict:
            tag = False
        if not tag and item in sql_dict:
            tag = True
            key = item
            continue
        if tag:
            sql_dict[key].append(item)
    #此时我们需要对where中的条件进行一步的清洗处理
    #现在的where条件:['id', '>', '10', 'and', 'id', '<', '14', 'or', 'name', 'like', '李']
    #需要处理成的形式:[['id', '>', '10'], 'and', ['id', '<', '14'], 'or', ['name', 'like', '李']]
    #需要处理成的形式:['not', ['id', '>=', '3']]
    if  sql_dict['where']:
        sql_dict['where'] = where_parse(sql_dict['where'])

    return sql_dict

def where_parse(where_list):
    """
    function:该函数的作用是对where中的条件进行进一步的处理
    :param sql_dict: ['id', '>', '10', 'and', 'id', '<', '14', 'or', 'name', 'like', '李']
    :return: ['not', ['id', '>=', '3']]
    """
    sens_list = ['and','or','not']
    where_result = []
    temp = []
    opt = ""

    tag = False
    for item in where_list:
        if item in sens_list:
            tag = True
            #通过if条件来限制not这种情况
            if temp:
                where_result.append(temp)
            temp = []   #注意:此处不能用list.clear(temp)
            opt = item
        if not tag and item not in sens_list:
            temp.append(item)
        if tag and item not in sens_list:
            tag = False
            where_result.append(opt)
            opt = ""
            temp.append(item)
    else:
        where_result.append(temp)

    return where_result

def select(sql_dict):
    """
    function:进一步实现查询功能
    :param sql_dict: 用户传进来的字典形式的数据
    :return: 日志列表
    """
    #1、先通过from获取对应的库名和表名
    db_name,table_name = sql_dict["from"][0].split(".")
    fr = open("%s/%s"%(db_name,table_name),"r",encoding="utf-8")
    #2、我们去解析where语句中的内容,(这是重点)
    where_list = where_action(fr,sql_dict["where"])
    #3、获取指定个数的行文本
    limit_list = limit_action(where_list,sql_dict.get("limit"))
    #4、获取指定字段个数的信息
    final_result = search_action(limit_list,sql_dict.get("select"))

    return final_result

def search_action(limit_list,select_list):
    """
    function:本函数的功能是获取指定字段个数的数据
    :param limit_list: list列表
    :param select_list: [id,name]
    :return: 返回一个指定字段的集合
    """
    #final_result用来存放最终的结果集合
    final_result = []
    title = "id,name,age,phone,dept,enroll_data"
    #先考虑*的情况
    if select_list[0] == "*":
        final_result = limit_list
    else:
        fields_list = select_list[0].split(",")
        for line in limit_list:
            user_info = dict(zip(title.split(","),line.split(",")))
            #用一个临时列表去存放中间数据
            r_l = []
            for field in fields_list:
                r_l.append(user_info[field])

            final_result.append(r_l)

    return final_result

def limit_action(where_list,limit_list):
    """
    function:获取指定个数的行文本:
    :param where_list: ['2,李四,25,19230267967,运维,2016-5-27\n', '6,李张明,26,18122677967,测试,2017-5-21\n']
    :param limit_num: 数值
    :return: 
    """
    limit_res = []

    if  limit_list:
        where_list  = where_list[0:int(limit_list[0])]
    return where_list

def where_action(fr,where_list):
    """
    function:通过where中的条件对文件中的内容进行过滤
    :param fr: 文件的句柄
    :param where_list: where列表中的内容
    :return: 返回一个列表的内容
    """
    #res_list用于存放结果集
    res_list = []
    #先考虑是否含有where条件
    if  where_list:
        title = "id,name,age,phone,dept,enroll_date"
        for line in fr.readlines():
            user_dict = dict(zip(title.split(","),line.strip().split(",")))
            #用logic_action
            logic_res = logic_action(user_dict,where_list)
            if logic_res:
                res_list.append(line)

    #在考虑无where条件
    else:
        res_list = fr.readlines()

    return res_list

def logic_action(user_dict,where_list):
    """
    function:通过where列表中的条件对文本进行过滤操作
    :param user_dict: 用户的记录字典形式:单行文本
    :param where_list: where条件列表形式[['id', '>', '10'], 'and', ['id', '<', '14'], 'or', ['name', 'like', '李']]
    :return: 返回逻辑值真或假
    """
    logic_result = []
    for item in where_list:
        if (type(item) == list):
            field,label,value = item
            line_value = user_dict[field]
            if label == "=":
                label = "=="
            if value.isdigit():
                value = int(value)
                line_value = int(line_value)
            if label != 'like':
                item = str(eval("%s%s%s" %(line_value,label,value)))
            else:
                if value in line_value:
                    item = 'True'
                else:
                    item = 'False'
        #如果不是列表类型,就是敏感词汇,此时直接加入到logic_result当中
        logic_result.append(item)
    logic_str = ' '.join(logic_result)
    logic_res = eval("%s"%logic_str)

    return logic_res


if __name__ == '__main__':
    while True:
        sql = input(">").strip()
        #此时的sql语句已经被解析成字典形式
        sql_dict = select_parse(sql)
        final_result = select(sql_dict)
        for line in final_result:
            print(line)

运行结果示例:

>select * from db1.emp where id > 5
6,李张明,26,18122677967,测试,2017-5-21

7,赵四,28,18523267967,运维6,2014-5-22

8,董七,25,18556267967,运维6,2014-5-27
>select id,name from db1.emp where id > 5
['6', '李张明']
['7', '赵四']
['8', '董七']
9、Python实现select查询功能

主要功能模块:
这里写图片描述
代码示例:

#!/usr/bin/python
# -*- coding:utf-8 -*-

import os
def select_parse(sql):
    """
    function:解析成字典形式
    :param sql: 
    :return: 
    """
    sql_dict = {
        "select":[],
        "from":[],
        "where":[],
        "limit":[],
    }
    sql_list = sql.split()
    sens_list = ["select","from","where","limit"]
    tag = False
    key_word = ""
    for item in sql_list:
        if tag and item in sens_list:
            tag = False
            key_word = ""
        if not tag and item in sens_list:
            tag = True
            key_word = item
            continue
        if tag:
            sql_dict[key_word].append(item)

    return sql_dict


def select(sql_dict):
    """
    function:本函数的功能是对实现select解析功能
    :param sql_dict: {'select': ['*'], 'where': ['id', '>', '5'], 'from': ['db1.emp'], 'limit': ['2']}
    :return: 返回最终的结果集合
    """
    #1、获取数据库名和表名
    db_name,table_name = sql_dict["from"][0].split(".")
    #2、获取整个文件的内容
    content_list =[]
    with open("%s/%s"%(db_name,table_name),"r",encoding="utf-8") as fr:
        content_list = fr.readlines()
    #3、对where中的条件进行进一步的处理: [['id', '>', '10'], 'and', ['id', '<', '14'], 'or', ['name', 'like', '李']]
    sql_dict["where"] = where_parse(sql_dict["where"])
    #4、通过where条件对content_list表中的内容进行第一次过滤
    where_result = handle_where(content_list,sql_dict["where"])
    #5、通过limit条件对where_result中的条件进行进一步的过滤
    limit_result = limit_parse(where_result,sql_dict["limit"])
    #6、输出select函数中指定的字段信息
    final_result = select_handle(limit_result,sql_dict['select'])

    return final_result


def select_handle(limit_result,select_list):
    """
    function:获取指定的字段信息
    :param limit_result: limit中的列表
    :param select_list: 指定字段或者*
    :return: 返回一个列表
    """
    #先考虑*的情况
    final_result = []
    title = "id,name,age,phone,dept,enroll_date"

    if select_list[0] == "*":
        return limit_result
    else:
        for item in limit_result:
            temp = []
            user_info = dict(zip(title.split(","),item.split(",")))
            for item in select_list[0].split(","):
                temp.append(user_info[item])
            final_result.append(temp)
        return final_result


def limit_parse(where_result,limit_list):
    """
    function:通过limit过滤功能对where中的条件进行进一步的过滤
    :param where_result: where过滤后产生的结果
    :param limit_list:  limit列表中的条件
    :return: 返回limit过滤之后的列表
    """
    limit_result = []
    if limit_list:
        limit_list = where_result[0:int(limit_list[0])]
        return limit_list
    else:
        #如果列表中的内容为空,则进行进一步的处理
        return where_result


def handle_where(content_list,where_list):
    """
    function:通过where列表中的内容对content_list中的行文本进行进一步的过滤
    :param content_list: 列表内容
    :param where_list: where条件列表
    :return: 返回一个过滤之后的列表
    """

    where_result = []
    title = "id,name,age,phone,dept,enroll_date"
    if where_list:
        for line in content_list:
            #以字典的形式去封装相应的信息
            user_info = dict(zip(title.split(","),line.split(",")))
            logic_result = logic_action(user_info,where_list)
            if logic_result:
                where_result.append(line)
        return where_result
    #如果where列表中内容为空,则直接返回整个列表内容
    else:
        where_result = content_list
        return where_result


def logic_action(user_dict,where_list):
    """
    function:通过where列表中的条件对文本进行过滤操作
    :param user_dict: 用户的记录字典形式:单行文本
    :param where_list: where条件列表形式[['id', '>', '10'], 'and', ['id', '<', '14'], 'or', ['name', 'like', '李']]
    :return: 返回逻辑值真或假
    """
    logic_result = []
    for item in where_list:
        if (type(item) == list):
            field,label,value = item
            line_value = user_dict[field]
            if label == "=":
                label = "=="
            if value.isdigit():
                value = int(value)
                line_value = int(line_value)
            if label != 'like':
                item = str(eval("%s%s%s" %(line_value,label,value)))
            else:
                if value in line_value:
                    item = 'True'
                else:
                    item = 'False'
        logic_result.append(item)

    logic_str = ' '.join(logic_result)
    logic_res = eval("%s"%logic_str)

    return logic_res


def where_parse(where_list):
    """
    function:对where中的条件进行进一步的处理
    :param where_list: 
    :return: 
    """
    where_result = []
    sens_list = ["and","or","not"]
    key_word = ""
    temp = []

    if where_list:
        tag = False
        for item in where_list:
            if item in sens_list:
                tag = True
                key_word = item
                if temp:where_result.append(temp)
                temp = []
            if not tag and  item not in sens_list:
                temp.append(item)
            if tag and item not in sens_list:
                tag = False
                where_result.append(key_word)
                key_word = ""
                temp.append(item)
        else:
            where_result.append(temp)

    return where_result


if __name__ == '__main__':
    while True:
        sql = input(">").strip()
        print("\033[50;1m%s\033[0m"%sql)
        sql_dict = select_parse(sql)
        final_result = select(sql_dict)
        for line in final_result:
            print(line)

运行结果示例:

>select * from db1.emp limit 3
select * from db1.emp limit 3
1,张德,25,18330267967,IT,2017-5-27

2,李四,25,19230267967,运维,2016-5-27

3,王五,35,15673026796,IT,2017-6-27

>select * from db1.emp where not id >= 3
select * from db1.emp where not id >= 3
1,张德,25,18330267967,IT,2017-5-27

2,李四,25,19230267967,运维,2016-5-27

>select * from db1.emp where id > 2 and id < 10 or name likeselect * from db1.emp where id > 2 and id < 10 or name like2,李四,25,19230267967,运维,2016-5-27

3,王五,35,15673026796,IT,2017-6-27

4,赵六,29,18122263467,测试,2017-5-28

5,董七,25,18556267967,运维6,2014-5-27

6,李张明,26,18122677967,测试,2017-5-21

7,赵四,28,18523267967,运维6,2014-5-22

8,董七,25,18556267967,运维6,2014-5-27
>select id,phone from db1.emp where name likeselect id,phone from db1.emp where name like 李
['2', '19230267967']
['6', '18122677967']
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

一只懒得睁眼的猫

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值