flask下的MySQL增删改查

flask下的MySQL增删配置

添加数据

@app.route('/add')
def add_data():
    u = UserInfo()
    new_user1 = UserInfo(nickname='flask_test1', mobile='13888888888', signature='理想', create_time=datetime.now(), role_id=1)
    new_user2 = UserInfo(nickname='flask_test2', mobile='13999999999', signature='信念', create_time=datetime.now(), role_id=2)
    new_user3 = UserInfo(nickname='flask_test3', mobile='13777777777', signature='坚持', create_time=datetime.now(), role_id=1)
    new_user4 = UserInfo(nickname='flask_test4', mobile='13666666666', signature='奋斗', create_time=datetime.now(), role_id=2)
    u.add(new_user1)
    u.add(new_user2)
    u.add(new_user3)
    u.add(new_user4)
    return "添加数据成功"

查询数据

@app.route('/query1')
def query_data1():
    user_list = UserInfo.query.all()
    result = []
    for user in user_list:
        result.append(user.to_dict())
    return {'users': result}

@app.route('/query2')
def query_data2():
    user=UserInfo.query.get(3)  #主键查询
    return {'users': user.to_dict()}

@app.route('/query3')
def query_data3():
    first_user = UserInfo.query.first()  #查询第一条数据
    return {'users': first_user.to_dict()}

@app.route('/query4')
def query_data4():
    user_list = UserInfo.query.filter(UserInfo.signature == '理想').all()  #条件查询
    result = []
    for user in user_list:
        result.append(user.to_dict())
    return {'users': result}

@app.route('/query5')
def query_data5():
    userlist = UserInfo.query.filter_by(signature='信念').all()  #根据字段
    result = []
    for user in userlist:
        result.append(user.to_dict())
    return {'users': result}

删除数据

db.session.delete(delete_user)
db.session.commit()

@app.route('/delete1')
def delete_data():
    try:
        user = UserInfo.query.get(4)
        db.session.delete(user)
        db.session.commit()
        return "删除id为4的数据成功,使用db.session.delete()方法且已db.session.commit()提交（容易误删）"
    except Exception as e:
        return "删除失败，错误信息：(数据不存在或已经被（彻底）删除过一次)" + str(e)
        
@app.route('/delete2')
def delete_data2():
    delete_user = UserInfo.query.get(3)
    delete_user.delete()
    return "删除id为3的数据成功,使用模型类的delete()方法（推荐）,修改status状态为0，已提交"

更新数据

@app.route('/update1')
def update_data():
    u = UserInfo()
    update_user = u.query.get(3)
    update_user.status = 1
    u.update()
    return "更新id为3的数据成功,使用模型类的update()方法（推荐）,将status状态改为1，已提交"