本文共 11182 字，大约阅读时间需要 37 分钟。

本文通过PLY实现一个简单的sql库

本文主要简述一下，有关语法解析的库的使用，并使用改实例编写一个简单的模仿最基本的sql语句的数据库，本文代码仅供示例参考。

语法解析与上下文

语法解析是一个比较大的内容，早起比较成熟的有lex与yacc，该工具相对比较成熟，详细的资料大家可以自行查阅资料，在编译中使用较多的是上下文无关文法主要的是BNF，大家有兴趣可查阅sql语句的，标准的sql的文法相对复杂，本文只是为了实现最基本的工作故只是做了最简单的解析。

示例代码

import ply.lex as leximport ply.yacc as yaccimport collectionstokens = (    'LFPARENTH',    'RGPARENTH',    'TABLE',    'CREATE',    'INSERT',    'UPDATE',    'INTO',    'VALUES',    'SELECT',    'COLUMN',    "COMMA",    'WHERE',    'FROM',    'AND',    'SET',    'EQUAL',    'STAR',    "END")t_LFPARENTH = r'\('t_RGPARENTH = r'\)'t_SELECT = r'SELECT|select't_CREATE = r'CREATE|create't_INSERT = r'INSERT|insert't_UPDATE = r'UPDATE|update't_INTO = r'INTO|into't_VALUES = r'VALUES|values't_WHERE = r'WHERE|where't_FROM = r'FROM|from't_AND = r'AND|and't_SET = r'SET|set't_EQUAL = r'\='t_TABLE = r'TABLE|table't_COMMA = r','t_STAR = r'\*'t_END = r';'def t_COLUMN(t):    r'[a-zA-Z0-9/.-]+'    if t.value in ['FROM', 'from']:        t.type = 'FROM'    if t.value in ['CREATE', 'create']:        t.type = 'CREATE'    if t.value in ['TABLE', 'table']:        t.type = 'TABLE'    if t.value in ['INSERT', 'insert']:        t.type = 'INSERT'    if t.value in ['INTO', 'into']:        t.type = 'INTO'    if t.value in ['VALUES', 'values']:        t.type = 'VALUES'    if t.value in ['UPDATE', 'update']:        t.type = 'UPDATE'    if t.value in ['SET', 'set']:        t.type = 'SET'    if t.value in ['WHERE', 'where']:        t.type = 'WHERE'    if t.value in ['SELECT', 'select']:        t.type = 'SELECT'    if t.value in ['AND', 'and']:        t.type = 'AND'    return tdef t_newline(t):    r'\n+'    t.lexer.lineno += len(t.value)t_ignore = ' \t'def t_error(t):    print("Illegal character {0}".format(t.value[0]))    t.lexer.skip(1)lexer = lex.lex()datas = {}class Stack(object):    def __init__(self):        self.is_columns = False        self._stack = []    def reset(self):        self._stack = []    def append(self, value):        self._stack.append(value)    def __iter__(self):        return iter(self._stack)    def __len__(self):        return len(self._stack)    def __str__(self):        print(self._stack)        return "stack"    def __getitem__(self, item):        return self._stack[item]    def __setslice__(self, i, j, sequence):        return self._stack[i:j]stack = Stack()current_action = Nonecolumns_dict = {}condition_dict = {}def reset_action():    global current_action, stack, columns_dict, condition_dict    current_action = None    stack.reset()    columns_dict = {}    condition_dict = {}class Select(object):    def __init__(self):        self.values = []        self.table = None    def set_table(self, table):        self.table = table        return table in datas    def add_stack(self, stack):        [self.add_values(v) for v in stack if v not in self.values]    def add_values(self, value):        self.values.append(value)    def action(self):        """展示数据"""        if self.table not in datas:            print("table {0} not exists")            return        table = datas[self.table]        if self.values:            for v in self.values:                if v in table:                    print("   {0} = {1}".format(v, table[v]))                else:                    print("   {0} not in table {1}".format(v, self.table))        else:            for v in table:                print("   {0} = {1}".format(v, table[v]))class Create(object):    def __init__(self):        self.values = []        self.table = None    def set_table(self, table):        self.table = table        return table not in datas    def add_stack(self, stack):        [self.add_values(v) for v in stack if v not in self.values]    def add_values(self, value):        self.values.append(value)    def action(self):        datas[self.table] = collections.OrderedDict()        for v in self.values:            datas[self.table][v] = []        print("create : ", datas)class Insert(object):    def __init__(self):        self.values = []        self.columns = set()        self.table = None        self._stack = None    def set_table(self, table):        self.table = table        return table not in datas    def add_stack(self, stack):        # 判断是否输入的sql 为 insert into table(c1, c2, c3) values(1,2,3)        self._stack = stack    def action(self):        table = datas[self.table]        if self._stack.is_columns:            if len(self._stack) and len(self._stack) % 2 == 0:                index = int(len(self._stack) / 2)                if index != len(table.keys()):                    print("error default columns")                    return                for i in range(index):                    if self._stack[i] in table:                        table[self._stack[i]].append(self._stack[i + index])            else:                print(" error columns and values not equal")                return        else:            if len(table.keys()) != len(self._stack):                print("input values len {0} not equal table columes len {1}".                      format(len(self._stack), len(table.keys())))                return            t_index = 0            for v in table.keys():                table[v].append(self._stack[t_index])                t_index += 1        print("insert : ", datas)class Update(object):    def __init__(self):        self.values = []        self.table = None        self.condition_dict = {}        self.columns_dict = {}        self.index_list = None    def set_table(self, table):        self.table = table        return table not in datas    def add_stack(self, condition, colums):        self.condition_dict = condition        self.columns_dict = colums    def check_dict_key(self, val_dict, table):        for key in val_dict.keys():            if key not in table:                return False        return True    def find_keys(self, val_dict, table):        keys = [key for key in val_dict]        values = [val_dict[key] for key in val_dict]        self.index_list = []        print(keys)        print(table)        result_list = []        if keys:            first_line = table[keys[0]]            for i in range(len(first_line)):                detail_value = []                for key in keys:                    detail_value.append(table[key][i])                result_list.append(detail_value)        print(values)        print(result_list)        for index, v in enumerate(result_list):            if v == values:                self.index_list.append(index)        print(self.index_list)        return self.index_list    def action(self):        table = datas[self.table]        if not (self.check_dict_key(self.condition_dict, table) and\                self.check_dict_key(self.columns_dict, table)):            print(" error found keys ")            return        index_list = self.find_keys(self.condition_dict, table)        if not index_list:            print(" update condition not found")            return        for k in self.columns_dict:            for index in index_list:                table[k][index] = self.columns_dict[k]def p_statement_expr(t):    '''expressions : expression                    | expressions expression'''    if current_action:        current_action.action()    reset_action()def p_expression_start(t):    '''expression :  exp_select                    | exp_create                    | exp_insert                    | exp_update'''def p_expression_select(t):    '''exp_select : SELECT columns FROM COLUMN END                    | SELECT STAR FROM COLUMN END'''    print(t[1], t[2])    global current_action    current_action = Select()    if not current_action.set_table(t[4]):        print("{0} table not exists".format(t[4]))        return    if not t[2]:        current_action.add_stack(stack)def p_expression_create(t):    '''exp_create : CREATE TABLE COLUMN LFPARENTH columns RGPARENTH END'''    print(t[1])    global current_action    current_action = Create()    if not current_action.set_table(t[3]):        print("{0} table already exists".format(t[3]))        return    # 处理参数    current_action.add_stack(stack)def p_expression_insert(t):    '''exp_insert : INSERT INTO COLUMN exp_insert_end'''    print(t[1])    global current_action    current_action = Insert()    if current_action.set_table(t[3]):        print("{0} table not exists".format(t[3]))        reset_action()        return    # 处理insert的参数    current_action.add_stack(stack)def p_expression_update(t):    '''exp_update : UPDATE COLUMN SET exp_update_colums WHERE exp_update_condition END'''    print(t[1])    global current_action    current_action = Update()    if current_action.set_table(t[2]):        print("{0} table not exists".format(t[2]))        return    print(condition_dict, columns_dict)    current_action.add_stack(condition_dict, columns_dict)def p_expression_update_columns(t):    '''exp_update_colums : COLUMN EQUAL COLUMN                         | COLUMN EQUAL COLUMN COMMA exp_update_colums'''    columns_dict[t[1]] = t[3]def p_expression_update_condition(t):    '''exp_update_condition : COLUMN EQUAL COLUMN                            | COLUMN EQUAL COLUMN AND exp_update_condition'''    condition_dict[t[1]] = t[3]def p_expresssion_insert_end(t):    '''exp_insert_end : VALUES LFPARENTH columns RGPARENTH END                      | LFPARENTH columns RGPARENTH VALUES LFPARENTH columns RGPARENTH END'''    if len(t) == 9:        stack.is_columns = Truedef p_expression_columns(t):    '''columns : COLUMN               | COLUMN COMMA columns'''    stack.append(t[1])def p_error(p):    if p:        print("Syntax error at {0}".format(p.value))    else:        print("Syntax error at EOF")while True:    data = input("sql>")    yacc.yacc()    yacc.parse(data)

运行该示例代码如下；

sql>create table t1 (line1, line2, line3);createcreate :  {'t1': OrderedDict([('line3', []), ('line2', []), ('line1', [])])}sql>insert into t1 values(1,2,3);insertinsert :  {'t1': OrderedDict([('line3', ['3']), ('line2', ['2']), ('line1', ['1'])])}sql>insert into t1 values(1,2,32);insertinsert :  {'t1': OrderedDict([('line3', ['3', '32']), ('line2', ['2', '2']), ('line1', ['1', '1'])])}sql>insert into t1 values(1,23, 33);insertinsert :  {'t1': OrderedDict([('line3', ['3', '32', '33']), ('line2', ['2', '2', '23']), ('line1', ['1', '1', '1'])])}sql>insert into t1 values(1,2, 43);insertinsert :  {'t1': OrderedDict([('line3', ['3', '32', '33', '43']), ('line2', ['2', '2', '23', '2']), ('line1', ['1', '1', '1', '1'])])}sql>update t1 set line1=0, line2=0 where line1=1 and line2=2;update{'line2': '2', 'line1': '1'} {'line2': '0', 'line1': '0'}['line2', 'line1']OrderedDict([('line3', ['3', '32', '33', '43']), ('line2', ['2', '2', '23', '2']), ('line1', ['1', '1', '1', '1'])])['2', '1'][['2', '1'], ['2', '1'], ['23', '1'], ['2', '1']][0, 1, 3]sql>select * from t1;select *   line3 = ['3', '32', '33', '43']   line2 = ['0', '0', '23', '0']   line1 = ['0', '0', '1', '0']sql>select line1, line2 from t1;select None   line2 = ['0', '0', '23', '0']   line1 = ['0', '0', '1', '0']sql>

本文仅仅是支持简单的语法，基本的select 语法也没有支持where条件语句，update的操作支持多条件多行的更改，创建表的语句create也并没有支持字段属性，所有的数据都是存储在python的字典中。

总结

本文只是作为在sql语法与编译器相关的一些基本的原理的知识的梳理，展示的脚本也仅仅是作为演示使用，大家有兴趣可自行学习库。由于本人才疏学浅，如有错误请批评指正。

转载地址：https://blog.csdn.net/qq_33339479/article/details/96435808 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！