sql python_用Python解析SQL

TwoLaid的Python的SQL语法分析器都非常好，我的目的。它用C编写，需要编译。它很健壮。它解析每个子句的各个元素。

我用它来解析出查询列名在报告标题中使用。这是一个例子。

import sqlparser

def get_query_columns(sql):

'''Return a list of column headers from given sqls select clause'''

columns = []

parser = sqlparser.Parser()

# Parser does not like new lines

sql2 = sql.replace('\n', ' ')

# Check for syntax errors

if parser.check_syntax(sql2) != 0:

raise Exception('get_query_columns: SQL invalid.')

stmt = parser.get_statement(0)

root = stmt.get_root()

qcolumns = root.__dict__['resultColumnList']

for qcolumn in qcolumns.list:

if qcolumn.aliasClause:

alias = qcolumn.aliasClause.get_text()

columns.append(alias)

else:

name = qcolumn.get_text()

name = name.split('.')[-1] # remove table alias

columns.append(name)

return columns

sql = '''

SELECT

a.a,

replace(coalesce(a.b, 'x'), 'x', 'y') as jim,

a.bla as sally -- some comment

FROM

table_a as a

WHERE

c > 20

'''

print get_query_columns(sql)

# output: ['a', 'jim', 'sally']