How to Use SQLAlchemy with Flask for Database Management

 Using SQLAlchemy with Flask is a powerful way to manage databases in Python web applications. SQLAlchemy is a feature-rich ORM (Object Relational Mapper), and Flask makes it easy to integrate through the Flask-SQLAlchemy extension.

🧱 What You'll Learn:

Installing Flask & SQLAlchemy

Setting up a Flask app with SQLAlchemy

Defining models (tables)

Creating and querying the database

Using migrations (optional but recommended)

✅ 1. Install Required Packages

bash

Copy

Edit

pip install Flask Flask-SQLAlchemy

For migrations:

bash

Copy

Edit

pip install Flask-Migrate

✅ 2. Basic Flask App with SQLAlchemy

python

Copy

Edit

from flask import Flask

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# Configure database URI (SQLite for simplicity)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# Initialize the database

db = SQLAlchemy(app)

✅ 3. Define a Model (Table)

python

Copy

Edit

class User(db.Model):

    id = db.Column(db.Integer, primary_key=True)

    username = db.Column(db.String(20), unique=True, nullable=False)

    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):

        return f"User('{self.username}', '{self.email}')"

✅ 4. Create the Database

You can do this in a Python shell:

bash

Copy

Edit

python

>>> from yourapp import db

>>> db.create_all()

Or from within your script:

python

Copy

Edit

with app.app_context():

    db.create_all()

✅ 5. Insert and Query Data

python

Copy

Edit

# Inserting a new user

new_user = User(username='alice', email='alice@example.com')

db.session.add(new_user)

db.session.commit()

# Querying users

users = User.query.all()

user = User.query.filter_by(username='alice').first()

✅ 6. Flask-Migrate (for Schema Changes)

Initialize migration support:

bash

Copy

Edit

flask db init

Create migration script after model change:

bash

Copy

Edit

flask db migrate -m "Added User model"

Apply migration to the database:

bash

Copy

Edit

flask db upgrade

📦 Project Structure Example

bash

Copy

Edit

/your_project

├── app.py

├── models.py

├── requirements.txt

└── migrations/

⚙️ Tips

Use nullable=False to enforce NOT NULL.

Use unique=True for unique constraints.

Use relationships for foreign keys.

📘 Summary

Task Code/Command

Define model class User(db.Model): ...

Create DB db.create_all()

Add row db.session.add(obj)

Query data Model.query.filter_by(...).first()

Migrations flask db migrate, flask db upgrade

Learn Full Stack Python Course in Hyderabad

Read More

Introduction to MongoDB for Full Stack Python

Creating and Managing Relationships in Databases with Django ORM

Implementing Authentication with Databases in Python

Using Django ORM to Interact with Databases

Visit Our IHUB Talent Training Institute in Hyderabad

Get Directions