top of page
This site was designed with the
.com
website builder. Create your website today.Start Now

Tutorial 6 - View SQL in HTML Table using both Python and Java, then compare the two approaches for a better understanding of what the code is doing.

Some beginners dismiss learning Java because it is too hard and they just go for Python. I have noticed that Python is indeed much easier but takes so many shortcuts that sometimes it is hard to understand what your code is really doing. So let’s write some code in Python but use Java to explain the code. This way u won’t be embarrassed later when someone asks u what your code is doing and because all u do is copy and paste u never bothered to learn. Don’t get caught in this embarrassing trap. Be Bilingual and be many steps ahead of your peers. Know and understand both Java and Python.

 

Let’s recap where we have been in previous tutorials.

 

Tutorial 2 - we downloaded and configured Tomcat to work in Eclipse so we can run servlets

​

Tutorial 3 - we downloaded Java libraries and also enabled Eclipse for Python

​

Tutorial 4 - we downloaded SQLite and created a table and inserted some data

 

Now in this tutorial we will view the data we inserted previously. In tutorial 4 we manually went into our test.db schema in sqlite and did this : select * from test_table; Now instead of doing this selecting at the command line we will write a Python app to select our data and then view our output in an HTML table object.

 

Let’s first do this using Python, then after we will do it in Java and explain all the Python steps

 

But first, some basic housekeeping: we will be using Flask in our Python example so be sure that Flask is installed. Also we need to copy both files test.db and sqlite3 from the workspace used in tutorial 4 to our new workspace package.

 

Pip is a package installer for Python and comes shipped with Python version 3.7.2 which we downloaded and installed in tutorial 3. Open a standard command prompt by typing cmd into search area of windows 10, then double click command prompt to open it then type pip and press enter. The standard pip menu will appear so we know that pip is installed. Then type

               pip install flask

at the command prompt and Flask will install. Wait till finished. Now we can use flask in Eclipse.

 

In Eclipse be sure to switch into the PyDev perspective and create a new PyDev module. Here a package is mandatory for our program to work. Right click on project then new then python module. Fill in package and module name and press enter. I used rick as my package name and html_test as the module name.

new python module.JPG

Click finish. Next copy the following code into the new html_test file




 

from flask import Flask, render_template

import sqlite3

 

app = Flask(__name__)

 

@app.route('/')  

def get_users():

   conn = sqlite3.connect('test.db')

   cursor = conn.cursor()

   cursor.execute('select column1,column2,column3,column4 from test_table')

 

   return render_template('print_items.html', list=cursor.fetchall())                                            

                                               

 

if __name__ == '__main__':

   app.run()




 

Next we need to create a folder in our package called templates so right click the package name then select new and then folder, then enter templates as folder name and press finish.

 

Now we need to create another file called print_items.html where we render our html output to. Right click on the templates folder and then new and then file, name the file and press finish.


 

Paste the following code into the print_items file and save


 

<table width='300'>

 

{% for item in list %}

 <!-- for each variable named item, loop thru the list array and append to table for each pass -->

 

<tr>

   <td width='20'><font color='red'>{{item[0]}}</font></td>

   <td width='20'>{{item[1]}}</td>

   <td width='20'>{{item[2]}}</td>  

   <td width='20'>{{item[3]}}</td>  

</tr>

 

</table>

 

{% endfor %}




 

Our Project explorer should look like this

python explorer.JPG

Notice that there are 2 other items in our view called sqlite3 and test2.db. To get these 2 files into your project explorer u need to navigate to the previous workspace we used in tutorial 4, copy both files and click on package name, then paste each file into our current package. test.db is what we used in tutorial 4 but I happen to have it as test2 here. We need the test.db schema used in tutorial 4

​

It is critical that our file structure is done this way. To summarize we have our main package and inside this package we have a folder called templates (the name templates is mandatory because python looks for this specific folder name). Inside the templates folder we have print_items.html. Also in our main package is our html_test.py and test.db and sqlite3. U will also notice _init_.py, this will automatically be inserted into our tree structure.

 

Now run html_test.py by right clicking, then run as, then Python run. Type http://localhost:5000 in your browser to see contents of our test_table in html format. Flask automatically configures a web server on port 5000 by default.



 

Now for the Java piece using JDBC (java data base connectivity)

 

In Eclipse go to the java EE perspective. We will need to run a servlet inside a Tomcat server because our output will be to a web server page at localhost, similar to flask. Plain Java is not useful here. Go back to tutorial 2 where we created a servlet class in Tomcat. If u are not configured for Tomcat yet we need to do this before we can run our servlet.

      (note: if u still have mortbay setup then u can run this servlet there without tomcat)

 

Right click on the Src folder in the project and then choose new and then servlet. Name the servlet as View_HTML_Table and Press finish, delete the contents inside and replace with the following code:







 

/**

* view output from SQL select in HTML format

*   

* also we use annotation instead of web.xml

*/

import javax.servlet.*;

import javax.servlet.http.*;

import java.io.*;

import java.sql.*;   //JDBC API

 

import javax.servlet.annotation.*; //this import necessary for annotation to work

          //no need to import servlet-api.jar or web.xml


 

@WebServlet("/View_HTML_Table")  //using annotation eliminates need for web.xml but must include import above

 

public class View_HTML_Table extends HttpServlet {

       

   Connection cn = null;     

   PrintWriter out;

  

 //Service the request

 public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

 

 response.setContentType("text/html");

 out = response.getWriter();

   

 

   try{

 

    loadDriver();       //connect to sqlite

   

    view_data();

   

   

} //end of try block

     

     catch (Exception e) {

        throw new ServletException("Exception in XLS  Servlet", e);

    }

   

   

 

  } //end of service method


 

public void loadDriver() {

try {

 Class.forName("org.sqlite.JDBC");

 System.out.println("The Driver has been loaded successfully!");

}   catch (Exception E1) {

         System.out.println("Unable to load the Driver!");

 System.out.println("Exceptions:" + E1.toString());

 System.exit(1);

}

 

System.out.println("Establishing connection to Server");

 

 

//Establishing the connection with the database

 

try {

  cn = DriverManager.getConnection("jdbc:sqlite:test.db");                                                                                 System.out.println("Connection to Server was Established");

       } catch (Exception E2) {

System.out.println(" Exceptions : " + E2.toString());

       }

 

 

} //end load driver method



 

public void view_data(){

 

    try{

   

PreparedStatement view = cn.prepareStatement("select * from test_table");

   

    ResultSet rs =view.executeQuery();

   

 //start html

out.println("<html><title>HTML table Example</title><head> ");

    out.println("</head><body>");

out.println("<table width='300'>");

   

        while(rs.next()) {

     

        out.println("<tr>");

       

     String col1 =  rs.getString("column1");

     String col2 =  rs.getString("column2");

     String col3 =  rs.getString("column3");

     String col4 =  rs.getString("column4");

     

          

         out.println (

           //these are the cols in each line/row

          "<td width='10'>"+"<font color='blue'>"+col1+"</font>"+"</td >"

    +"<td width='10'>"+col2+"</td >"

    +"<td width='10'>"+col3+"</td >"

    +"<td width='10'>"+col4+"</td >"

   

       

    );

         

         out.println("</tr>");

         

                

}  // end of result set

        

        

     out.println("</table>");   

     

         out.println("</body></html>");

        

     

    } catch(Exception e){

   

    System.out.println(" Exception : " + e.toString());

    }

 

    //then close connection

  try{

  cn.close();

  } catch (SQLException E3) {

System.out.println(" Exceptions : " + E3.toString());

       }

    

} //end view data method

 

} //end of class





 

Notice right away that this is much more extensive than Python, but we can see more clearly our steps, in my opinion

 

By the way, be sure that your test.db file is copied into your Eclipse workspace and also we need the sqlite jar file inside the WEB-INF \ lib folder. U can copy and paste from source directly into lib folder

web inf.JPG

Find the path for your eclipse workspace in your current project like we did in tutorial 4 and ensure that test.db is copied there because test.db contains the data we created in tutorial 4 and we want to use this data here in our java servlet.

 

Now right click on View_HTML_Table.java and then run as and then run on server. Our output will be the same as our Python output above but this time using java. Tomcat automatically creates a server on port 8080 by default. Go to http://localhost:8080 to view the result


 

 



 

Now for the Comparison….as is quite obvious Java was much more involved but Python is so short it seems like we need an explanation for everything

 

In java we needed to download both tomcat and sqlite. We needed to properly install Tomcat inside Eclipse and get it running. We had to download and install a library. But none of this for Python….only get Flask running….what a big difference already

 

For Python we only need to ensure that Flask is working (later in Production environment it is better to use Django for Python….Flask is only a minimal server for testing and not a production server)

 

So by the time we get our environment created using  java, in much less time, we ran python and we ran circles around java and saved a bunch of time. But do we really understand our Python code?


 

Now for the code comparison

 

Java shows how we connect to a database….Python just mysteriously connects to sqlite

With one line of code

        conn = sqlite3.connect('test.db')

 

In java we prepare a statement to query the data, then execute it and get a result set then as the result set iterates through each row we output our data using a printwriter object

 

Whereas in Python it is assumed that fetchall just magically pulls all results into a list object (an array) and then a for loop iterates thru the list

                       {% for item in list %}


 

For Java we have try and catch blocks and methods in blocks, Python does not do this

 

Python used 21 lines of code in 2 files….Java had 1 file with 69 lines of code

 

 

To understand the step by step process I am reprinting the Java code here and will include comments for each relevant line




 

/**

* view output from SQL select in HTML format

*   

* also we use annotation instead of web.xml

*/

import javax.servlet.*;

import javax.servlet.http.*;

import java.io.*;          //this is to enable Printwriter object

import java.sql.*;         //enables sql statements and result sets with JDBC

 

import javax.servlet.annotation.*; //this import necessary for annotation to work

          //no need to import servlet-api.jar or web.xml


 

        //this is a java annotation that automates the web xml functionality of a servlet

         //so we dont need to create a separate web xml which has always been a pain in servlets

@WebServlet("/View_HTML_Table")

 

public class View_HTML_Table extends HttpServlet {

       

   Connection cn = null;     //declare a connection object called cn

   PrintWriter out;                 //declare a printwriter called out….we can write html to this structure

  

      //Service the request

 public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

                       //we have both a request and a response object in this service method

 

 response.setContentType("text/html");    //enable html to be displayed

 out = response.getWriter();         //set up response object to use the writer

   

//we need try and catch blocks in java so as to catch errors

   try{

 

    loadDriver();       //connect to sqlite…...go to this method below

   

//after the load driver code is executed below come back here and execute view data method

   

view_data();

   

   

} //end of try block

     

     catch (Exception e) {

        throw new ServletException("Exception in XLS  Servlet", e);

    }

   

    //while in our service method if any runtime errors occur they will show up in java console   

 

  } //end of service method


 

public void loadDriver() {

try {

 Class.forName("org.sqlite.JDBC");

 System.out.println("The Driver has been loaded successfully!");

}   catch (Exception E1) {

         System.out.println("Unable to load the Driver!");

 System.out.println("Exceptions:" + E1.toString());

 System.exit(1);

}

 

System.out.println("Establishing connection to Server");

 

 

//Establishing the connection with the database

 

try {

  cn = DriverManager.getConnection("jdbc:sqlite:test.db");                                                                                 System.out.println("Connection to Server was Established");

       } catch (Exception E2) {

System.out.println(" Exceptions : " + E2.toString());

       }

 

 

} //end load driver method



 

public void view_data(){

 

    try{

        //query all records in test_table

PreparedStatement view = cn.prepareStatement("select * from test_table");

   

    ResultSet rs =view.executeQuery();

   

 //start html

out.println("<html><title>HTML table Example</title><head> ");

    out.println("</head><body>");

out.println("<table width='300'>");

                        

                           //while result set still has elements iterate thru each row

        while(rs.next()) {

                 //while in each row iterate thru all the columns in that row

        out.println("<tr>");

                                            //store each column value in a string called col1 etc

     String col1 =  rs.getString("column1");

     String col2 =  rs.getString("column2");

     String col3 =  rs.getString("column3");

     String col4 =  rs.getString("column4");

     

             //then print col1, col2 etc to html using Printwriter called out

         out.println (

           //these are the cols in each line/row

          "<td width='10'>"+"<font color='blue'>"+col1+"</font>"+"</td >"

    +"<td width='10'>"+col2+"</td >"

    +"<td width='10'>"+col3+"</td >"

    +"<td width='10'>"+col4+"</td >"

   

       

    );

         

         out.println("</tr>");

         

                

}  // end of result set

        

        

     out.println("</table>");   

     

         out.println("</body></html>");

        

     

    } catch(Exception e){

   

    System.out.println(" Exception : " + e.toString());

    }

 

    //then close connection

  try{

  cn.close();

  } catch (SQLException E3) {

System.out.println(" Exceptions : " + E3.toString());

       }

    

} //end view data method

 

} //end of class





 

Here is the python code with comments…..this code produces the same result as above


 

from flask import Flask, render_template        //use the render_template method of flask

import sqlite3

 

app = Flask(__name__)                     //app is the name we chose

 

@app.route('/')        // flask outputs to localhost:5000 or to sub folder...there is no sub folder here

def get_users():

   conn = sqlite3.connect('test.db')  //connect to our test.db schema

   cursor = conn.cursor()                 //declare a cursor

  cursor.execute('select column1,column2,column3,column4 from test_table')  //execute a query

 

return render_template('print_items.html', list=cursor.fetchall())

                                                                                //fetch all results into a Collection named list

                                                                                                      //which is also like an array

                                                                               //and then run code in print_items.html below

 

if __name__ == '__main__':

   app.run()                               //run the app



 

         Below is the code for enabling html result


 

<table width='300'>     //table beginning tag with set width of 300

 

{% for item in list %}       //declare item and for each item in our list created above, render to html

 <!-- for each variable named item, loop thru the list array and append to table for each pass -->

 

<tr>            //displays results vertically

   <td width='20'><font color='red'>{{item[0]}}</font></td>   //each td is a column

<td width='20'>{{item[1]}}</td>  //pulls item in the 0 position of the array, then the 1 position etc

                                                     //the first position of an array always starts at position 0

   <td width='20'>{{item[2]}}</td>  

   <td width='20'>{{item[3]}}</td>  

</tr>        //without these tr tags all results will be in one line versus vertical display of each row

 

</table>         //table ending tag

 

{% endfor %}       //end for loop



 

The Python code is following the same steps as in java, we connect to data, get a result and display the result in html

 

That’s it for now. I am hoping we are all having fun by now and there is more to come

 

About the author Rick Delpo, click here to find intro blog. Also take time to read my second blog piece on what it takes to be a programmer, found at very end of intro blog

bottom of page