This is the article to let you display the concept of database connectivity using FLask and Mysqlclient. Mysqlclient is basically a Python library. MySQL is realational database. If you want to connect with database using non-relational database you may want to view following article

MongoDB with Python Flask using PyMongo

This was a brief introduction with the article. Now, as per prequsite you may want to look at following articles before beginning with this article.

1. Setting Up and Running Python Flask Project

2. Introduction to Python Flask and Jinja2

I will create single pages web application using flask and mysql. I won't be using any templating here, rather use request to insert and view data. I have provide enough content and description so you can follow along.

Download MySQL

You can download MySQL from official site of MySQL. If you just want to quickly go to downloag page follow

https://dev.mysql.com/downloads/mysql/

 

Starting MySQL Server

Following code will be valid for linux versions in particular. Please look at setup and running guide to run MySQL in different versions

 $ sudo service mysql start # for ubuntu

 

More packages required for running MySQL

Mysql can be little complex during setup. So to make sure it runs smoothly  

Python 2

 $ sudo apt-get install python-dev libmysqlclient-dev # for Debian/Ubuntu 
 $ sudo dnf install python-devel mysql-devel # for Fedora $ brew install mysql-connector-c # for Mac OS

 

Python 3

 $ sudo apt-get install python3-dev libmysqlclient-dev # for Debian/Ubuntu 
 $ sudo dnf install python3-devel mysql-devel            # for Fedora

 

Installing all required packages

Once you have MySQL in place, we need Flask and mysqlclient to connect with MySQL database

 $ pip install flask 
 $ pip install mysqlclient

 

Checkincessary but I wouldn't recommend that.


$ mysql -u root -p 
(Your successful password will log ye>
 $ pip freeze

You will see mysqlclient listed on the installed packages

 

Creating a TestUser to Begin with

As MySQL Database is more demaning in terms of setting. If you like to create a user for this particular purpose please login to MySQL by your root account and create new account as shown below. If you want to use your root account and password this step is not necessary but I wouldn't recommend that.


$ mysql -u root -p 
(Your successful password will log you to mysql and you can create new user by) 
mysql> GRANT ALL ON testflask.* To 'testuser'@'localhost' IDENTIFIED BY 'testpassword'; 
mysql> quit

 

Creating Simple Schema to begin with our testuser

We need simple user table to work with our code proces. All data will go to this table from our flask application.


$ mysql -u testuser -p 
(Your successful password will log you to mysql with current testuser) 
mysql> CREATE DATABASE testflask; 
mysql> USE testflask; 
mysql> CREATE TABLE users( 
-> id INT(11) PRIMARY KEY AUTO_INCREMENT, 
-> username VARCHAR(20) NOT NULL, 
-> firstname VARCHAR(20) NOT NULL, 
-> lastname VARCHAR(20) NOT NULL);
mysql> quit

 

Writing code

The main concept of this tutorial is to get you through quick feel of Flask and MySQL Database. I could have created views get template in place but this approach will help to grab quick knowledge to play with data.

If you like to view GitHub version of code Click Here

OR

Create any filename similar to app.py which we will use to run later

from flask import Flask
 
import MySQLdb
import gc
 
app = Flask(__name__)
 
app.debug = True
 
# database connection method
def connection():
conn = MySQLdb.connect(
host = "localhost",
user = "testuser",
passwd = "testpassword",
db = "testflask"
)
 
c = conn.cursor()
return c, conn
 
c, con = connection()
 
# default routing provies reference to all routes
@app.route("/")
def hello():
    return 'Please follow links to make use of MySQL Database:
' \
        '1. /get/ - get all users
' \
        '3. /username/ - get particular user
' \
        '2. /delete/username/ - delete user with username
' \
        '3. /insert/username/firstname/lastname/ - insert user
' \
 
# getting all registered user data
# e.g. http://localhost:5000/get/
@app.route("/get/")
def get_data():
 
c.execute("SELECT * FROM users")
users = c.fetchall()

 

data = 'Name of Users:
'
for user in users:
data = data + user[1] + ': ' \
+ user[2] + user[3] + '
'

 

gc.collect()

 

return data
 
# insert user with username, firstname and password
# e.g. http://localhost:5000/insert/jeevan/Jeevan/Pant/
@app.route("/insert/")
@app.route("/insert////")
def insert_data(username=None, firstname=None, lastname=None):
if username != None and firstname != None and lastname != None:
c.execute('INSERT INTO users (username, firstname, lastname) VALUES(%s, %s, %s)',[
username,
firstname,
lastname
])
 
con.commit()
gc.collect()

 

return 'Data inserted successfully: ' + username + ', ' \
+ firstname + ' ' + lastname
else:
     return 'Data insufficient. Please try again!'
 
# delete user
# e.g. http://localhost:5000/delete/jeevan/
@app.route("/delete/")
@app.route("/delete//")
def delete_data(username=None):
if username != None:
try:
c.execute("SELECT * FROM users WHERE username=%s", [username,])
user = c.fetchone()

 

c.execute("DELETE FROM users WHERE username=%s", [
username
])

 

con.commit()
gc.collect()
return 'Data delected successfully with useraname: ' + username
except:
return "User couldn't be found"
else:
return 'Provide data to delete. Please try again!'
 
# get specific user
# e.g. http://localhost:5000/jeevan/
@app.route("//")
def users(username):
try:
c.execute("SELECT * FROM users WHERE username=%s", [username])
user = c.fetchone()
gc.collect()
return user[2] + ' ' + user[3]
except:
return "User couldn't be found"
 
if __name__ == "__main__":
app.run()

Description/Conculusion

 

In above program there are various functions to carry insert, read and delete. To update your data you can use update query which was left as practice assignment.