HTHSeven
Posts: 14
Joined: Tue Aug 09, 2016 7:25 am

Database python script to output tables into HTML webpage

Wed Aug 31, 2016 7:54 am

May I know that how can I fetch data from my 2 tables in a database and output them to a webpage displaying with 2 tables separately?

Here's the python script to fetch the data:

Code: Select all

import sqlite3
  from bottle import route, run, template



 host = '169.254.173.16'
 db = sqlite3.connect('/home/pi/Desktop/DB/blindlogs.db')
 c = db.cursor()

 @route('/')
 def index():


    response = "<head><meta http-equiv='refresh' content='5'></head>"

    c.execute("SELECT * FROM Auto ORDER BY tdate, ttime DESC LIMIT 12")
    c.execute("SELECT * FROM Manual ORDER BY tdate, ttime DESC LIMIT 12")
    data = c.fetchall()
    response += template('/home/pi/Desktop/DB/blind_logs.tpl', rows=data)

    return response
    run(host=host, port=86)
And here's the html tables to insert the data from two tables selected:

Code: Select all

<h1>Auto Mode Logs</h1>
<table>

<tr>
<th>Date</th>
<th>Time</th>
<th>Light Intensity</th>
<th>Temperature</th>
<th>Current Blind State</th>
</tr>

%for row in rows:
    <tr>
    %for col in row:
        <td>{{col}}</td>
    %end
    </tr>
%end

</table>

<h1>Manual Mode Logs</h1>
<table>

<tr>
<th>Date</th>
<th>Time</th>
<th>Current Blind State</th>
<th>Childlock</th>
</tr>
%for row in rows:
    <tr>
    %for col in row:
        <td>{{col}}</td>
    %end
    </tr>
%end

</table>
http://imgur.com/a/vjKhF
However, from the result, I can see that both tables are having duplicates data, may I know that how should I modify the codes so that both tables won't have ducplicated data?

Thanks for your guides!

dddomodossola
Posts: 42
Joined: Thu Jan 22, 2015 8:56 am

Re: Database python script to output tables into HTML webpag

Wed Aug 31, 2016 8:00 am

You can change your queries adding the DISTINCT keyword.
http://www.w3schools.com/sql/sql_distinct.asp

HTHSeven
Posts: 14
Joined: Tue Aug 09, 2016 7:25 am

Re: Database python script to output tables into HTML webpag

Wed Aug 31, 2016 8:27 am

dddomodossola wrote:You can change your queries adding the DISTINCT keyword.
http://www.w3schools.com/sql/sql_distinct.asp
Hi, thanks for the reply, but I would prefer to fetch all the data in that particular database where the database have 2 tables created.

When displaying in HTML webpage, how should I display each data in the tables in different tables of the html page. That's what I concern.

Thank you =)

dddomodossola
Posts: 42
Joined: Thu Jan 22, 2015 8:56 am

Re: Database python script to output tables into HTML webpag

Wed Aug 31, 2016 8:43 am

Previously you told about a rows duplication problem, now you says you would like to have two separate tables (there are already two separate tables). Maybe I missed something?
I have not understood the problem. Can you please better explain?

HTHSeven
Posts: 14
Joined: Tue Aug 09, 2016 7:25 am

Re: Database python script to output tables into HTML webpag

Wed Aug 31, 2016 8:53 am

dddomodossola wrote:Previously you told about a rows duplication problem, now you says you would like to have two separate tables (there are already two separate tables). Maybe I missed something?
I have not understood the problem. Can you please better explain?
Hi,

Sorry for the misunderstanding. You can click the photo link I attached in the post just now.
You can observe from my html code and my results in photo, there are two tables.

Table auto fetch data from table auto in the database
Table manual fetch data from table manual in the database

The columns in both tables are not the same, but my html page is just showing up the data displaying from table auto which it fetch from the table named as auto in my database.

I can clarify further if you can't get it. Thank you!

dddomodossola
Posts: 42
Joined: Thu Jan 22, 2015 8:56 am

Re: Database python script to output tables into HTML webpag

Wed Aug 31, 2016 9:29 am

Well. So I think you would like to pass multiple variables to the template function call.
Take a look at this: http://stackoverflow.com/questions/1174 ... -variables
;-)

HTHSeven
Posts: 14
Joined: Tue Aug 09, 2016 7:25 am

Re: Database python script to output tables into HTML webpag

Wed Aug 31, 2016 10:23 am

dddomodossola wrote:Well. So I think you would like to pass multiple variables to the template function call.
Take a look at this: http://stackoverflow.com/questions/1174 ... -variables
;-)
Hi,

Thanks for the links! I got the idea and just a simple modified codes make it works! Thank you!

Code: Select all

def index():

		
		response = "<head><meta http-equiv='refresh' content='5'></head>"

		c.execute("SELECT * FROM Auto ORDER BY tdate, ttime DESC LIMIT 12")
		
		data = c.fetchall()
		response += template('/home/pi/Desktop/DB/blind_logs.tpl', rows=data)
		c.execute("SELECT * FROM Manual ORDER BY tdate, ttime DESC LIMIT 12")
		data2 = c.fetchall()
		response += template('/home/pi/Desktop/DB/blind_logs2.tpl', rows=data2)
		return response
run(host=host, port=85)


Return to “Python”