cElementTree ftw!

#!/usr/bin/python

# usage: ./mylittlexmlscript.py

import shutil, sys, MySQLdb as Database
from xml.etree import cElementTree as ET

# the completed files should be moved out of the way as they're generated
# well, sure, they don't *have* to be - I just like keeping things neat and tidy
processed = "/someplace/toput/allthat/xml"

db = Database.connect(host="myhostname", port=0000, user="myusername", passwd="mypassword", db="")
cursor = db.cursor()

def walktheserver():
    """ walk the server, generate a list of dbs """
    sql = """SELECT table_schema FROM information_schema.tables GROUP BY table_schema"""
    cursor.execute(sql)
    dbnames = cursor.fetchall()
    return dbnames

def gettables(dbname):
    """ return all the table names for a specific db """
    tables = []

    sql = """SHOW TABLES FROM %s"""%(dbname)
    cursor.execute(sql)
    rows = cursor.fetchall()

    for table in rows:
        ltable = list(table)
        tables.append(ltable[0])

    return tables

def generate_xml(dbname):
    """ check to see if the db has either mailing list or user table 
        and generate the xml accordingly """

    tables = gettables(dbname)

    if "mailinglist" in tables:
	create_xml(dbname, 'ml') # ml = mailing list

    if "users" in tables:
    	create_xml(dbname, 'up') # up = user profiles

def create_xml(dbname, type):
    """ here's where we're actually going to put cElementTree to work """

    outputfile = dbname+"_"+type+".xml"    # name the output file

    BSWrapper = ET.Element("BSWrapper")    # create the root element of the XML tree
    Header = ET.SubElement(BSWrapper, "Header")    # create the first subelement, and give it a few attributes
    Header.set("AccountID", "996")
    Header.set("ListID", "885")
    Header.set("UserID", "774")

    sql = """USE %s"""%(dbname)
    cursor.execute(sql)
    if type is 'fcm':
	sql = """SELECT fcm.email AS Email, fcm.first_name AS FirstName 
	    FROM mailinglist AS fcm GROUP BY fcm.email ORDER BY Email"""
    if type is 'w3t':
        sql = """SELECT w3t.U_Email AS Email, w3t.U_First_Name AS FirstName
	    FROM users AS w3t GROUP BY Email ORDER BY Email"""
    cursor.execute(sql)
    rows = cursor.fetchall()

    for record in rows:
        data = ET.SubElement(BSFImport, "data")
        Email = ET.SubElement(data, "Email")
        Firstname = ET.SubElement(data, "Firstname")
	
        try:
	    email = record[0].encode('iso-8859-1')
	except AttributeError: 
	    email = ''
	except UnicodeDecodeError:
	    email = ''

        try:
            name = record[1].encode('iso-8859-1')
        except AttributeError:
            name = ''
        except UnicodeDecodeError:
            name = ''

	Email.text = email
        Firstname.text = name

    tree = ET.ElementTree(BSFImport)
    tree.write(outputfile)
    shutil.move(outputfile, processed)

def main():
    dblist = walktheserver()
    # they're not "bad", per se - I would never pass that kind of judgement on a database
    # these are just the dbs to be excluded from consideration
    badlist = ['information_schema', 'artist1', 'artist2', 'artist3']
    for dbname in dblist:
        if dbname[0] not in badlist:
            # print dbname[0]
            newxml = generate_xml(dbname[0])

    cursor.close()
    db.close()

if __name__ == "__main__":
    main()

<BSWrapper><Header AccountID="996" ListID="885" UserID="774" /><data><Email>12ewoldt@yahoo.com</Email>
<Firstname>christian</Firstname></data><data><Email>1995fxstc@sbcglobal.net</Email><Firstname>Daniel</Firstname>
</data></BSWrapper>