Last Updated:

Flask Tutorial with MySQL

Overview:

For this first Tutorial in our Book List application series, we will be creating a flask application which connects to our MySQL Application in Python.

If you want to skip to the end of this tutorial and just download the project, you can find it here at https://github.com/BryaSoftware/BryaSoftware_Book_Tut1 Or simply:

git clone https://github.com/BryaSoftware/BryaSoftware_Book_Tut1.git

PSA:

This tutorial assumes you have basic knowledge of MySQL, if you for any reason need a bit of a revisit on your MySQL knowledge, then please check out my MySQL Tutorial Here.

Requirements:

MySQL - https://dev.mysql.com/downloads/installer/
Python 3- https://www.python.org/downloads/
P
ostman - https://www.getpostman.com/downloads/

pip install flask
pip install flask-cors
pip install flask-mysql

Now let's hope into this:

Step 1- Setup your MySQL Environment:

If you have done our previous MySQL tutorial, then you can skip this step, otherwise for this example its best to setup your DB environment to be compatible with our tutorial code snippets.
Please Create a user named bookBoss
Then run this code to create our Schema:

CREATE DATABASE IF NOT EXISTS `mybookdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `mybookdb`;
-- MySQL dump 10.13 Distrib 8.0.17, for Win64 (x86_64)
--
-- Host: localhost Database: mybookdb
-- ------------------------------------------------------
-- Server version 8.0.17

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `books`
--

DROP TABLE IF EXISTS `books`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `books` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`book_name` varchar(255) DEFAULT NULL,
`book_author` varchar(255) DEFAULT NULL,
`book_amazon_url` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `books`
--

LOCK TABLES `books` WRITE;
/*!40000 ALTER TABLE `books` DISABLE KEYS */;
INSERT INTO `books` VALUES (1,'Land of Eternal Ice','Jacqueline Bryant',NULL),(2,'Heir of Fire','Sarah J Maas',NULL),(3,'Throne of Glass','Sarah J Maas',NULL),(4,'Queen of Shadows','Sarah J Maas',NULL),(5,'Kingdom of Ash','Sarah J Maas',NULL),(6,'Crown of Midnight','Sarah J Maas',NULL),(7,'The Assassins Blade','Sarah J Maas',NULL);
/*!40000 ALTER TABLE `books` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-10-12 14:29:17
Step 2 - create our python files:

Our folder structure is quite simple, it will only contain 3 files:

  • dbConfig.py
  • books.py
  • app.py

A very basic overview of these files is as follows:
dbConfig.Py- Contains our Database connection details, in this instance it is our MySQL Connection settings.
books.py- Contains our Flask Get functionality, executes our SQL and returns a JSON object.
app.py - Contains our Routing and initialises the application.
Step 3 - populate our dbConfig.py file:

First of please make sure you have installed our requirements.
Now we will create our dbConfig.py file in our directory, this file will contain our MySQL connection details.

from flask import Flask
from flaskext.mysql import MySQL
from flask_cors import CORS

mysql = MySQL()
app = Flask(__name__)
CORS(app)

#Here we set our DB Connection properties
app.config['MYSQL_DATABASE_USER'] = 'bookBoss'
app.config['MYSQL_DATABASE_PASSWORD'] = 'BryaSoftware_Book123'
app.config['MYSQL_DATABASE_DB'] = 'MyBookDB' #Provide Database name, in previious tutorial we created MyBookDB database in MySQL
app.config['MYSQL_DATABASE_HOST'] = 'localhost' #This can be either localhost or a ip ie 192.133.33.33

mysql.init_app(app)
 Step 4- populate our books.py file

Our books.py file is where we put our code in for our GET request, for this example we will have no paramaters specified and it will return a json object with the sql field headers.
First we connect to the database and execute our defined SQL Statement, this example has basic error handling.

from flask_restful import Resource, Api, reqparse
from dbconfig import mysql
from flask import jsonify

class Books(Resource):
#for this tutorial we will only be setting up GET requests
def get(self):
try:
conn = mysql.connect()
cursor = conn.cursor()

#Execute SQL
cursor.execute('SELECT * FROM books')
#Dictionary object to contain sql field header name
bookResults = [dict((cursor.description[i] [0], value)for i, value in enumerate(row)) for row in cursor.fetchall()]
print (bookResults)
return jsonify(bookResults)
except Exception as e:
return {'error': str(e)} 

 Step 5 - Finish up our app.py file

For our final bit of coding we just need to populate our app.py file.Its a pretty self explanatory code snippet.

from books import Books
from dbconfig import app
from flask_restful import Api

@app.route("/")
def index():
#This is our default ROUTE, it will display name
name = "www.bryasoftware.com"
return name

api = Api(app)
#URL Extension
api.add_resource(Books, '/Books')

if __name__ == '__main__':
#host and port can be changed to your environment
app.run(debug=False, port=5000, host='localhost')

Finishing up:

Now run your python project by typing
python app.py

into our terminal which is currently set to our folder location.

Now run postman and simply do a Get request to the address of:

http://localhost:5000/Books

If you have followed this tutorial correctly then your results will appear as follows:

Postman snapshot

Thankyou for following this tutorial, in our next tutorial for this series we will be creating the foundation of our angular app which will connect to our Flask application.