Parsing SQL query joins with python

Question

I'm trying to parse out sql queries. I'm using the [moz-sql-parser][1] to identify the sql parts in a query and then writing a function parse out table names and columns that were joined on.

I have a sample query below :

join_query2 = json.dumps(parse('''select * from tbl d
inner join jointbl1 c
on d.visit_id = c.session_id 
inner join jointbl2 b
on b.sv_id = c.sv_id'''))

join_query2 = json.loads(join_query2)

which when run through the moz-sql-parser yields :

    {'select': '*',
 'from': [{'value': 'tbl', 'name': 'd'},
  {'inner join': {'name': 'c',
    'value': 'jointbl1'},
   'on': {'eq': ['d.visit_id', 'c.session_id']}},
  {'inner join': {'name': 'b',
    'value': 'jointbl2'},
   'on': {'eq': ['b.sv_id', 'c.sv_id']}}]}

Now I've written functions which can parse out table names and column names :

def parse_table_names_v2(result):
    the_list = [] 
    for x in result['from']:
        try:
            if 'value' in x: #returning just the main table_name
                if 'name' in x:
                    the_list.append(x.get('name',None))
                the_list.append(x.get('value'))
            elif 'join' in x:
                join = x['join']
                if 'value' in join:
                    if 'name' in join:
                        the_list.append(join.get('name'))
                the_list.append(join.get('value'))
            elif 'inner join' in x:
                inner_join = x['inner join']
                if 'value' in inner_join:
                    if 'name' in inner_join:
                        the_list.append(inner_join.get('name'))
                the_list.append(inner_join.get('value'))

        except Exception as e:
            print(e)
    return the_list
    

def parse_column_names(result):
    columns = []
    for x in result['from']:
        try:
            if 'on' in x:
                on = x['on']
                if 'and' in on:
                    for x in on['and']:
                        if 'eq' in x:
                                columns.append(x['eq'])
                elif 'and' not in on:
                    if 'eq' in on:
                            columns.append(on['eq'])
        except Exception as e:
            print(e)     
    return columns   

It results in 2 lists as shown below :

['d',
 'tbl1',
 'c',
 'jointbl1',
 'b',
 'jointbl2']

and

[['d.visit_id', 'c.session_id'], ['b.sv_id', 'c.sv_id']]

But the trick here is that the desired output will look something like

Row1 -> tbl1 visit_id jointbl1 session_id
Row2 -> jointbl1 sv_id jointbl2 sv_id

My goal is to parse similar queries where I can build the output to a dataframe/list but struggling to output the parse this particular way. Any leads would be highly appreciated.

Answer 1

Will this work for what you are trying to do?

tables = ['d',
 'tbl1',
 'c',
 'jointbl1',
 'b',
 'jointbl2']


columns = [['d.visit_id', 'c.session_id'], ['b.sv_id', 'c.sv_id']]

# Convert table list to a lookup table
lookup_table = {}
alias = ""
tablename = ""
for idx, item in enumerate(tables):
    if idx % 2 != 1:
        alias = item
    else:
        tablename = item
        lookup_table[alias] = tablename

# Use the lookup table to build the new row format
new_rows = []
for row in columns:
    new_row = [] 
    for elem in row:
        item = elem.split('.')
        col_table = item[0]
        column = item[1]
        new_row.append(lookup_table[col_table])
        new_row.append(column)
    new_rows.append(new_row)

for row in new_rows:
    print(" ".join(row))

Output:

tbl1 visit_id jointbl1 session_id
jointbl2 sv_id jointbl1 sv_id