Example of how flask_sqlalchemy operates the database in python

This article brings you an example of how flask_sqlalchemy operates the database in Python. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

flask_sqlalchemy

Uses the Object-Relational Mapper (ORM) framework, which abstracts low-level database operation instructions into high-level object-oriented operations. In other words, if we use the database engine directly, we have to write SQL operation statements, but if we use the ORM framework, we can simplify the operation of database entities such as tables and documents into Python object operations

SQLAlchemy has become the standard for ORM in the Python world. Flask is a lightweight web framework that can be freely used with ORM. Flask-sqlalchemy is a plug-in specifically designed for Flask.

In Flask-SQLAlchemy, the database is specified using a URL.

MySQL -->  mysql://username:password@hostname/database

Installation

 pip install flask-sqlalchemy

Database operation

##1. How to create a database operation connection

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy(app)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:sheen@localhost/zaj_sql'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

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

To create the initial database, call the SQLAlchemy.create_all() method to create the tables and database:

   db.create_all()

Your database has been generated. Now let’s create some users

admin = User('admin', 'admin@example.com')
guest = User('guest', 'guest@example.com')

But they haven’t actually been written to the database yet, so let’s make sure they have been written to the database

db.session.add(admin)
db.session.add(guest)
db.session.commit()

2. Create relationships Type database table

SQLAlchemy is connected to a relational database. The best thing about relational data is relationships. Therefore, we will create an application that uses two related tables as an example.

The most common relationship is the one-to-many relationship. Because relationships are declared before they are created, you can use strings to refer to classes that have not yet been created
Relationships are represented using the relationship() function. However, foreign keys must be declared separately using the class sqlalchemy.schema.ForeignKey.

from datetime import datetime
from flask_bootstrap import Bootstrap
from flask_wtf import FlaskForm
from flask_sqlalchemy import SQLAlchemy
from flask import Flask
import pymysql
from sqlalchemy import desc

app = Flask(__name__)
db = SQLAlchemy(app)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:sheen@localhost/zaj_sql'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
bootstrap = Bootstrap(app)

class User(db.Model):
    id = db.Column(db.Integer,autoincrement=True,primary_key=True)
    name = db.Column(db.String(50),unique=True)
    passwd = db.Column(db.String(100))
    add_time = db.Column(db.DATETIME,default=datetime.now())
    gender = db.Column(db.BOOLEAN,default=True)
    role_id = db.Column(db.INTEGER,db.ForeignKey('role.id'))

    def __repr__(self):
        return '<user:>' %(self.name)

class Role(db.Model):
    id = db.Column(db.INTEGER,autoincrement=True,primary_key=True)
    name = db.Column(db.String(50),unique=True)
    users = db.relationship('User',backref='role')
    # 给Role模型添加users属性
    # backref 是定义反向引用
        def __repr__(self):
        return '<role:>' % (self.name)
if __name__ =='__main__':
    # 1. 创建数据库表
    # db.drop_all()
    # db.create_all()
    # # 2. 创建role数据库表数据
    role_1 = Role(name='超级会员')
    role_2 = Role(name='普通会员')

    db.session.add(role_1)
    db.session.add(role_2)

    db.session.commit()

    # # # 3. 添加user表内数据，100个用户，50个为超级会员，50个为普通会员
    for i in range(1,13):
        if i%2 == 0:
            u = User(name='sheen'+str(i),passwd='sheen',role_id=1)
            db.session.add(u)
        else:
            u = User(name='star'+str(i),passwd='star',role_id=2)
            db.session.add(u)
    db.session.commit()</role:></user:>

backref is a backref that defines Role objects and properties through User.role.

Query all data

    print('角色',Role.query.all())
    print('用户',User.query.all())

Query data based on conditions

    # select * from tablename where xxx=xxxxx
    print(User.query.filter_by(role_id=1).all())
    print(Role.query.filter_by().all())
    print(User.query.filter_by(role_id=2).all())

Update the found data

 print('进行数据更新',end='\n')
    u =User.query.filter_by(name='sheen2').first()
    print(u)
    u.passwd = '123'
    db.session.add(u)
    db.session.commit()

Filter data method 2 (filter), this method can see the native sql statement

    print('数据筛选', end='\n')
    user = User.query.filter(User.role_id==1)
    print(user)

Restrict the display of the queried information

    print('限制查询数据的显示', end='\n')

    users = User.query.filter_by(role_id=1).limit(3).all()
    print(users)

Sort the queried information and output it (by default, sort from small to large), if you want to sort from large to small: desc(User. add_time)

    print('数据再处理', end='\n')
    users = User.query.filter_by(role_id=1).order_by(desc(User.name)).all()
    print(users)

Multiple filter functions

print('多个过滤函数', end='\n')
    users = User.query.filter_by(role_id=1).order_by(desc(User.name)).limit(3).offset(1).all()
    print(users)
    users = User.query.filter_by(role_id=1).order_by(desc(User.name)).slice(1,4).all()
    print(users)

Paging, the first parameter indicates which page of data is displayed, and the second parameter indicates the display of each page How many pieces of data

    print('分页显示', end='\n')

    users = User.query.paginate(1,5)
    print(users.items)
    users = User.query.paginate(2, 5)
    print(users.items)

The above is the detailed content of Example of how flask_sqlalchemy operates the database in python. For more information, please follow other related articles on the PHP Chinese website!