Rest API using Flask & SqlAlchemy

REST APIREST API USING FLASK & SQLALCHEMYUSING FLASK & SQLALCHEMY Alessandro Cucci Python Developer, Energee3

REPRESENTATIONAL STATE TRANSFERREPRESENTATIONAL STATE TRANSFER ROY THOMAS FIELDING - 2010ROY THOMAS FIELDING - 2010 HTTP://WWW.ICS.UCI.EDU/~FIELDING/PUBS/DISSERTATION/REST_ARCH_STYLE.HTMHTTP://WWW.ICS.UCI.EDU/~FIELDING/PUBS/DISSERTATION/REST_ARCH_STYLE.HTM

RESTREST GUIDING CONSTRAINTSGUIDING CONSTRAINTS CLIENT-SERVERCLIENT-SERVER STATELESSSTATELESS CACHEABLECACHEABLE LAYERED SYSTEMLAYERED SYSTEM CODE ON DEMAND (OPTIONAL)CODE ON DEMAND (OPTIONAL)

RESTREST GUIDING CONSTRAINTSGUIDING CONSTRAINTS UNIFORM INTERFACEUNIFORM INTERFACE IDENTIFICATION OF RESOURCESIDENTIFICATION OF RESOURCES MANIPULATION OF RESOURCES THROUGH REPRESENTATIONSMANIPULATION OF RESOURCES THROUGH REPRESENTATIONS SELF-DESCRIPTIVE MESSAGESSELF-DESCRIPTIVE MESSAGES HYPERMEDIA AS THE ENGINE OF APPLICATION STATEHYPERMEDIA AS THE ENGINE OF APPLICATION STATE

REST URI EXAMPLESREST URI EXAMPLES h�p://myapi.com/customers h�p://myapi.com/customers/33245 REST ANTI-PATTERNSREST ANTI-PATTERNS h�p://myapi.com/update_customer&id=12345& format=json h�p://myapi.com/customers/12345/update

RELATIONSHIP BETWEEN URL AND HTTPRELATIONSHIP BETWEEN URL AND HTTP METHODSMETHODS URL GET PUT POST DELETE h�p://api.myvinylcollec�on.com /records/ LIST of records in collec�on Method not allowed. CREATE a new entry in the collec�on. DELETE the en�re collec�on. h�p://api.myvinylcollec�on.com /records/1 RETRIEVE a representa�on of the addressed member of the collec�on REPLACE the addressed member of the collec�on. Method not allowed. DELETE the addressed member of the collec�on.

WHAT DO WE NOT CARE FOR THIS EVENINGWHAT DO WE NOT CARE FOR THIS EVENING Stability & Tes�ng Long-Term maintainability Edge Cases Opera�ons, Caching & Deployment

MY VINYL COLLECTION APIMY VINYL COLLECTION API

HTTP://FLASK.POCOO.ORG/HTTP://FLASK.POCOO.ORG/

HELLO API!HELLO API! from flask import Flask app = Flask(__name__) if __name__ == '__main__': app.run() $ python myvinylcollectionapi.py * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

HELLO API!HELLO API! from flask import Flask app = Flask(__name__) @app.route("/") def hello(): return "Hello PyRE!" if __name__ == '__main__': app.run()

HELLO API!HELLO API! from flask import Flask, jsonify app = Flask(__name__) @app.route("/") def hello(): return jsonify(data="Hello PyRE!") if __name__ == '__main__': app.run()

HTTP GET METHODSHTTP GET METHODS URL GET h�p://api.myvinylcollec�on.com /records LIST of records in collec�on h�p://api.myvinylcollec�on.com /records/1 RETRIEVE a representa�on of the addressed member of the collec�on

from flask import Flask, jsonify, abort app = Flask(__name__) RECORDS = [ { 'id': 0, 'artist': "Queen", 'title': "A Night At The Opera", 'year': "1975", 'label': "EMI" }, { 'id': 1, 'artist': "Pink Floyd", 'title': "The Dark Side Of The Moon", 'year': "1989", 'label': "EMI" }, ... ]

@app.route("/records") def get_records(): return jsonify(RECORDS) @app.route("/records/<int:index>") def get_record(index): try: record = RECORDS[index] except IndexError: abort(404) return jsonify(record) if __name__ == '__main__': app.run()

HTTP GET METHODHTTP GET METHOD $ curl -X GET localhost:5000/records [ { "artist": "Queen", "id": 0, "label": "EMI", "title": "A Night At The Opera", "year": "1975" }, { "artist": "Pink Floyd", "id": 1, "label": "EMI", "title": "The Dark Side Of The Moon", "year": "1989" } ]

HTTP GET Method $ curl -X GET localhost:5000/records/1 { "artist": "Pink Floyd", "id": 1, "label": "EMI", "title": "The Dark Side Of The Moon", "year": "1989" } $ curl -X GET localhost:5000/records/5 <title>404 Not Found</title> <h1>Not Found</h1> <p>The requested URL was not found on the server. If you entered the URL manually please check your spelling and try again.</p>

JSONIFY THAT ERROR!JSONIFY THAT ERROR! @app.errorhandler(404) def page_not_found(error): return jsonify( error="Not Found", status_code=404 ), 404 $ curl -X GET localhost:5000/records/5 { "error": "Not Found", "status_code": 404 }

GOOD NEWS:GOOD NEWS: IT WORKS!IT WORKS! BAD NEWS:BAD NEWS: STATIC DATASTATIC DATA

SEPARATION OF CONCERNSSEPARATION OF CONCERNS

SQLITESQLITE + DISCOGS.COM+ DISCOGS.COM + PANDAS+ PANDAS

CREATE TABLE "collection" ( `index` INTEGER PRIMARY KEY AUTOINCREMENT, `Catalog#` TEXT, `Artist` TEXT, `Title` TEXT, `Label` TEXT, `Format` TEXT, `Rating` REAL, `Released` INTEGER, `release_id` INTEGER, `CollectionFolder` TEXT, `Date Added` TEXT, `Collection Media Condition` TEXT, `Collection Sleeve Condition` TEXT, `Collection Notes` REAL )

CSV COLLECTION EXPORTCSV COLLECTION EXPORT

import pandas import sqlite3 conn = sqlite3.connect('record_collection.db') conn.text_factory = sqlite3.Binary df = pandas.read_csv('collection.csv') df.to_sql('collection', conn)

OBJECT RELATIONAL MAPPER (ORM)OBJECT RELATIONAL MAPPER (ORM) HTTP://DOCS.SQLALCHEMY.ORGHTTP://DOCS.SQLALCHEMY.ORG

MODEL.PYMODEL.PY from flask_sqlalchemy import SQLAlchemy from flask_sqlalchemy import orm db = SQLAlchemy() class Record(db.Model): __tablename__ = "collection" index = db.Column(db.Integer, primary_key=True) Artist = db.Column(db.Text, nullable=False) Title = db.Column(db.Text, nullable=False) Label = db.Column(db.Text) Released = db.Column(db.Text) def as_dict(self): columns = orm.class_mapper(self.__class__).mapped_table.c return { col.name: getattr(self, col.name) for col in columns }

QUERYQUERY >>> # .all() return a list >>> all_records = Record.query.all() >>> len(all_records) 80 >>> # .first() return the first item that matches >>> record = Record.query.filter(Record.index == 9).first() >>> record.Title "Back In Black" >>> record.Artist "AC/DC" >>> record.Released "1980" >>> # .filter_by() is a shortcut >>> record = Record.query.filter_by(index == 6).first() >>> record.Title "Hotel California"

from flask import Flask, jsonify from model import db, Record app = Flask(__name__) app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///record_collection.db" db.init_app(app) @app.route("/records") def get_records(): records = [r.as_dict() for r in Record.query.all()] return jsonify(records) @app.route("/records/<int:index>") def get_record(index): record = Record.query.filter(Record.index == index).first_or_404() return jsonify(record.as_dict())

$ curl -X GET localhost:5000/records [ { "Artist": "The Police", "index": 0, "Title": "Reggatta De Blanc" }, { "Artist": "The Beatles", "index": 1, "Title": "Abbey Road" }, ... ] $ curl -X GET localhost:5000/records/1 { "Artist": "The Beatles", "index": 1, "Title": "Abbey Road" }

HTTP POST METHODSHTTP POST METHODS URL POST h�p://api.myvinylcollec�on.com /records/ CREATE a new entry in the collec�on. h�p://api.myvinylcollec�on.com /records/1 Method not allowed.

POST ON LOCALHOST:5000/RECORDS/IDPOST ON LOCALHOST:5000/RECORDS/ID @app.errorhandler(405) def method_not_allowed(error): return jsonify( error="Method Not Allowed", status_code=405 ), 405 from flask import Flask, jsonify, abort, request ... @app.route("/records/<int:index>", methods=['GET', 'POST']) def get_record(index): if request.method == 'POST': abort(405) record = Record.query.filter(Record.index == index).first_or_404() return jsonify(record.as_dict())

$ curl -X POST localhost:5000/records/1 { "error": "Method Not Allowed", "status_code": 405 }

INSERT INTO DATABASEINSERT INTO DATABASE >>> # .add() insert a record >>> db.session.add(record) >>> # changes won't be saved until committed! >>> db.session.commit()

ADDING A RECORD TO MY COLLECTIONADDING A RECORD TO MY COLLECTION @app.route("/records", methods=['GET', 'POST']) def get_records(): if request.method == 'POST': record = Record(**json.loads(request.data)) db.session.add(record) db.session.commit() return jsonify(record.as_dict()), 201 records = [r.as_dict() for r in Record.query.all()] return jsonify(records)

ADDING A RECORD TO MY COLLECTIONADDING A RECORD TO MY COLLECTION $ curl -i -H "Content-Type: application/json" -X POST localhost:5000/records > -d '{"Artist":"Neil Joung", "Title":"Harvest", > "Label":"Reprise Records", "Released":"1977"}' HTTP/1.0 201 CREATED Content-Type: application/json Content-Length: 104 Server: Werkzeug/0.11.11 Python/2.7.12+ Date: Sat, 03 Dec 2016 11:03:10 GMT { "Artist": "Neil Young", "Label": "Reprise Records", "Released": "1977", "Title": "American Stars 'N Bars", "index": 91 }

HTTP PUT METHODSHTTP PUT METHODS URL PUT h�p://api.myvinylcollec�on.com /records/ Method not allowed. h�p://api.myvinylcollec�on.com /records/1 REPLACE the addressed member of the collec�on.

@app.route("/records", methods=['GET', 'POST', 'PUT']) def get_records(): if request.method == 'POST': record = Record(**json.loads(request.data)) db.session.add(record) db.session.commit() return jsonify(record.as_dict()), 201 elif request.method == 'PUT': abort(405) records = [r.as_dict() for r in Record.query.all()] return jsonify(records), 200 @app.route("/records/<int:index>", methods=['GET', 'POST', 'PUT']) def get_record(index): if request.method == 'POST': abort(405) else: record = Record.query.filter(Record.index == index).first_or_404() if request.method == 'PUT': for k, v in json.loads(request.data).iteritems(): setattr(record, k, v) db.session.add(record) db.session.commit() return jsonify(record.as_dict()), 200

PUT ON COLLECTIONPUT ON COLLECTION $ curl -i -H "Content-Type: application/json" > -X POST localhost:5000/records > -d '{"Artist":"Neil Joung", "Title":"Harvest", > "Label":"Reprise Records", "Released":"1977"}' HTTP/1.0 405 METHOD NOT ALLOWED Content-Type: application/json Content-Length: 59 Server: Werkzeug/0.11.11 Python/2.7.12+ Date: Sat, 03 Dec 2016 10:20:06 GMT { "error": "Method Not Allowed", "status_code": 405 }

PUT ON RESOURCEPUT ON RESOURCE $ curl -i -H "Content-Type: application/json" > -X PUT localhost:5000/records/91 > -d '{"Artist":"Neil Joung", "Title":"Harvest", > "Label":"Reprise Records", "Released":"1977"}' HTTP/1.0 200 OK Content-Type: application/json Content-Length: 104 Server: Werkzeug/0.11.11 Python/2.7.12+ Date: Sat, 03 Dec 2016 11:07:22 GMT { "Artist": "Neil Young", "Label": "Reprise Records", "Released": "1977", "Title": "American Stars 'N Bars", "index": 91 }

HTTP DELETE METHODSHTTP DELETE METHODS URL DELETE h�p://api.myvinylcollec�on.com /records/ DELETE the en�re collec�on. h�p://api.myvinylcollec�on.com /records/1 DELETE the addressed member of the collec�on.

DELETE ON COLLECTIONDELETE ON COLLECTION @app.route("/records", methods=['GET', 'POST', 'PUT', 'DELETE']) def get_records(): if request.method == 'POST': record = Record(**json.loads(request.data)) db.session.add(record) db.session.commit() return jsonify(record.as_dict()), 201 elif request.method == 'PUT': abort(405) records = [r.as_dict() for r in Record.query.all()] if request.method == 'DELETE': for r in records: db.session.delete(r) db.session.commit() records = [r.as_dict() for r in Record.query.all()] return jsonify(records), 200

DELETE ON RESOURCEDELETE ON RESOURCE @app.route("/records/<int:index>", methods=['GET', 'POST', 'PUT', 'DELETE']) def get_record(index): if request.method == 'POST': abort(405) else: record = Record.query.filter(Record.index == index).first_or_404() if request.method == 'PUT': for k, v in json.loads(request.data).iteritems(): setattr(record, k, v) db.session.add(record) db.session.commit() elif request.method == 'DELETE': db.session.delete(record) db.session.commit() return jsonify(record.as_dict()), 200

DELETE ON RESOURCEDELETE ON RESOURCE $ curl -i -X DELETE localhost:5000/records/91 HTTP/1.0 200 OK Content-Type: application/json Content-Length: 104 Server: Werkzeug/0.11.11 Python/2.7.12+ Date: Sat, 03 Dec 2016 10:40:00 GMT { "Artist": "Neil Young", "Label": "Reprise Records", "Released": "1977", "Title": "American Stars 'N Bars", "index": 91 }

DELETE ON RESOURCEDELETE ON RESOURCE $ curl -i -X DELETE localhost:5000/records/91 HTTP/1.0 HTTP/1.0 404 NOT FOUND Content-Type: application/json Content-Length: 50 Server: Werkzeug/0.11.11 Python/2.7.12+ Date: Sat, 03 Dec 2016 10:40:09 GMT { "error": "Not Found", "status_code": 404 }

DELETE ON COLLECTIONDELETE ON COLLECTION $ curl -i -X DELETE localhost:5000/records

FLASK-LOGINFLASK-LOGIN HTTPS://FLASK-LOGIN.READTHEDOCS.IOHTTPS://FLASK-LOGIN.READTHEDOCS.IO

PWD AUTHENTICATIONPWD AUTHENTICATION from flask import Flask, jsonify, abort from flask_login import LoginManager, current_user app = Flask(__name__) login_manager = LoginManager(app) @login_manager.request_loader def check_token(request): token = request.headers.get('Authorization') if token == 'L3T_M3_PA55!': return "You_can_pass" # DON'T TRY THIS AT HOME! return None @app.route("/") def get_main_root(): if current_user: return jsonify(data='Hello Login'), 200 else: abort(401)

HOW IT WORKSHOW IT WORKS $ curl -i localhost:5000 HTTP/1.0 401 UNAUTHORIZED Content-Type: application/json WWW-Authenticate: Basic realm="Authentication Required" Content-Length: 37 Server: Werkzeug/0.11.11 Python/2.7.12+ Date: Sat, 03 Dec 2016 14:46:55 GMT { "error": "Unauthorized access" } $ curl -i -H "Authorization: L3T_M3_PA55!" localhost:5000 HTTP/1.0 200 OK Content-Type: application/json Content-Length: 28 Server: Werkzeug/0.11.11 Python/2.7.12+ Date: Sat, 03 Dec 2016 14:42:00 GMT { "data": "Hello Login" }

SECURING OUR API - RESOURCESECURING OUR API - RESOURCE @app.route("/records/<int:index>", methods=['GET', 'POST', 'PUT', 'DELETE']) def get_record(index): if request.method == 'POST': abort(405) else: record = Record.query.filter(Record.index == index).first_or_404() if request.method == 'PUT': if current_user: for k, v in json.loads(request.data).iteritems(): setattr(record, k, v) db.session.add(record) db.session.commit() else: abort(401) elif request.method == 'DELETE': if current_user: db.session.delete(record) db.session.commit() else: abort(401) return jsonify(record.as_dict()), 200

SECURING OUR API - COLLECTIONSECURING OUR API - COLLECTION @app.route("/records", methods=['GET', 'POST', 'PUT', 'DELETE']) def get_records(): if request.method == 'POST': record = Record(**json.loads(request.data)) db.session.add(record) db.session.commit() return jsonify(record.as_dict()), 201 elif request.method == 'PUT': abort(405) records = [r.as_dict() for r in Record.query.all()] if request.method == 'DELETE': if current_user: for r in records: db.session.delete(r) db.session.commit() records = [r.as_dict() for r in Record.query.all()] return jsonify(records), 200 else: abort(401) return jsonify(records), 200

HOMEWORKSHOMEWORKS Pagina�on with Flask-SqlAlchemy Rate Limi�ng with Flask-Limiter Cache with Flask-Cache

THANK YOU!THANK YOU! { 'slides': 'www.alessandrocucci.it/pyre/restapi', 'code': 'https://goo.gl/4UOqEr' }

Rest API using Flask & SqlAlchemy

More Related Content

What's hot

Similar to Rest API using Flask & SqlAlchemy

Recently uploaded

Rest API using Flask & SqlAlchemy