Python使用pyparsing解析SQL [英] Python-Parsing a SQL using pyparsing

问题描述

我想解析一个具有(内部联接，外部联接)的复杂SQL，并获取该SQL中使用的表名.

I want to parse a complex SQL which has (inner join,outer join) and get the table names used in the SQL.

如果能够简单地选择表，我就能得到表名，但是如果SQL具有内部联接(如下面所示)为左联接，那么结果就是只给出第一个表.

I am able to get the table names if it is simple select but if the SQL has inner join ,left join like below then the result is giving only the first table.

select * from xyz  inner join dhf  on df = hfj  where z > 100 

我使用的程序类似于Paul在下面的链接中提供的程序.

I am using the program similar what is present in the below link by Paul.

http://pyparsing.wikispaces.com/file/查看/select_parser.py/158651233/select_parser.py

有人可以告诉我如何获取如下所示的SQL中使用的所有表

Can someone tell me how to get all the tables used in a SQL like below

select * from xyz  inner join dhf  on df = hfj  where z > 100.  

推荐答案

此解析器是很久以前编写的，直到后来才处理结果名称中的多个值.

This parser was written a long time ago, and handling multiple values in a results name did not come along until later.

在您引用的解析器中更改此行:

Change this line in the parser you cited:

single_source = ( (Group(database_name("database") + "." + table_name("table")) | table_name("table")) + 

到

single_source = ( (Group(database_name("database") + "." + table_name("table*")) | table_name("table*")) + 

当我通过select_stmt解析器运行您的示例语句时，现在我得到了:

When I run your sample statement thru the select_stmt parser, I now get this:

select * from xyz  inner join dhf  on df = hfj  where z > 100
['SELECT', ['*'], 'FROM', 'xyz', 'INNER', 'JOIN', 'dhf', 'ON', ['df', '=', 'hfj'], 'WHERE', ['z', '>', '100']]
- columns: ['*']
- table: [['xyz'], ['dhf']]
  [0]:
    ['xyz']
  [1]:
    ['dhf']
- where_expr: ['z', '>', '100']

这篇关于Python使用pyparsing解析SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！