Establishing One-to-Many Relationships in Flask-SQLAlchemy

In Flask-SQLAlchemy, creating one-to-many relationships between tables involves using SQLAlchemy’s ORM (Object-Relational Mapping) system to define classes that represent database tables and their relationships. In this tutorial, we will cover how to set up a one-to-many relationship between two tables using Flask-SQLAlchemy.

First, ensure that you have Flask and Flask-SQLAlchemy installed in your project. You can install them using pip:

pip install Flask
pip install Flask-SQLAlchemy

Now, let’s create a simple Flask application with Flask-SQLAlchemy setup. Create a new Python file (app.py) and add the following code:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

class Author(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    author_id = db.Column(db.Integer, db.ForeignKey('author.id'))
    author = db.relationship('Author', backref='books')

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

In this code, we have defined two models: Author and Book. The Author model represents authors, while the Book model represents books. The Book model has a foreign key column author_id that references the id column of the Author model.

The db.relationship function in the Book model defines the one-to-many relationship between authors and books. The backref='books' parameter specifies that each author object will have a books attribute that contains a list of books written by that author.

Now, let’s create the database tables using Flask-Migrate. Install Flask-Migrate with the following command:

pip install Flask-Migrate

Next, create a new Python file (manage.py) and add the following code:

from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from app import app, db

migrate = Migrate(app, db)
manager = Manager(app)

manager.add_command('db', MigrateCommand)

if __name__ == '__main__':
    manager.run()

Now, run the following commands to set up the database migrations and create the initial migration:

python manage.py db init
python manage.py db migrate
python manage.py db upgrade

This will create a migrations folder in your project directory and create the necessary migration files based on your models.

Finally, let’s add some sample data to the database. Update app.py with the following code:

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

    author1 = Author(name='John Doe')
    author2 = Author(name='Jane Doe')

    book1 = Book(title='Flask 101', author=author1)
    book2 = Book(title='SQLAlchemy Guide', author=author1)
    book3 = Book(title='Python Basics', author=author2)

    db.session.add(author1)
    db.session.add(author2)
    db.session.add(book1)
    db.session.add(book2)
    db.session.add(book3)
    db.session.commit()

Run the Flask application using python app.py command. This will create the database tables and add sample data to the database.

You can now access the data in the database and view the one-to-many relationship between authors and books. Use SQLAlchemy queries to retrieve data from the database:

from app import Author, Book

# get all authors
authors = Author.query.all()
for author in authors:
    print(author.name)
    for book in author.books:
        print(book.title)

This will print out the names of authors and titles of books written by each author, demonstrating the one-to-many relationship between authors and books.

Congratulations! You have created a one-to-many relationship between tables in Flask-SQLAlchemy. You can now build more complex relationships and queries using SQLAlchemy ORM.