Display a table in a database in HTML using prettytable python

Hi everyone this is my 3rd blog post, on this blog post I’m going to show you how to display a table in a database in an HTML file using python prettytable.

To do this you need to have installed python on your computer. You can get it from python.org. Once python is installed you need to install prettytable library. To install prettytable enter follow the steps below

Install via pip:

python -m pip install -U prettytable

or install the latest development version:

python -m pip install -U git+https://github.com/jazzband/prettytable

After installing prettytable to connect to a database python requires a library. There are different types of libraries (SQLite, MySQL connector), In my case, I” ll be using “MySQL connector”.

To install MySQL connector just run the following command in the terminal

pip install mysql-connector-python

Once the above-mentioned libraries are installed, the next step is to create a python file, in my case, I created a file named “prettyTable.py”. After creating the file next the relevant libraries must be imported to the code as shown below.

The “import Error” is not a required function, I have used it to display the error if there’s a connectivity issue with the database.

Next, I’ve created a dictionary by including the database details (host, database, user, password) which are needed to connect to the database. In my case the database is run locally so the host is “localhost”, and my database name is “game”.

And after I used a try function to check the connectivity with the database. If the connection fails it will display the error using “MySQL.connector” Error function.

Once done next I executed the SQL query using the “cursor” instance. In this query, I import the table “gameRecords” from the “game” database.

We can generate a PrettyTable from the cursor using the from_db_cursor() method and assign it to a variable “mytable”

Now to turn “mytable” into an HTML table we can use get_html_string() method to generate the HTML code and assign it to a variable “htmlCode”

And at this point, if you print “htmlCode” the HTML code will be printed.

So now we have to write the “htmlCode” into a .html file. To do that, open the file using the python open() function and set the mode to write “w”, the file will be automatically created if it’s not available or overwritten if the file exists. And write the “htmlCode” to the .html file using write() function, remember to close the file at the end of the code using close() function.

Once you run the code an .html file will be created in the given directory. In my case prettyTable.html . And inside you can find a table similar to this when you open the file in your browser.

Thank you for reading, hope you learn something from this blog post… 🙂

Please find the full code attached below.