Hello friends how are you , Today in this blog i am going to teach you how you can fetch or access data from MySQL using Python and Flask Web Framework. If You want to create a project or Website in Python then this post will help you definitely.
In the previous post we have learnt that how we can store data in MySQL using Python and Flask Web Framework and today in this post i will teach you how you can access all the stored data from MySQL and populate in a HTML table. If you don't know how to create website in Python using Flask and store or insert data in MySQL using Python and Flask then there is a request that first visit below links.
Flask is a most important and popular web Framework which is used to create website using Python code and Django framework also can be used to create a website in Python. I will also make a post on How to create a website in Django using Python in upcoming post.
Here i am using Pycharm IDE for python code and Wamp Server for MySQL Database to store data. 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.
Records in Database
These are the records which is stored in my MySQL database and we have to learn that how to fetch these data from MySQL and populate in a webpage.
Install Python Packages or Libraries
We have to install the following libraries to communicate python with MySQL
Flask
Flask-MySQLdb
mysql
mysql-connector
In the above list of Python Libraries there are four Libraries in which two of them are related with Flask [Flask,Flask-MySQLdb] and the remaining two are related with MySQL [mysql,mysql-connector]These are the very important libraries for making a website project in Python because using these library we can manipulate data in MySQL Database. If you have not installed these libraries in your system then you are not able to communicate with MySQL Database.
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
I have already explained that how you can install these library if you are facing difficulty to install it then visit the below link.
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 will get a textbox and in that textbox type the name of HTML page. So just put "projectlist" as a name of HTML page.
Now i am giving a complete HTML code for HTML page "projectlist" , you have to just copy it and paste it in your HTML page.
<html> <head> <style> tr:nth-child(even) {background-color: #f2f2f2;} a{ color:white; background-color:#FF3399; padding:5px 15px 5px 15px; text-decoration:none; border-radius:20px; text-align:center; 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;} </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"> <tr> <td style="background-position:center center" valign="top" align="center"> <table border="0" cellpadding="8px" width="100%" style="background-color:#CCCCCC"> <tr style="background-color:navy;color:white"> <td>Project Name</td> <td>Technology Used</td> <td>Brief Description</td> <td>Group Members</td> <td>Status</td> </tr> {% for item in data %} <tr> <td>{{item.PRO_NAME}}</td> <td>{{item.TECHNOLOGY}}</td> <td>{{item.DESCRIPTION}}</td> <td>{{item.GROUP_NAME}}</td> {% if item.PRO_STATUS == 'Rejected' %} <td style="background-color:red"> {{item.PRO_STATUS }}</td> {% elif item.PRO_STATUS == 'Accepted' %} <td style="background-color:green"> {{item.PRO_STATUS }}</td> {% else %} <td style="background-color:yellow"> {{item.PRO_STATUS }}</td> {% endif %} </tr> {% endfor %} </table> </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 is the complete code for the python file app.py. Just type it or if you want to copy then you can copy it from here your personal use.
from flask import Flask, render_template from flask_mysqldb import MySQL import MySQLdb.cursors app = Flask(__name__) #code for connection #MySQL Hostname app.config['MYSQL_HOST'] = 'localhost' #MySQL username app.config['MYSQL_USER'] = 'root' #MySQL password here in my case password is null so i left empty app.config['MYSQL_PASSWORD'] = '' #Database name In my case database name is projectreporting app.config['MYSQL_DB'] = 'projectreporting' mysql = MySQL(app) @app.route('/') @app.route('/projectlist',methods=['GET','POST']) def projectlist(): #creating variable for connection cursor=mysql.connection.cursor(MySQLdb.cursors.DictCursor) #executing query cursor.execute("select * from pro_reg") #fetching all records from database data=cursor.fetchall() #returning back to projectlist.html with all records from MySQL which are stored in variable data return render_template("projectlist.html",data=data) 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.
In the above screenshot you will get the complete records of registered data in the MySQL database.
I hope now you can fetch data from 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.
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.
0 Comments