PF Flask DB Create, Update, Read, Delete (CRUD) Example by SQL

In this section we will first create model, after that will do CRUD operation

  • Create Person model

class Person(AppModel):
    first_name = db.Column(db.String(150), nullable=False)
    last_name = db.Column(db.String(150))
    email = db.Column(db.String(120), unique=True, nullable=False)
    age = db.Column(db.Integer)
    income = db.Column(db.Float, default=0)


with app.app_context():
    db.create_all()
  • Here :

    • db.create_all() : Create all schema during the application start.


  • Create : Raw Insert SQL run to database

@app.route('/create')
def create():
    insert = "INSERT INTO person (first_name, last_name, email) VALUES ('Raw First Name', 'Raw Last Name', 'hmtmcse.com@gmail.com')"
    app_db.run_sql(insert)
    response = "Data successfully Inserted"
    return response


  • Update : For update the raw SQL direct run to database

@app.route('/update')
def update():
    sql = "UPDATE person SET first_name = 'updated first name', last_name = 'updated last name' WHERE id = 1"
    persons = app_db.run_sql(sql)
    return "Data has been updated."


  • Delete : Direct Delete SQL

@app.route('/delete')
def delete():
    sql = "DELETE FROM person WHERE id = 1"
    persons = app_db.run_sql(sql)
    return "Record has been deleted"


  • Read list of inserted data : List all data using SELECT query

@app.route('/list')
def list():
    response = ""
    sql = "SELECT * FROM person"
    persons = app_db.run_sql(sql)
    for person in persons:
        response += person.first_name + " " + person.last_name + " " + person.email + "<br>"
    return response