CRUD with Flask and MySQL

Flask

October 15, 2022

CRUD with Flask and MySQL

Here, we will make a small web app, where the user can add, edit and delete information about books.

At first we will create the MySQL database and table.

The following code will create a database named ‘Books’ and a table named ‘Books’ with columns, id, name, writer and genre.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password"
)
print(mydb)

mycursor = mydb.cursor()
sql = "CREATE DATABASE IF NOT EXISTS Books"
mycursor.execute(sql)

sql = "USE Books"
mycursor.execute(sql)


sql = "CREATE TABLE IF NOT EXISTS Books (id INT(32) not null AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) not null,writer VARCHAR(255) not null,genre varchar(12) not null)"
mycursor.execute(sql)

mycursor.execute("SHOW TABLES")

for x in mycursor:
  print(x)

Flask main.py

from flask import Flask, render_template, request, redirect, url_for, flash
from flask_mysqldb import MySQL

# initializations
app = Flask(__name__)

# Mysql Connection
app.config['MYSQL_HOST'] = 'localhost' 
app.config['MYSQL_USER'] = 'user'
app.config['MYSQL_PASSWORD'] = 'password'
app.config['MYSQL_DB'] = 'Books'
mysql = MySQL(app)

# settings
app.secret_key = "mysecretkey"

# routes
@app.route('/')
def Index():
    cur = mysql.connection.cursor()
    cur.execute('SELECT * FROM Books')
    data = cur.fetchall()
    print(data)
    cur.close()
    return render_template('index.html', books = data)


@app.route('/add_book', methods=['POST'])
def add_book():
    if request.method == 'POST':
        name = request.form['name']
        writer = request.form['writer']
        genre = request.form['genre']
        cur = mysql.connection.cursor()
        cur.execute("INSERT INTO Books (name, writer, genre) VALUES (%s,%s,%s)", (name, writer, genre))
        mysql.connection.commit()
        flash('Book Added successfully')
        return redirect(url_for('Index'))

@app.route('/edit/<id>', methods = ['POST', 'GET'])
def get_book(id):
    cur = mysql.connection.cursor()
    cur.execute('SELECT * FROM Books WHERE id = %s', (id))
    data = cur.fetchall()
    cur.close()
    print(data[0])
    return render_template('edit.html', book = data[0])

@app.route('/update/<id>', methods=['POST'])
def update_book(id):
    if request.method == 'POST':
        name = request.form['name']
        writer = request.form['writer']
        genre = request.form['genre']
        cur = mysql.connection.cursor()
        cur.execute("""
            UPDATE Books
            SET name = %s,
                writer = %s,
                genre = %s
            WHERE id = %s
        """, (name, writer,genre, id))
        flash('Books Updated Successfully')
        mysql.connection.commit()
        return redirect(url_for('Index'))

@app.route('/delete/<id>', methods = ['POST','GET'])
def delete_book(id):
    cur = mysql.connection.cursor()
    cur.execute('DELETE FROM Books WHERE id = {0}'.format(id))
    mysql.connection.commit()
    flash('Book Removed Successfully')
    return redirect(url_for('Index'))

# starting the app
if __name__ == "__main__":
    app.run(port=5000, debug=True)

Code for layout.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8">
    <title>FLASK BOOK CRUD</title>
    <!-- BOOSTRAP 4 -->
    <link rel="stylesheet" href="https://bootswatch.com/4/materia/bootstrap.min.css">
    <link rel="stylesheet" href="{{url_for('static', filename='css/main.css')}}">
  </head>
  <body>

    <!-- NAVIGATION  -->
    <nav class="navbar navbar-dark bg-dark">
      <a class="navbar-brand" href="/">Flask Books App</a>
    </nav>

    <div class="container pt-4">
      {% block body %}
      {% endblock %}
    </div>

    <!-- footer -->
    <!-- scripts -->
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js" integrity="sha384-ZMP7rVo3mIykV+2+9J3UJ46jBk0WLaUAdn689aCwoqbBJiSnjAK/l8WvCWPIPm49" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
    <script src="{{url_for('static', filename='js/main.js')}}"></script>
  </body>
</html>

Code for index.html


{% extends "layout.html" %}
{% block body %}

  <div class="row">
    <div class="col-md-5">
      {% with messages = get_flashed_messages()  %}
      {% if messages %}
      {% for message in messages %}
      <div class="alert alert-success alert-dismissible fade show" role="alert">
        {{ message }}
        <button type="button" class="close" data-dismiss="alert" aria-label="Close">
          <span aria-hidden="true">×</span>
        </button>
      </div>
      {% endfor %}
      {% endif %}
      {% endwith %}
      <div class="card card-body">
        <form action="{{url_for('add_book')}}" method="POST">
          <div class="form-group">
            <input type="text" class="form-control" name="name" placeholder="Name">
          </div>
          <div class="form-group">
            <input type="text" class="form-control" name="writer" placeholder="Writer">
          </div>
          <div class="form-group">
            <input type="text" class="form-control" name="genre" placeholder="Genre">
          </div>
          <button class="btn btn-primary btn-block">
            Save 
          </button>
        </form>
      </div>
    </div>
    <div class="col-md-7">
      <table class="table table-striped table-hover table-bordered table-sm bg-white">
        <thead>
          <tr>
            <td>ID</td>
            <td>Name</td>
            <td>Writer</td>
            <td>Genre</td>
            <td>Operations</td>
          </tr>
        </thead>
        <tbody>
          {% for book in books %}
          <tr>
            <td>{{book.0}}</td>
            <td>{{book.1}}</td>
            <td>{{book.2}}</td>
            <td>{{book.3}}</td>
            <td>
              <a href="/edit/{{book.0}}" class="btn btn-secondary">edit</a>
              <a href="/delete/{{book.0}}" class="btn btn-danger btn-delete">delete</a>
            </td>
          </tr>
          {% endfor %}
        </tbody>
      </table>
    </div>
  </div>
</div>

{% endblock %}

Code for edit.html

{% extends "layout.html" %}
{% block body %}

  <div class="row">
    <div class="col-md-4 offset-md-4">
      <div class="card card-body">
        <form action="/update/{{book.0}}" method="POST">
          <div class="form-group">
            <input type="text" name="name" value="{{book.1}}" class="form-control">
          </div>
          <div class="form-group">
            <input type="text" name="writer" value="{{book.2}}" class="form-control">
          </div>
          <div class="form-group">
            <input type="text" name="genre" value="{{book.3}}" class="form-control">
          </div>
          <div class="form-group">
            <button type="submit" class="btn btn-primary btn-block">
              Update
            </button>
          </div>
        </form>
      </div>
    </div>
  </div>

{% endblock %}