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 XML2SQL .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, eller vi kunne laget en XML-dump direkte fra databasen, se modulen Databasedump .

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:\\articles\\ml\\olymp\\ol2db\\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"?>
<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 dist="%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

MySQL for Python sourceforge.net sourceforge.net/projects/mysql-python 14-03-2010

Vedlikehold

Børre Stenseth, okt 2006

( Velkommen ) XSL > XSLT > Olympiade >DB2XML ( Ting og tang )