<s> from pyparsing import * <EOL> LPAR , RPAR , COMMA = map ( Suppress , "<STR_LIT>" ) <EOL> select_stmt = Forward ( ) . setName ( "<STR_LIT>" ) <EOL> ( UNION , ALL , AND , INTERSECT , EXCEPT , COLLATE , ASC , DESC , ON , USING , NATURAL , INNER , <EOL> CROSS , LEFT , OUTER , JOIN , AS , INDEXED , NOT , SELECT , DISTINCT , FROM , WHERE , GROUP , BY , <EOL> HAVING , ORDER , BY , LIMIT , OFFSET ) = map ( CaselessKeyword , """<STR_LIT>""" . replace ( "<STR_LIT:U+002C>" , "<STR_LIT>" ) . split ( ) ) <EOL> ( CAST , ISNULL , NOTNULL , NULL , IS , BETWEEN , ELSE , END , CASE , WHEN , THEN , EXISTS , <EOL> COLLATE , IN , LIKE , GLOB , REGEXP , MATCH , ESCAPE , CURRENT_TIME , CURRENT_DATE , <EOL> CURRENT_TIMESTAMP ) = map ( CaselessKeyword , """<STR_LIT>""" . replace ( "<STR_LIT:U+002C>" , "<STR_LIT>" ) . split ( ) ) <EOL> keyword = MatchFirst ( ( UNION , ALL , INTERSECT , EXCEPT , COLLATE , ASC , DESC , ON , USING , NATURAL , INNER , <EOL> CROSS , LEFT , OUTER , JOIN , AS , INDEXED , NOT , SELECT , DISTINCT , FROM , WHERE , GROUP , BY , <EOL> HAVING , ORDER , BY , LIMIT , OFFSET , CAST , ISNULL , NOTNULL , NULL , IS , BETWEEN , ELSE , END , CASE , WHEN , THEN , EXISTS , <EOL> COLLATE , IN , LIKE , GLOB , REGEXP , MATCH , ESCAPE , CURRENT_TIME , CURRENT_DATE , <EOL> CURRENT_TIMESTAMP ) ) <EOL> identifier = ~ keyword + Word ( alphas , alphanums + "<STR_LIT:_>" ) <EOL> collation_name = identifier . copy ( ) <EOL> column_name = identifier . copy ( ) <EOL> column_alias = identifier . copy ( ) <EOL> table_name = identifier . copy ( ) <EOL> table_alias = identifier . copy ( ) <EOL> index_name = identifier . copy ( ) <EOL> function_name = identifier . copy ( ) <EOL> parameter_name = identifier . copy ( ) <EOL> database_name = identifier . copy ( ) <EOL> expr = Forward ( ) . setName ( "<STR_LIT>" ) <EOL> integer = Regex ( r"<STR_LIT>" ) <EOL> numeric_literal = Regex ( r"<STR_LIT>" ) <EOL> string_literal = QuotedString ( "<STR_LIT:'>" ) <EOL> blob_literal = Combine ( oneOf ( "<STR_LIT>" ) + "<STR_LIT:'>" + Word ( hexnums ) + "<STR_LIT:'>" ) <EOL> literal_value = ( numeric_literal | string_literal | blob_literal | <EOL> NULL | CURRENT_TIME | CURRENT_DATE | CURRENT_TIMESTAMP ) <EOL> bind_parameter = ( <EOL> Word ( "<STR_LIT:?>" , nums ) | <EOL> Combine ( oneOf ( "<STR_LIT>" ) + parameter_name ) <EOL> ) <EOL> type_name = oneOf ( "<STR_LIT>" ) <EOL> expr_term = ( <EOL> CAST + LPAR + expr + AS + type_name + RPAR | <EOL> EXISTS + LPAR + select_stmt + RPAR | <EOL> function_name + LPAR + Optional ( delimitedList ( expr ) ) + RPAR | <EOL> literal_value | <EOL> bind_parameter | <EOL> identifier <EOL> ) <EOL> UNARY , BINARY , TERNARY = <NUM_LIT:1> , <NUM_LIT:2> , <NUM_LIT:3> <EOL> expr << operatorPrecedence ( expr_term , <EOL> [ <EOL> ( oneOf ( '<STR_LIT>' ) | NOT , UNARY , opAssoc . LEFT ) , <EOL> ( '<STR_LIT>' , BINARY , opAssoc . LEFT ) , <EOL> ( oneOf ( '<STR_LIT>' ) , BINARY , opAssoc . LEFT ) , <EOL> ( oneOf ( '<STR_LIT>' ) , BINARY , opAssoc . LEFT ) , <EOL> ( oneOf ( '<STR_LIT>' ) , BINARY , opAssoc . LEFT ) , <EOL> ( oneOf ( '<STR_LIT>' ) , BINARY , opAssoc . LEFT ) , <EOL> ( oneOf ( '<STR_LIT>' ) | IS | IN | LIKE | GLOB | MATCH | REGEXP , BINARY , opAssoc . LEFT ) , <EOL> ( '<STR_LIT>' , BINARY , opAssoc . LEFT ) , <EOL> ( ( BETWEEN , AND ) , TERNARY , opAssoc . LEFT ) , <EOL> ] ) <EOL> compound_operator = ( UNION + Optional ( ALL ) | INTERSECT | EXCEPT ) <EOL> ordering_term = expr + Optional ( COLLATE + collation_name ) + Optional ( ASC | DESC ) <EOL> join_constraint = Optional ( ON + expr | USING + LPAR + Group ( delimitedList ( column_name ) ) + RPAR ) <EOL> join_op = COMMA | ( Optional ( NATURAL ) + Optional ( INNER | CROSS | LEFT + OUTER | LEFT | OUTER ) + JOIN ) <EOL> join_source = Forward ( ) <EOL> single_source = ( ( Group ( database_name ( "<STR_LIT>" ) + "<STR_LIT:.>" + table_name ( "<STR_LIT>" ) ) | table_name ( "<STR_LIT>" ) ) + <EOL> Optional ( Optional ( AS ) + table_alias ( "<STR_LIT>" ) ) + <EOL> Optional ( INDEXED + BY + index_name ( "<STR_LIT:name>" ) | NOT + INDEXED ) ( "<STR_LIT:index>" ) | <EOL> ( LPAR + select_stmt + RPAR + Optional ( Optional ( AS ) + table_alias ) ) | <EOL> ( LPAR + join_source + RPAR ) ) <EOL> join_source << single_source + ZeroOrMore ( join_op + single_source + join_constraint ) <EOL> result_column = "<STR_LIT:*>" | table_name + "<STR_LIT:.>" + "<STR_LIT:*>" | ( expr + Optional ( Optional ( AS ) + column_alias ) ) <EOL> whereExpression = Forward ( ) <EOL> and_ = Keyword ( "<STR_LIT>" , caseless = True ) <EOL> or_ = Keyword ( "<STR_LIT>" , caseless = True ) <EOL> in_ = Keyword ( "<STR_LIT>" , caseless = True ) <EOL> like_ = Keyword ( "<STR_LIT>" , caseless = True ) <EOL> E = CaselessLiteral ( "<STR_LIT:E>" ) <EOL> binop = oneOf ( "<STR_LIT>" , caseless = True ) <EOL> arithSign = Word ( "<STR_LIT>" , exact = <NUM_LIT:1> ) <EOL> realNum = Combine ( Optional ( arithSign ) + ( Word ( nums ) + "<STR_LIT:.>" + Optional ( Word ( nums ) ) | <EOL> ( "<STR_LIT:.>" + Word ( nums ) ) ) + <EOL> Optional ( E + Optional ( arithSign ) + Word ( nums ) ) ) <EOL> intNum = Combine ( Optional ( arithSign ) + Word ( nums ) + <EOL> Optional ( E + Optional ( "<STR_LIT:+>" ) + Word ( nums ) ) ) <EOL> columnRval = realNum | intNum | quotedString | result_column <EOL> ident = Word ( alphas , alphanums + "<STR_LIT>" + "<STR_LIT>" ) . setName ( "<STR_LIT>" ) <EOL> columnName = delimitedList ( ident , "<STR_LIT:.>" , combine = True ) . setParseAction ( upcaseTokens ) <EOL> fromToken = Keyword ( "<STR_LIT>" , caseless = True ) <EOL> tableName = delimitedList ( ident , "<STR_LIT:.>" , combine = True ) . setParseAction ( upcaseTokens ) <EOL> tableNameList = Group ( delimitedList ( tableName ) ) <EOL> whereCondition = Group ( <EOL> ( columnName + binop + columnRval ) | <EOL> ( columnName + in_ + "<STR_LIT:(>" + delimitedList ( columnRval ) + "<STR_LIT:)>" ) | <EOL> ( columnName + in_ + "<STR_LIT:(>" + select_stmt + "<STR_LIT:)>" ) | <EOL> ( "<STR_LIT:(>" + whereExpression + "<STR_LIT:)>" ) <EOL> ) <EOL> whereExpression << whereCondition + ZeroOrMore ( ( and_ | or_ ) + whereExpression ) <EOL> select_core = ( SELECT + Optional ( DISTINCT | ALL ) + <EOL> ( '<STR_LIT:*>' | Group ( delimitedList ( columnName ) ) ) ( "<STR_LIT>" ) + <EOL> fromToken + <EOL> tableNameList . setResultsName ( "<STR_LIT>" ) + <EOL> Optional ( WHERE + Group ( delimitedList ( whereExpression ) ) ( "<STR_LIT>" ) ) ) <EOL> select_stmt << ( select_core + ZeroOrMore ( compound_operator + select_core ) + <EOL> Optional ( ORDER + BY + Group ( delimitedList ( ordering_term ) ) ( "<STR_LIT>" ) ) + <EOL> Optional ( GROUP + BY + Group ( delimitedList ( ordering_term ) ) ( "<STR_LIT>" ) ) + <EOL> Optional ( HAVING + expr ( "<STR_LIT>" ) ) + <EOL> Optional ( LIMIT + ( integer + OFFSET + integer | integer + COMMA + integer ) ) ) <EOL> simpleSQL = select_stmt <EOL> sqlLiteComment = "<STR_LIT:#>" + restOfLine <EOL> simpleSQL . ignore ( sqlLiteComment ) <EOL> def parse_sql ( str ) : <EOL> try : <EOL> return simpleSQL . parseString ( str ) <EOL> except ParseException , err : <EOL> print "<STR_LIT:U+0020>" * err . loc + "<STR_LIT>" + err . msg <EOL> print err </s>
