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