def test_where_2(self): result = parse( "select * from blog where name='zhangsan' and (age<18 or age>30);") expected = { 'type': 'SELECT', 'column': [{ 'name': '*' }], 'table': [{ 'name': 'blog' }], 'join': [], 'where': [{ 'name': 'name', 'value': 'zhangsan', 'compare': '=' }, 'AND', [{ 'name': 'age', 'value': 18, 'compare': '<' }, 'OR', { 'name': 'age', 'value': 30, 'compare': '>' }]], 'group': [], 'having': [], 'order': [], 'limit': [] } self.assertEqual(result, expected)
def test_left_join(self): result = parse( "select * from blog b left join user u on b.name = u.name;") expected = { 'type': 'SELECT', 'column': [{ 'name': '*' }], 'table': [{ 'name': 'blog', 'alias': 'b' }], 'join': [{ 'type': 'LEFT', 'table': [{ 'name': 'user', 'alias': 'u' }], 'on': ['b.name', 'u.name'] }], 'where': [], 'group': [], 'having': [], 'order': [], 'limit': [] } self.assertEqual(result, expected)
def test_drop(self): result = parse("drop table blog;") expected = { 'type':'DROP', 'table': 'blog' } self.assertEqual(result,expected)
def test_group_by_2(self): result = parse( "select blog.name,blog.age,count(u.*) from blog as b,username as u;" ) expected = { 'type': 'SELECT', 'column': [{ 'name': 'blog.name' }, { 'name': 'blog.age' }, { 'name': { 'COUNT': 'u.*' } }], 'table': [{ 'name': 'blog', 'alias': 'b' }, { 'name': 'username', 'alias': 'u' }], 'join': [], 'where': [], 'group': [], 'having': [], 'order': [], 'limit': [] } self.assertEqual(result, expected)
def test_order_by(self): result = parse("select * from blog order by age desc,name;") expected = { 'type': 'SELECT', 'column': [{ 'name': '*' }], 'table': [{ 'name': 'blog' }], 'join': [], 'where': [], 'group': [], 'having': [], 'order': [{ 'name': 'age', 'type': 'DESC' }, { 'name': 'name', 'type': 'ASC' }], 'limit': [] } self.assertEqual(result, expected)
def test_alter(self): result = parse("alter table blog add tag varchar(5);") expected = { 'type':'ALTER', 'table': 'blog', 'columns': {'ADD':{'name':'tag','type':'VARCHAR(5)'}} } self.assertEqual(result,expected)
def test_delete(self): result = parse("delete from blog where name=dd;") expected = { 'type':'DELETE', 'table': [{'name':'blog'}], 'where' : [{'name': 'name', 'value': 'dd','compare': '='}] } self.assertEqual(result,expected)
def execute(self, query): asts = parse(query).asList() rs = [] for ast in asts: env = OrderedDict() r = self.evaluate(ast, env) rs.append(r) return rs
def test_update(self): result = parse("update blog set name=cc where name=dd;") expected = { 'type':'UPDATE', 'table': [{'name':'blog'}], 'column' : [{'name':'name','value':'cc'}], 'where' : [{'name': 'name', 'value': 'dd','compare': '='}] } self.assertEqual(result,expected)
def test_create(self): result = parse("create table blog (name varchar(30),age int);") expected = { 'type':'CREATE', 'table': 'blog', 'columns' : [{'name': 'name','type':'VARCHAR(30)'}, {'name': 'age','type':'INT'}] } self.assertEqual(result,expected)
def test_insert(self): result = parse("insert into blog (name,age) values (cc,18),('dd',2);") expected = { 'type':'INSERT', 'table': [{'name':'blog'}], 'columns' : [{'name': 'name'}, {'name': 'age'}], 'values' : [['cc',18], ['dd',2]] } self.assertEqual(result,expected)
def test_where_1(self): result = parse("select * from blog where name='zhangsan';") expected = { 'type':'SELECT', 'column':[{'name':'*'}], 'table':[{'name':'blog'}], 'join': [], 'where':[{'name': 'name', 'value': 'zhangsan', 'compare': '='}], 'group':[], 'having':[], 'order':[], 'limit':[] } self.assertEqual(result,expected)
def test_simple(self): result = parse("select * from blog;") expected = { 'type':'SELECT', 'column':[{'name':'*'}], 'table':[{'name':'blog'}], 'join':[], 'where':[], 'group':[], 'having':[], 'order':[], 'limit':[] } self.assertEqual(result,expected)
def test_distinct_2(self): result = parse("select avg(distinct(name)) from blog;") expected = { 'type':'SELECT', 'column':[{'name':{'AVG':{'DISTINCT':'name'}}}], 'table':[{'name':'blog'}], 'join': [], 'where':[], 'group':[], 'having':[], 'order':[], 'limit':[] } self.assertEqual(result,expected)
def test_where_3(self): result = parse("select * from blog where age is not null;") expected = { 'type':'SELECT', 'column':[{'name':'*'}], 'table':[{'name':'blog'}], 'join': [], 'where':[{'name': 'age', 'value': 'NOT NULL', 'compare': 'IS'}], 'group':[], 'having':[], 'order':[], 'limit':[] } self.assertEqual(result,expected)
def test_where_4(self): result = parse("select * from blog where age not in (13,12,11);") expected = { 'type':'SELECT', 'column':[{'name':'*'}], 'table':[{'name':'blog'}], 'join': [], 'where':[{'name': 'age', 'value': [13,12,11], 'compare': 'NOT IN'}], 'group':[], 'having':[], 'order':[], 'limit':[] } self.assertEqual(result,expected)
def test_column(self): result = parse("select count(*) as cnt from blog;") expected = { 'type':'SELECT', 'column':[{'name':{'COUNT':'*'},'alias':'cnt'}], 'table':[{'name':'blog'}], 'join': [], 'where':[], 'group':[], 'having':[], 'order':[], 'limit':[] } self.assertEqual(result,expected)
def exec(querys): ''' Парсинг SQL запросов querys - SQL запросы, разделённые ; пример: CREATE DATABASE school; CREATE TABLE student ( ... ); INSERT INTO school (...) VALUES (...); return - None во всех случаях, кроме SELECT: результат выборки, пример: [{ 'schema' : [ table1_attr1_name, table1_attr2_name, table2_attr2_name ] 'body' : [ (table1_attr1_val1, table1_attr2_val1, table2_attr2_val1), (table1_attr1_val2, table1_attr2_val2, table2_attr2_val2), ( ... ) ] }, { ... } ] ''' try: sqlparser.parse(querys) except Exception as e: raise SQLMY_Exception(e)
def test_table(self): result = parse("select b.name as name,b.age as age from blog b;") expected = { 'type':'SELECT', 'column':[{'name':'b.name','alias':'name'}, {'name':'b.age','alias':'age'}], 'table':[{'name':'blog','alias':'b'}], 'join': [], 'where':[], 'group':[], 'having':[], 'order':[], 'limit':[] } self.assertEqual(result,expected)
def test_join(self): result = parse("select * from blog b JOIN user u on b.name = u.name join tags t on b.tag=t.tag;") expected = { 'type':'SELECT', 'column':[{'name':'*'}], 'table':[{'name':'blog','alias':'b'}], 'join': [{'type':'INNER','table':[{'name':'user','alias':'u'}],'on':['b.name','u.name']}, {'type': 'INNER', 'table': [{'name': 'tags', 'alias': 't'}], 'on': ['b.tag', 't.tag']}], 'where':[], 'group':[], 'having':[], 'order':[], 'limit':[] } self.assertEqual(result,expected)
def test_group_by(self): result = parse("select name,age,count(*) from blog group by name,age;") expected = { 'type':'SELECT', 'column':[{'name':'name'}, {'name':'age'}, {'name':{'COUNT':'*'}}], 'table':[{'name':'blog'}], 'join': [], 'where':[], 'group':['name','age'], 'having':[], 'order':[], 'limit':[] } self.assertEqual(result,expected)
def test_like(self): result = parse("select * from blog where name like '%张%';") expected = { 'type':'SELECT', 'column':[{'name':'*'}], 'table':[{'name':'blog'}], 'join': [], 'where': [ {'name': 'name', 'value': '%张%', 'compare': 'LIKE'}, ], 'group':[], 'having':[], 'order':[], 'limit':[] } self.assertEqual(result,expected)
def test_having(self): result = parse( "select name,age,count(*),avg(age) from blog group by name,age having count(*)>2 and avg(age)<20;" ) expected = { 'type': 'SELECT', 'column': [{ 'name': 'name' }, { 'name': 'age' }, { 'name': { 'COUNT': '*' } }, { 'name': { 'AVG': 'age' } }], 'table': [{ 'name': 'blog' }], 'join': [], 'where': [], 'group': ['name', 'age'], 'having': [{ 'name': { 'COUNT': '*' }, 'value': 2, 'compare': '>' }, 'AND', { 'name': { 'AVG': 'age' }, 'value': 20, 'compare': '<' }], 'order': [], 'limit': [] } self.assertEqual(result, expected)
def test(str): print("\n", str, "->") tokens = sqlparser.parse(str) if tokens: print("tokens = ", tokens) print("tokens.columns =", tokens.columns) print("tokens.tables =", tokens.tables) print("tokens.where =", tokens.where) csvstr = """F1,F2,F3,F4,F5 "A",2,3,4,5 "B",6,7,8,9 """ r = csv.DictReader(csvstr.split("\n")) for row in r: for col in tokens.columns: print(row[col], end="") print() print