Parsing SQL file with Python

Question

I'm trying to parse a SQL code and extract all the table names from it.

The table names in the sql code are sometimes written with alias or schema.table or only table name.

I'm using sqlparse package but I'm only getting the alias of the tables, you will find my code bellow :

SELECT  *
FROM VA_ASSISTES va, datamart_Sales.dbo.Seller f,
datamart_Sales.dbo.ARTICLES a, SITE s, datamart_Sales.dbo.TEMPS t

This is what I get now as a result, only the alias :

Tables: va, f, a, s, t

However, I want to retrieve the table name like this : datamart_Sales.dbo.Seller , datamart_Sales.dbo.ARTICLES, datamart_Sales.dbo.TEMPS, SITE

I will really appreciate it if someone can help me to extract the table name in all the cases mentions above.

Ajax1234 · Accepted Answer · 2021-12-17 15:42:02Z

1

sqlparse does not specifically label identifiers as belonging to column names/aliases, or table names/aliases. Thus, you will have to loop over the parsed tokens and mark when a from keyword occurs, and then retain subsequent identifiers:

import sqlparse
s = """SELECT  *
FROM VA_ASSISTES va, datamart_Sales.dbo.Seller f,
datamart_Sales.dbo.ARTICLES a, SITE s, datamart_Sales.dbo.TEMPS t"""
s1="""SELECT job_id,AVG(salary) FROM VA_ASSISTES va, datamart_Sales.dbo.Seller f, datamart_Sales.dbo.ARTICLES a, SITE s, datamart_Sales.dbo.TEMPS t Havning job_id,AVG(salary)< (SELECT MAX(AVG(min_salary)) FROM jobs WHERE job_id IN (SELECT job_id FROM job_history WHERE department_id BETWEEN 50 AND 100) GROUP BY job_id));"""
def get_tables(p):
   f = False
   for i in p:
      if i.value.lower() == 'from':
         f = True
      if f and isinstance(i, (sqlparse.sql.Identifier, sqlparse.sql.IdentifierList)):
         if isinstance(i, sqlparse.sql.IdentifierList):
            yield from [j.value.split() for j in i.get_identifiers()]
         else:
             yield i.value.split()
         f = False
      yield from get_tables(getattr(i, '__iter__', lambda :[])())

print(list(get_tables(sqlparse.parse(s)[0])))
print(list(get_tables(sqlparse.parse(s1)[0])))

Output:

[['VA_ASSISTES', 'va'], ['datamart_Sales.dbo.Seller', 'f'], ['datamart_Sales.dbo.ARTICLES', 'a'], ['SITE', 's'], ['datamart_Sales.dbo.TEMPS', 't']]
[['VA_ASSISTES', 'va'], ['datamart_Sales.dbo.Seller', 'f'], ['datamart_Sales.dbo.ARTICLES', 'a'], ['SITE', 's'], ['datamart_Sales.dbo.TEMPS', 't'], ['jobs'], ['job_history']]

edited Dec 17, 2021 at 15:42

answered Dec 16, 2021 at 16:54

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abdelhak Over a year ago

Thank you for the prompte reply! How about if I have nested queries like this : SELECT job_id,AVG(salary) FROM VA_ASSISTES va, datamart_Sales.dbo.Seller f, datamart_Sales.dbo.ARTICLES a, SITE s, datamart_Sales.dbo.TEMPS t Havning job_id,AVG(salary)< SELECT MAX(AVG(min_salary)) FROM jobs WHERE job_id IN (SELECT job_id FROM job_history WHERE department_id BETWEEN 50 AND 100) GROUP BY job_id);

Ajax1234 Over a year ago

@Abdelhak You will need to use recursion. Please see my recent edit.

Collectives™ on Stack Overflow

Parsing SQL file with Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related