Lab Objectives

The objectives of this lab are to learn:

■  how to change from SQLite to MySQL db, and use it;

■  how to deploy your website on OpenShift.

PRELIMINARIES

- As before, this exercise is not assessed, but you should complete all tasks.

-  VPN connection is required to connect to MySQL, OpenShift, and phpMyAdmin.

- if you are not on the University network.

- The School’s MySQL server is available at csmysql .cs .cf .ac .uk . You should use this for all work in this module(1).

- As for all labs, you should do all the work with your virtual environment activated.

-  Review the RELTMTNARTES section in Flask 1 instructions - these are relevant to this lab too.

MySQL: Prerequisites

√  Make sure you have your MySQL account already set up.

●  If not, you need to go to https://dbmanager.cs.cf.ac.uk/mysql/ to create a new account. (You need to use VPN to access this resource when connecting from non-university network.)

● On that page, you can also create up to 5 databases on your account.

● Your MySQL account is not the same as your normal Cardiff university one, and you should set up a different password for this account. You can reset your MySQL password by going to https://dbmanager.cs.cf.ac.uk/mysql/ .

●  Further information on MySQL in the School is found at https://wiki.cs.cf.ac.uk/index.p hp?title=MySQL_in_the_School_of_Computer_Science_%26_Informatics .

You can interact with the MySQL server in several ways, using:

- web-based front end phpMyAdmin, available at https://phpmyadmin.cs.cf.ac.uk;

- terminal-based SQL command interface to MySQL (see quick guide: https://wiki.cs. cf.ac.uk/index.php?title=Accessing_MySQL_from_the_mysql_command);

-  GUI front end called “MySQL Workbench” (see: https://wiki.cs.cf.ac.uk/index.p hp?title=Accessing_MySQL_from_MySQL_Workbench).

NB: A worksheet, containing exercises on how to create, use and manage MySQL databases

along with accompanying video workthrough is available .  These are optional, but useful to

get a better understanding of how a MySQL db can be managed.

CHANGING DB to MyQL

So far, we  have  used SQLite db.   This was good enough for testing development of the

website locally.  To enable better control whilst deploying our blogging website on the remote

server, we will change our database to MySQL. Similar to Flask 2 Lab, we will be using Flask- SQLAlchemy(2) to manage connection to our db together with PyMySQL driver(3).

1.  If you haven’t done it yet, create a MySQL db to be used for your blogging website , e.g.

flask_lab_db

2.  Install pymysql .

3.  In __init__ .py , change the setting for the db connection by replacing :

app .config[ 'SQLALCHEMY_DATABASE_URI ' ]  =   'sqlite:/// '  +  os .path .join(basedir ,

二→        'blog .db ' )

with:

app .config[ 'SQLALCHEMY_DATABASE_URI ' ]  =

二→        'mysql+pymysql://USERNAME:PASSWORD@csmysql .cs .cf .ac .uk:3306/USERNAME_DATABASE_NAME '

To Note:

●  NB: USERNAME is the one that starts with the lowercase  ’c’ ; PASSWORD is your MySQL password (not your Cardiff university network one!); and USERNAME_DATABASE_NAME is the name of your db prefixed with your username, e.g. c123456_flask_lab_db . All are case sensitive!

● The following lines can now be deleted as they are no longer needed:

...

import  os

...

basedir  =  os .path .abspath(os .path .dirname(__file__ ))

...

4.  Using the db schema specified in models .py , create the two db tables, using python shell commands - see Flask 2 Task 6.

5.  Populate the db with some suitable data, using sql commands or phpMyAdmin GUI. page

DEPLYOMENT ON OPENSHIFT

To deploy our Flask website we will be using our School’s OpenShift server (https://console. openshift.cs.cf.ac.uk/).(4)

Initial Setup and Checks

6.  Check you have requirements .txt file in the root dir of your project, and all the necessary libraries are listed in this file.  If unsure, confer with the latest snapshot in the Flask Labs repository (https://git.cardiff.ac.uk/scmne/flask-labs)

7. We will be using Gunicorn(5) to deploy our flask website on OpenShift, so you need to make sure gunicorn is added to your requirements .txt .

8.  Edit wsgi .py file so the the first line reads as: from  blog  import  app  as  application

9.  Make sure you already have all the project files in the appropriate directories.

10.  Commit changes locally, push to the remote repo on GitLab, and check that all the files from the local directory have been successfully pushed.

Project on OpenShift

11.  Connect to VPN - if you’re not on the University network.

12.  Go to our OpenShift server, https://console.openshift.cs.cf.ac.uk/(6), and log in with your University network credentials.

13.  Create a project, by following the instructions in "Creating a Project" and "Adding Your SSH Key" sections on https://wiki.cs.cf.ac.uk/index.php?title=Using_OpenShift page.

14.  Click on +Add menu item on the left, and select From Git option.

Go to your remote repo’s home page on Gitlab .  Copy your repo’s URL by clicking on

Clone and then Clone  with  SSH . The URL should be in the format: [email protected]:USERNAME//REPO\_NAME.git

(b)  Paste the copied URL into Git Repo URL textfield.  (You will get a warning message

"URL is valid but cannot be reached.  If this is a private repository, enter a source Secret

in advanced Git options".)

(c)  Click on Show advanced Git options.

(d)  Expand Select Secret name dropdown menu, and click on the Secret you created as part of creating your project in the previous task (No. 13).