In this lab we will implement SQLAlchemy in the mywits app. SQLAlchemy is an example of an ORM (Object Relational Mapper), which means we can deal with our data models & queries as python objects and attributes. Using sqlalchemy means we can abstract our code away from using sql directly, and leave sqlalchemy to do secure & efficient interactions with the database behind the scenes.
This lab comes with full and annotated example code. However you are strongly encouraged to try each step for yourself as much as possible before resorting to the code in the repo.
pip install --user flask-sqlalchemy
setting up flask-sqlalchemy
To set up flask-sqlalchemy we need to
- import SQLAlchemy
- initiate the sqlalchemy db connection
- create the table classes for our model based on sqlalchemy's Model class
- create the fields in each table class using sqlalchemy's Column method
- set up any relationships between tables using sqlalchemy's ForeignKey and relationship
Using sqlalchemy is as simple as
- using the sqlalchemy classes directly (e.g. Users, Twits)
- using the query attribute of the classes to query our data
- using session.add(),session.delete() and session.commit() to change our data
this is all succinctly described in the flask-sqlalchemy quickstart http://flask-sqlalchemy.pocoo.org/2.3/quickstart/
Create a file called models.py for your db models. Import sqlalchemy:
from flask_sqlalchemy import SQLAlchemy
create the sqlalchemy object
db = SQLAlchemy()
create the models
Create your models (table descriptions) in models.py.
You should be able to write them directly by comparison with the examples in the quickstart. The main difference is that we are also including in the User class the attributes required by flask-login (which are not anything to do with sqlalchemy as such).
connect to the database
sqlalchemy needs to know how to connect to the actual underlying sql database. This is done in the main app file using the value of SQLALCHEMY_DATABASE_URI:
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://mytwits_user:mytwits_password@localhost/mytwits'
After the app itself is instantiated, you can initialise the connection to the database:
use sqlalchemy instead of sql
now we can use sqlalchemy classes to access our data; for example, to get all the twits for listing on the main page we can use
twits = Twits.query.order_by(Twits.created_at.desc()).all()
the query attribute is the way to query the data via sqlalchemy objects. you can see this in the quickstart and you can read more at http://flask-sqlalchemy.pocoo.org/2.3/queries/#querying-records
previously we dealt with password hashing and checking in our database methods. as we no longer need to deal directly with sql, we will break our password specific functions out in to a separate class called PassworHelper, which we store in it's own .py file and import into our main code.
my password helper file looks like this:
import hashlib import os import base64 class PasswordHelper: def get_hash(self, plain): return hashlib.sha512(plain).hexdigest() def get_salt(self): return base64.b64encode(os.urandom(20)) def validate_password(self, plain, salt, expected): return self.get_hash((salt+plain).encode('utf-8')) == expected
we create a password helper object near the start of our main code:
ph = PasswordHelper()
when logging in, we need to retrieve the salt and the hashed password for the user, for the username entered in the form. with sqlalchemy we can do this as
user = Users.query.filter_by(username=username).first()
we can then pass user.salt and user.hash to the validate_password method of the ph object.
accessing the user_id
to add a twit we are going to need the user_id of the user who is creating it. this is straightforward because this is the currently logged-in user, which flask-login makes available to us as 'current_user'.
make sure you have the following import at the top of your main file:
from flask_login import current_user
then in your add_twit function you can access the user id:
user_id = current_user.user_id
we will add a twit using the current user id and the value for the twit text that was submitted in the form. we create a new twit object directly from the sqlalchemy Twits class:
twit = form.twit.data user_id = current_user.user_id new_twit = Twits(twit=twit, user_id=user_id)
however, this is not yet saved in the database. to make the change permanent we need to add our new twit to the flask-sqlalchemy database session, and commit it:
deleting a twit is just as easy. given a twit_id in the http request, we retrieve the twit object, delete it, and call session.commit():
twit_id = request.args.get('id') twit_for_deletion = Twits.query.filter_by(twit_id = twit_id).first() db.session.delete(twit_for_deletion) db.session.commit()
to edit a twit, we can load the twit object with the required id, change the value of the twit content, and commit the session.
if form.validate_on_submit(): # get the twit_id back from the form twit_id = form.twit_id.data # load that twit twit = Twits.query.filter_by(twit_id = twit_id).first() # change the twit text to the text submitted in the form twit.twit = form.twit.data # commit the change db.session.commit()
create a view that shows the timeline for a particular user (i.e. lists only their twits).
using sqlalchemy, this is made easy by the fact that we can directly access all the twits for a spceific user e.g.
twits = Users.query.filter_by(username=username).first().twits
errors & omissions
please let me know about any errors or omissions in the lab notes or the code and i will do my best to fix them.