Using the Mozilla SQL Parser — Part 2

In the Using the Mozilla SQL Parser — Part 1 article on Mozilla SQL Parser, we covered how the moz-sql-parser package can be used to parse an SQL Select statement into a JSONified structure. In this part, we will cover how to extract names of the tables mentioned in the 'from' clause of the query.

The SQL we are using is

xxxxxxxxxx

SELECT s_name from student where s_id in

(select s_id from student_course where c_id in

(select c_id from course where c_name = 'DSA'

and c_name = 'dbms' or c_name = 'algorithm'));

To parse this query, we use

parsed_query = parse(query)

where, parse is a method provided by the moz_sql_parser.

After parsing, the module generates a JSONified structure, as given below.

xxxxxxxxxx

{

  "select": { "value": "s_name" },

  "from": "student",

  "where": {

    "in": [ "s_id", {

      "select": { "value": "s_id" },

      "from": "student_course",

      "where": {

        "in": [ "c_id", {

          "select": { "value": "c_id" },

          "from": "course",

          "where": {

            "or": [ {

              "and": [

                { "eq": [ "c_name", { "literal": "DSA" } ] },

                { "eq": [ "c_name", { "literal": "dbms" } ] }

              ] },

              { "eq": [ "c_name", { "literal": "algorithm" } ] }

            ]

          }

        } ]

      }

    } ]

  }

}

After parsing, the JSONified structure contains three main keys, namely 'select', 'from' and 'where'.

To extract names of tables, we will extract them from the 'from' clause. For this, we define a method named 'get_from_clause_tables'. To hold the table names and the table name aliases, we define two dictionaries, named table_map and table_alias_map.

xxxxxxxxxx

table_map = {}

table_alias_map = {}

if "from" in parsed_query:

    from_clause = parsed_query["from"]

    table_map, table_alias_map = get_from_clause_tables(from_clause, table_map, table_alias_map)

The 'get_from_clause_tables' method is

xxxxxxxxxx

def get_from_clause_tables(from_clause, table_usage_map, table_alias_usage_map, table_alias_map):

  if isinstance(from_clause, list):

    print("from_clause is type list")

    for t in from_clause:

      if isinstance(t, str):

        value = t

        table_usage_map[value] = table_usage_map.get(value, 0) + 1

      else:

        value = t["value"]

        alias = t["name"]

        table_usage_map[value] = table_usage_map.get(value, 0) + 1

        table_alias_usage_map[alias] = table_alias_usage_map.get(alias, 0) + 1

        table_alias_map[alias] = value

  elif isinstance(from_clause, str):

    print("from_clause is type string")

    table_usage_map[from_clause] = table_usage_map.get(from_clause, 0) + 1

  return table_usage_map, table_alias_usage_map, table_alias_map

In the method, we are checking the data type of the from clause. We need to check the instance type of the from_clause variable, due to the way the data table names are stored in the JSONified structure.

• Type string -- If the from_clause has only one table, its data type is string and the 'from_clause' is nothing but the name of the table mentioned in the from clause of the query.
• Type list -- If the from_clause has multiple tables, its data type is list (of type string) or list (of type dictionary), that contains names of the tables mentioned in the from clause. If the table has an alias, the entry is of type dictionary. If the table does not have an alias, the entry is of type string.

In the 'get_from_clause_tables' method, we are collecting the names of the tables used and their usage frequency. We are also collecting the alias names of the table and their usage frequency.

Upon execution, we get the output as

xxxxxxxxxx

table_usage_map - {'student': 1, 'course': 1}

table_alias_usage_map - {'c': 1}

table_alias_map - {'c': 'course'}

In this part, we covered how to extract names of tables given in the from clause. In the next part (part three), we will cover how to extract tables mentioned in the where clause.