Detailed explanation of ORM framework Flask-SQLAlchemy in Python

Flask-SQLAlchemy is an ORM (Object Relational Mapping) framework based on SQLAlchemy, suitable for Flask applications. The ORM framework shields the underlying database operations and provides a Python programming interface, allowing developers to directly use Python code to operate the database without writing SQL statements.

This article will introduce the use of the Flask-SQLAlchemy framework in detail, including how to connect to the database, create tables, insert, update, delete data and query data.

Connecting to the database

Before using Flask-SQLAlchemy, you first need to configure the database connection in Flask. The specific method is as follows:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = '数据库连接信息'  # 数据库连接信息
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False  # 是否追踪对象的修改
db = SQLAlchemy(app)

Among them, SQLALCHEMY_DATABASE_URI is the database connection information. If you are using SQLite, the format is sqlite:///file path; if it is MySQL, the format is mysql pymysql://username:password@hostname/databasename(pymysql is a Python library used to connect to MySQL); if it is PostgreSQL, the format is postgresql://username:password@hostname/database name.

SQLALCHEMY_TRACK_MODIFICATIONS indicates whether to track modifications to the object. If set to True, the state of the object is checked and written to the database at the end of each request. This feature can be used for debugging and performance optimization, but comes with some overhead. Therefore, it is recommended to turn it off in production environments.

Create table

Flask-SQLAlchemy supports defining tables through Python classes. Each class corresponds to a database table, and the attributes in the class correspond to the fields of the database table.

The following is an example:

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

    def __repr__(self):
        return '<User %r>' % self.username

Among them, db.Model is the base class of all model classes, indicating that this class is a database model. id, username, and email are the fields of this table, where id is the primary key, username and email are both unique and cannot be empty. The __repr__() method is used to print objects for easy debugging.

To create this table, just execute the following command:

from app import db
db.create_all()

This will create a table named User in the database. If it already exists, it will not be created again. If there are multiple tables that need to be created, they can be created using the db.create_all() command at the same place.

Insert data

To insert data into the database, you only need to create a class instance and call the add() and commit() methods.

from app import db
from app.models import User

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

In the above code, a User object is first created and then added to the database session. Finally, call the commit() method to commit the transaction and store the data in the database.

Update data

Updating data is similar to inserting data. You only need to query the data to be updated, modify its attributes and submit it.

from app import db
from app.models import User

user = User.query.filter_by(username='test').first()
user.email = 'new_test@test.com'
db.session.commit()

In the above code, the User object to be updated is first queried based on the user name, then the email attribute is modified and the modification is submitted.

Deleting data

is similar to updating data. You only need to query the data to be deleted first, and then call the delete() method to delete it.

from app import db
from app.models import User

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

In the above code, the User object to be deleted is first queried based on the user name, and then the delete() method is called to delete it.

Query data

Flask-SQLAlchemy supports a variety of query methods, including query by condition, sorting, paging, etc.

Query by condition

Query by condition is the most common way. You can use the query.filter() method to specify query conditions. The sample code is as follows:

from app import db
from app.models import User

# 查询所有
users = User.query.all()

# 条件查询
users = User.query.filter_by(username='test').all()

In the above code, the first line of code queries all the data in the User table; the second line of code queries the data according to the username='test' condition.

Sort query

You can use the order_by() method to sort the query results. The sample code is as follows:

from app import db
from app.models import User

# 按名称排序（升序）
users = User.query.order_by(User.username).all()

# 按名称排序（降序）
users = User.query.order_by(User.username.desc()).all()

In the above code, the first line of code sorts the query results in ascending order of username; the second line of code sorts the query results in descending order of username.

Paging query

You can use the paginate() method to implement paging query. The sample code is as follows:

from app import db
from app.models import User

# 每页2条，查询第1页
users = User.query.paginate(1, 2, False)

# 查询第一页
users = User.query.order_by(User.username).paginate(1, 10, False)

In the above code, the first line of code queries 2 pieces of data on page 1; the second line of code queries 10 pieces of data on page 1 sorted by username.

Summary

Flask-SQLAlchemy is a powerful ORM framework that provides a powerful interface for querying and operating databases, allowing developers to quickly and easily operate the database. This article introduces the use of Flask-SQLAlchemy, including creating tables, inserting, updating, deleting and querying data. I hope this article can help readers better understand and apply Flask-SQLAlchemy.

The above is the detailed content of Detailed explanation of ORM framework Flask-SQLAlchemy in Python. For more information, please follow other related articles on the PHP Chinese website!