FCM #85 Programming in Python # 54

# makedb.py
# DMC.xml to SQLite database
# For Full Circle Magazine #85

import apsw
from xml.etree import ElementTree as ET

tablename = "DMC"

def ReadXML():
    global connection
    global cursor
    fn = 'dmc.xml'
    tree = ET.parse(fn)
    root = tree.getroot()
    cntr = 0
    for floss in root.findall('floss'):
        name = floss.find('name').text
        desc = floss.find('description').text
        for colour in floss.findall('color'):
            red = colour.find('red').text
            green = colour.find('green').text
            blue = colour.find('blue').text
        SQL = "INSERT INTO DMC (DMC,Description,Red,Green,Blue) VALUES \
              ('%s','%s',%s,%s,%s)" % (name,desc,red,green,blue)
        cursor.execute(SQL)
        print "Working record {0}".format(cntr)
        cntr += 1

def OpenDB():
    global connection
    global cursor
    global ucursor
    global dbname
    connection = apsw.Connection("floss.db3")
    cursor = connection.cursor()
    ucursor = connection.cursor()

def MakeTables():
    sql = '''CREATE TABLE IF NOT EXISTS DMC
             (pkID INTEGER PRIMARY KEY, DMC INTEGER,
             Description TEXT, Red INTEGER, Green INTEGER, Blue INTEGER,
             HEX TEXT,H INTEGER,S INTEGER,V INTEGER)'''
    cursor.execute(sql)

def EmptyTables():
    sql="DELETE FROM %s" % tablename
    cursor.execute(sql)

def rgb2hex(rgb):
    return '%02x%02x%02x' % rgb

def rgb2hsv(r, g, b):
    r, g, b = r/255.0, g/255.0, b/255.0
    mx = max(r, g, b)
    mn = min(r, g, b)
    df = mx-mn
    if mx == mn:
        h = 0
    elif mx == r:
        h = (60 * ((g-b)/df) + 360) % 360
    elif mx == g:
        h = (60 * ((b-r)/df) + 120) % 360
    elif mx == b:
        h = (60 * ((r-g)/df) + 240) % 360
    if mx == 0:
        s = 0
    else:
        s = df/mx
    v = mx
    return int(round(h,0)), int(round(s*100,0)), int(round(v*100,0))

def UpdateDB():
    global ucursor
    global cursor
    sql = "SELECT * FROM DMC"
    c = cursor.execute(sql)

    for i in c:
        pkid = i[0]
        r = i[3]
        g = i[4]
        b = i[5]
        hex = rgb2hex((r,g,b))
        h,s,v = rgb2hsv(r,g,b)
        sql2 = "UPDATE DMC SET hex='{0}',h={1},s={2},v={3} WHERE \
                pkid = {4}".format(hex,h,s,v,pkid)
        print ("Updating record {0}".format(pkid))
        ucursor.execute(sql2)

OpenDB()
MakeTables()
EmptyTables() # Just to be safe
ReadXML()
UpdateDB()
print "Finished"