Display a Web Page using EasyPHP, Python and PHP

by

This article focuses on configuring Python to work with MySQL. This article also assumes you have an IDE installed and have completed the following articles:

and the following steps:

  • Started EasyPHP
  • Opened the Dashboard
  • Started the HTTP and Database servers

Filter the Database and Display Results

In our article CSV to MySQL Table [EasyPHP + PHPMyAdmin], we imported a CSV file and renamed both the database and table.

Let’s move forward and write a Python script to connect to this database table, filter the records, and output it as an HTML web page.

Install the Required Library

Navigate to an IDE and open a terminal session. At the terminal prompt, enter the following code and press the <Enter> key to start the installation process.

pip install mysql

If successful, a message will display indicating this.

Create a Folder inside EasyPHP-Devserver-17 folder

Navigate to the shortcut folder created in the previous article (located on the Desktop). Double-click to open the folder.

Create a new folder under the eds-www folder called FINXTER.

Create the Python Script

In an IDE, create a new Python file called connect.py. Copy and paste the contents below to this file. Save this file to the FINXTER folder created above.

import mysql.connector
from mysql.connector import Error

config = {
         'user':     'root',
         'password': '',
         'host':     'localhost',
         'database': 'finxters_db',
         'raise_on_warnings': True
}

try:
    con = mysql.connector.connect(**config)
except Error as e:
    print(f"The error '{e}' occurred")

mycursor = con.cursor()
mycursor.execute(f'USE finxters_db;')
con.commit()

mycursor.execute("SELECT First_Name, Last_Name, Username, Rank, Solved FROM users_tbl WHERE Rank='Authority';")
myresult = mycursor.fetchall()

print(f"<p align=center>{len(myresults)} records returned</p>")
print("<table class='table'>")
print("<thead>")
print("  <th>First Name</th>")
print("  <th>Last Name</th>")
print("  <th>Username</th>")
print("  <th>Rank</th>")
print("  <th>Solved</th>")
print("</thead>")

for r in myresults:
    print("<tr>")
    print(f"<td>{r[0]}</td>")
    print(f"<td>{r[1]}</td>")
    print(f"<td>{r[2]}</td>")
    print(f"<td>{r[3]}</td>")
    print(f"<td>{r[4]}</td>")
    print("</tr>")
print("</table>")
print("<br/><br/>")
con.close()

This script does the following:

  • Imports the required MySQL.connector library to allow Python to connect with MySQL.
import mysql.connector
from mysql.connector import Error
  • The config variable is created. This contains all the pertinent information to connect to the finxters_db database.
    • The user: root.
    • The host: localhost.
    • The password: '' (an empty string).
    • The raise warnings option.

💡Note: These credentials would be locked down in a secure production environment.

config = {
         'user':     'root',
         'password': '',
         'host':     'localhost',
         'database': 'finxters_db',
         'raise_on_warnings': True
}
  • Python then attempts to connect to the selected database inside a try/except statement. If successful, code execution continues. Otherwise, the script ends, and an error message displays.
try:
    con = mysql.connector.connect(**config)
except Error as e:
    print(f"The error '{e}' occurred")
  • The following section creates a cursor() and uses execute() to pass the MySQL statement USE finxters_db as an argument. This is done, so MySQL knows what database to reference.
mycursor = con.cursor()
mycursor.execute(f'USE finxters_db;')
  • The following section calls in 5 of the 11 columns from users_tbl and filters the results based on a specific Rank. The results save to myresults.
mycursor.execute("SELECT First_Name, Last_Name, Username, Rank, Solved FROM users_tbl WHERE Rank='Authority';")
myresult = mycursor.fetchall()

💡Note: You may want to navigate to phpMyAdmin and familiarize yourself with the contents of finxters_db and users_tbl.

  • The last section displays the total number of results and the matching records found in an HTML Table format and closes the database connection.
print(f"<p align=center>{len(myresults)} records returned</p>")
print("<table class='table'>")
print("<thead>")
print("  <th>First Name</th>")
print("  <th>Last Name</th>")
print("  <th>Username</th>")
print("  <th>Rank</th>")
print("  <th>Solved</th>")
print("</thead>")

for r in myresults:
    print("<tr>")
    print(f"<td>{r[0]}</td>")
    print(f"<td>{r[1]}</td>")
    print(f"<td>{r[2]}</td>")
    print(f"<td>{r[3]}</td>")
    print(f"<td>{r[4]}</td>")
    print("</tr>")
print("</table>")
print("<br/><br/>")
con.close()

We won’t be able to see this code working until we create a PHP script and calls in connect.py.

Create the PHP Script

We must create a PHP file to get the Python script to work with this example.

Navigate to the IDE, and create the PHP file index.php. Copy and paste the contents below to this file. Save this file to the FINXTER folder mentioned above.

<!DOCTYPE html>
<html>
  <head>
   <title>Finxter Users</title>
   <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
   <style>
    h1 {
	    color: goldenrod;
            padding-top: 30px;
            text-align: center;
            text-shadow: 2px 2px ghostwhite;
    }
    h2 {
            font-size: 19px;
            text-align: center;
            padding-bottom: 20px;
            letter-spacing: 2px;
    }
    h3 {
            font-size: 15px;
            text-align: center;
            padding-bottom: 30px;
            letter-spacing: 1px;
    }
    p {
            font-size: 14px;
    }
    table {
            background-color: #f5f5f5;
    }
    th {
            background-color: #F5F5F5;
            color: black;
            font-weight: 600;
    } 
    tr {
            line-height: 11px;
            min-height: 11px;
            height: 11px;
    }
	</style>
  </head>
  
  <body>
  <div class="container">
    <h1>Finxter Users</h1>
    <h2>Authority Level</h2>
  
    <?php
        $output = shell_exec("python connect.py");
        echo $output;
    ?>
    </div>
    <br/><br/>
  </body>
</html>

As you will see in the code above, we created an HTML web page and added some CSS styles for a stylish output. However, let’s concentrate on the PHP lines highlighted above in yellow.

You can break out of HTML and instantiate PHP by adding the following code:

<?php

?>

Between the PHP opening and closing tags, PHP code is added to call the shell_exec() function. This function passes python connect.py as an argument.

<?php
$output = shell_exec("python connect.py");
?>

Next, the contents of connect.py is read in and saved it to the PHP variable $output. The contents of $output is printed out only when the index file is called.

<?php
$output = shell_exec("python connect.py");
echo $output;
?>

Putting it Together!

Now that both the connect.py and index.php files reside in the FINXTER folder outlined above; let’s view it in a browser!

From the EasyPHP Dashboard, click the icon below to expand.

Click to open the FINXTER folder.

Click index.php to open a browser tab and view the results.

If successful, the Python code displays the following HTML page in conjunction with the Python code.

To view the HTML generated by connect.py, right-mouse-click over the above web page to display a pop-up menu. From this pop-up, select View Page Source. Scroll up and down to see the HTML and CSS code.

An alternate way to view it is to enter localhost:8080 in a browser tab, navigate to the FINXTER folder and click to select index.php.

🌟The Finxter Challenge
Modify the HTML code in connect.py.
Reload/refresh the web page to view the changes. Good Luck!

Summary

In the following article, Install the Python Application Component in EasyPHP, we update the required applications and install the Python application to work directly with EasyPHP.

See you in the following article!