Do you think IIT Guwahati certified course can help you in your career?
Introduction
What is SQLAlchemy?
SQLAlchemy is an open-source database API (Application Programming Interface) and query language. It is often used as a data access layer, translating database queries and commands into Python calls and functions. It can be used with various databases, including PostgreSQL, MariaDB, MySQL, and Microsoft SQL Server. SQLAlchemy aims to provide a single API for accessing a range of databases, rather than using a bespoke API for each one.
Flask-SQLAlchemy is a Python-based tool for creating, managing, and manipulating data in a database. It is an object-relational mapper (ORM), which means that it provides a way to translate data into objects and back again, making it easier to work with databases. SQLAlchemy in flask provides database access and management to web applications.
In this article, we will discuss how to integrate SQLAlchemy in flask and simplify our Database operations in a flask application.
Install and Setup SQLAlchemy in Flask
1. We can install SQLAlchemy through pip package manager with the following command:
Here, sqlite:///Database.sqlite3 is the URL address of the database to be configured with SQLAlchemy in flask in the config attribute of the flask application object. It is of the type : [DB_TYPE]+[DB_CONNECTOR]://[USERNAME]:[PASSWORD]@[HOST]:[PORT]/[DB_NAME]
4.Now, we will create an SQLAlchemy object named database with the flask application object as its parameter:
database = SQLAlchemy(app)
5. We have the database declared and initialized. We will now define a model for the SQLAlchemy object databaseto establish the symbiotic relationship between user data and the database. Following is an example of creating a model SQLAlchemy in flask of employees:
class employees(db.Model):
id = database .Column('employee_id', database .Integer, primary_key = True)
name = database .Column(database .String(70))
city = database .Column(database .String(20))
address = database .Column(database .String(200))
pin = database .Column(database .String(8))
SQLAlchemy object for CRUD operations
Let's discuss the various SQLAlchemy object for CRUD operations:
Model.session.add(model obj): Add a new record in the mapped table.
Model.session.delete(model obj): Delete a record from the table.
Model.session.commit(): Committing the changes to the database.
Query Object: It is a method used for querying the table's records. It has various important methods:
Model.query.all(): Retrieves all records from the table.
Model.query.filter_by(condition).all: Retrieves all records from the table filtered with the corresponding query.
Example
Let us combine all the information above and develop a web application that adds and query all the employees' records.
First, we will design a home template that displays all the records of the employees in the table, which will look like the following:
<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">Pincode</label><br>
<input type = "text" name = "pincode" placeholder = "pin" /><br>
<input type = "submit" value = "Submit" />
</form>
Output
The table will be empty, and hence no data will be displayed in the employee's table.
On clicking Add an employee:
Output
The complete app.py file for the above web application that handles from rendering the pages to saving the data through SQLAlchemy in flask is given below:
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'
database = SQLAlchemy(app)
class employees(database.Model):
id = database.Column('student_id', database.Integer, primary_key = True)
name = database.Column(database.String(70))
city = database.Column(database.String(20))
address = database.Column(database.String(200))
pincode = database.Column(database.String(8))
def __init__(self, name, city, address,pincode):
self.name = name
self.city = city
self.address = address
self.pin = pincode
@app.route('/')
def show_all():
# Query to fetch all the records from model employee
return render_template('home.html', employees = employees.query.all() )
@app.route('/add_new_record', methods = ['GET', 'POST'])
def add_new_record():
if request.method == 'POST':
if not request.form['name'] or not request.form['city'] or not request.form['address']:
flash('Please enter all the fields', 'error')
else:
record = employees(request.form['name'], request.form['city'],
request.form['address'], request.form['pincode'])
database.session.add(record)
database.session.commit()
flash('Employee was successfully added')
return redirect(url_for('home'))
return render_template('add_new_record.html')
if __name__ == '__main__':
database.create_all() # To create and map the tables and database
app.run(debug = True)
You can also try this code with Online Python Compiler
After successfully adding records in the database, we are redirected to home.html where the query, employees.query.all() fetches all the records and renders them back to home.html.
Output
And this is how SQLAlchemy in flask simplifies CRUD operations on our database with many easy-to-use and user-friendly features and methods.
FAQs
1. Why should we use SQLAlchemy? Ans. SQLAlchemy is amazing because it has a solid connection/pooling architecture, a decent Pythonic query building infrastructure, and an ORM infrastructure that can handle complicated queries and mappings (as well as some pretty stone-simple ones).
2. Will our record be inserted in the database immediately when we send data to the app.py file? Ans:- The record is added to the mapped table as soon as the session.add(Model Obj) executes, but the actual change will reflect in the database connected only when we use session.commit() method.
3. What is an ORM? Ans. Object-Relational mappers find patterns in data and classify things. They can do everything from finding the best salesperson for each customer, detecting fraudulent transactions, and identifying and protecting the country's top political dissidents.
ORM turns data into understandable, searchable information for users. OR mappers can help users find information that fits their query, regardless of the context. For example, if a user searches for "Chicago weather forecast" an OR mapper could look for "weather" or "temperature" and provide the results to the user.
Key Takeaways
In this article, we have extensively discussed how SQLAlchemy in flask works and how it simplifies the CRUD operations in the database.
We hope that this blog has helped you enhance your knowledge regarding flask and if you would like to learn more, check out our articles on Code Studio. Do upvote our blog to help other ninjas grow. Happy Coding!
Live masterclass
Multi-Agent AI Systems: Live Workshop for 25L+ CTC at Google
by Saurav Prateek
09 Feb, 2026
03:00 PM
Beginner to GenAI Engineer Roadmap for 30L+ CTC at Amazon
27+ registered 
