分享一个python版的SQL Select语句解析代码

<h1 id="pythonsql-select">分享一个python版的SQL Select语句解析代码</h1>

<p>标签（空格分隔）： python sqlparser</p>

<hr />

<p>```python
# select_parser.py
# Copyright 2010, Paul McGuire
#
# a simple SELECT statement parser, taken from SQLite’s SELECT statement
# definition at http://www.sqlite.org/lang_select.html
#
from pyparsing import *</p>

<p>LPAR,RPAR,COMMA = map(Suppress,”(),”)
select_stmt = Forward().setName(“select statement”)</p>

<h1 id="keywords">keywords</h1>
<p>(UNION, ALL, AND, INTERSECT, EXCEPT, COLLATE, ASC, DESC, ON, USING, NATURAL, INNER,
 CROSS, LEFT, OUTER, JOIN, AS, INDEXED, NOT, SELECT, DISTINCT, FROM, WHERE, GROUP, BY,
 HAVING, ORDER, BY, LIMIT, OFFSET) =  map(CaselessKeyword, “"”UNION, ALL, AND, INTERSECT,
 EXCEPT, COLLATE, ASC, DESC, ON, USING, NATURAL, INNER, CROSS, LEFT, OUTER, JOIN, AS, INDEXED, NOT, SELECT,
 DISTINCT, FROM, WHERE, GROUP, BY, HAVING, ORDER, BY, LIMIT, OFFSET””“.replace(“,”,””).split())
(CAST, ISNULL, NOTNULL, NULL, IS, BETWEEN, ELSE, END, CASE, WHEN, THEN, EXISTS,
 COLLATE, IN, LIKE, GLOB, REGEXP, MATCH, ESCAPE, CURRENT_TIME, CURRENT_DATE,
 CURRENT_TIMESTAMP) = map(CaselessKeyword, “"”CAST, ISNULL, NOTNULL, NULL, IS, BETWEEN, ELSE,
 END, CASE, WHEN, THEN, EXISTS, COLLATE, IN, LIKE, GLOB, REGEXP, MATCH, ESCAPE,
 CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP””“.replace(“,”,””).split())
keyword = MatchFirst((UNION, ALL, INTERSECT, EXCEPT, COLLATE, ASC, DESC, ON, USING, NATURAL, INNER,
                      CROSS, LEFT, OUTER, JOIN, AS, INDEXED, NOT, SELECT, DISTINCT, FROM, WHERE, GROUP, BY,
                      HAVING, ORDER, BY, LIMIT, OFFSET, CAST, ISNULL, NOTNULL, NULL, IS, BETWEEN, ELSE, END, CASE, WHEN, THEN, EXISTS,
                      COLLATE, IN, LIKE, GLOB, REGEXP, MATCH, ESCAPE, CURRENT_TIME, CURRENT_DATE,
                      CURRENT_TIMESTAMP))</p>

<p>identifier = ~keyword + Word(alphas, alphanums+”_”)
collation_name = identifier.copy()
column_name = identifier.copy()
column_alias = identifier.copy()
table_name = identifier.copy()
table_alias = identifier.copy()
index_name = identifier.copy()
function_name = identifier.copy()
parameter_name = identifier.copy()
database_name = identifier.copy()</p>

<h1 id="expression">expression</h1>
<p>expr = Forward().setName(“expression”)</p>

<p>integer = Regex(r”[+-]?\d+”)
numeric_literal = Regex(r”\d+(.\d*)?([eE][+-]?\d+)?”)
string_literal = QuotedString(“’”)
blob_literal = Combine(oneOf(“x X”) + “’” + Word(hexnums) + “’”)
literal_value = ( numeric_literal | string_literal | blob_literal |
                  NULL | CURRENT_TIME | CURRENT_DATE | CURRENT_TIMESTAMP )
bind_parameter = (
    Word(“?”,nums) |
    Combine(oneOf(“: @ $”) + parameter_name)
)
type_name = oneOf(“TEXT REAL INTEGER BLOB NULL”)</p>

<p>expr_term = (
    CAST + LPAR + expr + AS + type_name + RPAR |
    EXISTS + LPAR + select_stmt + RPAR |
    function_name + LPAR + Optional(delimitedList(expr)) + RPAR |
    literal_value |
    bind_parameter |
    identifier
)</p>

<p>UNARY,BINARY,TERNARY=1,2,3
expr « operatorPrecedence(expr_term,
                           [
                               (oneOf(‘- + ~’) | NOT, UNARY, opAssoc.LEFT),
                               (‘||’, BINARY, opAssoc.LEFT),
                               (oneOf(‘* / %’), BINARY, opAssoc.LEFT),
                               (oneOf(‘+ -‘), BINARY, opAssoc.LEFT),
                               (oneOf(‘« » &amp; |’), BINARY, opAssoc.LEFT),
                               (oneOf(‘&lt; &lt;= &gt; &gt;=’), BINARY, opAssoc.LEFT),
                               (oneOf(‘= == != &lt;&gt;’) | IS | IN | LIKE | GLOB | MATCH | REGEXP, BINARY, opAssoc.LEFT),
                               (‘||’, BINARY, opAssoc.LEFT),
                               ((BETWEEN,AND), TERNARY, opAssoc.LEFT),
                               ])</p>

<table>
  <tbody>
    <tr>
      <td>compound_operator = (UNION + Optional(ALL)</td>
      <td>INTERSECT</td>
      <td>EXCEPT)</td>
    </tr>
  </tbody>
</table>

<table>
  <tbody>
    <tr>
      <td>ordering_term = expr + Optional(COLLATE + collation_name) + Optional(ASC</td>
      <td>DESC)</td>
    </tr>
  </tbody>
</table>

<table>
  <tbody>
    <tr>
      <td>join_constraint = Optional(ON + expr</td>
      <td>USING + LPAR + Group(delimitedList(column_name)) + RPAR)</td>
    </tr>
  </tbody>
</table>

<table>
  <tbody>
    <tr>
      <td>join_op = COMMA</td>
      <td>(Optional(NATURAL) + Optional(INNER</td>
      <td>CROSS</td>
      <td>LEFT + OUTER</td>
      <td>LEFT</td>
      <td>OUTER) + JOIN)</td>
    </tr>
  </tbody>
</table>

<p>join_source = Forward()
single_source = ( (Group(database_name(“database”) + “.” + table_name(“table”)) | table_name(“table”)) +
                  Optional(Optional(AS) + table_alias(“table_alias”)) +
                  Optional(INDEXED + BY + index_name(“name”) | NOT + INDEXED)(“index”) |
                  (LPAR + select_stmt + RPAR + Optional(Optional(AS) + table_alias)) |
                  (LPAR + join_source + RPAR) )</p>

<p>join_source « single_source + ZeroOrMore(join_op + single_source + join_constraint)</p>

<p>result_column = “<em>” | table_name + “.” + “</em>” | (expr + Optional(Optional(AS) + column_alias))
select_core = (SELECT + Optional(DISTINCT | ALL) + Group(delimitedList(result_column))(“columns”) +
               Optional(FROM + join_source) +
               Optional(WHERE + expr(“where_expr”)) +
               Optional(GROUP + BY + Group(delimitedList(ordering_term)(“group_by_terms”)) +
                        Optional(HAVING + expr(“having_expr”))))</p>

<p>select_stmt « (select_core + ZeroOrMore(compound_operator + select_core) +
                Optional(ORDER + BY + Group(delimitedList(ordering_term))(“order_by_terms”)) +
                Optional(LIMIT + (integer + OFFSET + integer | integer + COMMA + integer)))</p>

<p>tests = “””\
    select * from xyzzy where z &gt; 100
    select * from xyzzy where z &gt; 100 order by zz
    select * from xyzzy””“.splitlines()
for t in tests:
    print t
    try:
        print select_stmt.parseString(t).dump()
    except ParseException, pe:
        print pe.msg
    print</p>

<p>```</p>

<p>测试环境：
    &gt;python 2.7.5
    &gt;pyparsing 2.03</p>

<p>运行结果：</p>

<pre><code>select * from xyzzy where z &gt; 100 ['SELECT', ['*'], 'FROM', 'xyzzy', 'WHERE', ['z', '&gt;', '100']] - columns: ['*'] - table: ['xyzzy'] - where_expr: ['z', '&gt;', '100']

select * from xyzzy where z &gt; 100 order by zz ['SELECT', ['*'], 'FROM', 'xyzzy', 'WHERE', ['z', '&gt;', '100'], 'ORDER', 'BY', ['zz']] - columns: ['*'] - order_by_terms: ['zz'] - table: ['xyzzy'] - where_expr: ['z', '&gt;', '100']

select * from xyzzy ['SELECT', ['*'], 'FROM', 'xyzzy'] - columns: ['*'] - table: ['xyzzy']
</code></pre>