[Python知识库]flask使用Flask-SQLAlchemy操作数据库及增删改查操作

models.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# 配置 sqlalchemy 数据库驱动
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+mysqlconnector://root:password@localhost:3306/database?charset=utf8'
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False  # 关闭对模型修改的监控
# 初始化
db = SQLAlchemy(app)


# 创建数据库模型
class User(db.Model):
    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    age = db.Column(db.Integer)

db.create_all()

user.py

from models import db, User

# 增加单个数据
user = User(name='ming', age=26)
db.session.add(user)
db.session.commit()

# 增加多个数据
user1 = User(name='a', age=1)
user2 = User(name='b', age=2)
user3 = User(name='c', age=3)
db.session.add_all([user1, user2, user3])
db.session.commit()

# 查询一个数据
data = User.query.first()
print('User [id:{0},name:{1}]'.format(data.age, data.name))

# 查询所有数据
users = User.query.all()
for user in users:
    print('User [id:{0},name:{1}]'.format(user.age, user.name))

# 查询数据量
num = User.query.count()

# 按条件查询
user = User.query.filter_by(age=18)
# 按条件过滤
user0 = db.session.query(User).filter(User.name == "tom").first()
# 自动以主键查询
user1 = User.query.get(1)
# 按照组
user2 = User.query.group_by('age')
# 排序
user3 = User.query.order_by(User.age)
# 倒序排序
user4 = User.query.order_by(User.age.desc())

# 修改User模型中主键为 2 的记录
data = User.query.get(2)
data.age = 26
db.session.commit()

# 根据过滤条件进行修改
user5 = db.session.query(User).filter(User.name == "tom").first()
user5.age = 26
db.session.commit()

# 删除了 User模型中主键为 2 的记录
data = User.query.get(2)
db.session.delete(data)
db.session.commit()

# 删除全部
users = User.query.all()
for i in users:
    db.session.delete(i)
db.session.commit()