python解析sql语句_Python使用pyparsing解析SQL

I want to parse a complex SQL which has (inner join,outer join) and get the table names used in the 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

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

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")) +

to

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

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']