POST method to SQL using Stored Procedure call in Flask-SQL Alchemy

Question

I have a working SQL stored procedure and have flask-sqlAlchemy set up in Python and connected to my MySQL database. I'm trying to build a simple API that will take in 6-7 request parameters entered via URL request and send these to my call procedure and update the database accordingly based on the stored procedure which inserts a new record in the DB table (the sp is 'add_spotting' below).

I am trying to do this via a POST method and believe I'm taking in the request argument parameters correctly and calling the procedure but I'm not sure how to execute those procedures and actually inject to the 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!")

Hi @Shane, would you mind sharing the code for the database models as well? — realr, Jul 30 '19 at 01:48
@calestini Sure! I added it above - I'm not sure if my data model is complete or if I actually need it for this post method. I already have my database populated and stored with the data so I don't need to create it via Python but I did want to be able to use an API to demonstrate a simple insertion via my stored procedure. — , Jul 30 '19 at 02:19

score 1 · Answer 1 · answered Jul 30 '19 at 02:34

If all you want is to test the POST request with the code you provided, you can simple use the database model object to populate it:

#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!")

With that said, two things I would point out:

You should probably be careful when inserting data via url params. A more recommended approach would be to pass data to your POST request, and possibly validate via a form or authentication
You can create an initiator for your model class, so its easier to insert data. For example:

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()

Thanks. That makes sense to create an object of the model and pass that to the database. However, we want to take advantage of the SQL stored procedure we have created which only takes 6 parameters (animal, user, trail, quantity, lat, lon, desc) and then used those parameters to find the relative foreign key ID references in the SQL database so the actual data that gets inserted is slightly inserted than the data the is input by the request. For example, a user may insert their user name, animal name, trail name ... but the actual database table has the respective user_id, animal_id, etc — , Jul 30 '19 at 02:57
I see @Shane, that makes sense. There is a good thread on how to call stored procedures with sqlalchemy [here](https://stackoverflow.com/questions/3563738/stored-procedures-with-sqlalchemy), it might be more insightful — realr, Jul 30 '19 at 03:07
`"call add_spotting('" + animal + "','"+ user + "','" + trail + \ "','" + quantity + "','" + lat + "','" + lon + "','" + desc + "')"` is ripe for injection. `"call add_spotting(:animal, :user, :trail, :quantity, :lat, :lon, :desc)"` and `mysql.engine.execute(text(proc_call), animal=animal, ...)` should be used instead. — Ilja Everilä, Jul 30 '19 at 04:43

POST method to SQL using Stored Procedure call in Flask-SQL Alchemy

1 Answers1