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.




Advertisement