How to Create Table in Flask-SQLAlchemy: Simple Guide

In Flask-SQLAlchemy, you create a table by defining a model class that inherits from db.Model with attributes as columns using db.Column. Then, call db.create_all() to create the table in the database.

Syntax

To create a table, define a Python class that inherits from db.Model. Each attribute in the class represents a column, defined with db.Column specifying the data type and constraints.

• db.Model: Base class for models.
• db.Column: Defines a column in the table.
• db.Integer, db.String: Common data types.
• primary_key=True: Marks the column as the primary key.

python

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

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

Example

This example shows a complete Flask app that defines a User table and creates it in the database.

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)
    email = db.Column(db.String(120), unique=True, nullable=False)

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

print('Tables created successfully')

Output

Tables created successfully

Common Pitfalls

Common mistakes when creating tables in Flask-SQLAlchemy include:

• Not calling db.create_all() inside the Flask app context.
• Forgetting to set primary_key=True on at least one column.
• Using incorrect data types or missing nullable constraints.
• Not configuring the database URI properly.

python

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

# Wrong: Missing primary key
class Product(db.Model):
    name = db.Column(db.String(50))

# Right: Add primary key
class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)

Quick Reference

Step	Description
Define model class	Create a class inheriting from db.Model
Add columns	Use db.Column with data types and constraints
Configure app	Set SQLALCHEMY_DATABASE_URI in Flask config
Create tables	Call db.create_all() inside app context

Key Takeaways

Define tables by creating model classes inheriting from db.Model with columns as attributes.

Always set a primary key column using primary_key=True.

Call db.create_all() inside the Flask app context to create tables in the database.

Configure the database URI correctly in your Flask app before creating tables.

Use appropriate data types and constraints like unique and nullable for columns.