Multiple variable to store on a column of database Flask

Question

I'm working on an app for Transport Company and on the client page, we can add Delivery Tour and these Delivery Tours can be planned on different days and hours :

exemple :

A delivery tour is planned on Monday at 4pm and Thursday 10am.

Currently, I store this in my SQLAlchemy Delivery Tour database in JSON Format like this :

{'Monday':10,'Thursday':16}

But the thing is when I rendered the HTML file I can't success to iterate through the JSON file and have the correct day and hour associated to the correct Delivery Tour...

So I was wondering is there a more efficient way to do this ? Like maybe create a new table but I have no idea how to arrange columns.

EDIT

My HTML :

<div class="card">
            <div class="card-header-spe"> 
                <p style="text-align:left;">
                    Delivery Tour
                    <span style="float:right;">
                        <a class=" card-category btn btn-primary" href="{{ url_for('deliver', client_id=client.id )}}">Add New</a>
                    </span>
                </p>
            </div>
            <table class="table table-striped table-hover">
                <thead>
                    <tr>
                    <th scope="col">Title</th>
                    <th scope="col">Description</th>
                    <th scope="col">Schedule</th>
                    <th scope="col">Driver</th>
                    </tr>
                </thead>

                <tbody>

                    <tr>   
                        {% for deliv in delivery %}
                            <th scope="row">{{ deliv.title }}</th>
                            <td>{{ deliv.description }}</td>
                            <td >{{ deliv.schedule }} </td>
                            {% if deliv.driver_assigned %}
                                {% for d in driver %}
                                    {% if d.id == deliv.driver_assigned %}
                                    <td>{{d.first_name}}</td>
                                    {% endif %}
                                {% endfor %}
                            {% else %}
                                <td></td>
                            {% endif %}
                    </tr>
                        {% endfor %}


                </tbody>
            </table>
        </div>

And here is a photo of my Delivery database :

Answer 1

You have the basics of it already:

{% for day, hour in eval(deliv.schedule).items() %}

You would have to do eval because unless I'm wrong, you have that column stored as a string in the table.

If it were me, I'd create a table just for the schedules and make a one-to-many relationship to your delivery_tour table: (note: drop the schedule column on your delivery_tour table)

class TourSchedule(Base):
    __tablename__ = 'tour_schedules'
    id = Column(Integer, primary_key=True)
    tour_id = Column(Integer, ForeignKey('delivery_tour.id'))
    tour = relationship('DeliveryTour', backref='schedule')
    day = Column(String(16))
    hour = Column(Integer)

Then you can just iterate over it like you otherwise would:

<td>
    <ul style="list-style-type: none;">
        {% for sched in deliv.schedule %}
            <li>{{sched.day}}: {{sched.hour}}</li>
        {% endfor %}
    </ul>
</td>