Introduction to SQLAlchemy in Flask

---

Flask-SQLAlchemy is an ORM (Object Relational Mapper) for managing database interactions in Flask applications. This tutorial covers installing Flask-SQLAlchemy, setting up a database, and defining models and relationships with real examples.

Step 1: Installing Flask-SQLAlchemy

Install Flask-SQLAlchemy using pip:

pip install flask flask-sqlalchemy

Once installed, you can use Flask-SQLAlchemy to interact with your database.

Step 2: Setting Up the Database

Create a new Python file, for example, app.py, and import the required modules:

    from flask import Flask
    from flask_sqlalchemy import SQLAlchemy

    app = Flask(__name__)

    # Configure the database URI
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

    db = SQLAlchemy(app)
        

Here, we are using SQLite as the database. The URI sqlite:///example.db specifies the SQLite database file.

Step 3: Defining Models

Models represent tables in the database. Create a model by subclassing db.Model:

    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)

        def __repr__(self):
            return f''
        

Each attribute in the class corresponds to a column in the database table. For example, the id attribute maps to a primary key column, and username maps to a unique string column.

Step 4: Defining Relationships

Flask-SQLAlchemy supports relationships between models. For example, we can create a one-to-many relationship between a User and their Posts:

    class Post(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        title = db.Column(db.String(120), nullable=False)
        content = db.Column(db.Text, nullable=False)
        user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

        user = db.relationship('User', backref=db.backref('posts', lazy=True))

        def __repr__(self):
            return f''
        

Here, each post is associated with a user through the user_id foreign key. The user attribute defines the relationship.

Step 5: Creating the Database

Initialize the database and create the tables by running the following commands in a Python shell:

    >>> from app import db
    >>> db.create_all()
        

This will create the User and Post tables in the database.

Step 6: Adding and Querying Data

Add some sample data to the database:

    >>> from app import User, Post, db
    >>> user = User(username='john', email='john@example.com')
    >>> db.session.add(user)
    >>> db.session.commit()

    >>> post = Post(title='First Post', content='Hello, world!', user_id=user.id)
    >>> db.session.add(post)
    >>> db.session.commit()
        

Query the data:

    >>> User.query.all()
    []

    >>> Post.query.filter_by(user_id=user.id).all()
    []
        

Complete Example

Here is the complete app.py file:

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

        def __repr__(self):
            return f''

    class Post(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        title = db.Column(db.String(120), nullable=False)
        content = db.Column(db.Text, nullable=False)
        user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

        user = db.relationship('User', backref=db.backref('posts', lazy=True))

        def __repr__(self):
            return f''

    if __name__ == '__main__':
        app.run(debug=True)
        

Conclusion

In this tutorial, we introduced Flask-SQLAlchemy, installed it, set up a database, and defined models and relationships. Flask-SQLAlchemy simplifies database operations and is a powerful tool for Flask applications.