Is there any function to parse a complete SQL query in Python?

Question

I am using postgreSQL queries. I want to extract all information from a SQL query, for example

sql = " select d_year, s_nation, p_category, sum(lo_revenue - lo_supplycost) as profit from DATES, CUSTOMER, SUPPLIER, PART, LINEORDER where lo_custkey  =  c_custkey and lo_suppkey  =  s_suppkey and lo_partkey  =  p_partkey and lo_orderdate  =  d_datekey and c_region  =  'AFRICA' and s_region  =  'AFRICA' and (d_year  =  1996 or d_year  =  1997) and (p_mfgr  =  'MFGR#2' or p_mfgr  =  'MFGR#4') group by d_year, s_nation, p_category order by d_year, s_nation, p_category "

I want to get all tables concerned, all selection predicate, and all join predicate, group by part, and order by part.

I used sqlparse and I found a way to get only the tables concerned. Is there any examples of how to extract this information?

You can use Antlr to parse SQL statements and extract the AST. — Laurent LAPORTE
– Laurent LAPORTE, Commented Nov 2, 2019 at 9:58

Laurent · Accepted Answer · 2021-08-23 06:05:29Z

2

This algorithm gives the exact element between each keyword. I used sqlparse

parsed = sqlparse.parse(sql)
stmt = parsed[0]
from_seen = False
select_seen = False
where_seen = False
groupby_seen = False
orderby_seen = False

for token in stmt.tokens:
    if select_seen:
        if isinstance(token, IdentifierList):
            for identifier in token.get_identifiers():
                print("{} {}\n".format("Attr = ", identifier))
        elif isinstance(token, Identifier):
            print("{} {}\n".format("Attr = ", token))
    if from_seen:
        if isinstance(token, IdentifierList):
            for identifier in token.get_identifiers():
                print("{} {}\n".format("TAB = ", identifier))
        elif isinstance(token, Identifier):
            print("{} {}\n".format("TAB = ", token))
    if orderby_seen:
        if isinstance(token, IdentifierList):
            for identifier in token.get_identifiers():
                print("{} {}\n".format("ORDERBY att = ", identifier))
        elif isinstance(token, Identifier):
            print("{} {}\n".format("ORDERBY att = ", token))
    if groupby_seen:
        if isinstance(token, IdentifierList):
            for identifier in token.get_identifiers():
                print("{} {}\n".format("GROUPBY att = ", identifier))
        elif isinstance(token, Identifier):
            print("{} {}\n".format("GROUPBY att = ", token))

    if isinstance(token, Where):
        select_seen = False
        from_seen = False
        where_seen = True
        groupby_seen = False
        orderby_seen = False
        for where_tokens in token:
            if isinstance(where_tokens, Comparison):
                print("{} {}\n".format("Comparaison = ", where_tokens))
            elif isinstance(where_tokens, Parenthesis):
                print("{} {}\n".format("Parenthesis = ", where_tokens))
                # tables.append(token)
    if token.ttype is Keyword and token.value.upper() == "GROUP BY":
        select_seen = False
        from_seen = False
        where_seen = False
        groupby_seen = True
        orderby_seen = False
    if token.ttype is Keyword and token.value.upper() == "ORDER BY":
        select_seen = False
        from_seen = False
        where_seen = False
        groupby_seen = False
        orderby_seen = True
    if token.ttype is Keyword and token.value.upper() == "FROM":
        select_seen = False
        from_seen = True
        where_seen = False
        groupby_seen = False
        orderby_seen = False
    if token.ttype is DML and token.value.upper() == "SELECT":
        select_seen = True
        from_seen = False
        where_seen = False
        groupby_seen = False
        orderby_seen = False

edited Aug 23, 2021 at 6:05

Laurent

13.7k7 gold badges30 silver badges49 bronze badges

answered Nov 4, 2019 at 13:12

Islacine

1052 silver badges9 bronze badges

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

3 Comments

RakeshV Over a year ago

``` if isinstance(token, Where):``` what is where here?

Islacine Over a year ago

@RakeshV it's an Instance. When using an IDE the "Where" will return in a different color. Same as isinstance(token, Identifier) - isinstance(token, IdentifierList) in this program

Mustafa Poya Over a year ago

@RakeshV the where is from sqlparse.sql.Where

Collectives™ on Stack Overflow

Is there any function to parse a complete SQL query in Python?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related