#!//usr/bin/python3
#=======================================================================
# Get data from a database
#=======================================================================
import cgi                              #Optional - to make it work on the web
import pymysql as mydb					#Python 3x Mysql database driver
# import cx_Oracle as oradb			    #if using Oracle database driver

print("Content-Type: text/html \n")     #Optional - to make it work on the web

data = [ ]							    #2 dimensional list
#======================================================================
def read_data( ):

    try:  
        conn  = mydb.connect(host='localhost',user='demo2',password='demo2',database='demo2') 
#       conn  = oradb.connect( 'demo2/demo2@localhost:1521/orcl' )   

        cursor = conn.cursor( )                     #create a cursor

        sql  = "SELECT firstname,lastname,address,flavor,topping,creditCard,order_id " 
        sql += "FROM cust_order   " 
        sql += "ORDER BY order_id " 

        result = cursor.execute(sql);               #execute the query

    except mydb.Error as e:                         #catch MySql DB errors
        errorNum = e.args[0]
        errorMsg = e.args[1]
        print( 'Database Error - ' + str(errorNum) + errorMsg)
        return 
#   except oradb.DatabaseError as e:                #catch Oracle DB errors
#       print( 'Database Error - ' + e )
#       return  

#---Option 1 (get all the rows)-------------------------------------------

#    results = cursor.fetchall( )                   #get all the rows at once
#    for row in results:                            #loop thru the list of rows
#        for col in row:                            #loop thru the tuple of columns 
#            print(col, '\t', end='') 
#        print( ) 

#---Option 2  (get one row at a time)-------------------------------------

    row = cursor.fetchone( )                        #get first row 
    while row is not None:                          #loop until no more rows
        data.append(row)                            #save in the 2dim data[ ] list
        row = cursor.fetchone( )                    #get next row

    cursor.close( )                                 #close the cursor/buffer
    conn.close( )                                   #close the connection

#======================================================================
def display( ):

    print("Order# \t Name \t\t Address \t Flavor \t Topping \t CC ")
    print("------ \t -------\t --------\t -------\t --------\t ---")

    for row in data:                                #loop thru the data rows
        first	 = row[0]                           #obtain each element 
        last	 = row[1]
        addr	 = row[2][0:12]                     #take a substring only
        flavor   = row[3][0:12]
        topping  = row[4][0:12]
        cc 		 = row[5][0:6]
        order_id = row[6]

        name = (first+' '+last+'   ')[0:12]
 
        print(order_id,"\t",name,"\t",addr,"\t",flavor,"\t",topping,"\t",cc )

#=======================================================================
#  Main code
#=======================================================================
read_data( );
display( );