十一、数据库-增删改查操作（CRUD操作）

使用ORM进行CRUD（create、read、uodate、delete）操作，需要先把操作添加到会话中，通过db.session可以获取到会话对象，会话对象存在内存中，如果要把会话中的对操作提取到数据库中，需要调用db.session.commit()操作，如果要把会话中的操作回滚，则需要调用db.session.rollback()实现，下面分别对CRUD操作进行讲解

1、Create操作

先使用ORM模型创建一个对象，然后添加到会话中，再进行commit操作即可

@app.route('/user/add')
def user_add():
    user1 = User(username='张三',password='123456')
    user2 = User(username='李四',password="654321")
    db.session.add(user1)
    db.session.add(user2)
    db.session.commit()
    return "用户添加成功！"

以上代码，首先用User创建了2个user对象,创建对象时，必须通过关键字参数给字段赋值，否则SQLAlchemy将不知道是给哪个字段赋值，然后把2个对象添加到session中，最后再进行commit操作，即可把数据添加到数据库中，此时使用navicat到user表中查看，有新添加的两条用户

2、Read操作

read就是查询操作，ORM模型都是继承自db.Model，db.Model内置的query属性上有许多方法，可以实现对ORM模型的查询操作，query上的方法可以分为两大类，分别是提取方法和过滤方法，首先来看提取方法

1. 提取方法

@app.route("/user/read")
def user_read():
    #1、获取User中所有数据
    users= User.query.all()
    for i in users: #打印id 和 username
        print(i.id)
        print(i.username)

    #获取主键为1的User对象
    user1 = User.query.get(1)
    print(user1.username)
    #获取第一条数据
    user2 = User.query.first()
    print(user2.username)

    return "数据获取成功！"

以上代码，通过all()方法获取所有User对象，通过get方法获取指定主键的User对象，通过first()方法获取第一个User对象，提取数据的常用方法如下表

方法名	描述
query.all()	获取查询结果集中的所有对象，是列表类型
query.first()	获取查询结果集中的第一个对象
query.one()	获取查询结果集中的一个对象，如果结果集中对对象数量不等于1，则会抛出异常
query.one_or_none()	与query.one()方法类似，结果集中对象数量不等于1时，不是抛出异常，而是返回None
query.get(pk)	根据主键获取当前ORM模型的第一条数据
query.exists()	判断数据是否存在
query.count()	获取查询结果集的个数

2. 过滤方法-filter

@app.route("/user/filter")
def user_filiter():
    #1、filter方法
    users = User.query.filter(User.username=="张三").all()
    for i in users:
        print(i.id,i.username)
    #2、filter_by方法
    users1 = User.query.filter_by(username='张三').all()
    for j in users1:
        print(j.id,j.username)
    return "查询成功！"

3. 模糊查询-like

@app.route("/user/like")
def user_filter_like():
    # 3、模糊查询
    user2 = User.query.filter(User.username.contains("张"))
    for i in user2:
        print(i.id,i.username)
    user3= User.query.filter(User.username.like("%三%"))
    for i in user3:
        print(i.id,i.username)
    user4 = User.query.filter(User.username.like("_四"))
    for i in user4:
        print(i.id,i.username)
    return "查询成功！"

4.判断值是否在指定数据集中-in

in:判断值是否在数据集中，如果是就提取，否则就不提取，为了不与Python中in关键字混淆，在in后加下划线，实际的方法名为in_,示例代码如下

@app.route("/user/in")
def user_in():
    users = User.query.filter(User.username.in_(["张三","李四",'王五','韩流']))
    for i in users:
        print(i.id,i.username)
    return "查询成功"

5. 判断值不在数据集中-not in

not in:作用于in相反，其使用方式是在in_方法所在代码表达式前添加~，示例代码如下

@app.route("/user/notin")
def user_notin():
    users = User.query.filter(~User.username.in_(["张三","韩流"]))
    for i in users:
        print(i.id,i.username)
    return "查询成功！"

6.判断是否为空：is null

null:判断是否为空，如果为空就提取，否则就不提取，可以通过判断是否为none，或通过is_方法实现，为了不与Python中的in关键字混淆，同样需要再is后加下划线，示例代码如下

@app.route("/user/null")
def user_null():
    #方式一
    users = User.query.filter(User.username==None)
    for i in users:
        print(i.id,i.username)
    #方式二
    users1 = User.query.filter(User.username.is_(None))
    for i in users1:
        print(i.id,i.username)
    return "查询成功！"

7.判断不为空：is not null 作用于is null相反，实际的方法名为isnot,示例代码如下

@app.route("/user/notnull")
def user_not_null():
    users = User.query.filter(User.username != None)
    for i in users:
        print(i.id,i.username)
    users1 = User.query.filter(User.username.isnot(None))
    for i in users1:
        print(i.id,i.username)
    return "查询成功！"

8.同时满足多条件的查询：and

用于同时满足多条件的查询，实际的方法名为and_，示例代码如下

@app.route("/user/and")
def user_and():
    from sqlalchemy import and_
    users = User.query.filter(and_(User.username=='张三',User.id <10))
    for i in users:
        print(i.id,i.username)
    return "查询成功"

9.满足一个或多个条件的查询：or

用于满足一个或多个条件的查询，实际的方法名为or_,示例代码如下

@app.route("/user/or")
def user_or():
    from sqlalchemy import or_
    users = User.query.filter(or_(User.username=='张三',User.id == 3))
    for i in users:
        print(i.id,i.username)
    return "查询成功！"

10.排序方法

@app.route("/user/orderby")
def user_orderby():
    #正序排列
    user1 = User.query.order_by("id")
    for i in user1:
        print(i.id,i.username)
    user2 = User.query.order_by(User.id)
    for i in user2:
        print(i.id,i.username)

    #倒序排列
    user3 = User.query.order_by(db.text("-id"))
    for i in user3:
        print(i.id,i.username)
    user4 = User.query.order_by(User.id.desc())
    for i in user4:
        print(i.id,i.username)
    return "查询成功！"

11.分组方法

query.group_by（）方法是根据某个字段进行分组，分组的主要目的是获取分组后的数量、最大值、最小值、平均值、总和等，因为提取的数据不再是某个模型，所以不能通过<模型>.query的方式获取，而是通过db.session.query来提取，如要获取所有用户名所在表中存在的个数，可以通过如下代码

@app.route("/user/groupby")
def user_groupby():
    from sqlalchemy import func
    users = db.session.query(User.username,func.count(User.id)).group_by("username").all()
    for i in users:
        print(i)
    return "分组过滤成功"

常用的查询过滤方法：

方法名	描述
query.filter()	根据查询条件过滤
query.filter_by()	根据关键字参数过滤
query.slice()	对结果进行切片操作
query.limit()	对结果数量进行限制
query.offset()	在查询时跳过前面offset条数据
query.order_by()	根据给定字段进行排序
query.group_by()	根据给定字段进行分组

3、update操作

更新操作数据分为两种，第一种针对一条数据，第二种针对多条数据，针对一条数据可以直接修改对象的属性，然后执行commit操作,执行完如下代码，去数据库表中查看，数据更新为修改后的，代码如下

@app.route("/user/update")
def user_update():
    #1、一条数据进行更新
    user = User.query.get(1)
    user.username = "张三——重新修改"
    db.session.commit()
    return "更新数据成功"

更新多条数据的操作方式，通过调用filter或者filter_by方法获取BaseQuery对象，然后再调用update方法，实现批量修改的，示例代码如下：

@app.route("/user/up")
def user_update_more():
    User.query.filter(User.username.like("%张三%")).update({"password":"000000"},synchronize_session=False)
    db.session.commit()
    return "更新成功"

4、delete操作

删除操作也分为两种，一种是删除一条数据，第二种是删除多条数据，删除单条数据的操作方式是直接调用db.session.delete方法即可

@app.route("/user/delete")
def user_del():
    user = User.query.get(1)
    db.session.delete(user)
    db.session.commit()
    return "删除成功"

删除多条数据的操作方式类似更新多条数据，通过BaseQuery的delete方法即可实现，代码如下

@app.route("/user/del")
def user_del_mor():
    User.query.filter(User.username.contains("张三")).delete(synchronize_session=False)
    db.session.commit()
    return "批量删除成功！"

整体代码如下：

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text
app = Flask(__name__)



#然后还需要再app.config中设置SQLALCHMEY_DATABASE_URI,来配置数据库的连接
#mysql所在的主机名
HOSTNAME = "127.0.0.1"
#MYSQL监听的端口号，默认3306
PORT = 3306
#连接MYSQL的用户名，用户用自己设置的
USERNAME = 'root'
#连接mysql的密码，用户自己设置的
PASSWORD = 'root123456'
#mysql上创建的数据库名称
DATABASE = 'database_learn'
app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8mb4"


# 创建一个有Flask-SQLAlchemy提供的SQLAlchemy类的对象db，在创建这个类时，要传入当前的app
db = SQLAlchemy(app)


class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    username = db.Column(db.String(100),nullable=False)
    password = db.Column(db.String(100),nullable=False)

# with app.app_context():
#     db.create_all()

# @app.route("/")
# def hello_world():
#     return "hello world"

# create操作
@app.route('/user/add')
def user_add():
    user1 = User(username='张三',password='123456')
    user2 = User(username='李四',password="654321")
    db.session.add(user1)
    db.session.add(user2)
    db.session.commit()
    return "用户添加成功！"

# read操作
@app.route("/user/read")
def user_read():
    #1、获取User中所有数据
    users= User.query.all()
    for i in users: #打印id 和 username
        print(i.id)
        print(i.username)

    #获取主键为1的User对象
    user1 = User.query.get(1)
    print(user1.username)
    #获取第一条数据
    user2 = User.query.first()
    print(user2.username)

    return "数据获取成功！"

@app.route("/user/filter")
def user_filiter():
    # 1、filter方法
    users = User.query.filter(User.username=="张三").all()
    for i in users:
        print(i.id,i.username)
    #2、filter_by方法
    users1 = User.query.filter_by(username='张三').all()
    for j in users1:
        print(j.id,j.username)

@app.route("/user/like")
def user_filter_like():
    # 3、模糊查询
    user2 = User.query.filter(User.username.contains("张"))
    for i in user2:
        print(i.id,i.username)
    user3= User.query.filter(User.username.like("%三%"))
    for i in user3:
        print(i.id,i.username)
    user4 = User.query.filter(User.username.like("_四"))
    for i in user4:
        print(i.id,i.username)
    return "查询成功！"

@app.route("/user/in")
def user_in():
    users = User.query.filter(User.username.in_(["张三","李四",'王五','韩流']))
    for i in users:
        print(i.id,i.username)
    return "查询成功"

@app.route("/user/notin")
def user_notin():
    users = User.query.filter(~User.username.in_(["张三","韩流"]))
    for i in users:
        print(i.id,i.username)
    return "查询成功！"

@app.route("/user/null")
def user_null():
    #方式一
    users = User.query.filter(User.username==None)
    for i in users:
        print(i.id,i.username)
    #方式二
    users1 = User.query.filter(User.username.is_(None))
    for i in users1:
        print(i.id,i.username)
    return "查询成功！"

@app.route("/user/notnull")
def user_not_null():
    users = User.query.filter(User.username != None)
    for i in users:
        print(i.id,i.username)
    users1 = User.query.filter(User.username.isnot(None))
    for i in users1:
        print(i.id,i.username)
    return "查询成功！"

@app.route("/user/and")
def user_and():
    from sqlalchemy import and_
    users = User.query.filter(and_(User.username=='张三',User.id <10))
    for i in users:
        print(i.id,i.username)
    return "查询成功"

@app.route("/user/or")
def user_or():
    from sqlalchemy import or_
    users = User.query.filter(or_(User.username=='张三',User.id == 3))
    for i in users:
        print(i.id,i.username)
    return "查询成功！"


@app.route("/user/orderby")
def user_orderby():
    #正序排列
    user1 = User.query.order_by("id")
    for i in user1:
        print(i.id,i.username)
    user2 = User.query.order_by(User.id)
    for i in user2:
        print(i.id,i.username)

    #倒序排列
    user3 = User.query.order_by(db.text("-id"))
    for i in user3:
        print(i.id,i.username)
    user4 = User.query.order_by(User.id.desc())
    for i in user4:
        print(i.id,i.username)
    return "查询成功！"


@app.route("/user/groupby")
def user_groupby():
    from sqlalchemy import func
    users = db.session.query(User.username,func.count(User.id)).group_by("username").all()
    for i in users:
        print(i)
    return "分组过滤成功"

@app.route("/user/update")
def user_update():
    #1、一条数据进行更新
    user = User.query.get(1)
    user.username = "张三——重新修改"
    db.session.commit()
    return "更新数据成功"

@app.route("/user/up")
def user_update_more():
    User.query.filter(User.username.like("%张三%")).update({"password":"000000"},synchronize_session=False)
    db.session.commit()
    return "更新成功"

@app.route("/user/delete")
def user_del():
    user = User.query.get(1)
    db.session.delete(user)
    db.session.commit()
    return "删除成功"

@app.route("/user/del")
def user_del_mor():
    User.query.filter(User.username.contains("张三")).delete(synchronize_session=False)
    db.session.commit()
    return "批量删除成功！"

if __name__ == "__main__":
    app.run(debug=True,host="0.0.0.0",port=8000)