diff options
Diffstat (limited to 'src/examples/simpleSQL.py')
-rw-r--r-- | src/examples/simpleSQL.py | 142 |
1 files changed, 142 insertions, 0 deletions
diff --git a/src/examples/simpleSQL.py b/src/examples/simpleSQL.py new file mode 100644 index 0000000..fc9e408 --- /dev/null +++ b/src/examples/simpleSQL.py @@ -0,0 +1,142 @@ +# 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, Paul McGuire
+#
+from pyparsing import Literal, CaselessLiteral, Word, Upcase, delimitedList, Optional, \
+ Combine, Group, alphas, nums, alphanums, ParseException, Forward, oneOf, quotedString, \
+ ZeroOrMore, restOfLine, Keyword
+
+def test( str ):
+ print str,"->"
+ try:
+ tokens = simpleSQL.parseString( str )
+ print "tokens = ", tokens
+ print "tokens.columns =", tokens.columns
+ print "tokens.tables =", tokens.tables
+ print "tokens.where =", tokens.where
+ except ParseException, err:
+ print " "*err.loc + "^\n" + err.msg
+ print err
+ print
+
+
+# define SQL tokens
+selectStmt = Forward()
+selectToken = Keyword("select", caseless=True)
+fromToken = Keyword("from", caseless=True)
+
+ident = Word( alphas, alphanums + "_$" ).setName("identifier")
+columnName = Upcase( delimitedList( ident, ".", combine=True ) )
+columnNameList = Group( delimitedList( columnName ) )
+tableName = Upcase( delimitedList( ident, ".", combine=True ) )
+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 << ( selectToken +
+ ( '*' | columnNameList ).setResultsName( "columns" ) +
+ fromToken +
+ tableNameList.setResultsName( "tables" ) +
+ Optional( Group( CaselessLiteral("where") + whereExpression ), "" ).setResultsName("where") )
+
+simpleSQL = selectStmt
+
+# define Oracle comment format, and ignore them
+oracleSqlComment = "--" + restOfLine
+simpleSQL.ignore( oracleSqlComment )
+
+
+test( "SELECT * from XYZZY, ABC" )
+test( "select * from SYS.XYZZY" )
+test( "Select A from Sys.dual" )
+test( "Select A,B,C from Sys.dual" )
+test( "Select A, B, C from Sys.dual" )
+test( "Select A, B, C from Sys.dual, Table2 " )
+test( "Xelect A, B, C from Sys.dual" )
+test( "Select A, B, C frox Sys.dual" )
+test( "Select" )
+test( "Select &&& frox Sys.dual" )
+test( "Select A from Sys.dual where a in ('RED','GREEN','BLUE')" )
+test( "Select A from Sys.dual where a in ('RED','GREEN','BLUE') and b in (10,20,30)" )
+test( "Select A,b from table1,table2 where table1.id eq table2.id -- test out comparison operators" )
+
+"""
+Test output:
+>pythonw -u simpleSQL.py +SELECT * from XYZZY, ABC -> +tokens = ['select', '*', 'from', ['XYZZY', 'ABC']] +tokens.columns = * +tokens.tables = ['XYZZY', 'ABC'] + +select * from SYS.XYZZY -> +tokens = ['select', '*', 'from', ['SYS.XYZZY']] +tokens.columns = * +tokens.tables = ['SYS.XYZZY'] + +Select A from Sys.dual -> +tokens = ['select', ['A'], 'from', ['SYS.DUAL']] +tokens.columns = ['A'] +tokens.tables = ['SYS.DUAL'] + +Select A,B,C from Sys.dual -> +tokens = ['select', ['A', 'B', 'C'], 'from', ['SYS.DUAL']] +tokens.columns = ['A', 'B', 'C'] +tokens.tables = ['SYS.DUAL'] + +Select A, B, C from Sys.dual -> +tokens = ['select', ['A', 'B', 'C'], 'from', ['SYS.DUAL']] +tokens.columns = ['A', 'B', 'C'] +tokens.tables = ['SYS.DUAL'] + +Select A, B, C from Sys.dual, Table2 -> +tokens = ['select', ['A', 'B', 'C'], 'from', ['SYS.DUAL', 'TABLE2']] +tokens.columns = ['A', 'B', 'C'] +tokens.tables = ['SYS.DUAL', 'TABLE2'] + +Xelect A, B, C from Sys.dual -> +^ +Expected 'select' +Expected 'select' (0), (1,1) + +Select A, B, C frox Sys.dual -> + ^ +Expected 'from' +Expected 'from' (15), (1,16) + +Select -> + ^ +Expected '*' +Expected '*' (6), (1,7) + +Select &&& frox Sys.dual -> + ^ +Expected '*' +Expected '*' (7), (1,8) + +>Exit code: 0
+"""
\ No newline at end of file |