Flask-SQLAlchemy setup

Introduction

Flask-SQLAlchemy helps you connect your Flask app to a database easily. It lets you work with databases using Python code instead of writing SQL queries.

You want to save user information like names and emails in a database.

You need to store and retrieve data for a blog or a small website.

You want to manage data without writing complex SQL commands.

You are building a Flask app that needs to remember data between visits.

Syntax

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

Set SQLALCHEMY_DATABASE_URI to tell Flask where your database is.

Creating SQLAlchemy(app) links the database to your Flask app.

Examples

This sets up a SQLite database file named site.db in your project folder.

Flask

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

This connects to a PostgreSQL database with username and password.

Flask

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:password@localhost/mydb'

This creates the database object you use to define tables and interact with data.

Flask

db = SQLAlchemy(app)

Sample Program

This Flask app sets up a SQLite database named test.db. It defines a User table with an ID and username. It creates the table, adds one user named 'alice', saves it, and then prints the first user.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.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)

    def __repr__(self):
        return f'<User {self.username}>'

with app.app_context():
    db.create_all()
    new_user = User(username='alice')
    db.session.add(new_user)
    db.session.commit()
    user = User.query.first()
    print(user)

Important Notes

Always set SQLALCHEMY_TRACK_MODIFICATIONS to False to avoid extra warnings.

Use app.app_context() when working with the database outside request handlers.

Remember to call db.create_all() once to create tables before adding data.

Summary

Flask-SQLAlchemy connects your Flask app to a database simply.

Configure the database URI and create a SQLAlchemy object linked to your app.

Define tables as Python classes and use the database session to add or query data.