1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 from pyparsing import Literal, CaselessLiteral, Word, delimitedList, Optional, \
22 Combine, Group, alphas, nums, alphanums, ParseException, Forward, oneOf, quotedString, \
23 ZeroOrMore, restOfLine, Keyword, upcaseTokens, removeQuotes, Suppress
24
26 print
27 print str,"->"
28 try:
29 tokens = simpleSQL.parseString( str )
30 print " tokens = ", tokens
31 print " tokens.columns =", tokens.columns
32 print " tokens.tables =", tokens.tables
33 print " tokens.where =", tokens.where
34 except ParseException, err:
35 print " "*err.loc + "^\n" + err.msg
36 print err
37
38 selectStmt = Forward()
39 selectToken = Keyword("select", caseless=True)
40 fromToken = Keyword("from", caseless=True)
41
42 ident = Word( alphas, alphanums + "_$" ).setName("identifier")
43 ident2 = quotedString.setParseAction(removeQuotes) | Word( alphanums, alphanums + "_")
44 columnName = ident2
45 columnNameList = Group((Literal("images") | Literal("albums")) + Suppress("(") + Group(delimitedList( columnName )) + Suppress(")"))
46 tableName = ident2
47 tableNameList = tableName
48
49 whereExpression = Forward()
50 and_ = Keyword("and", caseless=True)
51 or_ = Keyword("or", caseless=True)
52 in_ = Keyword("in", caseless=True)
53 contains_ = Keyword("contains", caseless=True)
54
55 E = CaselessLiteral("E")
56 binop = oneOf("= != < > >= <= eq ne lt le gt ge", caseless=True)
57 arithSign = Word("+-",exact=1)
58
59
60
61 intNum = Combine( Optional(arithSign) + Word( nums ) + Optional( E + Optional("+") + Word(nums) ) )
62
63 columnRval = intNum | quotedString | columnName
64 whereCondition = Group(
65 ( columnName + binop + columnRval ) |
66 ( columnName + contains_ + columnRval ) |
67 ( columnName + in_ + "(" + delimitedList( columnRval ) + ")" ) |
68 ( columnName + in_ + "(" + selectStmt + ")" ) |
69 ( "(" + whereExpression + ")" )
70 )
71 whereExpression << whereCondition + ZeroOrMore( ( and_ | or_ ) + whereExpression )
72
73
74 selectStmt << ( selectToken +
75 ( '*' | columnNameList ).setResultsName( "columns" ) +
76 fromToken +
77 tableNameList.setResultsName( "tables" ) +
78 Optional( CaselessLiteral("where").suppress() + whereExpression, "" ).setResultsName("where") )
79
80 simpleSQL = selectStmt
81
82 sqlComment = "--" + restOfLine
83 simpleSQL.ignore( sqlComment )
84
85
86
87
88 test('select images(FileName) from "Israel" where Keywords contains "blah";')
89 test('select images(FileName) from 4687221 where Keywords contains "blah" and ISO = 200;')
90 test('select images(FileName, Size) from 4687221_XoUoj where Keywords contains "blah";')
91
92
93