How to Connect Flask to Database: Full Tutorial

How to Connect Flask to a Database: Step-by-Step Tutorial

Connecting a Flask web application to a database is essential for building dynamic, data-driven apps. Databases store your data reliably so you can create, read, update, and delete (CRUD) records efficiently. This tutorial walks you through connecting Flask to a database using Python and the popular SQLAlchemy ORM.

Prerequisites

Basic Python knowledge
Flask installed (refer to our How to Install Flask tutorial for setup)
A database system: SQLite (default with Python), MySQL, or PostgreSQL
pip for installing Python packages

Step 1: Install Required Packages

Install Flask-SQLAlchemy, an extension that simplifies using SQLAlchemy ORM with Flask.

pip install flask-sqlalchemy

Step 2: Create Your Flask App

Create a Python file (e.g., app.py) and initialize Flask and SQLAlchemy:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# For SQLite database stored in the same directory
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

Step 3: Define Your Database Model

Create a model to define your database tables. Here is an example User model:

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}')"

Step 4: Create the Database

Run the following commands in a Python shell or a script to create the database and tables:

from app import db

db.create_all()

This generates the SQLite database file site.db with the User table.

Step 5: Use the Database in Flask

Here is how to add a new user to the database within a Flask route or script:

new_user = User(username='john_doe', email='[email protected]')
db.session.add(new_user)
db.session.commit()

Connecting to Other Databases

For MySQL or PostgreSQL, change the database URI accordingly. Example MySQL URI:

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://username:password@localhost/dbname'

Ensure you have the appropriate database driver installed, e.g., pip install pymysql for MySQL.

Troubleshooting

ModuleNotFoundError: Make sure all required packages are installed.
Database connection errors: Verify your database server is running and credentials are correct.
SQLAlchemy warnings: Disable SQLALCHEMY_TRACK_MODIFICATIONS or update your code as per the warning details.

Summary Checklist

Install Flask-SQLAlchemy
Configure your Flask app with the database URI
Define your models using SQLAlchemy
Create the database and tables via db.create_all()
Use session commands to add, update, and query data
Test database connectivity and handle errors

For more Flask development tips, check our related tutorial on How to Create Routes in Flask.

Connecting Flask to a database unlocks the power to build complex applications that manage real-time data efficiently and securely. With this knowledge, you can expand your Flask apps to handle user accounts, product listings, content management, and much more.