CODEWITHSUNDEEP
pythonstored-proceduresflasksqlalchemyflask-sqlalchemy
user6557421
user6557421

Reputation:

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

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

Upvotes: 1

Views: 1845

Answers (1)

rrcal
rrcal

Reputation: 3752

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:

  1. 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

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

Upvotes: 1

Related Questions