1. Introduction

In this tutorial, we'll learn about basic database operations using Flask and SQLAlchemy. We'll cover how to create, read, update, and delete (CRUD) records in the database.

By the end of this tutorial, you will:

• Understand how to perform CRUD operations in a database using Flask and SQLAlchemy.
• Be able to implement these operations in your own web applications.

Prerequisites

This tutorial assumes you have some knowledge of Python and basic familiarity with Flask. You should also have Flask and SQLAlchemy installed in your development environment.

2. Step-by-Step Guide

In order to interact with a database using Flask and SQLAlchemy, we first need to define our database model, then perform operations on it.

Defining the Database Model

We create a database model by defining a class that inherits from db.Model. The class variables represent the fields of our database table.

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

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

Creating Records

To create a new record, we instantiate our model class and add it to our database session. Then, we commit the session to save the changes.

new_user = User(username='test', email='test@email.com', password='password')
db.session.add(new_user)
db.session.commit()

Reading Records

Reading records is done using the query attribute of our model class.

users = User.query.all()  # returns a list of all User records
user = User.query.filter_by(username='test').first()  # returns the first User with username 'test'

Updating Records

To update a record, we first need to fetch it. Then, we change its attributes and commit the session.

user = User.query.filter_by(username='test').first()
user.username = 'new_username'
db.session.commit()

Deleting Records

Deleting a record is similar to updating, but instead of changing the record's attributes, we call the delete method on it.

user = User.query.filter_by(username='new_username').first()
db.session.delete(user)
db.session.commit()

3. Code Examples

Now that we've covered the basics, let's look at some practical examples.

Creating a New User

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

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

@app.route('/create')
def create_user():
    new_user = User(username='test', email='test@email.com', password='password')
    db.session.add(new_user)
    db.session.commit()
    return f"User created with id: {new_user.id}"

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

In the above code snippet, we first configure our Flask application and SQLAlchemy. Then, we define our User model and a route that creates a new user.

Reading User Records

@app.route('/read')
def read_users():
    users = User.query.all()
    return ', '.join([u.username for u in users])

In this route, we fetch all user records and return a string of their usernames.

4. Summary

In this tutorial, we learned about performing CRUD operations in a database using Flask and SQLAlchemy. We've covered how to define a database model and how to create, read, update, and delete records.

For further learning, you can explore how to implement validation, handle errors, and perform complex queries using SQLAlchemy.

5. Practice Exercises

Here are some exercises to help you practice:

1. Create a Flask route that updates a user's email based on their username.
2. Create a Flask route that deletes a user based on their id.
3. Implement a route that takes a username and password and returns the user data if the username and password match a record in the database.

Remember, practicing is crucial to solidify your understanding. Happy coding!