lab 14

today we will

• make sure we have both mongo and mysql running on our vs
• set up access to a database for both apis
• test that we can show twits, and add them
• create a mock twitter app which uses the full range of CRUD operations

note that because of the redirect issue, flask code on the virtual servers will currently need to use my hack ('vs_url_for') rather than url_for

first: clone the lab-14 repo

you will need the /dump and dump.sql files from http://gitlab.doc.gold.ac.uk/data-networks-web/lab-exercises/tree/master/lab-14

so when you're in the lab repo, do 'git pull origin master'

mongo setup

is mongo installed?

check if you have mongo installed

mongo --version

if not, follow sorrel's instructions from lab 5: http://gitlab.doc.gold.ac.uk/data-networks-web/lab-exercises/wikis/web-server

if mongo is installed, check that it is running

sudo systemctl status mongodb

restore the mongo dump from the repo:

mongorestore

you can invoke the mongo shell by typing

mongo

check the mywits db

show dbs
use mytwits
db.twits.find()

here's a good crib sheet of mongo commands https://docs.mongodb.com/manual/reference/mongo-shell/#command-helpers

to use mongo with flask you will also need to install pymongo, so you can use python with the database api. installing pymongo is also covered in last terms lab slides.

http://gitlab.doc.gold.ac.uk/data-networks-web/lab-exercises/wikis/database-apis

mysql setup

is mysql installed?

if you chose the (default) webserver option when you created your virtual server, you will already have mysql installed.

if you chose the minimal option when you created your virtual server, you will have mysql if you installed it last term.

is mysql installed?

mysql --version

only if mysql is not installed, follow the instuctions to install mysql WARNING: do this only if you do not already have mysql installed.

https://www.linode.com/docs/databases/mysql/install-mysql-on-ubuntu-14-04/

i recommend you install mysql version 5.7, so the command would be

 sudo apt-get install mysql-server-5.7

create a mytwits database

you need to login to your mysql console.

if you have the webserver vs,

sudo mysql -u root

if you installed mysql yourself,

mysql -u root -p

& use your password.

create database mytwits;

now load the sql provided in the repo:

use mytwits
source dump.sql;

note: this will only work if you are in the lab-14 folder of the repo or if you have copied these files to your own repo

to check the data has been imported:

use mytwits;
select * from twits;

to see the constraints (e.g. foreign key)

show create table twits;

setting up a mysql user

it's not a good idea to use your root user for regular mysql access. we will set up a new user.

you need to login to your mysql console.

if you have the webserver vs,

sudo mysql -u root

if you installed mysql yourself,

mysql -u root -p

& use your password.

note that each of the following lines is a single mysql shell command:

CREATE USER 'mytwits_user'@'localhost' IDENTIFIED BY 'mytwits_password';

GRANT ALL PRIVILEGES ON mytwits.* TO 'mytwits_user'@'localhost';

FLUSH PRIVILEGES;

to check privileges:

show grants for 'testuser'@'localhost';

you can now use this user for db access in your app.

pymysql

you also need the pymysql package as yout python api for mysql:

pip3 install --user pymysql

mytwits

we are going to create a mock twitter application.

TASK 1: display the twits

the first task is to access the twits and display them.

you should do this for both the mongo and mysql versions.

in the lab-14 repo, the code for this is mytwits_mongo_1.py and mytwits_mysql_1.py

to access and display the twits in the mongo db:

• create a DBHelper() class
• in __init__ set up a connection to the database

using pymongo:

def __init__(self):
    client = pymongo.MongoClient()
    self.db = client['mytwits']

using mysql:

def __init__(self):
    self.db = pymysql.connect(host='localhost',
        user='<your username>',
        passwd='<your password>',
        db='mytwits')

• add a get_all_twits method

• for mongo you can use

  self.db.twits.find()

to access and display the twits from the mysql db:

• set up a connection to the database using pymysql

• write a get_all_twits method

• for mysql you need a query string like

  "select u.username, t.twit, t.created_at from twits t, users u where t.user_id=u.user_id order by t.created_at desc;"

• execute using cursor

• return fetchall: this returns results as a list of tuples

• so the whole method looks something like

    query = "select u.username, t.twit, t.created_at from twits t, users u where t.user_id=u.user_id order by t.created_at desc;"
    with self.db.cursor() as cursor:
        cursor.execute(query)
        return cursor.fetchall()

TASK 2: adding twits

in the lab-14 repo, the code for this is mytwits_mongo_2.py and mytwits_mysql_2.py

• create a simple form to add a twit; this should POST the form to a route like /add_twit
• create an add_twit method in the DBHelper class, which takes the twit as a parameter
• create an add_twit() view for this route which calls the db.add_twit method
• important: as we are not yet using sessions to track the user, just set the user as a default value

TASK 3: adding twits using a flask-wtf form

in the lab-14 repo, the code for this is mytwits_mongo_3.py and mytwits_mysql_3.py

• convert your code for adding twits to use flask-wtf
• use your learning from the week-13 lab
• import what you need from flask-wtf
• create a class for your form
• create a template that uses the form to generate the fields
• instantiate that class in your add_twit method
• check if the form validates; if it does, add the twit to the db
• remember to pass the form to your template

TASK 4 (extension)

in the lab-14 repo, the code for this is mytwits_mysql_4.py

• add methods, views, and if necessary templates, to edit and delete the tweets

that's all folks!