MySQL x Flask: Add MySQL database with Flask App
Tags: database, flask, frontend, python
Published: 2023-10-22 05:49:34

**MySQL** is a widely used open-source relational database known for its performance, reliability, and scalability. It is suitable for various types of software applications, including web applications, e-commerce platforms, and content management systems.

In this article, you'll learn how to create and integrate a MySQL database with a Flask application using the [**PyMySQL**](https://pymysql.readthedocs.io/en/latest/index.html) driver, which provides convenient access to MySQL databases within the **Flask** framework.

## Pre-Requisite

You must have Python, Flask, and MySQL installed in your system and have a basic understanding of the Flask framework, Jinja templating, and SQL.

## Install PyMySQL Package

You must have the [**PyMySQL**](https://pymysql.readthedocs.io/en/latest/user/installation.html) package installed in your project environment before proceeding with this tutorial.

To install the PyMySQL package in your local or [virtual environment](https://geekpython.in/how-to-create-virtual-environments-using-venv-in-python), open a terminal window and type the following command. It will be used to create and connect the MySQL database to your Flask app.

```bash
pip install PyMySQL
```

## Creating MySQL Database using PyMySQL

The **PyMySQL** library makes it easy to interact with **MySQL databases**. It enables Python applications to connect to and manipulate MySQL databases. In this section, you'll use the **PyMySQL** library to create a MySQL database.

Unlike SQLite database, you need to set a **username**, **password**, and **hostname** of the MySQL database using the configuration keys.

```python
import pymysql

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

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

# Creating cursor object
cursor = db.cursor()

# Executing SQL query
cursor.execute("CREATE DATABASE IF NOT EXISTS books_db")
cursor.execute("SHOW DATABASES")

# Displaying databases
for databases in cursor:
    print(databases)

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

The `pymysql` library has been imported and will be used to connect to the MySQL server and create the database.

The connection to the MySQL server is initialized using the `pymysql.connections.Connection` object with required credentials such as `hostname`, `username`, and `password`. The instance is then stored inside the `db` variable.

The cursor object of the `db` is created and stored inside the `cursor` variable to interact with the MySQL database.

The first SQL query creates a `books_db` database on the MySQL server, if no such database exist.

The second SQL query uses the `SHOW DATABASES` statement to retrieve a list of databases on the MySQL server. This query's output is then iterated over and printed.

In the end, the cursor and database connection is closed using the `cursor.close()` and `db.close()` respectively.

```bash
('books_db',)
('information_schema',)
('mysql',)
('performance_schema',)
('sakila',)
('sys',)
('world',)
```

As can be seen in the output, a database named `books_db` is created on the MySQL server.

## Integrating MySQL Database with Flask App

In this section, you'll learn to **integrate MySQL database with your Flask app** and create a model using **SQLAlchemy**.

### Flask App and SQLALchemy Setup

```python
from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy

# Creating Flask app
app = Flask(__name__)

# Creating SQLAlchemy instance
db = SQLAlchemy()
```

The above code imports the `Flask` class as well as the `flask` module's `render_template`, `request`, `redirect`, and `url_for` methods and functions. The `SQLAlchemy` is then imported from the `flask_sqlalchemy` module.

> You need to install the Flask-SQLAlchemy library if you haven't installed already.
> 
> Run `pip install Flask-SQLAlchemy` command in your terminal to install this dependency.

The Flask app is created by instantiating the `Flask` class with the value `__name__` and storing it in the `app` variable.

The SQLAlchemy (`SQLAlchemy()`) instance is created and stored inside the `db` variable.

### Configuring MySQL Database Connection URI

```python
user = "root"
pin = "your_password"
host = "localhost"
db_name = "books_db"

# Configuring database URI
app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{user}:{pin}@{host}/{db_name}"

# Disable modification tracking
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
```

The database connection parameters are defined first. These variables store the database connection parameters, such as MySQL username (`user`), MySQL password (`pin`), hostname (`host`), and database name (`db_name`) to connect to.

The following line of code sets up the SQLAlchemy database URI to connect to the MySQL database. The URI is broken down as follows:

* `mysql+pymysql://`: This means that the `PyMySQL` driver is to be used.
    
* `username`: This is to specify the username and this will be replaced with the value of the `user` variable.
    
* `password`: This will be replaced with the value of the `pin` variable.
    
* `hostname`: This will be replaced with the value of the `host` variable.
    
* `database_name`: This will be replaced with the value of the `db_name` variable.
    

In the end, the modification tracking is disabled using the `app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False` configuration.

### Initializing Flask App with SQLAlchemy

```python
# Initializing Flask app with SQLAlchemy
db.init_app(app)
```

The SQLAlchemy instance (`db`) is initialized using the `init_app()` method with the Flask app (`app`).

### Creating a Database Model

```python
# Creating Models
class Books(db.Model):
    __tablename__ = "books"

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(500), nullable=False, unique=True)
    author = db.Column(db.String(500), nullable=False)
```

The code creates a SQLAlchemy model class (`Books`) that represents a database table. The `__tablename__` specifies the name of the table (`books`).

Within the table, three columns are created: **id**, **title**, and **author**. The **db.Column** function is used to define database columns.

* `id`: The `id` column is of type integer (`db.Integer`) and is a primary key (`primary_key=True`) which means it will be unique for each row.
    
* `title`: It is of type string (`db.String`) and it cannot be left empty (`nullable=False`) and it will contain unique values (`unique=True`).
    
* `author`: It is of type string (`db.String`) and cannot be left empty (`nullable=False`).
    

### Creating Table in MySQL Database

```python
def create_db():
    with app.app_context():
        db.create_all()
```

The above code defines the function `create_db()`, which is used to create a table (`books`) within the MySQL database (`books_db`). Flask app (`app`) is run within the application context within this function to ensure that it has access to the Flask app and its configuration.

Within the context block, `create_all()` method is called on the SQLAlchemy instance (`db`) to create the table defined by the SQLAlchemy model class (`Books`).

## Database Operation - Adding Data Using Flask App

Now that the MySQL database and table have been created, it is time to see if they work and if data can be added to the database. You can do this manually with MySQL workbench or other applications, but you'll do it programmatically with the Flask framework on the frontend.

### Creating Backend Logic

```python
# Home route
@app.route("/")
def home():
    details = Books.query.all()
    return render_template("home.html", details=details)


# Add data route
@app.route("/add", methods=['GET', 'POST'])
def add_books():
    if request.method == 'POST':
        book_title = request.form.get('title')
        book_author = request.form.get('author')

        add_detail = Books(
            title=book_title,
            author=book_author
        )
        db.session.add(add_detail)
        db.session.commit()
        return redirect(url_for('home'))

    return render_template("books.html")
```

The above code defines two routes "**Home**" and "**Add data**" route for displaying and adding the books detail.

**Home Route:**

* The user can access the Home route by using the root URL `"/"`, and when the user accesses the root URL, the `home()` function is executed.
    
* Using `Books.query.all()`, the `home()` function retrieves all records from the database table.
    
* The `details` variable is then used to pass all of the records to the `home.html` template, and the template is then rendered.
    

**Add data Route:**

* Users can add data to the database table by visiting the `"/add"` URL path, which accepts both `GET` and `POST` requests.
    
* When users enter the URL path or make a GET request, the `books.html` template is rendered, which includes a form with two fields.
    
* The `add_books()` function is defined, and this function handles the user's `POST` request.
    
* The `request.form.get()` method is used to retrieve the value of the `title` and `author` from the form, which is then passed to the database table fields (`title` and `author`).
    
* Then, using the `db.session.add()` method, all the details are added to the database, and the changes are committed to the MySQL database using the `db.session.commit()` method.
    
* When all of this is completed, users are redirected to the home page via the `url_for()` method.
    

### Running the Script

```python
if __name__ == "__main__":
    create_db()
    app.run(debug=True)
```

The above code will call the `create_db()` function, which will create the table within the database and the `app.run()` will launch the Flask app on the localhost server.

However, first you must create the HTML template for the frontend.

## Creating Frontend using BootStrap

Create a directory named `templates` in the root of your project directory and within this directory create three HTML files:

* **base.html**
    
* **home.html**
    
* **books.html**
    

Using the Jinja templating, the data from the database will be displayed.

**base.html**

```xml
<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">

    <title>{% block title%} {% endblock %}</title>
  </head>
  <body>
    {% block content %} {% endblock %}

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p" crossorigin="anonymous"></script>
  </body>
</html>
```

This HTML file contains the **Bootstrap CSS and JavaScript CDN** (Content Delivery Network). You can see some Jinja templating, this is for injecting content in the body section and dynamic title without using the basic HTML structure again.

**home.html**

```xml
{% extends 'base.html' %}

{% block title %}
Home of Books
{% endblock %}

{% block content %}
<h1 class="text-center my-5">📚Books Detail📚</h1>
<div class="container d-flex justify-content-center align-items-center">
    <a class="btn btn-outline-info mb-3" href="{{ url_for('add_books') }}">Add Books</a>
</div>
<div class="container">
    <table class="table table-dark table-striped">
        <thead>
        <tr>
            <th scope="col">ID</th>
            <th scope="col">Book Title</th>
            <th scope="col">Author</th>
        </tr>
        </thead>
        {% if not details%}
        <div class="text-center">
            <h3 class="my-5">No Records to Display!</h3>
        </div>
        {% else %}
        <tbody>
        {% for data in details %}
        <tr>
            <th scope="row">{{data.id}}</th>
            <td>{{data.title}}</td>
            <td>{{data.author}}</td>
        </tr>
        {% endfor %}
        </tbody>
        {% endif %}
    </table>
</div>
{% endblock %}
```

In this template, `base.html` template is used as a layout using `{% extends 'base.html' %}` and page title is set with the `title block` and within the `content block`, data stored in the database (`details`) is iterated and displayed in the table format using the Bootstrap.

**books.html**

```xml
{% extends 'base.html' %}

{% block title %}
Add Books
{% endblock %}

{% block content %}
<h1 class="text-center my-5">📚Book Details📚</h1>
<div class="container">
  <a href="{{ url_for('home') }}" class="btn mb-3 btn-outline-info">Go to Home</a>
  <form action="/add" method="POST">
<div class="mb-3">
  <label for="title" class="form-label">Title</label>
  <input type="text" class="form-control" name="title" id="title" placeholder="Title of the book" required>
</div>
<div class="mb-3">
  <label for="author" class="form-label">Author</label>
  <input type="text" class="form-control" name="author" id="author" placeholder="Author of the book">
</div>
  <button type="submit" class="btn mt-3 btn-outline-success">Add Book</button>
  </form>
  </div>
{% endblock %}
```

This HTML file contains a form with two input fields (**Title** and **Author**) and a submit button ("**Add Book**"). This form will be submitted on the `"/add"` URL (`action="/add"`) and "`method="POST"`" specifies that `POST` HTTP method to be used.

## Adding Data Using Frontend

Run your `app.py` file, or whatever you named your main Flask app file, and navigate to `http://localhost:5000` to access the frontend.

You will see a homepage with no records displayed, as shown in the image below. You must first enter the data on the `"/add"` URL or by clicking the "**Add Book**" button on the homepage.


![Empty homepage](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8bn1qft8ouxty3pink58.png)

This is the page where you will enter your book information and submit the form. The submitted information will be entered into the database.


![Details filled in the form](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nkw4y4nypbfdc60fhsub.png)

After clicking the "**Add Book**" button, you'll be taken to the homepage, where your entered data will be displayed.


![Books detail displayed on the homepage](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z4k7g2m6x4qb18jvxpwb.png)

## Conclusion

The `PyMySQL` driver is used in this tutorial to connect to the **MySQL server** and create the MySQL database after connecting to the MySQL server by executing the raw SQL query.

The Flask app then defines the database connection URI string, and SQLAlchemy is initialised with the Flask app.

The **SQLAlchemy** is then used to create table within the database in an object-oriented way using Python class. The backend is designed to handle database operations, while the frontend is designed to add data to the MySQL database and display it on the homepage.

---

🏆**Other articles you might be interested in if you liked this one**

✅[How to create and connect SQLite database within Flask app](https://geekpython.in/connect-sqlite-database-with-flask-app).

✅[Structure your Flask app with Flask Blueprint](https://geekpython.in/structure-flask-app-with-blueprint).

✅[Upload and display images on the frontend using Flask](https://geekpython.in/render-images-from-flask).

✅[What is session in Flask and how to use it for storing temporary details on server](https://geekpython.in/how-to-use-sessions-in-flask).

✅[How to display user specific messages on the frontend using flash in Flask](https://geekpython.in/flash-messages-on-frontend-using-flask).

✅[How to create a database on Appwrite cloud using only Python](https://geekpython.in/database-in-appwrite-using-python).

---

**That's all for now**

**Keep Coding✌✌**