Connecting MySQL to Flask Web App

Flask can use sqlite for its database, this would be ideal for most situations. Sqlite is serverless and creates the database in a file contained within the project. This would work well if a single copy of the app exists and is accessed by everyone, and you want the app and data on the same server. SQL wise sqlite supports practically everything, but there are some things not supported (you are unlikely to encounter any of these). You could therefore install MySQL support instead.

Adding MySQL Support

Download the following zip file and extract it into your site-packages folder. If you are using your own machine installing flask-mysql an pymysql will do the same thing.

We need to run the USBWebserver, in College this must be on your compiled storage folder (download it HERE). When you run the executable you should get 2 firewall messages, click allow. If you get no firewall messages on the first run, then your system is preventing it from running:

This shows USBWebserver running and fully active. Now click PHPmyAdmin button to open PHPmyAdmin:

Now click on database and enter a name to create a new database:

You should now be able to add the following code to import MySQL and connect to a database:

from flaskext.mysql import MySQL

mysql = MySQL()
 
# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'usbw'
app.config['MYSQL_DATABASE_DB'] = 'Test'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

conn = mysql.connect()

Database Class

A good way of handling databases is to put all of the code into a class. I have created this so far:

class Database:
    app.config['MYSQL_HOST'] = 'localhost'
    app.config['MYSQL_USER'] = 'root'
    app.config['MYSQL_PASSWORD'] = 'usbw'
    app.config['MYSQL_DB'] = 'test'

    def connect(self, mysql):
        try:
            conn = mysql.connect()
            return conn
        except:
            print("connection error")
            return None


    def execute(self, sql):
        try:
            c = self.connection.cursor()
            c.execute(sql)
            self.connection.commit()
            self.connection.close()
            return True
        except:
            print("execute error")
            return False

    def select(self, sql):
        try:
            cur = self.connection.cursor()
            cur.execute(sql)
            return cur.fetchall()
        except:
            print("select error")
            return False
    
    def __init__(self):
        self.connection = self.connect(MySQL(app))
        c = self.connection.cursor()
        c.execute("use "+ app.config['MYSQL_DB'])

The connection fails to set the database to use, the last 2 lines of the __init__ will do this instead. It also contains a method to run the execute style commands (ie create, insert, update, delete) and a method to run the retrieve style commands (ie select). This method will allow you to change the connection methods at a later date without needing to change any of the web app code.

You could extend this by creating individual methods for 'CheckLogin', or 'GetProduct' or 'GetQuiz', alternatively the sql could be within your routes. This will keep all of the database code in one place.

Creating a table

I have added the following code to create a new route called 'create':

@app.route('/create')
def createuserdb():
    db = Database()
    sql = """CREATE TABLE IF NOT EXISTS users(
                UserID varchar(9) PRIMARY KEY,
                UserPass varchar(16) NOT NULL,
                UserEmail varchar(255) NOT NULL
                )"""
    check = db.execute(sql)

MySQL uses a specific set of data types, these are different from sqlite.