How to extract table names and column names from sql query?

Question

So let assume we have such simple query:

Select a.col1, b.col2 from tb1 as a inner join tb2 as b on tb1.col7 = tb2.col8;

The result should looks this way:

tb1 col1
tb1 col7
tb2 col2
tb2 col8

I've tried to solve this problem using some python library:

1) Even extracting only tables using sqlparse might be a huge problem. For example this official book doesn't work properly at all.

2) Using regular expression seems to be really hard to achieve.

3) But then I found this , that might help. However the problem is that I can't connect to any database and execute that query.

Any ideas?

Answer 1

sql-metadata is a Python library that uses a tokenized query returned by python-sqlparse and generates query metadata.

This metadata can return column and table names from your supplied SQL query. Here are a couple of example from the sql-metadata github readme:

>>> sql_metadata.get_query_columns("SELECT test, id FROM foo, bar")
[u'test', u'id']

>>> sql_metadata.get_query_tables("SELECT test, id FROM foo, bar")
[u'foo', u'bar']

>>> sql_metadata.get_query_limit_and_offset('SELECT foo_limit FROM bar_offset LIMIT 50 OFFSET 1000')
(50, 1000)

A hosted version of the library exists at sql-app.infocruncher.com to see if it works for you.