Autogenerate SQL statements from ragged Python dictionary

Question

I'm attempting to loop through a dict to autogenerate SQL WHERE statements. A sample of the dict is below as well as the desired output (which has been optimized w.r.t. the use of logical OR/AND operators).

There are 5 fields that require filtering (i.e. field1, field2, field3, field4, value5). The dict stores the values to filter each field by in tuples (where tuple[0] and tuple[1] are represented as the lower and upper bound of the filtering range).

You'll notice in the desired output (see below) that the logical operators have been optimized to eliminate any redundancy. For example, for GROUP1, field1 and field2 values (i.e. 40000 to 50000 and 0000 to ZZZZ, respectively) have only been referenced once in the WHERE statement and are followed by an AND operator and a series of filters in which field3 (and its corresponding field4/field5 filters) are separated by an OR operator (i.e. it's at field3 where the filtering "forks" and a logical OR operator is required). For GROUP2, a similar construct exist; however, field2 is where this "fork" emerges.

input_range_dict = {
    "GROUP1": {
        (40000, 50000): { # field1
            ("0000", "ZZZZ"): { # field2
                ("000", "ZZZ"): { # field3
                    ("10000000", "99999999"): # field4
                         [(123, 123), (456, 456)], # field5 
                    ("20000000", "40000000"): # field4
                         [("000", "ZZZ")]}, # field5
                ("AAA", "BBB"): { # field3
                    ("10000000", "99999999"): # field4
                         [("000", "ZZZ")]} # field5
                               }
                         }
                },
    "GROUP2": {
        (70000, 90000): {
            ("0000", "ZZZZ"): { 
                ("000", "ZZZ"): {
                    ("10000000", "99999999"):
                         [(123, 123), (456, 456)],
                    ("20000000", "40000000"):
                         [("000", "ZZZ")]},
            ("AAAA", "ZZZZ"): {
                ("AAA", "BBB"): {
                    ("10000000", "99999999"):
                         [("000", "ZZZ")]}
                               }
                         }
                }
        }
}

Output String for GROUP1:

WHERE
  field1 BETWEEN "40000" AND "50000"
  AND field2 BETWEEN "0000" AND "ZZZZ"
  AND
    (
      (
        field3 BETWEEN "000" AND "ZZZ"
        AND
          (
            (
              field4 BETWEEN "10000000" AND "99999999"
              AND
                (
                  field5 BETWEEN "123" AND "123"
                  OR field5 BETWEEN "456" AND "456"
                 )
             )
             OR
             (
               field4 BETWEEN "20000000" AND "40000000"
               AND field5 BETWEEN "000" AND "ZZZ"
              )
           )
       )
       OR
       (
         field3 BETWEEN "AAA" AND BBB"
         AND field4 BETWEEN "10000000" AND "99999999"
         AND field5 BETWEEN "000" AND "ZZZ"
       )        
    )  

Output String for GROUP2:

WHERE
  field1 BETWEEN "70000" AND "90000" 
  AND 
    (
      field2 BETWEEN "0000" AND "ZZZZ"
      AND field3 BETWEEN "000" AND "ZZZ"
      AND
        ( 
          (
            field4 BETWEEN "10000000" AND "99999999"
            AND
              (
                 field5 BETWEEN "123" AND "123"
                 OR field5 BETWEEN "456" AND "456"
              )
           )
           OR
           (
             field4 BETWEEN "20000000" AND "40000000"
             AND field5 BETWEEN "000" AND "ZZZ"
           )
         )
      )
   OR
    (
      field2 BETWEEN "AAAA" AND "ZZZZ"
      AND field3 BETWEEN "AAA" AND "BBB"
      AND field4 BETWEEN "10000000" AND "99999999"
      AND field5 BETWEEN "000" AND "ZZZ"
     )
    

Answer 1

Here is a solution that forms the conditional logic based on the group structure but without the pretty printing:

def gen_sql(d, c = 1, l = 0):
   if isinstance(d, list):
      return ("" if len(d) < 2 else "(")+ ' or '.join(f'field{c} between {a} and {b}' for a, b in d)+ ("" if len(d) < 2 else ")")
   return ' or '.join(("" if len(d) < 2 else "(")+f'field{c} between {a} and {b} and {gen_sql(k, c = c+1, l = l+(len(d) > 1))}'+("" if len(d) < 2 else ")") for (a, b), k in d.items())
   
input_range_dict = {'GROUP1': {(40000, 50000): {('0000', 'ZZZZ'): {('000', 'ZZZ'): {('10000000', '99999999'): [(123, 123), (456, 456)], ('20000000', '40000000'): [('000', 'ZZZ')]}, ('AAA', 'BBB'): {('10000000', '99999999'): [('000', 'ZZZ')]}}}}, 'GROUP2': {(70000, 90000): {('0000', 'ZZZZ'): {('000', 'ZZZ'): {('10000000', '99999999'): [(123, 123), (456, 456)], ('20000000', '40000000'): [('000', 'ZZZ')]}}, ('AAAA', 'ZZZZ'): {('AAA', 'BBB'): {('10000000', '99999999'): [('000', 'ZZZ')]}}}}}
for a, b in input_range_dict.items():
    print(f'{a}:')
    print(gen_sql(b))
    print()

Output:

GROUP1:
field1 between 40000 and 50000 and field2 between 0000 and ZZZZ and (field3 between 000 and ZZZ and (field4 between 10000000 and 99999999 and (field5 between 123 and 123 or field5 between 456 and 456)) or (field4 between 20000000 and 40000000 and field5 between 000 and ZZZ)) or (field3 between AAA and BBB and field4 between 10000000 and 99999999 and field5 between 000 and ZZZ)

GROUP2:
field1 between 70000 and 90000 and (field2 between 0000 and ZZZZ and field3 between 000 and ZZZ and (field4 between 10000000 and 99999999 and (field5 between 123 and 123 or field5 between 456 and 456)) or (field4 between 20000000 and 40000000 and field5 between 000 and ZZZ)) or (field2 between AAAA and ZZZZ and field3 between AAA and BBB and field4 between 10000000 and 99999999 and field5 between 000 and ZZZ)

Note: the dictionary value for GROUP2 is not quite formatted in a way that matches your desired output : an }, needs to go before the key ("AAAA", "ZZZZ").