Konstruksjon av XML fra database

Hva:

Genererer XML fra database

I denne modulen skal vi konstruere den opprinnelige XML-fila for olympiade eksempelet fra den databasestrukturen vi lage i modulen XML-->SQL.Eksempelet bruker MySQL.

Vi kan gjøre dette på forskjelige måter. Her er valgt en løsning som bruker et Pythonskript. Vi kunne tenke oss en kombinasjon av Python og XSLT som alternativ.

Databasen har en tabell som er satt opp slik:

	CREATE TABLE results(
		resultid  INT PRIMARY KEY AUTO_INCREMENT,
		place VARCHAR(20),
		year INT,
		event VARCHAR(10),
		name VARCHAR(35),
		nation VARCHAR(10),
		result VARCHAR(10)
	);

Skriptet er laget slik at det skal kunne handtere vilkårlige olympiader, og det skal kunne handtere andre resulteter enn 100m, 200m og 400m.

import MySQLdb
"""
    Use MySQL
    Read a database and establish a XML-structur
    Database table:
    CREATE TABLE results (
        resultid  INT PRIMARY KEY AUTO_INCREMENT,
        place VARCHAR(20),
        year INT,
        event VARCHAR(10),
        name VARCHAR(35),
        nation VARCHAR(10),
        result VARCHAR(10)
    );

"""
XML_FILE='c:\\projects\\pydb\\made_results.xml'

#----------------------------------
# file IO
def storeTextFile(filename,txt):
    try:
        outfile=open(filename,'w')
        outfile.write(txt)
        outfile.close()
    except:
        print 'Error writing file ',filename
#----------------------------------
# connect and execute a sql-request
def connectAndExecute(sql):
    try:
        myBase=MySQLdb.connect(host='localhost',
                               user='root',
                               passwd='hemmelig',
                               db='olympic')
        myTab=myBase.cursor()
        myTab.execute(sql)
        myBase.commit()
        myBase.close()
        return myTab.fetchall()
    except MySQLdb.Error, e:
        print "Error %d: %s" % (e.args[0], e.args[1])
        myBase.close()

#----------------------------------
# build the simple xml-structure:
XML_WRAPPER="""<?xml version="1.0" encoding="ISO-8859-1"?>
<?xml-stylesheet type="text/xsl" href="recordtoall.xslt"?>
<IOC>
%s
</IOC>
"""
XML_FRAGMENT="""
         <athlet>
            <name>%s</name>
            <nation>%s</nation>
            <result>%s</result>
         </athlet>"""

SQL_SELECT_ATHLETS="""SELECT name,nation,result FROM results 
WHERE place='%s' AND event='%s';"""
SQL_SELECT_GAMES="""SELECT place,year FROM results;"""
SQL_SELECT_EVENTS="""SELECT event FROM results;"""
GAMES=[]
EVENTS=[]

# final build
def buildXMLTree():
    xmlstr=''
    for game in GAMES:
        xmlstr+='   <OlympicGame place="%s" year="%s">'%\
        (game[0],game[1])
        for event in EVENTS:
            xmlstr+='\n      <event track="%s">'%event
            results=connectAndExecute(SQL_SELECT_ATHLETS%(game[0],event))
            for ix in range(0,len(results)):
                record=results[ix]
                xmlstr+=XML_FRAGMENT%(record[0],record[1],record[2])
            xmlstr+='</event>\n'
        xmlstr+='</OlympicGame>\n'
    return XML_WRAPPER%xmlstr
        
# find all pairs of place,year 
def prepareGameList():
    #return [['Barcelona','1992'],...['Athens','2004']]
    gamelist=[]
    results=connectAndExecute(SQL_SELECT_GAMES)
    for result in results:
        pair=[result[0],result[1]]
        if pair in gamelist:
            continue
        gamelist.append(pair)
    return gamelist

#find all events
def prepareEventList():
    # return ['100m','200m','400m']
    eventlist=[]
    results=connectAndExecute(SQL_SELECT_EVENTS)
    for result in results:
        event=result[0]
        if event in eventlist:
            continue
        eventlist.append(event)
    return eventlist

GAMES=prepareGameList()
EVENTS=prepareEventList()
storeTextFile(XML_FILE,buildXMLTree())

Referanser og eksempler:

MySQL for Python: sourceforge.net/projects/mysql-python

Produksjon, kontroll og vedlikehold:

Børre Stenseth, okt 2006

Markup og web fra Høgskolen i Østfold: http://www.ia.hiof.no/~borres/ml/