# Jay Summet
# CS 1803 Example Program
# Copyright 2010
#
# This example program demonstrates retrieving data from a
# MySQL database and exporting it as an XML file.
#
# Complication: We want to separate out items by date. So, we
# will create a new <date value="2010-11-28"> </date> element for
# each individual day in the database. To do this, we will sort the
# data by date, and every time the date changes, we will start a new
# date element with all items from that date as sub-elements.
#
#

#We are using a version of pymysql that works with python 3.
#This is not installed by default with python, so you must find and
#install it on your computer before you can import it.
import pymysql

#Python 3 has several XML parsing modules built in that we can
#choose from. We are using the ElementTree module, and renaming it as
# etree when we do the import:
import xml.etree.ElementTree as etree


# This method retrieves the data from the database and
# returns it as a list of tuples (ordered by date!)
def retrieveData(dbhost, username, password):

    #connect to the database:
    db = pymysql.connect( host=dbhost, passwd= password,
                          user=username, db='cs1803_ie')

    #Create a cursor to use for our query
    cursor = db.cursor()

    #Execute the query. (Note, 'demand' is the name of the table,
    # and is ALSO the name of a data column IN the table...perhaps
    # this is an example of bad data design/naming conventions...)

    sql = "SELECT name, date, demand FROM demand ORDER BY date"
    cursor.execute(sql)

    #Copy  the data from the cursor object into a list of tuples...

    #The first item (name - index 0) is a string.
    #
    #The 2nd item (date - index 1) is returned as a datetime.date
    #object. At some point we will have to convert this into a text
    #string to export to XML!
    #
    #The third item (demand - index 2) is an integer, which will also have to
    #be converted into a string before the XML export.
    # (Also, depending upon who has access to your database, you may
    #  need to watch out for malformed data...)
    data = []
    for item in cursor:
        data.append(item)

    return(data)


#This method will export data to an XML file. It is expecting the
# data to be in the same format as retrieved by the retrieveData
#function. (namely, a list of tuples of 3 items: Name, Date, DemandNumber)
#
# It will create an XML file with a root of "DemandData", that has a
# sub element for each Day. Each Day sub-element will have Item
# sub-elements that hold data for each item sold on that day.
#
# We will use attributes to specify the date of the day and the
# name of the item.
# We will use tag text to specify the demand for each item.
#
# This function is taking database data ordered in records, and structuring
# it into a format more suitable for XML.
#
def exportData(data, filename):
    #first, we create a root for our tree.
    root = etree.Element("DemandData")

    #We will re-create a NEW date element every time the date
    #string changes from the current date string...
    # We are counting on whoever retrieved the data to order it so
    # that all records with the same day are grouped together!
    
    dayElement = None
    lastDateString = None

    for item in data:
        #Figure out the date string of this item.
        itemDateString = str( item[1] )
        if lastDateString != itemDateString:
            #We have a new date!
            #Append the old date to the root (if it exists)
            if dayElement != None:
                root.append(dayElement)
            #Create a new date Element, with date attribute!
            dayElement = etree.Element("Day", date=itemDateString)

            #update the lastDateString:
            lastDateString = itemDateString

        #Regardless of if this is a new date or not, we need to
        #create an item element and add it to the Day element.
        #Because the name is (supposed to be) a string, we technically
        #don't NEED to convert item[0] to a string, but we do it
        #anyways to be safe!
        itemElement = etree.Element("Item", name= str( item[0] ))
        #Use tag text to hold the demand for this item.
        #Note the string conversion!
        itemElement.text = str(item[2])

        #Add this item element as a sub-element of the dateElement
        dayElement.append(itemElement)
    


    #After we have added all items to the root, we need to create an
    #actual tree that contains the root:
    tree = etree.ElementTree(root)

    #After we have added all items to our XML tree (in memory)
    #we need to write the tree out to disk. (This is where most
    #exceptions will occur...)

    tree.write(filename, "UTF-8")




    
ourData = retrieveData( 'academic-mysql.cc.gatech.edu',
                        'cs1803_ie', 'SECRET_PASSWORD')

exportData(ourData, "dataOut.xml")
print("Wrote the file!")