**Course Title:** Mastering Flask Framework: Building Modern Web Applications **Section Title:** Working with Databases: SQLAlchemy **Topic:** Understanding relationships and querying with SQLAlchemy **Overview** In the previous topics, we have covered the basics of SQLAlchemy and how to integrate it with Flask. In this topic, we will dive deeper into understanding relationships and querying with SQLAlchemy. **Understanding Relationships in SQLAlchemy** In SQLAlchemy, relationships are used to connect tables and establish connections between them. There are several types of relationships: * **One-To-One**: A one-to-one relationship is established between two tables when one table contains a primary key that is also a foreign key referencing the primary key of another table. * **One-To-Many**: A one-to-many relationship is established between two tables when one table contains a primary key that is referenced by a foreign key in another table. This relationship implies that for every record in the first table, there are multiple records in the second table. * **Many-To-Many**: A many-to-many relationship is established between two tables when both tables have foreign keys referencing each other. Let's consider an example: We have two tables, **users** and **posts**. Each user can have multiple posts, but each post belongs to only one user. This is an example of a one-to-many relationship. ```sql CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL ); CREATE TABLE posts ( id SERIAL PRIMARY KEY, title VARCHAR(100) NOT NULL, content TEXT NOT NULL, user_id INTEGER NOT NULL, CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id) ); ``` To establish a relationship in SQLAlchemy, we use the `relationship` function provided by SQLAlchemy. ```python from sqlalchemy import create_engine, Column, Integer, String, ForeignKey from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base engine = create_engine('postgresql://user:password@host:port/dbname') Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) posts = relationship("Post", backref="user") class Post(Base): __tablename__ = 'posts' id = Column(Integer, primary_key=True) title = Column(String) content = Column(String) user_id = Column(Integer, ForeignKey('users.id')) Base.metadata.create_all(engine) ``` **Querying with SQLAlchemy** SQLAlchemy provides several ways to query data from the database. * **query()**: This function returns a Query object which can be used to fetch data from the database. * **filter()**: This function is used to apply filters to the query to narrow down the results. * **all()**: This function returns a list of all objects that match the query. * **first()**: This function returns the first object that matches the query. Let's consider an example: We want to retrieve all posts from the database. ```python from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker engine = create_engine('postgresql://user:password@host:port/dbname') Session = sessionmaker(bind=engine) session = Session() posts = session.query(Post).all() for post in posts: print(post.title) ``` **Eager Loading and Lazy Loading** In SQLAlchemy, there are two ways to fetch data from related tables: **Eager Loading** and **Lazy Loading**. * **Eager Loading**: This loads all related data in a single query. * **Lazy Loading**: This loads the primary object and related data are loaded on demand. Consider the following example to illustrate the difference: We want to retrieve all users along with their posts. ```python # Eager Loading session.query(User).options(joinedload(User.posts)).all() # Lazy Loading session.query(User).all() ``` **Practical Takeaways** In this topic, we have covered understanding relationships and querying with SQLAlchemy. Here are some key takeaways: * Understand the different types of relationships and how to establish them in SQLAlchemy. * Learn how to query data from the database using SQLAlchemy's query() function. * Understand the difference between eager loading and lazy loading and how to use them. **What's Next?** In the next topic, we will cover handling sessions and database transactions with SQLAlchemy. This topic is critical to ensuring data consistency and integrity in your web application. **Leave a Comment/Ask for Help** If you have any questions or need help with implementing relationships and querying in your Flask application, leave a comment below. **External Resources** * [SQLAlchemy Documentation](https://www.sqlalchemy.org/library.html) * [Flask-SQLAlchemy Documentation](https://flask-sqlalchemy.palletsprojects.com/en/2.x/) By following this topic, you should now have a solid understanding of how to work with relationships and querying in SQLAlchemy. Practice the concepts learned here by implementing them in your Flask application.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    content TEXT NOT NULL,
    user_id INTEGER NOT NULL,
    CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id)
);

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('postgresql://user:password@host:port/dbname')

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    posts = relationship("Post", backref="user")

class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    title = Column(String)
    content = Column(String)
    user_id = Column(Integer, ForeignKey('users.id'))

Base.metadata.create_all(engine)

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('postgresql://user:password@host:port/dbname')

Session = sessionmaker(bind=engine)
session = Session()

posts = session.query(Post).all()
for post in posts:
    print(post.title)

# Eager Loading
session.query(User).options(joinedload(User.posts)).all()

# Lazy Loading
session.query(User).all()