###################################################################
# SQLITE - Select - Display result as HTML table
###################################################################
import sqlite3

conn  = sqlite3.connect('/home/sultans/data/sqlite/sqlite.db')      #server database
#conn = sqlite3.connect('/sqlite/sqlite.db')                        #local database 

conn.row_factory = sqlite3.Row                          #return a row dictionary (with col names/values) 
                                                        #instead of row tuple (with col values only)
curs = conn.cursor()

sql = '''
    SELECT * 
    FROM student join class on ssn=stu_ssn
    ORDER BY student_id
'''
curs.execute(sql)                                       #execute SQL

data = curs.fetchall()

def displayHTML(data):
    print('<table border=1>')
    col_names = data[0].keys()                          #get column headers from 1st row 
    print('<tr bgcolor=gray>')
    for name in col_names:                              #loop thru column headers
        print('<th>', name.upper(), end='')
    print()
    for row in data:                                    #loop thru column values 
        print('<tr>')
        for col in row:
            print('<td>', col, end='')
        print()
    print('</table>')

displayHTML(data)

curs.close()
conn.close()