How can one use moz_sql_parser and DeepDiff to compare two sql Querys?

Question

I am using moz_sql_parser to compare two SQL querys. And DeepDiff to see the diferences between the two querys.

I want a diferent kind of dictionary as showed below to perform better comparissons.

from moz_sql_parser import parse
import json
from jsondiff import diff
from deepdiff import DeepDiff
import pprint

sqlr = "SELECT distinct c.nome FROM compositor c, faixas f, artista ar, album a, fx_comp fx WHERE fx.compositor = c.compositor_id AND fx.faixa_id = f.faixa_id AND f.album = a.album_id AND (a.titulo = 'Help!' OR a.titulo = 'Abbey Road') AND ar.nome = 'The Beatles' AND f.faixa_numero < 6 Order By c.nome asc;"
sqlr1 = "SELECT c.nome FROM artista ar, album a, faixas f, compositor c,  fx_comp fx WHERE fx.compositor = c.compositor_id AND fx.faixa_id = f.faixa_id AND f.album = a.album_id AND (a.titulo = 'Help!' OR a.titulo = 'Abbey Road') AND ar.nome = 'The Beatles' AND f.faixa_numero < 6 Order By c.nome desc;"
sqlr2 = "SELECT * FROM (SELECT * FROM ALBUM as x ,ARTISTA as pto WHERE ARTISTA.ARTISTA_ID = ALBUM.ARTISTA AND ARTISTA.ARTISTA_ID = 4) AS B;"

#print(json.dumps(parse(sql8), sort_keys=True, indent=4))
data1 = json.loads(json.dumps(parse(sqlr.lower()),sort_keys=True))
data2 = json.loads(json.dumps(parse(sqlr1.lower()),sort_keys=True))

pprint.pprint(data1)
print('###################################')
#data3 = diff(data2, data1)
ddiff = DeepDiff(data1,data2, ignore_order=True)
pprint.pprint((ddiff))
print(ddiff['type_changes']["root['select']['value']"])
#print(type(data1['select']['value']))

At the current version data1 and data2 are like: And to parse the data of the dictionary to make it more legible and usable.

DATA1
{'from': [{'name': 'c', 'value': 'compositor'},
          {'name': 'f', 'value': 'faixas'},
          {'name': 'ar', 'value': 'artista'},
          {'name': 'a', 'value': 'album'},
          {'name': 'fx', 'value': 'fx_comp'}],
 'orderby': {'sort': 'asc', 'value': 'c.nome'},
 'select': {'value': {'distinct': 'c.nome'}},
 'where': {'and': [{'eq': ['fx.compositor', 'c.compositor_id']},
                   {'eq': ['fx.faixa_id', 'f.faixa_id']},
                   {'eq': ['f.album', 'a.album_id']},
                   {'or': [{'eq': ['a.titulo', {'literal': 'help!'}]},
                           {'eq': ['a.titulo', {'literal': 'abbey road'}]}]},
                   {'eq': ['ar.nome', {'literal': 'the beatles'}]},
                   {'lt': ['f.faixa_numero', 6]}]}}
DATA2
{'from': [{'name': 'ar', 'value': 'artista'},
          {'name': 'a', 'value': 'album'},
          {'name': 'f', 'value': 'faixas'},
          {'name': 'c', 'value': 'compositor'},
          {'name': 'fx', 'value': 'fx_comp'}],
 'orderby': {'sort': 'desc', 'value': 'c.nome'},
 'select': {'value': 'c.nome'},
 'where': {'and': [{'eq': ['fx.compositor', 'c.compositor_id']},
                   {'eq': ['fx.faixa_id', 'f.faixa_id']},
                   {'eq': ['f.album', 'a.album_id']},
                   {'or': [{'eq': ['a.titulo', {'literal': 'help!'}]},
                           {'eq': ['a.titulo', {'literal': 'abbey road'}]}]},
                   {'eq': ['ar.nome', {'literal': 'the beatles'}]},
                   {'lt': ['f.faixa_numero', 6]}]}}

But it keeps changing. So the structure of the dictionary is never the same, witch makes it a mess to parse.

i want to transform data1 and data2 in something like:

[['Table1','Alias','variable1associatedwithtable1','variable1associatedwithtable12',],
['Table2name','Alias','variable1associatedwithtable2name','variable1associatedwithtable2name']]

for Tables and their own variables incluing the alias used

[['Table2name','function','variable_associated_with_table_name'],

How can one use moz_sql_parser and DeepDiff to compare two sql Querys?

0 Answers0