How to display data from MySQL in HTML table using Python
In this article, you will learn very simple code to fetch data from a MySQL database and display it in an HTML table using the Python programming language.
MySQL is the most popular and widely used open source relational database management system. It can run on many different platforms without failure, even on a low-powered PC, and Python provides support to work with the MySQL database. Programming in Python is considerably simpler and more efficient compared to other languages. It has a set of useful libraries and packages that minimize the use of code in day-to-day life. Python needs a MySQL driver to access the MySQL database. Python provides various modules to access MySQL databases from a web server, such as PyMySQL, mysql.connector, etc.
In this article, we used the MySQL Connector module. The MySQL Connector module is written in pure Python and is compatible with Python 3. It is self-sufficient to execute database queries through Python.
MySQL Database
Suppose we have an "employee" table that contains an employee id, name, email, and phone number. We want to display the employee's whole information on a web page in an HTML table.
CREATE TABLE IF NOT EXISTS `employee` (
`emp_id` int(11) NOT NULL AUTO_INCREMENT,
`emp_name` varchar(150) NOT NULL,
`email` varchar(150) NOT NULL,
`phone` varchar(100) NOT NULL,
PRIMARY KEY (`emp_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
INSERT INTO `employee` (`emp_id`, `emp_name`, `email`, `phone`) VALUES
(1, 'John', This email address is being protected from spambots. You need JavaScript enabled to view it. ', '2323234543'),
(2, 'Smith', This email address is being protected from spambots. You need JavaScript enabled to view it. ', '9898577442'),
(3, 'Priska', This email address is being protected from spambots. You need JavaScript enabled to view it. ', '9393452387'),
(4, 'Gaga', This email address is being protected from spambots. You need JavaScript enabled to view it. ', '8482764537');
Step by Step process to fetch data from MySQL
We need two modules for this application, webbrowser and mysql.connector. The webbrowser module is by default available with the python package. We do not need to install this, but we do need to install the mysql.connector module.
Python MySQL Installer
The installer requires 'python.exe' in your system PATH, otherwise it will fail to install. So make sure to add Python in your system environment. We can also install MySQL Connector using the pip command.
pip install mysql-connector
As, this connector is already installed in my system. It returns the following-
c:\python37\Scripts>pip install mysql-connector
Requirement already satisfied: mysql-connector in c:\python37\lib\site-packages (2.2.9)
This is also a way to check the successful installation of the MySQL connector.
Import modules
First, we need to import both modules at the top of the script.
import mysql.connector
import webbrowser
Python connecting to MySQL
Next, use the connect() constructor to create a connection to the MySQL server. Make sure to replace 'hostname', 'username', 'password' and 'database' with your database credentials and name.
conn = mysql.connector.connect(user='root', password='',
host='localhost',database='company')
if conn:
print ("Connected Successfully")
else:
print ("Connection Not Established")
Python MySQL Fetch Data and store in a variable
Here, we have used the select query to fetch data. Next, we iterated on the fetched data and stored it in a list variable in an HTML tabular format.
select_employee = """SELECT * FROM employee"""
cursor = conn.cursor()
cursor.execute(select_employee)
result = cursor.fetchall()
p = []
tbl = "IDNameEmailPhone"
p.append(tbl)
for row in result:
a = "%s"%row[0]
p.append(a)
b = "%s"%row[1]
p.append(b)
c = "%s"%row[2]
p.append(c)
d = "%s"%row[3]
p.append(d)
Create HTML Template
Next, we have created an HTML template and passed the above list variable.
contents = '''<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="content-type">
<title>Python Webbrowser</title>
</head>
<body>
<table>
%s
</table>
</body>
</html>
'''%(p)
Open a web browser
The Python webbrowser module provides a feature to open a webpage in the browser. In the given code, we have created an HTML file and placed the above generated HTML content in it, rendering it in a web browser.
filename = 'webbrowser.html'
def main(contents, filename):
output = open(filename,"w")
output.write(contents)
output.close()
main(contents, filename)
webbrowser.open(filename)
Complete code to display MySQL data in an HTML table using Python
Above, we have explained the code in chunks. Here we have merged them all together to get the complete code to display the MySQL data in an HTML table using the Python programming language.
import mysql.connector
import webbrowser
conn = mysql.connector.connect(user='root', password='',
host='localhost',database='company')
if conn:
print ("Connected Successfully")
else:
print ("Connection Not Established")
select_employee = """SELECT * FROM employee"""
cursor = conn.cursor()
cursor.execute(select_employee)
result = cursor.fetchall()
p = []
tbl = "<tr><td>ID</td><td>Name</td><td>Email</td><td>Phone</td></tr>"
p.append(tbl)
for row in result:
a = "<tr><td>%s</td>"%row[0]
p.append(a)
b = "<td>%s</td>"%row[1]
p.append(b)
c = "<td>%s</td>"%row[2]
p.append(c)
d = "<td>%s</td></tr>"%row[3]
p.append(d)
contents = '''<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="content-type">
<title>Python Webbrowser</title>
</head>
<body>
<table>
%s
</table>
</body>
</html>
'''%(p)
filename = 'webbrowser.html'
def main(contents, filename):
output = open(filename,"w")
output.write(contents)
output.close()
main(contents, filename)
webbrowser.open(filename)
if(conn.is_connected()):
cursor.close()
conn.close()
print("MySQL connection is closed.")
Once you run the above code, it returns something like this in the browser.
Related Articles
Convert MySQL query result to JSON in PythonInsert XML Data to MySQL Table using Python
CRUD operations in Python using MYSQL Connector
Python Converting a CSV File to a MySQL Table
Get data from MySQL in Django View with Models
Insert data in MySQL database from an HTML form using Django
MongoDB CRUD operations Python
Write DataFrame to csv Python
Python Pandas Tutorial
Pandas DataFrame example
Python tkinter messagebox options
Tkinter geometry window size
Tkinter scale style
Python Tkinter tutorial
Python turtle graphics code
Factorial program in Python with recursion
Python code for snake game
Python extract data from JSON file
Python XML to dict ElementTree
Python dict to XML attributes
Python convert list to numpy array
How to shuffle a list in Python
Print hollow diamond pattern in Python
Python *args **kwargs