Download - Rest API using Flask & SqlAlchemy
REST APIREST APIUSING FLASK & SQLALCHEMYUSING FLASK & SQLALCHEMY
Alessandro CucciPython Developer, Energee3
REPRESENTATIONAL STATE TRANSFERREPRESENTATIONAL STATE TRANSFERROY 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
RESTRESTGUIDING CONSTRAINTSGUIDING CONSTRAINTS
CLIENT-SERVERCLIENT-SERVERSTATELESSSTATELESSCACHEABLECACHEABLELAYERED SYSTEMLAYERED SYSTEMCODE ON DEMAND (OPTIONAL)CODE ON DEMAND (OPTIONAL)
RESTRESTGUIDING CONSTRAINTSGUIDING CONSTRAINTS
UNIFORM INTERFACEUNIFORM INTERFACEIDENTIFICATION 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 EXAMPLESh�p://myapi.com/customersh�p://myapi.com/customers/33245
REST ANTI-PATTERNSREST ANTI-PATTERNSh�p://myapi.com/update_customer&id=12345&format=jsonh�p://myapi.com/customers/12345/update
RELATIONSHIP BETWEEN URL AND HTTPRELATIONSHIP BETWEEN URL AND HTTPMETHODSMETHODS
URL GET PUT POST DELETE
h�p://api.myvinylcollec�on.com/records/
LIST of records incollec�on
Method notallowed.
CREATE anew entry inthecollec�on.
DELETE theen�recollec�on.
h�p://api.myvinylcollec�on.com/records/1
RETRIEVE arepresenta�on ofthe addressedmember of thecollec�on
REPLACEtheaddressedmember ofthecollec�on.
Method notallowed.
DELETE theaddressedmember ofthecollec�on.
WHAT DO WE NOT CARE FOR THIS EVENINGWHAT DO WE NOT CARE FOR THIS EVENINGStability & Tes�ngLong-Term maintainabilityEdge CasesOpera�ons, Caching & Deployment
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 METHODSURL GET
h�p://api.myvinylcollec�on.com/records
LIST of records incollec�on
h�p://api.myvinylcollec�on.com/records/1
RETRIEVE arepresenta�on of theaddressed member ofthe 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 checkyour spelling and try again.</p>
JSONIFY THAT ERROR!JSONIFY THAT [email protected](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
}
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
)
import pandasimport sqlite3
conn = sqlite3.connect('record_collection.db')conn.text_factory = sqlite3.Binarydf = 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.PYfrom flask_sqlalchemy import SQLAlchemyfrom 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, jsonifyfrom 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 METHODSURL POST
h�p://api.myvinylcollec�on.com/records/
CREATE a newentry in thecollec�on.
h�p://api.myvinylcollec�on.com/records/1
Method notallowed.
POST ON LOCALHOST:5000/RECORDS/IDPOST ON LOCALHOST:5000/RECORDS/[email protected](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())
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 [email protected]("/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 CREATEDContent-Type: application/jsonContent-Length: 104Server: 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 METHODSURL PUT
h�p://api.myvinylcollec�on.com/records/
Method not allowed.
h�p://api.myvinylcollec�on.com/records/1
REPLACE theaddressed memberof 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()), 201elif 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 ALLOWEDContent-Type: application/jsonContent-Length: 59Server: 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 OKContent-Type: application/jsonContent-Length: 104Server: 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 METHODSURL DELETE
h�p://api.myvinylcollec�on.com/records/
DELETE the en�recollec�on.
h�p://api.myvinylcollec�on.com/records/1
DELETE theaddressed memberof the collec�on.
DELETE ON COLLECTIONDELETE ON [email protected]("/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()), 201elif 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 [email protected]("/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/91HTTP/1.0 200 OKContent-Type: application/jsonContent-Length: 104Server: 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/91HTTP/1.0 HTTP/1.0 404 NOT FOUNDContent-Type: application/jsonContent-Length: 50Server: Werkzeug/0.11.11 Python/2.7.12+Date: Sat, 03 Dec 2016 10:40:09 GMT
{"error": "Not Found","status_code": 404
}
PWD AUTHENTICATIONPWD AUTHENTICATIONfrom flask import Flask, jsonify, abortfrom flask_login import LoginManager, current_user
app = Flask(__name__)login_manager = LoginManager(app)
@login_manager.request_loaderdef 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:5000HTTP/1.0 401 UNAUTHORIZEDContent-Type: application/jsonWWW-Authenticate: Basic realm="Authentication Required"Content-Length: 37Server: 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:5000HTTP/1.0 200 OKContent-Type: application/jsonContent-Length: 28Server: 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 - [email protected]("/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 - [email protected]("/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()), 201elif 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), 200else:
abort(401)return jsonify(records), 200
HOMEWORKSHOMEWORKSPagina�on with Flask-SqlAlchemyRate Limi�ng with Flask-LimiterCache with Flask-Cache