|
|
## 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! |