ORM in Python Flask

ORM in flask a quick start tutorial

What is ORM?

ORM(Object Relationship Mapping) is a programming construct where the DB tables are treated as objects, and the operations on top of them are carried out via functions.

Video Tutorial

ORM in Python

Django, the web framework in Python, has one of the most solid ORMs. For non-Django applications, SQLAlchemy has proved to be effective.

ORM in Flask

Flask doesn't support ORM out of the box, but with the help of flask-sqlalchemy, we can achieve the ORM functionalities.


pip install flask-sqlalchemy

Documentation For Reference

Flask SQLalchemy has extensive documentation.

Configure DB

Let's start writing some code. Open a Python file
Import flask-sqlalchemy and connect to the DB
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)

Defining a Model

Each table is called a Model(a Python class) when defining an ORM. The table columns will become attributes of the class.
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
joined_on = = db.Column(db.DateTime, nullable=False)

Creating the Table

The above snippet is just a configuration to create a table in DB. You need to use

CRUD Operations

Create a new Item

# creates a Python Object
admin = User(username='admin', email='[email protected]')
# adds to the db session
# Makes the entry in the DB
# you can do multiple db.session.add before committing

List all items

users = User.query.all()
results = []
for user in users:

Filter Items

users = User.query.filter_by(username='admin').all()
# use the same for loop as above

GET by id

user = User.query.filter_by(id=1).first()
result = {

Selecting Specific Columns

fields = ["book_id", "book_name", "author"]
field_objects = [getattr(Book, "name") for field in fields]
books = Book.query.with_entities(*field_objects).all()

Update an Item

User.query.filter_by(id=1).update({"name": "admin001"})

Delete an Item

user = User.query.filter_by(id=1).first()

Run the script


How to add One-Many and Many-Many relationship

Checkout the following snippet


  1. 1.
    Pick one model for your application
  2. 2.
    Add the Model and fields
  3. 3.
    Try all the CRUD operations