SQL query to JSON formatted result

Python Help
1

Hi All,
My first task on Python,
I would like to convert a SQL query (not query result) into JSON, I’m searching for some pre-build libraries and found few like mo_sql_parsing, sqlparse but need more structured JSON. Like below, any quick help is appreciated
INPUT:
SELECT A, B FROM (SELECT A, B FROM FROMTABLE WHERE FROMCONDITION = ‘FROMCONDITION’)
WHERE C = (SELECT C FROM WHERETABLE WHERE WHERECONDITION = ‘WHERECONDITION’)
ORDER BY A

EXPECTED OUTPUT:
{
“CRUD”: [“SELECT”],
“COLUMN”: [
“A”,
“B”
],
“ORDER_BY”: [“A”],
“TABLE”: [“(SELECT A, B FROM FROMTABLE WHERE FROMCONDITION = ‘FROMCONDITION’)”],
“TABLE SUB QUERY 1”: [“(SELECT A, B FROM FROMTABLE WHERE FROMCONDITION = ‘FROMCONDITION’)”],
“TABLE SUB QUERY ANALYSE 1”: {
“CRUD”: [“SELECT”],
“COLUMN”: [
“A”,
“B”
],
“TABLE”: [“FROMTABLE”],
“WHERE”: [“FROMCONDITION = ‘FROMCONDITION’”]
},
“WHERE”: [“C = (SELECT C FROM WHERETABLE WHERE WHERECONDITION = ‘WHERECONDITION’)”],
“WHERE SUB QUERY 1”: [“(SELECT C FROM WHERETABLE WHERE WHERECONDITION = ‘WHERECONDITION’)”],
“WHERE SUB QUERY ANALYSE 1”: {
“CRUD”: [“SELECT”],
“COLUMN”: [“C”],
“TABLE”: [“WHERETABLE”],
“WHERE”: [“WHERECONDITION = ‘WHERECONDITION’”]
}
}

2

I would definitely try to use an existing parser for at least part of this. :slightly_smiling_face:

Are you familiar with these ideas?

(You don’t have to be an expert, but it would be helpful to know the concepts, especially abstract syntax trees (AST))

I would divide this into 2 steps:

  1. Parse SQL into an AST
  2. Convert the AST into your desired JSON format

If at all possible, use an existing parser for step 1. Implementing a complete SQL parser would be a lot of work and there’s no reason to do it yourself. Step 2 you must do yourself - unless the JSON format you want is standardized somehow, there will not be any existing library that produces it.

Look for a SQL parser that can produce an AST for you, and learn about that structure. Your code will be a transformation from that structure into your dictionary-based format. (If you can convert the whole AST into dictionaries, then you can just JSON serialize the dictionaries.)

1 Like