Using raw SQL in the Flask Web application to perform CRUD operations on the database can be cumbersome.

Instead, SQLAlchemy, the Python Toolkit is a powerful OR Mapper, which provides application developers with the full functionality and flexibility of SQL.

Flask-SQLAlchemy is a Flask extension that adds support for SQLAlchemy to the Flask application.

Related course: Python Flask: Create Web Apps with Flask

What is ORM?

ORM is short for Object Relation Mapping (sometimes Object Relationship Mapping).

  • Most programming language platforms are object-oriented.
  • the data in the RDBMS server is stored in tables.

Object-relational mapping is a technique that maps object parameters to the structure of a layer RDBMS table. The ORM API provides a way to perform CRUD operations without writing raw SQL statements.

Flask SQLAlchemy

Setup

In this section, we will study the ORM technology of Flask-SQLAlchemy and build a small web application.

Step 1 - Install the Flask-SQLAlchemy extension.

1
pip install flask-sqlalchemy

Step 2 - You need to import the SQLAlchemy class from this module.

1
from flask_sqlalchemy import SQLAlchemy

Step 3 - Now create a Flask application object and set the URI for the database to use.

1
2
app = Flask (__name__)
app.config ['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///students.sqlite3'

Step 4 - then use the application object as a parameter to create an object of class SQLAlchemy.The object contains an auxiliary function for the ORM operation.It also provides a parent Model class that uses it to declare a user-defined model.In the code snippet below, the studients model is created.

1
2
3
4
5
6
7
8
9
10
11
12
13
db = SQLAlchemy(app)
class students(db.Model):
   id = db.Column('student_id', db.Integer, primary_key = True)
   name = db.Column(db.String(100))
   city = db.Column(db.String(50))  
   addr = db.Column(db.String(200))
   pin = db.Column(db.String(10))

def __init__(self, name, city, addr,pin):
   self.name = name
   self.city = city
   self.addr = addr
   self.pin = pin

Step 5 - To create/use the database mentioned in the URI, run the create_all() method.

1
db.create_all()

CRUD

SQLAlchemy’s Session object manages all persistence operations for ORM objects.

The following session method performs CRUD operations:

1
2
# Inserts records into a mapping table
db.session.add (model object)
1
2
# delete records from a table
db.session.delete (model object)
1
2
# retrieves all records (corresponding to SELECT queries) from the table.
model.query.all ()

You can apply filters to the retrieved record set by using the filter attribute.For example, to retrieve records for city = ‘Tokyo’ in student tables, use the following statement:

1
students.query.filter_by(city = ’Tokyo’).all()

With so many backgrounds, now we will provide a view function for our application to add student data.

Related course: Python Flask: Create Web Apps with Flask

Views

Show all

The entry point of the application is the show_all () function that is bound to the ‘ /‘ URL.The record set of the student table is sent as a parameter to the HTML template.The server-side code in the template renders the record as an HTML table.

1
2
3
@app.route('/')
def show_all():
   return render_template('show_all.html', students = students.query.all() )

The HTML script for the template (‘show_all.html ‘) is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<!DOCTYPE html>
<html lang = "en">
   <head></head>
   <body>
      
      <h3>
         <a href = "{{ url_for('show_all') }}">Comments - Flask 
            SQLAlchemy example</a>
      </h3>
      
      <hr/>
      {%- for message in get_flashed_messages() %}
         {{ message }}
      {%- endfor %}
		
      <h3>Students (<a href = "{{ url_for('new') }}">Add Student
         </a>)</h3>
      
      <table>
         <thead>
            <tr>
               <th>Name</th>
               <th>City</th>
               <th>Address</th>
               <th>Pin</th>
            </tr>
         </thead>
         
         <tbody>
            {% for student in students %}
               <tr>
                  <td>{{ student.name }}</td>
                  <td>{{ student.city }}</td>
                  <td>{{ student.addr }}</td>
                  <td>{{ student.pin }}</td>
               </tr>
            {% endfor %}
         </tbody>
      </table>
      
   </body>
</html>

New record

The web page contains a hyperlink to the ‘/new’ URL mapping new () function.When clicked, the Student Information form opens. Data is published to the same URL in the POST method.

The view new.html contains:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<!DOCTYPE html>
<html>
   <body>
   
      <h3>Students - Flask SQLAlchemy example</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 = "email">City</label><br>
         <input type = "text" name = "city" placeholder = "city" /><br>
         <label for = "addr">addr</label><br>
         <textarea name = "addr" placeholder = "addr"></textarea><br>
         <label for = "PIN">City</label><br>
         <input type = "text" name = "pin" placeholder = "pin" /><br>
         <input type = "submit" value = "Submit" />
      </form>
      
   </body>
</html>

flask sqlalchemy form

POST

When the http method is detected as POST, the form data is added to the student table, and the application is returned to the home page of the display adding data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@app.route('/new', methods = ['GET', 'POST'])
def new():
   if request.method == 'POST':
      if not request.form['name'] or not request.form['city'] or not request.form['addr']:
         flash('Please enter all the fields', 'error')
      else:
         student = students(request.form['name'], request.form['city'],
            request.form['addr'], request.form['pin'])
         
         db.session.add(student)
         db.session.commit()
         
         flash('Record was successfully added')
         return redirect(url_for('show_all'))
   return render_template('new.html')

SQLAlchemy Example

The complete code for the application (app.py) is given below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

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:///students.sqlite3'
app.config['SECRET_KEY'] = "random string"

db = SQLAlchemy(app)

class students(db.Model):
   id = db.Column('student_id', db.Integer, primary_key = True)
   name = db.Column(db.String(100))
   city = db.Column(db.String(50))
   addr = db.Column(db.String(200)) 
   pin = db.Column(db.String(10))

def __init__(self, name, city, addr,pin):
   self.name = name
   self.city = city
   self.addr = addr
   self.pin = pin

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

@app.route('/new', methods = ['GET', 'POST'])
def new():
   if request.method == 'POST':
      if not request.form['name'] or not request.form['city'] or not request.form['addr']:
         flash('Please enter all the fields', 'error')
      else:
         student = students(request.form['name'], request.form['city'],
            request.form['addr'], request.form['pin'])
         
         db.session.add(student)
         db.session.commit()
         flash('Record was successfully added')
         return redirect(url_for('show_all'))
   return render_template('new.html')

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

Related course: Python Flask: Create Web Apps with Flask