使用Flask炼金术中的存储过程调用将方法发送到SQL

Question

我有一个工作的SQL存储过程，并在Python中设置了烧瓶-sqlAlchemy，并连接到我的MySQL数据库。我正在尝试构建一个简单的API，它将接受通过URL请求输入的6-7个请求参数，并将这些参数发送到我的调用过程，并根据存储过程相应地更新数据库，该存储过程在DB表中插入一个新记录(下面的sp是'add_spotting‘)。

我试图通过POST方法来实现这一点，并且相信我正确地接受了请求参数并调用了这个过程，但是我不知道如何执行这些过程并将其实际注入到DB中。

class Spotting(mysql.Model):
    __tablename__ = 'spotting'
    spotting_id = mysql.Column(mysql.Integer, primary_key = True)
    animal_id = mysql.Column(mysql.Integer, mysql.ForeignKey('animal.animal_id'), nullable=False)
    user_id = mysql.Column(mysql.Integer, mysql.ForeignKey('user.user_id'), nullable=False)
    trail_id = mysql.Column(mysql.Integer, mysql.ForeignKey('trail.trail_id'), nullable=False)
    quantity = mysql.Column(mysql.Integer, nullable = False)
    lat = mysql.Column(mysql.Float, nullable=True)
    lon = mysql.Column(mysql.Float, nullable=True)
    description = mysql.Column(mysql.String(250), nullable=False)


#Post method 
@application.route('/spotting', methods=['POST'])
def postSpotting():
    animal = request.args.get('animal')
    user = request.args.get('user')
    trail = request.args.get('trail')
    quantity = request.args.get('quantity')
    lat = request.args.get('lat')
    lon = request.args.get('lon')
    desc = request.args.get('desc')
    proc_call = "call add_spotting('" + animal + "','"+ user + "','" + trail + \
                "','" + quantity + "','" + lat + "','" + lon + "','" + desc + "')"
    mysql.engine.execute(proc_call)

    ### Here I want to post and commit this to the MySQL DB via the stored proc and return a message such as
    # ## 'Spotting successfully added'
    return ("Successfully posted!")

Answer 1

如果您只想用您提供的代码测试POST请求，可以简单地使用数据库模型对象来填充它：

#Post method 
@application.route('/spotting', methods=['POST'])
def postSpotting():
    animal = request.args.get('animal')
    user = request.args.get('user')
    trail = request.args.get('trail')
    quantity = request.args.get('quantity')
    lat = request.args.get('lat')
    lon = request.args.get('lon')
    desc = request.args.get('desc')
    proc_call = "call add_spotting('" + animal + "','"+ user + "','" + trail + \
                "','" + quantity + "','" + lat + "','" + lon + "','" + desc + "')"
    mysql.engine.execute(proc_call)

    ### create a new instance of your model
    new_post = Post()
    new_post.lat = lat
    new_post.lon = lon
    ... #etc

    ### commit your changes
    session = db.session
    session.add(new_post)
    db.session.commit()

    # ## 'Spotting successfully added'
    return ("Successfully posted!")

说到这里，我要指出两件事：

1. 在通过url参数插入数据时，您可能应该小心。更推荐的方法是将data传递给您的POST请求，并可能通过表单或身份验证进行验证。
2. 您可以为您的模型类创建一个启动器，因此插入数据比较容易。例如：

class Spotting(mysql.Model):
    __tablename__ = 'spotting'
    spotting_id = mysql.Column(mysql.Integer, primary_key = True)
    animal_id = mysql.Column(mysql.Integer, mysql.ForeignKey('animal.animal_id'), nullable=False)
    user_id = mysql.Column(mysql.Integer, mysql.ForeignKey('user.user_id'), nullable=False)
    trail_id = mysql.Column(mysql.Integer, mysql.ForeignKey('trail.trail_id'), nullable=False)
    quantity = mysql.Column(mysql.Integer, nullable = False)
    lat = mysql.Column(mysql.Float, nullable=True)
    lon = mysql.Column(mysql.Float, nullable=True)
    description = mysql.Column(mysql.String(250), nullable=False)

    def __init__(self, lat, lon): #etc
        self.lat = lat
        self.lon = lon
        ## etc

        db.session.add(self)
        db.session.commit()