CRUD Operations in Flask: Querying the Database

---

Introduction

This article demonstrates how to perform CRUD operations, query filtering, and joins in Flask using SQLAlchemy.

Step 1: Setting Up Flask and SQLAlchemy

First, create a Flask project and set up SQLAlchemy as your ORM. Install Flask and SQLAlchemy using pip:

            pip install flask flask_sqlalchemy
        

Next, configure your application and database:

            from flask import Flask
            from flask_sqlalchemy import SQLAlchemy

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

Step 2: Defining Models

Create two models: User and Post. The User model will represent users, and the Post model will represent blog posts.

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

                posts = db.relationship('Post', backref='author', lazy=True)

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

Run the following command to create the database:

            db.create_all()
        

Step 3: Performing CRUD Operations

Here are examples of creating, reading, updating, and deleting records in the database.

Creating Records

            user = User(name='John Doe', email='john@example.com')
            db.session.add(user)
            db.session.commit()

            post = Post(title='My First Post', content='This is the content.', user_id=user.id)
            db.session.add(post)
            db.session.commit()
        

Reading Records

Query all users:

            users = User.query.all()
            for user in users:
                print(user.name, user.email)
        

Query a specific user by ID:

            user = User.query.get(1)
            print(user.name, user.email)
        

Updating Records

            user = User.query.get(1)
            user.email = 'newemail@example.com'
            db.session.commit()
        

Deleting Records

            user = User.query.get(1)
            db.session.delete(user)
            db.session.commit()
        

Step 4: Query Filtering and Joins

Use filtering to narrow down queries:

            users = User.query.filter_by(name='John Doe').all()
            for user in users:
                print(user.name, user.email)
        

Use joins to fetch related data:

            posts = db.session.query(Post).join(User).filter(User.name == 'John Doe').all()
            for post in posts:
                print(post.title, post.author.name)
        

Conclusion

In this article, we demonstrated how to perform CRUD operations, query filtering, and joins in Flask using SQLAlchemy.

Next