sqlalchemy

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.

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/

configure flask-sqlalchemy

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:

	db.init_app(app)

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

password helper

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

logins

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

adding twits

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:

	db.session.add(new_twit)
	db.session.commit()

deleting twits

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

editing twits

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

extension

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.