Create a history view to see recent updates to db (Flask CRUD app + SQLAlchemy)

Question

I have a CRUD app to manage trucking appointments. I want to create a page where the user can see the 10 most recent changes that were made to the schedule.

In pseudo-code:

If an appointment is created/updated/deleted
    Then take that appointment data and insert it into a new db table

Then on my new "History" route/page, the user would just see the 10 most recent additions to that db table.

I tried adding a second commit within each of my view functions but I was unable to add the data to the new db table. Here's what I have so far:

app.py:

class appts_db(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    carrier = db.Column(db.String(100), nullable=False)
    volume = db.Column(db.Integer)
    pickup_date = db.Column(db.String(10))

class log_db(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    appt_id = db.Column(db.Integer, db.ForeignKey('appts_db.id'))
    modified_on = db.Column(db.DateTime, default=datetime.now)
    action = db.Column(db.String(7))

@app.route('/create', methods=['GET', 'POST'])
def create():
    if request.method == 'POST':
        carrier_name = request.form['carrier']
        requested_volume = request.form['volume']
        pickup_date_input = request.form['pickup_date']

        new_appt = appts_db(carrier=carrier_name, volume=requested_volume,
            pickup_date=pickup_date_input)

        db.session.add(new_appt)
        db.session.commit()
        return redirect('/')

    else:
        return render_template('create.html')

@app.route('/update/<int:id>', methods=['GET', 'POST'])
def update(id):
    appt = appts_db.query.get_or_404(id)

    if request.method == 'POST':
        appt.carrier = request.form['carrier']
        appt.volume = request.form['volume_update']
        appt.pickup_date = request.form['pickup_date']

        db.session.commit()
        return redirect('/')
    
    else:
        return render_template('update.html', appt=appt)

@app.route('/delete/<int:id>')
def delete(id):
    appt_to_delete = appts_db.query.get_or_404(id)
    db.session.delete(appt_to_delete)
    db.session.commit()
    return redirect('/')

Answer 1

These 3 links will help provide information for any similar problem in the future:

One-to-Many relationships with Flask + SQLAlchemy

SQLAlchemy docs - one to many relationships

Great example of implementing an update log

Here's my new code:

app = Flask (__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
db = SQLAlchemy(app)


class appts_db(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    carrier = db.Column(db.String(100))
    volume = db.Column(db.Integer)
    material = db.Column(db.String(10))
    pickup_date = db.Column(db.String(10))
    pickup_time = db.Column(db.String(5))
    PO_number = db.Column(db.String(30))

    #backref behaves like a column in the log_db model. This way, 
    #you can access the appt that the log was assigned to using the
    #"appt" attribute. 
    log_id = db.relationship("log_db", backref="appt", lazy=True)

class carriers_db(db.Model):
    carrier_id = db.Column(db.Integer, primary_key=True)
    carrier_name = db.Column(db.String(100))
    phone_number = db.Column(db.String(15))

@event.listens_for(appts_db, "after_insert")
def insert_log(mapper, connection, target):
    po = log_db.__table__
    connection.execute (po.insert().values(appt_id=target.id, action='Created'))

@event.listens_for(appts_db, "after_update")
def update_log(mapper, connection, target):
    po = log_db.__table__
    connection.execute (po.insert().values(appt_id=target.id, action='Updated'))

@event.listens_for(appts_db, "before_delete")
def delete_log(mapper, connection, target):
    po = log_db.__table__
    connection.execute (po.insert().values(appt_id=target.id, action='Deleted'))

class log_db(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    appt_id = db.Column(db.Integer, db.ForeignKey('appts_db.id'))
    modified_on = db.Column(db.DateTime, default=datetime.now)
    action = db.Column(db.String(7))
    #appts = db.relationship("appts_db", back_populates="log_db")

    def __repr__(self):
        return '<Appt %r>' % self.id

and a sample of the code in html to query the data:

{% for item in log %}
    <tr>
        <td>{{ item.action }}</td>
        <td>{{ item.appt_id }}</td>
        <td>{{ item.appt.carrier }}</td>
        <td>{{ item.appt.pickup_date }}</td>
        <td>{{ item.modified_on.strftime("%Y-%m-%d %H:%M:%S") }}</td>
    </tr>
{% endfor %}