Example of how flask_sqlalchemy operates the database in python-Python Tutorial-php.cn

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

Copy after login

Installation

pip install flask-sqlalchemy

Copy after login

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)

Copy after login

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

db.create_all()

Copy after login

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

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

Copy after login

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()

Copy after login

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 '' %(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 '' % (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()

Copy after login

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

Example of how flask_sqlalchemy operates the database in python

Query all data

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

Copy after login

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())

Copy after login

Example of how flask_sqlalchemy operates the database in python

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()

Copy after login

Example of how flask_sqlalchemy operates the database in python

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)

Copy after login

Example of how flask_sqlalchemy operates the database in python

Restrict the display of the queried information

print('限制查询数据的显示', end='\n') users = User.query.filter_by(role_id=1).limit(3).all() print(users)

Copy after login

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)

Copy after login

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)

Copy after login

Example of how flask_sqlalchemy operates the database in python

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)

Copy after login

Example of how flask_sqlalchemy operates the database in python

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!