Nested Data structure

Question

I have the flask code below to query from sqlite.

@app.route('/recipe_pretty') 
@flask_login.login_required
def recipe_pretty():
    from functions.sqlquery import sql_query
    results2 = sql_query('''  SELECT  
                             REPLACE(R.Amount, '.0', '')  ||' ' || U.Name || "-" || I.Name || " (" || IT.Name ||")"  as AmountPretty,  R.SectionName  FROM Recipe as R
                            INNER JOIN Units as U on U.Id = R.UnitId
                            INNER JOIN Ingredient as I on I.Id = R.IngredientId
                            INNER JOIN IngredientType as IT on IT.Id = I.IngredientTypeId
                            INNER JOIN RecipeHeader as RH on RH.Id = R.RecipeId
                            ORDER BY R.SectionName''')
    
    

    
    return render_template('recipe_present.html', results2=results2)   

This returns a list of SQlite rows. The Sql_query function is list below this returns sqlite3.Rows into a List. See the conn.row_factory.

DB  = env.str("DB_NAME")
# Create a database
conn = sqlite3.connect(DB, check_same_thread=False)

conn.row_factory = sqlite3.Row

# Make a convenience function for running SQL queries
def sql_query(query):
    cur = conn.cursor()
    cur.execute(query)
    rows = cur.fetchall()
    return rows

List

I'm trying to loop through this in the following way to get the SectionName as sub header with the AmountPretty repeating under each SectionName.

  <ul>
                {% for section in results2 %}
                <li>
                <h2>{{section.SectionName}}</h2>
                <ul>
                    {% for ing in section %}
                    <li>{{ing.AmountPretty}}</li>
                    {% endfor %}
                </ul>
                </li>
                {% endfor %}
  </ul>

This isn't working. I just get the list of SectionNames with no AmountPretty. Do I need to split this single table into a nested list using the 2 columns for this to work?

Any help would be much appreciated

Answer 1

I had to restructure the sql data with

lists = {}

for k, g in groupby(results2, key=lambda t: t['SectionName']):
   lists[k] = list(g)

That allowed me to access with this in my template :

  {% for list, items in lists.items() %}
              <div class="card text-sm" style="width: 18rem; margin-bottom: 50px;">
                  <div class="card-header">
                      <h3>{{ list }}</h3>
                  </div>
                  <ul class="list-group list-group-flush">
                      {% for item in items %}
                          <li class="list-group-item">
                            {{ item['AmountPretty'] }}
                          </li>
                      {% endfor %}
                      </ul>
                </div>
                {% endfor %}