Flask meets MySQL

Flask

October 14, 2022

For installation and basics of MySQL check MySQL meets Python

Installation

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential

pip install mysqlclient
pip install flask-mysqldb

Creating Database and Table

import mysql.connector

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

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

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


sql = "CREATE TABLE IF NOT EXISTS Books (id INT AUTO_INCREMENT PRIMARY KEY,book VARCHAR(255) not null,Writer varchar(12) not null,Publication varchar(12) not null)"
mycursor.execute(sql)

mycursor.execute("SHOW TABLES")

for x in mycursor:
  print(x)

Following code will ask user to input name of book, its writer and publication and save it in the database.

It will also display the contents of the database on the home page.

When the user add a record successfully in the database, the page will also print ‘Record Added Successfully’.

Code for book.py

from flask import Flask,render_template, request
from flask_mysqldb import MySQL

app = Flask(__name__)

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'user'
app.config['MYSQL_PASSWORD'] = 'password'
app.config['MYSQL_DB'] = 'Library'

mysql = MySQL(app)

@app.route('/')
def form():
    #code for displaying the contents of the table
    cursor = mysql.connection.cursor()
    cursor.execute('SELECT * FROM Books')
    data = cursor.fetchall()
   
    return render_template('home.html',data=data)
    

@app.route('/',methods=['POST'])
def login():
    #code for reading and saving the data entered in form to the database
    book = request.form['book']
    Writer = request.form['Writer']
    Publication = request.form['Publication']
    cursor = mysql.connection.cursor()
    sql = """INSERT INTO Books (book,Writer,Publication) VALUES(%s,%s,%s)"""
    val = (book,Writer,Publication)
    flag = False
    try:
        cursor.execute(sql,val)
        mysql.connection.commit()
        flag = True

    except:
        print("An exception occurred")
    
    #Reading the Data from database
    cursor = mysql.connection.cursor()
    cursor.execute('SELECT * FROM Books')
    data = cursor.fetchall()
    cursor.close()

    return render_template('home.html',data=data,flag=flag)


if __name__ == '__main__':
    app.run(port=5000,debug=True)

Code for home.html

<!DOCTYPE html>
<html>
    <body>
        <form action="/" method = "POST">
            <p>Book <input type = "text" name = "book" /></p>
            <p>Writer <input type = "text" name = "Writer" /></p>
            <p>Publication<input type = "text" name = "Publication" /></p>
            <p><input type = "submit" value = "Submit" /></p>
         </form>

         {% if flag == True %} 
         <h2>Row added</h2>
         {% endif %}

        <table>
            <tbody>
                {% for row in data %}
                <tr>
                    <td>{{ row[0] }}</td>
                    <td>{{ row[1] }}</td>
                    <td>{{ row[2] }}</td>
                    <td>{{ row[3] }}</td>
                </tr>
                  {% endfor %}
              
             </tbody>

        </table>

    </body>
</html>

Output: Web Form

Output: After Submission

Refreshing The Page will display the form and the contents of the table

pontu