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