Subscribe Us

Insert/Store Data in MySQL using Flask | Create Website in Python

Anil Singhania December 15, 2020


Hello friends how are you , Today in this blog i will teach you how to store data in MySQL using Python. Here i will use Flask Web Framework for doing this. If You want to create a project or Website in Python then this post will help you definitely.

Flask is a popular web Framework which is used to create website using Python code and We can also use Django framework to create a website in Python. I will also make a post on How to create a website in Django using Python in next post.

Here i am using Pycharm IDE for python and Wamp Server for MySQL Database to create this particular page of registration. This is not compulsory for you to use Pycharm IDE and Wamp Server for MySQL, you can use any other IDE or application that support Python and MySQL, But i will suggest you to use these because i these application are very easy in use.

Now i am going to explain this one by one so to get complete knowledge just go through this post step by step.

Let's start

If you don't know how to run HTML page in Python using Flask then first visit the below link first.

Run HTML Page in Python [Flask Web Framework]

Create a Database

First of all i am going to tell you how to create database and table inside the database. Open your MySQL and create a database "ProjectReporting" or you can any name for database. If you want to create database using query then copy the code below. Following is the query of creating "ProjectReporting" database in MySQL 

CREATE DATABASE ProjectReporting

Create a Table

Now its time to create a table inside the database "ProjectReporting". You just need to copy the below query to create table in MySQL. Following is the query to create a table "PRO_REG" in MySQL.

CREATE TABLE PRO_REG (
    PROJECT_ID int NOT NULL AUTO_INCREMENT,
    PRO_NAME varchar(255) NOT NULL,
    TECHNOLOGY varchar(255),
    DESCRIPTION varchar(255),
    GROUP_NAME varchar(255),
    PRO_STATUS varchar(255),
    PRIMARY KEY (PROJECT_ID)
);

After this we have a database "ProjectReporting" and a table "PRO_REG" without any data like below


Install Python Packages or Libraries

We have to install the following libraries to communicate python with MySQL
Flask
Flask-MySQLdb
mysql
mysql-connector

If you want to communicate with MySQL using Python then some libraries are essential otherwise we can't insert or retrieve data from MySQL using Python. If you are using Python IDLE for coding of python then you will install these library using command prompt. Now open command prompt in your system and execute the below command one by one, see the below screenshot of command prompt


pip install Flask-MySQLdb
pip install Flask
pip install mysql
pip install mysql-connector

If you want to install these library in a very simple way i will suggest you to use Pycharm IDE and visit the below link and scroll down you will get a heading Install  Package or Library
Create HTML page inside template folder

Visit the above link if you don't know how to execute an HTML page in Python using Flask.
Now open Pycharm and follow the steps. To create HTML page right click on template folder then go to new and click on HTML


After clicking on HTML file you will get a screen like below


Here in above screenshot you have to just enter name of html page in the given textbox. So just put "projectreg" as a name of HTML page.

Now i am giving a complete HTML code for HTML page "projectreg" , you have to just copy it and paste it in your HTML page. 

<html>
<head>
<style>

a{
color:white;
background-color:#FF3399;
padding:5px 15px 5px 15px;
text-decoration:none;
border-radius:20px;
display: block;
width: 120px;
}
.login-dark
{
background-color:rgb(30,40,51);
text-align:center;
padding:20px;
width:350px;
height:450px;
border-radius:5px;
}

* {box-sizing: border-box;}
body {font-family: Verdana, sans-serif;}
.mySlides {display: none;}
img {vertical-align: middle;}

/* Slideshow container */
.slideshow-container {
  
  position: relative;
  margin: auto;
}

/* Caption text */
.text {
  color: #f2f2f2;
  font-size: 15px;
  padding: 8px 12px;
  position: absolute;
  bottom: 8px;
  width: 100%;
  text-align: center;
}

/* Number text (1/3 etc) */
.numbertext {
  color: #f2f2f2;
  font-size: 12px;
  padding: 8px 12px;
  position: absolute;
  top: 0;
}

/* The dots/bullets/indicators */
.dot {
  height: 15px;
  width: 15px;
  margin: 0 2px;
  background-color: #bbb;
  border-radius: 50%;
  display: inline-block;
  transition: background-color 0.6s ease;
}

.active {
  background-color: #717171;
}

/* Fading animation */
.fade {
  -webkit-animation-name: fade;
  -webkit-animation-duration: 1.5s;
  animation-name: fade;
  animation-duration: 1.5s;
}

@-webkit-keyframes fade {
  from {opacity: .4} 
  to {opacity: 1}
}

@keyframes fade {
  from {opacity: .4} 
  to {opacity: 1}
}

/* On smaller screens, decrease text size */
@media only screen and (max-width: 300px) {
  .text {font-size: 11px}
}
</style>
</head>
<body style="background-color:rgb(6,23,58)">
<table border="0" width="100%" height="10%" bgcolor="#000000" style="position: absolute; top: 0; bottom: 0; left: 0; right: 0;border-bottom:1px dotted white;">
<tr>

<td style="color:#ffffff;font-size:50px;text-shadow:2px 2px 2px red">Project Reporting</td>
<td width="10%" align="center"> <a href="index">Home</a></td>
<td width="10%" align="center"> <a href="about">About</a></td>
<td width="10%" align="center"> <a href="projectreg">Registration</a></td>
<td width="10%" align="center"> <a href="projectlist">Project List</a></td>
	<td width="10%" align="center"> <a href="proupdate">Pro Update</a></td>
<td width="10%" align="center"> <a href="admin">Admin</a></td>
</tr>
</table><br><br><br><br><br><br><br>

<table border="0" style="box-shadow:1px 1px 10px white" width="70%" height="70%" align="center" background="static/image/bgg.jpg">
<tr>

<td style="background-position:center center" align="center">

 <div class="login-dark">
	 <label style="color:white">{{ msg }}</label>
        <form action="{{ url_for('projectreg') }}" 	method="post">
            <h2 class="sr-only" style="color:#6666ff">Project Registration</h2>
            <div class="illustration"><i class="icon ion-ios-locked-outline"></i></div>
            <div class="form-group" style="padding:10px">
			<input class="form-control" style="padding:5px" type="text" name="name" placeholder="Project Name" required>
			</div>
            <div class="form-group" style="padding:10px">
			<input class="form-control" style="padding:5px" type="text" name="tech" placeholder="Technology Used" required></div>
			 <div class="form-group" style="padding:10px;">
			<textarea style="width:240px;margin-left:58px" placeholder="Brief Description" rows="5" name="desc" required></textarea>
			</div>
			 <div class="form-group" style="padding:10px;">
			<textarea style="width:240px;margin-left:58px" placeholder="Group Members" rows="5" name="gname" required></textarea>
			</div>
			
            <div class="form-group" style="padding:10px">
			<button class="btn btn-primary btn-block" style="background-color:rgb(33,74,128);width:169px;border:none;color:white;padding:5px 10px 5px 10px;border-radius:2px;" type="submit">Submit</button></div>
			</form>
    </div>
</td>
	<td align="center"  width="50%">

</td>
</tr>
<tr style="background-color:black;color:white;height:25px">
<td colspan="2"><marquee behavior="alternate">
Welcome of You in my Project.
</marquee></td>
</tr>
</table>
</body>
</html>

Create python file to execute this HTML file

To create a python file right click on project name and go to new and click on python file


After clicking on Python file you will a screen like below


Here you have to put a name for you python file ,in my case it is "app" so just type app and press enter this will create a app.py file automatically [Remember: No need to type app.py you have to type only app and press enter]. After doing this your screen will look like below screenshot


Write Python Code

Here i am going to give the complete code of python. So now open app.py python file and type the below code, You can copy this code your personal use.

#import libraries
from flask import Flask, render_template, request
from flask_mysqldb import MySQL
import MySQLdb.cursors
app = Flask(__name__)

#code for connection
app.config['MYSQL_HOST'] = 'localhost'#hostname
app.config['MYSQL_USER'] = 'root'#username
app.config['MYSQL_PASSWORD'] = ''#password
#in my case password is null so i am keeping empty
app.config['MYSQL_DB'] = 'projectreporting'#database name

mysql = MySQL(app)
@app.route('/')

@app.route('/projectreg',methods=['GET','POST'])
def projectreg():
    msg=''
    #applying empty validation
    if request.method == 'POST' and 'name' in request.form and 'tech' in request.form and 'desc' in request.form and 'gname' in request.form:
        #passing HTML form data into python variable
        n = request.form['name']
        t = request.form['tech']
        d = request.form['desc']
        g = request.form['gname']
        #creating variable for connection
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        #query to check given data is present in database or no
        cursor.execute('SELECT * FROM pro_reg WHERE PRO_NAME = % s', (n,))
        #fetching data from MySQL
        result = cursor.fetchone()
        if result:
            msg = 'Project already exists !'
        else:
            #executing query to insert new data into MySQL
            cursor.execute('INSERT INTO PRO_REG VALUES (NULL, % s, % s, % s,% s,% s)', (n, t, d, g,'Pending',))
            mysql.connection.commit()
            #displaying message
            msg = 'You have successfully registered !'
    elif request.method == 'POST':
        msg = 'Please fill out the form !'
    return render_template('projectreg.html', msg=msg)
if __name__ == '__main__':
    app.run(port=5000,debug=True)

Run Python Code

After doing all these things now you have to run the python code and look on to the console screen which is below the python code or editor, for better understanding see the below screenshot


When you will paste the obtained URL in any browser then you will get an output like below screenshot.



Fill data and click on Submit Button

Now fill the data in all textbox here in the above output screen if you left blank any textbox you will get an warning that "Fill all the fields" and after filling all the fields click on submit button.


After filling all details when you click on submit button then given data will be stored in MySQL database and you will get a message "You have successfully registered!".


Note : Here i have applied a validation that if you enter a project name which is already present in the database then you will get a message "Project already exists !"


Registration Records

Now i am going to display a screenshot of all registered data which is present in MySQL database.
Take a look.



I hope now you can insert data in MySQL in Python using Flask Web Framework. If you have any doubt regarding this post  or you want something more in this post then let me know by comment below i will work on it definitely.

If you want complete Web project in [Python,Flask]  then comment your response i will make a complete post about project. 

Request:-If you found this post helpful then let me know by your comment and share it with your friend. 
 
If you want to ask a question or want to suggest then type your question or suggestion in comment box so that we could do something new for you all. 

If you have not subscribed my website then please subscribe my website. Try to learn something new and teach something new to other. 

If you like my post then share it with your friends. Thanks😊Happy Coding.

Tags

Post a Comment

0 Comments