How To Use MySQL Database With Flask?

Flask is a lightweight web application framework known for its simplicity and elegance. Unlike some other frameworks, Flask allows developers the freedom to choose the tools and libraries that best suit their requirements. Additionally, Flask is well-suited for single-page web applications, utilizing side tabs to present different content sections seamlessly. Developers often use flask with MySQL to create dynamic and data-driven websites.

Download Your Guide to Essential Best Practices for Post-Load Transformations

Download for Free

This blog is tailored to anyone aspiring to create dynamic websites using the Python Flask MySQL combination. By the end of this article, you will gain a moderate level of expertise in harnessing the power of Python and Flask MySQL connections.

Setting Up Flask MySQL Database

Understanding how to connect Flask with MySQL is essential for creating dynamic and data-driven web applications.

Here are the 4 steps to get started with Flask MySQL Database Connection:

• Flask MySQL Step 1: Connecting a Flask Application to a MySQL Database
• Flask MySQL Step 2: Configuring the MySQL Connection Cursor
• Flask MySQL Step 3: Programming a Flask Application
• Flask MySQL Step 4: Putting the Code into Action

Flask MySQL Step 1: Connecting a Flask Application to a MySQL Database

Before attempting to connect Flask to MySQL, ensure the MySQL server is running and accessible to avoid any potential connection issues.

The following is the procedure we use to connect Flask MySQL

from flask import Flask,render_template, request
from flask_mysqldb import MySQL
 
app = Flask(__name__)
 
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'flask'
 
mysql = MySQL(app)

Flask MySQL Step 2: Configuring the MySQL Connection Cursor

We can’t interact with DB tables with the setup described above. We’ll need something called a cursor for that.

Cursor thus provides a means for Flask to interact with the database tables. It can scan the database for data, execute SQL queries, and delete table records.

The cursor is employed in the following manner:

mysql = MySQL(app)
 
#Creating a connection cursor
cursor = mysql.connection.cursor()
 
#Executing SQL Statements
cursor.execute(''' CREATE TABLE table_name(field1, field2...) ''')
cursor.execute(''' INSERT INTO table_name VALUES(v1,v2...) ''')
cursor.execute(''' DELETE FROM table_name WHERE condition ''')
 
#Saving the Actions performed on the DB
mysql.connection.commit()
 
#Closing the cursor
cursor.close()

Because MySQL is not an auto-commit DB, we must manually commit, i.e. save the changes/actions performed by the cursor execute on the DB.

Flask MySQL Step 3: Programming a Flask application

Now we’ll create a small Flask application that will store user-submitted data in the MySQL DB Table. Take a look at the following Application Code:

from flask import Flask,render_template, request
from flask_mysqldb import MySQL
 
app = Flask(__name__)
 
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'flask'
 
mysql = MySQL(app)
 
@app.route('/form')
def form():
    return render_template('form.html')
 
@app.route('/login', methods = ['POST', 'GET'])
def login():
    if request.method == 'GET':
        return "Login via the login Form"
     
    if request.method == 'POST':
        name = request.form['name']
        age = request.form['age']
        cursor = mysql.connection.cursor()
        cursor.execute(''' INSERT INTO info_table VALUES(%s,%s)''',(name,age))
        mysql.connection.commit()
        cursor.close()
        return f"Done!!"
 
app.run(host='localhost', port=5000)

When the user submits the data, the cursor inserts it into the MySQL DB.

Command to be executed Info table is the name of my table.

The form.html will be as follows:

<form action="/login" method = "POST">
   <p>name <input type = "text" name = "name" /></p>
   <p>age <input type = "integer" name = "age" /></p>
   <p><input type = "submit" value = "Submit" /></p>
</form>

Flask MySQL Step 4: Putting the Code into Action

• Now start the server and navigate to “/form”
• Enter the information and press the Submit button.
• Let’s take a look at it in the phpMyAdmin web interface now.

This concludes the setting up of Python Flask MySQL database connection.

Using PyMySQL to Integrate MySQL Database with Flask App

Step 1: Install the PyMySQL package

The PyMySQL package will allow us to connect to and interact with MySQL databases from our Flask app. To install the PyMySQL package, we can use the following command:

pip install PyMySQL

Step 2: Create a MySQL database

We will create a MySQL database named books_db using the PyMySQL library and the CREATE DATABASE statement. We will also check if the database exists on the MySQL server using the SHOW DATABASES statement. To do this, we can use the following Python script:

import pymysql

hostname = 'localhost'
user = 'root'
password = 'your_password'

db = pymysql.connections.Connection(
    host=hostname,
    user=user,
    password=password
)

cursor = db.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS books_db")
cursor.execute("SHOW DATABASES")

for databases in cursor:
    print(databases)

# Closing the cursor and connection to the database
cursor.close()
db.close()

Step 3: Configure the Flask app to connect to the MySQL database

We will configure the Flask app to connect to the MySQL database using the app.config dictionary and the SQLALCHEMY_DATABASE_URI key. We will also import the SQLAlchemy module and create an instance of the SQLAlchemy class with the Flask app as an argument. To do this, we can use the following Python code:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# Configuring the Flask app to connect to the MySQL database
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:your_password@localhost/books_db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# Creating an instance of the SQLAlchemy class
db = SQLAlchemy(app)

Step 4: Define a model for the database table

We will define a model for the database table using the SQLAlchemy declarative base and the db.Model class. We will specify the table name, the columns, and their data types using the db.Column class. We will also define a constructor method and a __repr__ method for the model. To do this, we can use the following Python code:

# Defining the Book model
class Book(db.Model):
    # Specifying the table name
    __tablename__ = 'books'

    # Specifying the columns and their data types
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    author = db.Column(db.String(50), nullable=False)
    genre = db.Column(db.String(20), nullable=False)

    # Defining the constructor method
    def __init__(self, title, author, genre):
        self.title = title
        self.author = author
        self.genre = genre

    # Defining the __repr__ method
    def __repr__(self):
        return f'<Book {self.title}>'

Step 5: Create the database table

We will create the database table using the db.create_all() method. This method will create all the tables defined by the models in the database. We will also check if the table exists on the MySQL server using the SHOW TABLES statement. To do this, we can use the following Python script:

from app import Book, db

# Creating the books table in the database
db.create_all()

# Checking if the table exists on the MySQL server
cursor = db.cursor()
cursor.execute("SHOW TABLES")

for tables in cursor:
    print(tables)

# Closing the cursor
cursor.close()

Setting Up MySQL Server

In this section, we will download and establish our MySQL server to be able to establish Python Flask and MySQL integration.

• XAMPP Installation on a Server
• Fire up Apache and MySQL
• Installing the Flask MySQL library

XAMPP Installation on a Server

• To use MySQL, we now need a software tool to handle MySQL administration over the web.
• You can use phpMyAdmin. You can also use other software if you are familiar with it.
• The PHPMyAdmin web interface is provided by the Xampp software. You can get XAMPP from this page.
• Alternatively, you can go directly to Google and search for download Xampp. The first link alone will suffice!!
• Download the version that is appropriate for your operating system and architecture.

Fire up Apache and MySQL

Start the following two processes once XAMPP has been installed and loaded:

• The Apache Webserver is used to serve HTTP requests.
• MySQL Server – the database server

• Keep in mind that the default MySQL port is 3306. Go to https://localhost in your browser now.

• This is the Xampp Host webpage. To access the PHP web interface, click on phpMyAdmin in the upper right corner.

• Here,
• By clicking new in the left column, you can create a new database.
• Maintain a suitable name for the database.

• Create a table in the database. Enter the table name in the space provided, as shown in the image, and press Go.

Related: Developing RESTful Flask APIs with Python: A Comprehensive Guide 101

Installing the Flask MySQL library

When it comes to integrating a MySQL database into a Flask web application, the Flask MySQLdb connector is a great choice. To use this connector, you’ll need to install the package using the following command:

pip install flask_mysqldb

Usage

Initialize the extension :

from flaskext.mysql import MySQL
mysql = MySQL()
mysql.init_app(app)

Obtain a cursor :

cursor = mysql.get_db().cursor()

This will give you access to all the tools you need to connect your Flask application to a MySQL database. With Flask MySQLdb, you can easily perform database operations and manipulate data using SQL queries – making it an essential tool for any Flask developer.

Conclusion

You learned how to set up Python Flask MySQL to integrate a MySQL database with your Python application in this blog.

You learned some programming along the way that will come in handy when it comes to establishing a connection, creating tables, and inserting and updating records in a database application.

You may want to go one step further and analyze the Webhooks data. This will require you to transfer data from the Webhooks account to a Data Warehouse using various complex ETL processes. Hevo Data will automate your data transfer process, allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. Sign up for Hevo’s 14-day free trial and experience seamless data migration.

FAQ on MySQL Database With Flask

Share your experience of setting up Python Flask MySQL in the comments section below!

References:

• Flask-MySQL — flask-mysql 1.4.0 documentation