Flask 使用SQLAlchemy操作数据库

在Flask框架中使用SQLAlchemy进行操作数据库。

初始化数据库，这里为方便演示，使用sqlite数据库。

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///example.sqlite"
db = SQLAlchemy(app)

创建用户类

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String, unique=True, nullable=False)

然后，创建数据库表

db.create_all()

下面使用用户接口的crud操作进行示例整体流程。

1.新增

新增用户操作

@app.route("/add_user", methods=['POST'])
def add_user():
    username = request.form.get('username')
    db.session.add(User(username=username))
    db.session.commit()
    return jsonify(username=username)

2.查询

查询用户操作

# 查询所有用户
# localhost:5000/get_users
@app.route("/get_users")
def get_users():
    users = db.session.query(User.id, User.username).all()
    user_list = []
    for u in users:
        user_dict = {'id': u.id, 'username': u.username}
        user_list.append(user_dict)
    return jsonify(data=user_list)


# 查询用户
# localhost:5000/get_user?username=lucifer
@app.route("/get_user")
def get_user():
    username = request.args.get("username")
    user = db.session.query(User.id, User.username).filter(User.username == username).one()

    return jsonify(
        id=user.id,
        username=user.username,
    )

3.更新

更新用户操作

@app.route("/update_user", methods=['POST'])
def update_user():
    user_id = request.json.get("id")
    username = request.json.get("username")
    print(username)
    # 修改对象属性
    user = db.session.query(User).filter(User.id == user_id).first()
    print(user.id)
    user.username = username
    db.session.add(user)
    db.session.commit()
    return jsonify(user_id=user_id,
                   username=username)

4.删除

删除用户操作

@app.route("/del_user", methods=['POST'])
def del_user():
    username = request.json.get("username")
    db.session.query(User).filter(User.username == username).delete()
    db.session.commit()
    return jsonify(del_user=username)

5.整体案例

from flask import Flask, jsonify, request
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///example.sqlite"
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String, unique=True, nullable=False)


# 新增用户
@app.route("/add_user", methods=['POST'])
def add_user():
    username = request.form.get('username')
    db.session.add(User(username=username))
    db.session.commit()
    return jsonify(username=username)


# 查询所有用户
# localhost:5000/get_users
@app.route("/get_users")
def get_users():
    users = db.session.query(User.id, User.username).all()
    user_list = []
    for u in users:
        user_dict = {'id': u.id, 'username': u.username}
        user_list.append(user_dict)
    return jsonify(data=user_list)


# 查询用户
# localhost:5000/get_user?username=lucifer
@app.route("/get_user")
def get_user():
    username = request.args.get("username")
    user = db.session.query(User.id, User.username).filter(User.username == username).one()

    return jsonify(
        id=user.id,
        username=user.username,
    )


# 更新用户
@app.route("/update_user", methods=['POST'])
def update_user():
    user_id = request.json.get("id")
    username = request.json.get("username")
    print(username)
    # 修改对象属性
    user = db.session.query(User).filter(User.id == user_id).first()
    print(user.id)
    user.username = username
    db.session.add(user)
    db.session.commit()
    return jsonify(user_id=user_id,
                   username=username)


# 删除用户
@app.route("/del_user", methods=['POST'])
def del_user():
    username = request.json.get("username")
    db.session.query(User).filter(User.username == username).delete()
    db.session.commit()
    return jsonify(del_user=username)


if __name__ == "__main__":
    db.create_all()
    app.run()