Harnessing the Power of Flask SQLAlchemy
Flask SQLAlchemy is a powerful and versatile library that brings the benefits of SQLAlchemy, a popular Object-Relational Mapping (ORM) tool, to Flask, a lightweight and flexible web framework in Python. This winning combination allows developers to seamlessly integrate databases into their Flask applications, making data management and persistence a breeze.
In this article, we will explore the features and capabilities of Flask SQLAlchemy and delve into the steps required to utilize it effectively in your Flask projects.
Understanding SQLAlchemy: The Backbone of Flask SQLAlchemy
Before diving into Flask SQLAlchemy, it’s important to grasp the fundamentals of SQLAlchemy. SQLAlchemy is a comprehensive SQL toolkit and Object-Relational Mapping library that provides a high-level, Pythonic interface for interacting with databases. It abstracts away the complexities of raw SQL queries and allows developers to interact with databases using Python objects, making database operations more intuitive and efficient.
Flask SQLAlchemy builds upon SQLAlchemy, extending its capabilities and integrating seamlessly with Flask. It provides a simplified interface and additional functionalities specifically tailored for Flask applications. With Flask SQLAlchemy, you can define database models as Python classes, perform database operations using simple Python methods, and leverage the power of SQLAlchemy’s query capabilities.
To integrate SQLAlchemy into your Flask application, you can use Flask-SQLAlchemy, a Flask extension that adds support for SQLAlchemy. By using Flask-SQLAlchemy, you can streamline the process of performing database operations within your Flask application. This will allow you to focus on developing the application logic without having to worry about the intricacies of SQL queries.
What is ORM (Object Relation Mapping)?
If you’re familiar with programming languages, you might know that they are mostly object-oriented. However, when it comes to relational database management system (RDBMS) servers, data is stored in tables. To bridge this gap, developers often use object-relational mapping (ORM) techniques, which involve mapping object parameters to the underlying RDBMS table structure. Using an ORM API, you can perform CRUD operations without having to write raw SQL statements.
Setting Up Flask SQLAlchemy: Configuration and Initialization
To get started with Flask SQLAlchemy, the first step is to set up the necessary configurations and initialize the extension within your Flask application. This involves installing the required dependencies and defining the database connection string, which specifies the database type, location, and credentials.
Once the dependencies are installed and the connection string is defined, Flask SQLAlchemy can be initialized by creating an instance of the SQLAlchemy class and associating it with your Flask app. This allows Flask to communicate with the database and utilize the features provided by Flask SQLAlchemy.
In this section, we will delve into the ORM techniques used in Flask SQLAlchemy and create a simple web application. By understanding how Flask SQLAlchemy works, you’ll be able to take advantage of its ORM functionality to easily interact with your database without having to write complex SQL queries.
Step 1 − Install Flask SQLAlchemy extension.
pip install flask-sqlalchemy
Step 2 − To use SQLAlchemy in your Flask application, it is necessary to import the SQLAlchemy class from the relevant module.
from flask_sqlalchemy import SQLAlchemy
Step 3 − Next, you should create an instance of a Flask application object and specify the URI for the database you wish to use.
app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///employees.sqlite3'
Step 4 − Afterward, instantiate an object of the SQLAlchemy class, passing the previously created Flask application object as the parameter. This object includes utility functions for ORM operations, as well as a base Model class that can be used to define user-defined models. In the example code below, a students model is defined.
db = SQLAlchemy(app)
class Employees(db.Model):
id = db.Column('employee_id', db.Integer, primary_key = True)
name = db.Column(db.String(100))
city = db.Column(db.String(50))
addr = db.Column(db.String(200))
age = db.Column(db.String(3))
salary = db.Column(db.String(10))
def __init__(self, name, city, addr,age,salary):
self.name = name
self.city = city
self.addr = addr
self.age = age
self.salary = salaryStep 5 − To create or use the database specified in the URI, execute the create_all() method.
db.create_all()
The SQLAlchemy Session object is responsible for handling all the persistence operations related to the ORM objects.
The CRUD operations are performed using the session methods listed below:
- db.session.add(model object) − This method adds a new entry into the table that is linked to the ORM object.
- db.session.delete(model object) − This method removes a record from the table.
- model.query.all() − This method returns all the records from a table, similar to executing a SELECT query.
To filter the retrieved records in the employee table, you can use the filter attribute. For example, if you want to retrieve records with a city value of ‘California‘, you can use the following statement:
Employees.query.filter_by(city = ’California’).all()
With this much of background, now we shall provide view functions for our application to add a employee data.
The entry point of the application is display_all() function bound to ‘/’ URL. The Record set of employees table is sent as parameter to the HTML template. The Server side code in the template renders the records in HTML table form.
@app.route('/')
def display_all():
return render_template('display_all.html', employees = employees.query.all() )The HTML code of the template named ‘display_all.html‘ looks like this:
<!DOCTYPE html>
<html lang = "en">
<head></head>
<body>
<h3>
<a href = "{{ url_for('show_all') }}">Employees - Flask
SQLAlchemy example</a>
</h3>
<hr/>
{%- for message in get_flashed_messages() %}
{{ message }}
{%- endfor %}
<h3>Employees (<a href = "{{ url_for('newdata') }}">Add Employee
</a>)</h3>
<table>
<thead>
<tr>
<th>Name</th>
<th>City</th>
<th>Address</th>
<th>Age</th>
<th>Salary</th>
</tr>
</thead>
<tbody>
{% for employee in employees %}
<tr>
<td>{{ student.name }}</td>
<td>{{ student.city }}</td>
<td>{{ student.addr }}</td>
<td>{{ student.age }}</td>
<td>{{ student.salary }}</td>
</tr>
{% endfor %}
</tbody>
</table>
</body>
</html>The page mentioned above includes a link to the URL mapped to the newdata() function. When this link is clicked, it will open a form for entering Employee Information.
Once the form is submitted, the data is posted to the same URL in a POST method.
newdata.html file code:
<!DOCTYPE html>
<html>
<body>
<h3>Employees - 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 = "addr">Address</label><br>
<textarea name = "addr" placeholder = "addr"></textarea><br>
<label for = "city">City</label><br>
<input type = "text" name = "city" placeholder = "city" /><br>
<label for = "age">Age</label><br>
<input type = "text" name = "age" placeholder = "age" /><br>
<label for = "salary">Salary</label><br>
<input type = "text" name = "salary" placeholder = "salary" /><br>
<input type = "submit" value = "Submit" />
</form>
</body>
</html>
If the HTTP method is identified as POST, the information submitted through the form is inserted into the employees table, and the application then redirects back to the homepage where the added data is displayed.
@app.route('/newdata', methods = ['GET', 'POST'])
def newdata():
if request.method == 'POST':
if not request.form['name'] or not request.form['city'] or not request.form['addr'] or not request.form['age'] or not request.form['salary']:
flash('Please enter all the fields', 'error')
else:
employee = employees(request.form['name'], request.form['city'],
request.form['addr'], request.form['age'], request.form['salary'])
db.session.add(employee)
db.session.commit()
flash('Record was successfully added')
return redirect(url_for('show_all'))
return render_template('newdata.html')
The following is the full code for the application (main.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.db'
app.config['SECRET_KEY'] = "random string"
db = SQLAlchemy(app)
class Employees(db.Model):
id = db.Column('employee_id', db.Integer, primary_key = True)
name = db.Column(db.String(100))
city = db.Column(db.String(50))
addr = db.Column(db.String(200))
age = db.Column(db.String(3))
salary = db.Column(db.String(10))
def __init__(self, name, city, addr, age, salary):
self.name = name
self.city = city
self.addr = addr
self.age = age
self.salary = salary
@app.route('/')
def display_all():
employees = Employees.query.all()
return render_template('display_all.html', employees=employees)
@app.route('/newdata', methods = ['GET', 'POST'])
def newdata():
if request.method == 'POST':
if not request.form['name'] or not request.form['city'] or not request.form['addr'] or not request.form['age'] or not request.form['salary']:
flash('Please enter all the fields', 'error')
else:
employee = Employees(request.form['name'], request.form['city'], request.form['addr'], request.form['age'], request.form['salary'])
db.session.add(employee)
db.session.commit()
flash('Record was successfully added')
return redirect(url_for('display_all'))
return render_template('newdata.html')
if __name__ == '__main__':
# db.create_all()
app.run(debug = True)Execute the script in the Python shell and type http://localhost:5000/ in the web browser.
Click on the link labeled ‘Add Employee’ to open a form for providing information about the employee.
Complete the form and click the submit button. The homepage will display again, showing the submitted information. The resulting output is demonstrated below.
Flask Sqlalchemy Benefits
- It uses ORM which means that developers can interact with a database using Python classes and methods, making it easy to manipulate data without having to write complex SQL queries.
- Flask SQLAlchemy supports multiple database backends, including PostgreSQL, MySQL, SQLite, and Oracle. Developers can easily switch between database backends without changing the codebase.
- Flask SQLAlchemy can handle large-scale applications with ease. It provides a powerful query interface that allows developers to write complex queries and perform data analysis with ease.
- Flask SQLAlchemy provides built-in security measures to protect against SQL injection attacks, making it a reliable and secure way to work with databases.
- Flask SQLAlchemy integrates seamlessly with other Flask extensions, making it easy to build complex applications with multiple features.
Conclusion
If you need to integrate a database into your Flask web application, you can benefit from using Flask SQLAlchemy. It allows you to easily map Python objects to relational database tables and provides a user-friendly interface for data querying. Furthermore, it seamlessly integrates with Flask, making setup and configuration a breeze. By using Flask and SQLAlchemy together, you can create flexible and effective database-driven web applications.
