Using the Mozilla SQL Parser — Part 2
Join the DZone community and get the full member experience.
Join For FreeIn 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 useparsed_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 thefrom_clause
has only one table, its data type isstring
and the 'from_clause
' is nothing but the name of the table mentioned in thefrom
clause of the query. - Type
list
-- If thefrom_clause
has multiple tables, its data type islist
(of typestring
) orlist
(of typedictionary
), that contains names of the tables mentioned in thefrom
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.
Opinions expressed by DZone contributors are their own.
Trending
-
Is Podman a Drop-In Replacement for Docker?
-
Effective Java Collection Framework: Best Practices and Tips
-
Avoiding Pitfalls With Java Optional: Common Mistakes and How To Fix Them [Video]
-
Writing a Vector Database in a Week in Rust
Comments