Flask SQLAlchemy

Flask SQLAlchemy is an ORM tool which establishes the relationship between the objects and the tables of the relational databases.

The mapping between the both is important because the python is capable of storing the data in the form of objects whereas the database stores the data in the form of relational tables, i.e. the collection of rows and columns.

The object-relational mapping is the technique of storing python objects into the database tables without writing the raw SQL queries.

In this section of the tutorial, we will create a small web application using flask-sqlalchemy ORM techniques.

Install flask-sqlalchemy:

To create a web application using the flask ORM techniques, we must need to install flask-sqlalchemy using pip installer.

To confirm the installation, try to import the module on the python shell; if it is successfully imported, the installation is successful.

Creating a small web application using flask-sqlalchemy

In this section of the tutorial, we will create a CRUD Application in python using ORM SQLAlchemy.

Example

add.html

<!DOCTYPE html>
<html>
   <body>
      <h3>Add new Employee</h3>
      <hr/>
      
      {%- for category, message in get_flashed_messages(with_categories = true) %}
         <div class = "alert alert-danger">
            {{ message }}
         </div>
      {%- endfor %}
      
      <form action = "{{ request.path }}" method = "post">
         <label for = "name">Name</label><br>
         <input type = "text" name = "name" placeholder = "Name" /><br>
 
         <label for = "salary">Salary</label><br>
         <input type = "text" name = "salary" placeholder = "salary" /><br>
         
         <label for = "age">Age</label><br>
         <textarea name = "age" placeholder = "age"></textarea><br>
         
         <label for = "PIN">Pin</label><br>
         <input type = "text" name = "pin" placeholder = "pin" /><br>
         
         <input type = "submit" value = "Submit" />
      </form>
   </body>
</html>

list_employees.html

<!DOCTYPE html>
<html lang = "en">
   <head><title>Home</title></head>
   <body>
      <h3>
         <a href = "{{ url_for('list_employees') }}">Employee Management System</a>
      </h3>
      
      <hr/>
      {%- for message in get_flashed_messages() %}
         {{ message }}
      {%- endfor %}
		
      <h3>Employees List</h3>
      <table border="2" padding = "5">
         <thead>
            <tr>
               <th>Name</th>
               <th>Salary</th>
               <th>Age</th>
               <th>Pin</th>
            </tr>
         </thead>

         <tbody>
            {% for employee in Employees %}
               <tr>
                  <td>{{ employee.name }}</td>
                  <td>{{ employee.salary }}</td>
                  <td>{{ employee.age }}</td>
                  <td>{{ employee.pin }}</td>
               </tr>
            {% endfor %}
         </tbody>
      </table>
      <br><br>
      <a href="{{ url_for('addEmployee') }}">Add New Employee</a>
   </body>
</html>

app.py

from flask import Flask, request, flash, url_for, redirect, render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///employees.sqlite3'
app.config['SECRET_KEY'] = "secret key"

db = SQLAlchemy(app)

class Employees(db.Model):
   id = db.Column('employee_id', db.Integer, primary_key = True)
   name = db.Column(db.String(100))
   salary = db.Column(db.Float(50))
   age = db.Column(db.String(200)) 
   pin = db.Column(db.String(10))

   def __init__(self, name, salary, age,pin):
      self.name = name
      self.salary = salary
      self.age = age
      self.pin = pin

@app.route('/')
def list_employees():
   return render_template('list_employees.html', Employees = Employees.query.all() )

@app.route('/add', methods = ['GET', 'POST'])
def addEmployee():
   if request.method == 'POST':
      if not request.form['name'] or not request.form['salary'] or not request.form['age']:
         flash('Please enter all the fields', 'error')
      else:
         employee = Employees(request.form['name'], request.form['salary'],
            request.form['age'], request.form['pin'])
         
         db.session.add(employee)
         db.session.commit()
         flash('Record was successfully added')
         return redirect(url_for('list_employees'))
   return render_template('add.html')

if __name__ == '__main__':
   db.create_all()
   app.run(debug = True)

Output: Flask SQLAlchemy