Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

flask

How does Flask handle database requests?

Momin Imran Qureshi

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Database requests in Flask

Flask is a Python framework used to create web applications. It can be combined with SQLAlchemyA Python SQL toolkit and object relational mapper (ORM) to handle database requests for SQL databases like MySQL, PostgreSQL, SQLite and others. We’ll use SQLite as an example in this Answer.

Prerequisites

  • Installed Python compiler.
  • Installed pip.

What are database requests?

To send data from a database to a website, database requests are handled by backend languages like Python and frameworks like Flask to ensure data is correctly transferred to the web page.

How to create tables in SQLite

  • First install DB Browser for SQLite, an application that provides functionality to create, design, and edit database files compatible with SQLite.
  • We’ll use the following database as an example:
showroom.db database
showroom.db database

How to read from tables in SQLite

Install Flask and flask-sqlalchemy (if they are not already present) by entering the following commands in the terminal:

pip install Flask
pip install flask-sqlalchemy
Code for installing Flask and flask-sqlalchemy

Testing the database connection

Enter the following into a code editor such as VSCode.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.sql import text
app = Flask(__name__)
# the name of the database; add path if necessary
database_Name = 'showroom.db'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + database_Name
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
# The variable database will be used for all SQLAlchemy commands
database = SQLAlchemy(app)
@app.route('/')
def main():
database.session.query(text('1')).from_statement(text('SELECT 1')).all()
return '<h1>Test Successful</h1>'
if __name__ == '__main__':
app.run(debug=True)
Code to test database connectivity

Explanation

  • Lines 1–3: We import the relevant libraries from Flask and SQLAlchemy.
  • Line 5: We create a Flask application object (app) which inherits all the attributes and methods from the class Flask.
  • Line 10: Contains the database connection string needed to connect to the database.
  • Line 12: SQLALCHEMY_TRACK_MODIFICATIONS is a configuration key which if set to true, enables track modifications of objects and emits signals.
  • Line 15: We create an SQLAlchemy object database which contains functions from both sqlalchemy and sqlalchemy.orm. It also declares a class called Model which will be used to read from the database.
  • Lines 17–20: Used to test the connection. The highlighted line 19 interacts with the database. Line 20 returns an HTML syntax which displays "Test Successful" on your webpage.
  • Lines 22–23: Part of the syntax needed for Flask to run.

Note: Ensure the database file is in the same folder as this file.

Reading from the database

First define a class for each table in the database. An example is shown below for the Car table:

class Car(database.Model):
__tablename__ = 'cars'
id = database.Column(database.Integer, primary_key=True)
name = database.Column(database.String)
type = database.Column(database.String)
colour = database.Column(database.String)
price = database.Column(database.String)
Creating a class for our car table

Syntax

Column_name = db.Column (db.variable_type, constraints (optional))

In order to retrieve information from the database we'll use the two functions shown below:

@app.route('/')
def index():
# get a list of unique values in the style column
types = Car.query.with_entities(Car.type).distinct()
return render_template('index.html', types=types)
@app.route('/inventory/<type>')
def inventory(type):
try:
cars = Car.query.filter_by(type=type).order_by(Car.name).all()
return render_template('list.html', cars=cars, type=type)
except Exception as e:
# e holds description of the error
error_text = "<p>The error:<br>" + str(e) + "</p>"
hed = '<h1>Something is broken.</h1>'
return hed + error_text
Functions that read and display data from database

Explanation

  • The first function is the index function altered to query the database and returns the car types that are distinct.
  • Line 4: Car.query queries the cars table, with_entities() method restricts the columns returned to the Car.type column specified. The distinct() method returns only unique values in the column.
  • Line 5: The types are sent as a tuple to the index.html page where they are displayed through a loop. The index.html page contains jinja syntax for displaying data from backend.
  • The second function takes the type as a parameter and displays all the cars of that type by querying the database.
  • Line 11: Car.query queries the cars table, filter_by() limits our query similar to the where clause in SQL, order_by() is passed a column to determine the order of the data (Ascending by default). The all() method ensures more then one record is returned.
  • Line 12: The cars tuple along with their types are sent to the list.html page where they are displayed in rows.
  • Line 17: In case there is an error the exception is handled and an error is shown.

Code example

{% extends 'bootstrap/base.html' %}

{% block styles %}
{{ super() }}
	<style>
		body { background: #e8f1f9; }
	</style>
{% endblock %}


{% block title %}
Car Type Lists
{% endblock %}


{% block content %}

<div class="container">
  <div class="row pb-5">
    <div class="col-md-10 col-lg-8 mx-lg-auto mx-md-auto">

      <h1 class="pt-5 pb-2">List of Cars</h1>

      <p class="lead alert alert-warning">This is a list of all the cars of the
			<strong>{{ type }}</strong> type in our inventory.</p>

	  <p class="ml-4"><a href="{{ url_for('index') }}">Return to the index.</a></p>

		<table class="table">
			<tr>
				<th>Car Name</th>
				<th>Type</th>
				<th>Color</th>
				<th>Price</th>
			</tr>

			<!-- here is a for-loop to give us all the cars data -->
			{% for car in cars %}
			<tr>
				<td>{{ car.name }}</td>
				<td>{{ car.type }}</td>
				<td>{{ car.colour }}</td>
				<td>{{ car.price }}</td>
			</tr>
			{% endfor %}
		</table>

		</div>
	</div>
</div>

{% endblock %}

Explanation

  • The main.py file contains the complete code, including the connection string, the cars class and the two functions needed to read data from the database.
  • In index.html the highlighted lines receive data from index() function in main.py and display it in a list.
  • In list.html the highlighted lines receive data from inventory() function in main.py and display it in a list.

Expected output

Upon adding the showroom.db database file in the project folder, the output is shown below:

Main page
Main page
The list of cars upon selecting sedan
The list of cars upon selecting sedan

RELATED TAGS

flask

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Keep Exploring