1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
|
# simpleSQL.py
#
# simple demo of using the parsing library to do simple-minded SQL parsing
# could be extended to include where clauses etc.
#
# Copyright (c) 2003,2016, Paul McGuire
#
from pyparsing import Literal, CaselessLiteral, Word, delimitedList, Optional, \
Combine, Group, alphas, nums, alphanums, ParseException, Forward, oneOf, quotedString, \
ZeroOrMore, restOfLine, Keyword, upcaseTokens
# define SQL tokens
selectStmt = Forward()
SELECT = Keyword("select", caseless=True)
FROM = Keyword("from", caseless=True)
WHERE = Keyword("where", caseless=True)
ident = Word( alphas, alphanums + "_$" ).setName("identifier")
columnName = ( delimitedList( ident, ".", combine=True ) ).addParseAction(upcaseTokens)
columnNameList = Group( delimitedList( columnName ) )
tableName = ( delimitedList( ident, ".", combine=True ) ).addParseAction(upcaseTokens)
tableNameList = Group( delimitedList( tableName ) )
whereExpression = Forward()
and_ = Keyword("and", caseless=True)
or_ = Keyword("or", caseless=True)
in_ = Keyword("in", caseless=True)
E = CaselessLiteral("E")
binop = oneOf("= != < > >= <= eq ne lt le gt ge", caseless=True)
arithSign = Word("+-",exact=1)
realNum = Combine( Optional(arithSign) + ( Word( nums ) + "." + Optional( Word(nums) ) |
( "." + Word(nums) ) ) +
Optional( E + Optional(arithSign) + Word(nums) ) )
intNum = Combine( Optional(arithSign) + Word( nums ) +
Optional( E + Optional("+") + Word(nums) ) )
columnRval = realNum | intNum | quotedString | columnName # need to add support for alg expressions
whereCondition = Group(
( columnName + binop + columnRval ) |
( columnName + in_ + "(" + delimitedList( columnRval ) + ")" ) |
( columnName + in_ + "(" + selectStmt + ")" ) |
( "(" + whereExpression + ")" )
)
whereExpression << whereCondition + ZeroOrMore( ( and_ | or_ ) + whereExpression )
# define the grammar
selectStmt <<= (SELECT + ('*' | columnNameList)("columns") +
FROM + tableNameList( "tables" ) +
Optional(Group(WHERE + whereExpression), "")("where"))
simpleSQL = selectStmt
# define Oracle comment format, and ignore them
oracleSqlComment = "--" + restOfLine
simpleSQL.ignore( oracleSqlComment )
if __name__ == "__main__":
simpleSQL.runTests("""\
SELECT * from XYZZY, ABC
select * from SYS.XYZZY
Select A from Sys.dual
Select A,B,C from Sys.dual
Select A, B, C from Sys.dual
Select A, B, C from Sys.dual, Table2
Xelect A, B, C from Sys.dual
Select A, B, C frox Sys.dual
Select
Select &&& frox Sys.dual
Select A from Sys.dual where a in ('RED','GREEN','BLUE')
Select A from Sys.dual where a in ('RED','GREEN','BLUE') and b in (10,20,30)
Select A,b from table1,table2 where table1.id eq table2.id -- test out comparison operators""")
|
