simpleSQL.py 2.9 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495
  1. # simpleSQL.py
  2. #
  3. # simple demo of using the parsing library to do simple-minded SQL parsing
  4. # could be extended to include where clauses etc.
  5. #
  6. # Copyright (c) 2003,2016, Paul McGuire
  7. #
  8. from pyparsing import Word, delimitedList, Optional, \
  9. Group, alphas, alphanums, Forward, oneOf, quotedString, \
  10. infixNotation, opAssoc, \
  11. ZeroOrMore, restOfLine, CaselessKeyword, pyparsing_common as ppc
  12. # define SQL tokens
  13. selectStmt = Forward()
  14. SELECT, FROM, WHERE, AND, OR, IN, IS, NOT, NULL = map(CaselessKeyword,
  15. "select from where and or in is not null".split())
  16. NOT_NULL = NOT + NULL
  17. ident = Word( alphas, alphanums + "_$" ).setName("identifier")
  18. columnName = delimitedList(ident, ".", combine=True).setName("column name")
  19. columnName.addParseAction(ppc.upcaseTokens)
  20. columnNameList = Group( delimitedList(columnName))
  21. tableName = delimitedList(ident, ".", combine=True).setName("table name")
  22. tableName.addParseAction(ppc.upcaseTokens)
  23. tableNameList = Group(delimitedList(tableName))
  24. binop = oneOf("= != < > >= <= eq ne lt le gt ge", caseless=True)
  25. realNum = ppc.real()
  26. intNum = ppc.signed_integer()
  27. columnRval = realNum | intNum | quotedString | columnName # need to add support for alg expressions
  28. whereCondition = Group(
  29. ( columnName + binop + columnRval ) |
  30. ( columnName + IN + Group("(" + delimitedList( columnRval ) + ")" )) |
  31. ( columnName + IN + Group("(" + selectStmt + ")" )) |
  32. ( columnName + IS + (NULL | NOT_NULL))
  33. )
  34. whereExpression = infixNotation(whereCondition,
  35. [
  36. (NOT, 1, opAssoc.RIGHT),
  37. (AND, 2, opAssoc.LEFT),
  38. (OR, 2, opAssoc.LEFT),
  39. ])
  40. # define the grammar
  41. selectStmt <<= (SELECT + ('*' | columnNameList)("columns") +
  42. FROM + tableNameList( "tables" ) +
  43. Optional(Group(WHERE + whereExpression), "")("where"))
  44. simpleSQL = selectStmt
  45. # define Oracle comment format, and ignore them
  46. oracleSqlComment = "--" + restOfLine
  47. simpleSQL.ignore( oracleSqlComment )
  48. if __name__ == "__main__":
  49. simpleSQL.runTests("""\
  50. # multiple tables
  51. SELECT * from XYZZY, ABC
  52. # dotted table name
  53. select * from SYS.XYZZY
  54. Select A from Sys.dual
  55. Select A,B,C from Sys.dual
  56. Select A, B, C from Sys.dual, Table2
  57. # FAIL - invalid SELECT keyword
  58. Xelect A, B, C from Sys.dual
  59. # FAIL - invalid FROM keyword
  60. Select A, B, C frox Sys.dual
  61. # FAIL - incomplete statement
  62. Select
  63. # FAIL - incomplete statement
  64. Select * from
  65. # FAIL - invalid column
  66. Select &&& frox Sys.dual
  67. # where clause
  68. Select A from Sys.dual where a in ('RED','GREEN','BLUE')
  69. # compound where clause
  70. Select A from Sys.dual where a in ('RED','GREEN','BLUE') and b in (10,20,30)
  71. # where clause with comparison operator
  72. Select A,b from table1,table2 where table1.id eq table2.id
  73. """)