How to Use Database in Flask: Simple Guide with Example

To use a database in Flask, install and configure Flask-SQLAlchemy, set your database URI in app.config, and define models as Python classes. Then, use SQLAlchemy methods to create, read, update, and delete data within your Flask app.

Syntax

Here is the basic syntax to set up a database in Flask using Flask-SQLAlchemy:

• app.config['SQLALCHEMY_DATABASE_URI']: Set your database connection string.
• db = SQLAlchemy(app): Initialize the database object.
• Define models by creating classes that inherit from db.Model.
• Use db.create_all() to create tables.

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

Example

This example shows a simple Flask app that connects to a SQLite database, defines a User model, adds a user, and queries all users.

python

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)

@app.route('/')
def index():
    # Add a new user
    new_user = User(username='alice')
    db.session.add(new_user)
    db.session.commit()

    # Query all users
    users = User.query.all()
    return '<br>'.join([user.username for user in users])

if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(debug=True)

Output

alice

Common Pitfalls

Common mistakes when using databases in Flask include:

• Not calling db.create_all() inside the app context, causing tables not to be created.
• Forgetting to commit the session after adding or modifying data, so changes are not saved.
• Using SQLALCHEMY_TRACK_MODIFICATIONS = True which adds overhead and is deprecated.
• Not handling unique constraints, leading to errors when inserting duplicate data.

python

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'] = True  # Avoid this

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()

# Wrong: forgetting to commit
new_user = User(username='bob')
db.session.add(new_user)
# db.session.commit()  # Missing commit means no save

# Right way:
db.session.commit()

Quick Reference

Here is a quick summary of key steps to use a database in Flask:

• Install Flask-SQLAlchemy with pip install flask-sqlalchemy.
• Set app.config['SQLALCHEMY_DATABASE_URI'] to your database URL.
• Initialize db = SQLAlchemy(app).
• Define models as classes inheriting from db.Model.
• Use db.create_all() inside app context to create tables.
• Add data with db.session.add() and save with db.session.commit().
• Query data using model query methods like User.query.all().

Key Takeaways

Use Flask-SQLAlchemy to easily connect and manage databases in Flask.

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

Remember to commit your session after adding or changing data.

Set SQLALCHEMY_TRACK_MODIFICATIONS to False to avoid unnecessary overhead.

Define your database models as Python classes inheriting from db.Model.