Spark Databox

AWS Online Training

375$ 599.99$

Python Flask-Flask SQLALchemy

FLASK-SQLALchemy

Flask web applications to perform CRUD operations on the database can be done by using raw SQL. A Python toolkit is a powerful or mapper that gives application developers the full power and flexibility of SQL. Flask-SQLAlchemy is the Flask extension that adds support for SQLAlchemy to your Flask application.

Object Relation Mapping:

Most of the programming language platforms are object-oriented. Data in RDBMS servers on the other hand is stored as tables. Object relation mapping is a technique of mapping object parameters to the underlying RDBMS table structure. An ORM API provides methods to perform CRUD operations without having to write raw SQL statements.

Now we are going to study the ORM techniques of Flask-SQLAlchemy and build a small web application.

Install Flask-SQLAlchemy extension.

pip install flask-sqlalchemy

Import SQLAlchemy class from this module.

from flask_sqlalchemy import SQLAlchemy

Create a Flask application object and set URI for the database to be used.

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

Create an object of SQLAlchemy class with application object.This object contains helper functions for ORM operations. It provides a parent Model class using which user defined models are declared. In the below, a students model is created.

db = SQLAlchemy(app)
class students(db.Model):
   id = db.Column('student_id', db.Integer, primary_key = True)
   name = db.Column(db.String(50))
   city = db.Column(db.String(40)) 
   addr = db.Column(db.String(100))
   pin = db.Column(db.String(10))
def __init__(self, name, city, address,pin):
   self.name = name
   self.city = city
   self.addr = address
   self.pin = pin

To use database mentioned in URI, run the create_all() method.

db.create_all()

The Session object of SQLAlchemy manages all persistence operations of ORM object.

The following session methods perform CRUD operations −

DB.session.add− It inserts a record into a mapped table.
DB.session.delete − It deletes the record from the table.
model.query.all() − It retrieves all the records from a table (corresponding to SELECT query).

Now you can apply a filter to the retrieved record set by using the filter attribute. In order to retrieve records with city = ’Bangalore’ in students table, use the following

Students.query.filter_by(city = ’Bangalore’).all()

With this background, we shall provide a view function for our application to add a student data.

The point of the application is show_all() function bound to ‘/’ URL. The recordset of the students table is sent as a parameter to the HTML template. The Server side code in the template renders the records in HTML table form.

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

HTML script of the template (‘show_all.html’) is -

<!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.address }}</td>
                  <td>{{ student.pin }}</td>
               </tr>
            {% endfor %}
         </tbody>
      </table>
   </body>
</html>

The above page contains a hyperlink to ‘/new’ URL mapping new() function. When clicked, it opens a Student Information Form. By the POST method, the data is posted to the same URL.

new.html
<!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 = "address">address</label><br>
         <textarea name = "address" placeholder = "address"></textarea><br>
         <label for = "PIN">City</label><br>
         <input type = "text" name = "pin" placeholder = "pin" /><br>
         <input type = "submit" value = "Submit" />
      </form>
   </body>
</html>

The form data is added in the students table and the application returns to homepage showing the added data it happens when the http method is detected as POST.

@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 fields', 'error')

      else:

         student = students(request.form['name'], request.form['city'],

            request.form['address'], 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')

Given below is the complete code of application (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:///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(50))

   city = db.Column(db.String(40))

   addr = db.Column(db.String(250))

   pin = db.Column(db.String(10))

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

   self.name = name

   self.city = city

   self.addr = address

   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['address']:

         flash('Please enter all fields', 'error')

      else:

         student = students(request.form['name'], request.form['city'],

            request.form['address'], request.form['pin'])

         db.session.add(student)

         db.session.commit()

         flash(‘Successfully record added')

         return redirect(url_for('show_all'))

   return render_template('new.html')

if __name__ == '__main__':

   db.create_all()

   app.run(debug = True)

Run the script from Python shell and enter http://localhost:5000/ in the browser.

Introduction

Flask Templates

Flask Methods

Flask Cookies

Flask Session

Flask Message Flashing

Flask SQLite

Flask SQLALchemy

AWS Online Training

Python Flask-Flask SQLALchemy

Recent Blog

Live Online Courses

Contact Us