[Practical Flask API Project Guide] Part 6 Database Integration SQLAlchemy

Practical Flask API Project Guide – Database Integration

This series of articles will take you to explore in depth Practical Flask API Project Guide. By following Xiaocai’s learning journey, you will gradually master the application of Flask in actual projects. Let’s embark on this exciting learning journey together!

Foreword

In the previous article, we implemented the backend of a system API in a library. Xiaocai felt that the shortcoming was that it used a Python list to store book information, which was a Local version of the library management system back-end API. If you restart the program, the book data will be lost. So in this section, we will use a database to help Xiaocai solve this pain point and achieve persistent data storage.

When Xiaocai steps into the world of Flask back-end development, the database is the key to storing and managing data.

Flask does not have built-in database functions, but it provides an extension mechanism to easily integrate third-party database libraries. This article will introduce how to integrate SQLAlchemy, a popular Python ORM library, in a Flask project. We will rewrite the previous lesson to let readers understand how to integrate a database in a Flask application.

Note: This article goes directly to the code and is full of useful information.

SQLAlchemy

1. Install dependencies

In Flask, you can use various databases, such as SQLite, MySQL, PostgreSQL, etc. First, you need to install the required database driver library, such as flask-sqlalchemy for integrating SQLAlchemy.

When using SQLAlchemy for database operations, most operations are similar regardless of which database type is used. (This article uses MYSQL)

First we need to install the corresponding dependent libraries and use the following command.

pip install flask-sqlalchemy flask-mysqldb

2. Configure database

Configure database connection information in the Flask application. In the application’s configuration, add the connection string for the database.

• Make sure to replace username, password, localhost and flask with your own MySQL database username, password, host and database name.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# mysql example
# app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:password@localhost/database'
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:123456@localhost/flask'


db = SQLAlchemy(app)

3. Define data model

Using SQLAlchemy, you can define data models as Python classes. Each class corresponds to a table, and the attributes of the class correspond to the columns in the table. A data model is an abstract representation of a table in a database, which defines the structure and fields of the table.

In the following code, a data model named Book is defined, which has three columns

• The book_id field is used as the primary key (primary key). It is unique and is not allowed to be empty.

• The title field represents the title of the book, string type, the maximum length is 100 characters, and is not allowed to be empty.

• The author field represents the author of the book, string type, the maximum length is 50 characters, and is not allowed to be empty.

• Because in our case, the data table only needs these three columns.

class Book(db.Model):
    book_id = db.Column(db.Integer, primary_key=True, unique=True, nullable=False)
    title = db.Column(db.String(100), nullable=False)
    author = db.Column(db.String(50), nullable=False)

Attached are the commonly used column setting options in SQLAlchemy:

Options	Description
primary_key=True	Mark the column as the primary key, which is used to uniquely identify each row of data.
nullable=False	The specified column does not allow null values.
unique=True	Ensures that the values in the column are unique and no duplicate values are allowed.
default=	Set a default value for the column. If no value is provided when inserting data, the default is used. value.
index=True	Create an index for the column to improve retrieval performance.
autoincrement=True	Automatically generate incrementing values (usually used with primary keys).
onupdate=	Set the column value to the specified value when updating the row.
server_default=	Set the server default value of the column, usually implemented at the database level.

4. Common database operations

When using SQLAlchemy, there are many common database operation methods used to perform CRUD (create, read, update, delete) operations. Here are some examples of commonly used SQLAlchemy how-tos:

Please note that these examples assume that you have configured SQLAlchemy and the database connection correctly.

1. Create:

   # Create a new object and add it to the database
   new_book = Book(title="Sample Book", author="John Doe")
   db.session.add(new_book)
   db.session.commit()
   

2. Read data (Read):

   # Query all books
   books = Book.query.all()
   
   # Query books based on conditions
   specific_book = Book.query.filter_by(title="Sample Book").first()
   

3. Update:

   # Query the object to be updated
   book_to_update = Book.query.filter_by(title="Sample Book").first()
   
   #Update object properties
   book_to_update.author = "New Author"
   db.session.commit()
   

4. Delete data (Delete):

   # Query the objects to be deleted
   book_to_delete = Book.query.filter_by(title="Sample Book").first()
   
   # Delete objects from database
   db.session.delete(book_to_delete)
   db.session.commit()
   

5. Filter and Sort:

   # Query all books whose author is "John Doe"
   johns_books = Book.query.filter_by(author="John Doe").all()
   
   # Query the first 5 books and sort them in ascending order by title
   top_books = Book.query.order_by(Book.title).limit(5).all()
   

6. Aggregate and Count:

   # Calculate the total number of books
   book_count = Book.query.count()
   
   # Count the number of books by different authors
   author_book_count = db.session.query(Book.author, db.func.count(Book.book_id)).group_by(Book.author).all()
   

Commonly used operations and their descriptions in SQLAlchemy:

Operation	Description
Define data model	Use db.Model to define the data model and define fields and their properties.
Create data table	Use db.create_all() to create a data table corresponding to the defined data model.
Query data	Use db.session.query() to create a query object and add query conditions.
Insert data	Use db.session.add() to add new data objects and commit the changes.
Update data	Get the data object, modify the properties and use db.session.commit() to submit the changes.
Delete data	Use db.session.delete() to add the data object to be deleted and commit the changes.
Filter conditions	Use filter, filter_by and other methods to add filter conditions in the query.
Sort	Use the order_by method to specify the sorting method of query results.
Limit the number	Use limit and offset to limit the number and offset of query results .
Aggregation and statistics	Use the func function for aggregation and statistical operations, such as func.count().
Association table query	Use relationship to define the association relationship, and use join to perform association query.
Transaction management	Use db.session.begin() to start a transaction and use commit Commit changes, or rollback rollback changes.
Batch operations	Use db.session.bulk_insert_mappings() for batch insertion, use db.session. bulk_update_mappings()Perform bulk updates.
Join query	Use join for multi-table connection query, use select_from, outerjoin and other methods perform different types of connections.
Original SQL query	Use db.session.execute() to execute the original SQL query.

5. Create data table

At the end of app.py, add the following code to create the data table:

In Flask-SQLAlchemy, you can use db.create_all() to create data tables corresponding to all defined data models. At the end of app.py, add the following code:

db.create_all()

But sometimes a RuntimeError exception will be thrown,

The prompt says to work outside the application context, so add with app.app_context() in front, as shown below:

# Create data table
with app.app_context():
    db.create_all() # or other operations that require application context

6. Library management system for persistent data storage

Based on the previous lesson Local version of the library management system, we will use SQLAlchemy to rewrite it into a persistent data storage library management system.

Upload code

# -*- coding: utf-8 -*-

from flask_sqlalchemy import SQLAlchemy
from flask import (Flask, jsonify, request)

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:123456@localhost/flask' # Replace with your database URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)


#Define Book model class
class Book(db.Model):
    book_id = db.Column(db.Integer, primary_key=True, unique=True, nullable=False)
    title = db.Column(db.String(100), nullable=False)
    author = db.Column(db.String(50), nullable=False)


# Get all books
@app.route("/books", methods=["GET"])
def get_all_books():
    books = Book.query.all()
    book_list = [{<!-- -->"id": book.book_id, "title": book.title, "author": book.author} for book in books]
    return jsonify(book_list), 200


# Get specific books
@app.route("/books/<int:book_id>", methods=["GET"])
def get_book(book_id):
    book = Book.query.get(book_id)
    if book:
        return jsonify({<!-- -->"id": book.book_id, "title": book.title, "author": book.author}), 200
    return jsonify({<!-- -->"error": "Book not found."}), 404


#Create new book
@app.route("/books", methods=["POST"])
def create_book():
    data = request.json
    new_book = Book(title=data["title"], author=data["author"])
    db.session.add(new_book)
    db.session.commit()
    return jsonify({<!-- -->"id": new_book.book_id, "title": new_book.title, "author": new_book.author}), 201


# Update book information
@app.route("/books/<int:book_id>", methods=["PUT"])
def update_book(book_id):
    book = Book.query.get(book_id)
    if book:
        data = request.json
        book.title = data["title"]
        book.author = data["author"]
        db.session.commit()
        return jsonify({<!-- -->"id": book.book_id, "title": book.title, "author": book.author}), 200
    return jsonify({<!-- -->"error": "Book not found."}), 404


# Delete books
@app.route("/books/<int:book_id>", methods=["DELETE"])
def delete_book(book_id):
    book = Book.query.get(book_id)
    if book:
        db.session.delete(book)
        db.session.commit()
        return "", 204
    return jsonify({<!-- -->"error": "Book not found."}), 404


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

Now, Xiaocai can use GET, POST, PUT, and DELETE requests to access API endpoints and operate on book data. This example demonstrates how to integrate a database, define a data model, perform database operations, and use API endpoints to manipulate data in a Flask application. This will help Xiaocai better understand the database integration in Flask.

Summary

This article delves into the key steps of integrating a database into a Flask application, by introducing SQLAlchemy, the popular Python ORM library. Persistent storage of data. The article first introduces the process of installing dependencies and configuring the database, and then explains in detail how to define the data model and common database operation methods. Emphasis is placed on how to use the Flask-SQLAlchemy extension to simplify the process of database interaction.

Through the above steps, Xiaocai has successfully integrated the MySQL database into the Flask application, and has implemented operations such as adding, deleting, modifying, and checking books. Xiaocai gained the following knowledge:

• How to configure the Flask application to connect to the database.
• How to use SQLAlchemy to define data models and table structures.
• How to perform common database operations, including creating, reading, updating, and deleting data.
• How to use the Flask-SQLAlchemy extension to simplify database interaction.

Through the study of this article, Xiaocai has understood the database integration and operation in Flask, which laid a solid foundation for Xiaocai to implement the back-end API platform later!