How to Update Flask SQLAlchemy

We will learn, with this explanation, how to insert and update a row with the help of the SQLAlchemy database in the Flask app.

Update a Row With the Help of the SQLAlchemy Database in the Flask App

We have a simple model called Employee_Model, which has a couple of fields. We will be working on this model.

from datetime import datetime
from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///db.sqlite3"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db = SQLAlchemy(app)


class Employee_Model(db.Model):
    Employee_ID = db.Column(db.Integer, primary_key=True)
    Employee_Name = db.Column(db.String(50))
    Employee_Email = db.Column(db.String(100), unique=True)
    Date_Field = db.Column(db.Date, default=datetime.utcnow)

    def __repr__(self):
        return f"<Employee_Model: {self.Employee_Email}>"


if __name__ == "__main__":
    app.run(debug=True)

Now we will open the Python shell and import db and Employee_Model from our Flask app. Then, we will create the database using db.create_all().

We will use Employee_Model.query.all() to show you that there is nothing in the database.

>>> from app import db,Employee_Model
>>> db.create_all()
>>> Employee_Model.query.all()
[]

Now the first thing we need to do is insert data into the database before updating data. To do this, we are instantiating our Employee_Model model.

We are going to instantiate it with the fields. We are not using Employee_ID or Date_Field because both fields can be generated automatically.

Employee_ID is generated by the database, and Date_Field is given the default, that is, datetime.utcnow. Then we will add a single row of data using db.session.add() and pass an object.

Then we will commit the data using db.session.commit(), which will save everything. When we run the command Employee_Model.query.all(), we will see one Employee in the database, and we can see the Date_Field object using the jack.Date_Field that has the date.

>>> jack=Employee_Model(Employee_Name='jack',Employee_Email='jack@something.com')
>>> db.session.add(jack)
>>> db.session.commit()
>>> Employee_Model.query.all()
[<Employee: jack@something.com>]
>>> jack.Date_Field
datetime.date(2022, 7, 14)

We can do it manually if we do not want to use the default date. To do this, we will import the date from datetime, and then we can create a new Employee with the object name laura.

We will supply a name in the email to the Employee_Model() class, and then we pass Date_Field.

Now we need to add the laura object inside the database and save it to the database. If we extract the query from the database, we see two employees in the database.

>>> from datetime import date
>>> from app import db,Employee_Model
>>>laura=Employee_Model(Employee_Name='laura',Employee_Email='laura@something.com',Date_Field=date(2022,7,14))
>>> db.session.add(laura)
>>> db.session.commit()
>>> Employee_Model.query.all()
[<Employee: jack@something.com>, <Employee: laura@something.com>]

Now we will update data in the database for a particular thing. Suppose we want to query for the jack object.

To do this, we will filter the jack object using filter_by(Employee_Name='jack'), which means we are looking for any row with the name jack. We want to get the first result, and it should be unique.

Now, we will update the Employee_Email field using the assignment operator and then commit it to save the updated record in the database. When we perform the query and get all the users in the database, instead of seeing jack@something.com, we see jack123@something.com.

>>> from app import db,Employee_Model
>>> jack=Employee_Model.query.filter_by(Employee_Name='jack').first()
>>> jack
<Employee: jack@something.com>
>>> jack.Employee_Email='jack123@something.com'
>>> db.session.commit()
>>> Employee_Model.query.all()
[<Employee: jack123@something.com>, <Employee: laura@something.com>]
>>> jack.Employee_Email
'jack123@something.com'