本文通过PLY实现一个简单的sql库
本文主要简述一下，有关语法解析的库的使用，并使用改实例编写一个简单的模仿最基本的sql语句的数据库，本文代码仅供示例参考。
语法解析与上下文
语法解析是一个比较大的内容，早起比较成熟的有lex与yacc，该工具相对比较成熟，详细的资料大家可以自行查阅资料，在编译中使用较多的是上下文无关文法主要的是BNF，大家有兴趣可查阅sql语句的BNF文档，标准的sql的文法相对复杂，本文只是为了实现最基本的工作故只是做了最简单的解析。
示例代码
import ply.lex as lex
import ply.yacc as yacc
import collections
tokens = (
'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 t
def 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 = None
columns_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 = True
def 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);
create
create : {'t1': OrderedDict([('line3', []), ('line2', []), ('line1', [])])}
sql>insert into t1 values(1,2,3);
insert
insert : {'t1': OrderedDict([('line3', ['3']), ('line2', ['2']), ('line1', ['1'])])}
sql>insert into t1 values(1,2,32);
insert
insert : {'t1': OrderedDict([('line3', ['3', '32']), ('line2', ['2', '2']), ('line1', ['1', '1'])])}
sql>insert into t1 values(1,23, 33);
insert
insert : {'t1': OrderedDict([('line3', ['3', '32', '33']), ('line2', ['2', '2', '23']), ('line1', ['1', '1', '1'])])}
sql>insert into t1 values(1,2, 43);
insert
insert : {'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语法与编译器相关的一些基本的原理的知识的梳理，展示的脚本也仅仅是作为演示使用，大家有兴趣可自行学习库。由于本人才疏学浅，如有错误请批评指正。
