How to Use Flask-SQLAlchemy: Setup and Basic Usage

To use flask-sqlalchemy, first install it and initialize SQLAlchemy with your Flask app. Define your database models as Python classes inheriting from db.Model, then use session methods like add() and commit() to save data.

Syntax

Here is the basic syntax to set up Flask-SQLAlchemy:

• from flask_sqlalchemy import SQLAlchemy: Import the extension.
• db = SQLAlchemy(app): Initialize with your Flask app.
• Define models by subclassing db.Model with columns as db.Column instances.
• Use db.session.add() and db.session.commit() to save changes.

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)

# Create tables
with app.app_context():
    db.create_all()

Example

This example shows a complete Flask app using Flask-SQLAlchemy to create a user and query it.

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)

with app.app_context():
    db.create_all()

    # Add a new user
    new_user = User(username='alice')
    db.session.add(new_user)
    db.session.commit()

    # Query the user
    user = User.query.filter_by(username='alice').first()
    print(f'User found: {user.username}')

Output

User found: alice

Common Pitfalls

Common mistakes when using Flask-SQLAlchemy include:

• Not calling db.create_all() inside the Flask app context, causing tables not to be created.
• Forgetting to set SQLALCHEMY_TRACK_MODIFICATIONS = False to avoid unnecessary warnings.
• Not committing the session after adding or modifying data, so changes are not saved.
• Using the wrong database URI format or forgetting to configure it.

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
# Missing this line causes a warning
# app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)

with app.app_context():
    # Forgetting app context causes error
    # db.create_all()
    pass

# Correct way:
# with app.app_context():
#     db.create_all()

Quick Reference

Here is a quick reference for common Flask-SQLAlchemy tasks:

Task	Code Example
Initialize SQLAlchemy	db = SQLAlchemy(app)
Define model	class User(db.Model):\n id = db.Column(db.Integer, primary_key=True)\n name = db.Column(db.String(80))
Create tables	with app.app_context():\n db.create_all()
Add record	user = User(name='bob')\ndb.session.add(user)\ndb.session.commit()
Query record	user = User.query.filter_by(name='bob').first()

Key Takeaways

Initialize Flask-SQLAlchemy with your Flask app and set the database URI.

Define models by subclassing db.Model and adding db.Column fields.

Always create tables inside the app context using db.create_all().

Use db.session.add() and db.session.commit() to save data changes.

Set SQLALCHEMY_TRACK_MODIFICATIONS to False to avoid warnings.