def createUser(query): userStmt = Forward() CREATE, USER, IDENTIFIED, BY = map(CaselessKeyword, "create user identified by".split()) ident = Word(alphas).setName("identifier") userName = delimitedList(ident).setName("user") userName.addParseAction(ppc.upcaseTokens) #tableNameList = Group(delimitedList(tableName)) intValue = ppc.signed_integer() pwdValue = Group("passer" + intValue) # define the grammar userStmt <<= (CREATE + USER + userName("user") + IDENTIFIED + BY + pwdValue("pwd") + Literal(';')) simpleSQL = userStmt # define Oracle comment format, and ignore them oracleSqlComment = "--" + restOfLine simpleSQL.ignore(oracleSqlComment) return simpleSQL.parseString(query)
def datetime_parser() -> ParseResults: # pylint: disable=too-many-locals ( # pylint: disable=invalid-name DATETIME, DATEADD, DATETRUNC, LASTDAY, HOLIDAY, YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, ) = map( CaselessKeyword, "datetime dateadd datetrunc lastday holiday " "year quarter month week day hour minute second".split(), ) lparen, rparen, comma = map(Suppress, "(),") int_operand = pyparsing_common.signed_integer().setName("int_operand") text_operand = quotedString.setName("text_operand").setParseAction( EvalText) # allow expression to be used recursively datetime_func = Forward().setName("datetime") dateadd_func = Forward().setName("dateadd") datetrunc_func = Forward().setName("datetrunc") lastday_func = Forward().setName("lastday") holiday_func = Forward().setName("holiday") date_expr = (datetime_func | dateadd_func | datetrunc_func | lastday_func | holiday_func) datetime_func <<= (DATETIME + lparen + text_operand + rparen).setParseAction(EvalDateTimeFunc) dateadd_func <<= ( DATEADD + lparen + Group(date_expr + comma + int_operand + comma + (YEAR | QUARTER | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND) + ppOptional(comma)) + rparen).setParseAction(EvalDateAddFunc) datetrunc_func <<= ( DATETRUNC + lparen + Group(date_expr + comma + (YEAR | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND) + ppOptional(comma)) + rparen).setParseAction(EvalDateTruncFunc) lastday_func <<= (LASTDAY + lparen + Group(date_expr + comma + (YEAR | MONTH | WEEK) + ppOptional(comma)) + rparen).setParseAction(EvalLastDayFunc) holiday_func <<= (HOLIDAY + lparen + Group(text_operand + ppOptional(comma) + ppOptional(date_expr) + ppOptional(comma) + ppOptional(text_operand) + ppOptional(comma)) + rparen).setParseAction(EvalHolidayFunc) return date_expr
def _prepare_sql_parser(): """ Подготовка SQL-like парсера, для разбора условий в фильтрах Returns ------- simple_sql : obj Объект класса отвечающего за парсинг """ # define SQL tokens select_stmt = Forward() AND, OR, IN, NIN = map(CaselessKeyword, "and or in nin".split()) ident = Word(alphas, alphanums + "_$").setName("identifier") column_name = delimitedList(ident, ".", combine=True).setName("column name") column_name.addParseAction() binop = oneOf("= != > < <= >=", caseless=True) real_num = ppc.real() int_num = ppc.signed_integer() column_rval = (real_num | int_num | quotedString | column_name ) # need to add support for alg expressions where_condition = Group( (column_name + binop + column_rval) | (column_name + IN + Group("(" + delimitedList(column_rval) + ")")) | (column_name + IN + Group("(" + select_stmt + ")"))) where_expression = infixNotation( where_condition, [ (AND, 2, opAssoc.LEFT), (OR, 2, opAssoc.LEFT), ], ) # define the grammar select_stmt <<= where_expression simple_sql = select_stmt # define Oracle comment format, and ignore them oracle_sql_comment = "--" + restOfLine simple_sql.ignore(oracle_sql_comment) return simple_sql
def connexion(query): # define SQL tokens stmt = Forward() MYSQL, U, P = map(CaselessKeyword, "mysql u p".split()) ident = Word(alphas).setName("identifier") userName = delimitedList(ident).setName("user") userName.addParseAction(ppc.upcaseTokens) intValue = ppc.signed_integer() pwdValue = quotedString() # define the grammar stmt <<= (MYSQL + Literal('-') + U + userName("user") + Literal('-') + P + pwdValue("pwd") + Literal(';')) simpleSQL = stmt # define Oracle comment format, and ignore them oracleSqlComment = "--" + restOfLine simpleSQL.ignore(oracleSqlComment) return simpleSQL.parseString(query)
def getLiteral(self): ''' get the literal sub Grammar ''' uri=Regex(SiDIFParser.getUriRegexp())('uri') booleanLiteral=oneOf(["true","false"]).setParseAction(self.convertToBoolean)('boolean') hexLiteral=(Suppress("0x")+(Word(hexnums).setParseAction(tokenMap(int, 16))))('hexLiteral') integerLiteral=pyparsing_common.signed_integer('integerLiteral') floatingPointLiteral=Group( pyparsing_common.sci_real|pyparsing_common.real ).setParseAction(self.handleGroup)('floatingPointLiteral') timeLiteral=Regex(r"[0-9]{2}:[0-9]{2}(:[0-9]{2})?").setParseAction(self.convertToTime)('timeLiteral') dateLiteral=pyparsing_common.iso8601_date.copy().setParseAction(pyparsing_common.convertToDate())('dateLiteral') dateTimeLiteral=Group( dateLiteral+Optional(timeLiteral) ).setParseAction(self.handleDateTimeLiteral)('dateTimeLiteral') stringLiteral=Group( Suppress('"')+ZeroOrMore(CharsNotIn('"')|LineEnd())+Suppress('"') ).setParseAction(self.handleStringLiteral)('stringLiteral') literal=Group( uri | stringLiteral | booleanLiteral | hexLiteral | dateTimeLiteral | timeLiteral | floatingPointLiteral| integerLiteral ).setParseAction(self.handleGroup)("literal") return literal
def createTable(query): # define SQL tokens createStmt = Forward() CREATE, TABLE, INT, DATE, TIME, CHAR, VARCHAR, FLOAT = map( CaselessKeyword, "create table int date time char varchar float".split()) ident = Word(alphas).setName("identifier") #columnName = delimitedList(column) #columnName.addParseAction(ppc.upcaseTokens) #columnNameList = Group(delimitedList(columnName)) tableName = delimitedList(ident, ".", combine=True).setName("table") tableName.addParseAction(ppc.upcaseTokens) #tableNameList = Group(delimitedList(tableName)) intValue = ppc.signed_integer() INTEGER = INT varcharType = Combine(VARCHAR + "(" + intValue + ")") columnType = (INT | INTEGER | TIME | DATE | varcharType | CHAR | FLOAT) columnValue = Group(ident + columnType) columnNameList = Group(delimitedList(columnValue, ",")) # define the grammar createStmt <<= (CREATE + TABLE("create") + tableName("table") + Literal("(") + columnNameList("column") + Literal(")") + Literal(';')) simpleSQL = createStmt # define Oracle comment format, and ignore them oracleSqlComment = "--" + restOfLine simpleSQL.ignore(oracleSqlComment) return simpleSQL.parseString(query)
SELECT, FROM, WHERE = map(CaselessKeyword, "select from where".split()) ident = Word( alphas, alphanums + "_$" ).setName("identifier") columnName = delimitedList(ident, ".", combine=True).setName("column name") columnName.addParseAction(pyparsing_common.upcaseTokens) columnNameList = Group( delimitedList(columnName)) tableName = delimitedList(ident, ".", combine=True).setName("table name") tableName.addParseAction(pyparsing_common.upcaseTokens) tableNameList = Group(delimitedList(tableName)) whereExpression = Forward() and_, or_, in_ = map(CaselessKeyword, "and or in".split()) binop = oneOf("= != < > >= <= eq ne lt le gt ge", caseless=True) realNum = pyparsing_common.real() intNum = pyparsing_common.signed_integer() 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"))
from pyparsing import (alphanums, alphas, CaselessKeyword, delimitedList, Group, infixNotation, oneOf, opAssoc, pyparsing_common as ppc, quotedString, Word) _logger = logging.getLogger(__name__) AND, OR, IN, IS, NOT, NULL, BETWEEN = map( CaselessKeyword, "and or in is not null between".split()) NOT_NULL = NOT + NULL ident = Word(alphas, alphanums + "_$").setName("identifier") columnName = delimitedList(ident, ".", combine=True).setName("column name") binop = oneOf("= == != < > >= <= eq ne lt le gt ge <>", caseless=False) realNum = ppc.real() intNum = ppc.signed_integer() columnRval = (realNum | intNum | quotedString | columnName) # need to add support for alg expressions whereCondition = Group( (columnName + binop + columnRval) | (columnName + IN + Group("(" + delimitedList(columnRval) + ")")) | (columnName + IS + (NULL | NOT_NULL)) | (columnName + BETWEEN + columnRval + AND + columnRval)) whereExpression = infixNotation( Group(whereCondition | NOT + whereCondition | NOT + Group('(' + whereCondition + ')')
def create_sql(self): selectStmt = Forward() SELECT, FROM, WHERE, AND, OR, IN, IS, NOT, NULL, COUNT, AVG, MIN, MAX, SUM, AS = map( CaselessKeyword, "select from where and or in is not null count avg min max sum as". split()) NOT_NULL = NOT + NULL ident = Word(alphas, alphanums + "_$").setName("identifier") alias = delimitedList(ident, ".", combine=True).setName("alias") alias.addParseAction(ppc.upcaseTokens) columnName = delimitedList(ident, ".", combine=True).setName("column name") columnName.addParseAction(ppc.upcaseTokens) columnNameList = Group(delimitedList(columnName)) tableName = delimitedList(ident, ".", combine=True).setName("table name") tableName.addParseAction(ppc.upcaseTokens) tableNameRalias = Group((tableName("table") + AS + alias("alias")) | (tableName("table"))) tableNameList = Group(delimitedList(tableNameRalias)) binop = oneOf("= != < > >= <= eq ne lt le gt ge", caseless=True) realNum = ppc.real() intNum = ppc.signed_integer() columnRval = realNum | intNum | quotedString | columnName # need to add support for alg expressions val = realNum | intNum | quotedString columnRstar = '*' | columnName # need to add support for alg expressions EquiJoin = (columnName('col1') + '=' + columnName('col2')) equalityPredicate = columnName('col1') + '=' + columnRval('val') Predicates = Group((columnName('col1') + binop + columnRval) | (columnName('col1') + IN + Group("(" + delimitedList(columnRval) + ")")) | (columnName('col1') + IN + Group("(" + selectStmt + ")")) | (columnName + IS + (NULL | NOT_NULL))) whereCondition = Group( EquiJoin('equijoin') | equalityPredicate('equalitypredicate') | Predicates('otherPredicates')) whereCondition_sketch = Group( EquiJoin('equijoin') | equalityPredicate('equalitypredicate')) Aggregates = Group(((COUNT | AVG | MIN | MAX | SUM)("operator") + (Group("(" + columnName + ")"))("operand")) | (COUNT("operator") + Group("(" + "*" + ")")("operand"))) AggregateExpression = delimitedList(Aggregates) whereExpression_predicates = infixNotation(whereCondition_sketch, [ (AND, 2, opAssoc.LEFT), ]) whereExpression = infixNotation(whereCondition, [ (NOT, 1, opAssoc.RIGHT), (AND, 2, opAssoc.LEFT), (OR, 2, opAssoc.LEFT), ]) # define the grammar selectStmt <<= (SELECT + ((AggregateExpression)("aggregates") | ('*' | columnNameList)("columns")) + FROM + tableNameList("tables") + WHERE + (whereExpression_predicates)("sketch_predicates") + Optional(AND + (whereExpression)("ignored_predicates"))) simpleSQL = selectStmt # define Oracle comment format, and ignore them oracleSqlComment = "--" + restOfLine simpleSQL.ignore(oracleSqlComment) return simpleSQL
SELECT, FROM, WHERE = map(CaselessKeyword, "select from where".split()) ident = Word( alphas, alphanums + "_$" ).setName("identifier") columnName = delimitedList(ident, ".", combine=True).setName("column name") columnName.addParseAction(pyparsing_common.upcaseTokens) columnNameList = Group( delimitedList(columnName)) tableName = delimitedList(ident, ".", combine=True).setName("table name") tableName.addParseAction(pyparsing_common.upcaseTokens) tableNameList = Group(delimitedList(tableName)) whereExpression = Forward() and_, or_, in_ = map(CaselessKeyword, "and or in".split()) binop = oneOf("= != < > >= <= eq ne lt le gt ge", caseless=True) realNum = pyparsing_common.real() intNum = pyparsing_common.signed_integer() 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"))
"programa car int retorne".split()) ESCREVA, NOVALINHA, SE, ENTAO = map(CaselessKeyword, "escreva novalinha se entao".split()) SENAO, ENQUANTO, EXECUTE, LEIA, TERMINATOR = map( CaselessKeyword, "senao enquanto execute leia ;".split()) keywords = MatchFirst( (PROGRAMA, CAR, INT, RETORNE, ESCREVA, NOVALINHA, SE, ENTAO, SENAO, ENQUANTO, EXECUTE, LEIA)).setName("Reserved Words") #Define the Terminator character TERMINATOR = Word(";").setName("Terminator") #Define the numbers realNum = ppc.real().setName("Real Number") intNum = ppc.signed_integer().setName("Integer Number") #Define the identificator identifier = Word(alphas, alphanums + "_$").setName("Identifier") #Types Definition Type = (INT | CAR).setName("Type") #<<<<<<<<<<<<<<< BASICS DEFINITIONS<<<<<<<<<<<<<<<<<<<<<<<< #>>>>>>>>>>>>>>> EXPRESSIONS DECLARATIONS>>>>>>>>>>>>>>>>>> #Assigning the recursive expressions Command = Forward() Expr = Forward() AssignExpr = Forward()
def __init__(self): """ A program is a list of statements. Statements can be 'set' or 'select' statements. """ statement = Forward() SELECT, FROM, WHERE, SET, AS = map(CaselessKeyword, "select from where set as".split()) ident = Word( "$" + alphas, alphanums + "_$" ).setName("identifier") columnName = delimitedList(ident, ".", combine=True).setName("column name") columnNameList = Group( delimitedList(columnName)) tableName = delimitedList(ident, ".", combine=True).setName("column name") tableNameList = Group(delimitedList(tableName)) SEMI,COLON,LPAR,RPAR,LBRACE,RBRACE,LBRACK,RBRACK,DOT,COMMA,EQ = map(Literal,";:(){}[].,=") arrow = Literal ("->") t_expr = Group(ident + LPAR + Word("$" + alphas, alphanums + "_$") + RPAR + ZeroOrMore(LineEnd())).setName("t_expr") | \ Word(alphas, alphanums + "_$") + ZeroOrMore(LineEnd()) t_expr_chain = t_expr + ZeroOrMore(arrow + t_expr) whereExpression = Forward() and_, or_, in_ = map(CaselessKeyword, "and or in".split()) binop = oneOf("= != < > >= <= eq ne lt le gt ge", caseless=True) realNum = ppc.real() intNum = ppc.signed_integer() columnRval = realNum | intNum | quotedString | columnName # need to add support for alg expressions whereCondition = Group( ( columnName + binop + (columnRval | Word(printables) ) ) | ( columnName + in_ + "(" + delimitedList( columnRval ) + ")" ) | ( columnName + in_ + "(" + statement + ")" ) | ( "(" + whereExpression + ")" ) ) whereExpression << whereCondition + ZeroOrMore( ( and_ | or_ ) + whereExpression ) ''' Assignment for handoff. ''' setExpression = Forward () setStatement = Group( ( ident ) | ( quotedString("json_path") + AS + ident("name") ) | ( "(" + setExpression + ")" ) ) setExpression << setStatement + ZeroOrMore( ( and_ | or_ ) + setExpression ) optWhite = ZeroOrMore(LineEnd() | White()) """ Define the statement grammar. """ statement <<= ( Group( Group(SELECT + t_expr_chain)("concepts") + optWhite + Group(FROM + tableNameList) + optWhite + Group(Optional(WHERE + whereExpression("where"), "")) + optWhite + Group(Optional(SET + setExpression("set"), ""))("select") ) | Group( SET + (columnName + EQ + ( quotedString | intNum | realNum )) )("set") )("statement") """ Make a program a series of statements. """ self.program = statement + ZeroOrMore(statement) """ Make rest-of-line comments. """ comment = "--" + restOfLine self.program.ignore (comment)