XML to SQL or XML into Tables SomeHow

[Robert Roy]

On Thu, 25 May 2000 11:11:32 GMT, thomas at cintra.no (Thomas Weholt)
wrote:

>Hi,
>
>I want to submit a special formattet xml, nothing more than xml based
>on a specified DTD, to a module/script that "transforms" the xml data
>to a valid SQL insert-statement. 
>
>Ex.
>
><xml-data>
>	<database name="test">
>		<table name ="person">
>			<row>
>			    <field field_name="name" value="thomas">
>		  	    <field field_name="sex" value="male">
>			</row>
>			<row>
>			    <field field_name="name" value="bill">
>		  	    <field field_name="sex" value="male">
>			</row>
>			<row>
>			    <field field_name="name" value="jane">
>		  	    <field field_name="sex" value="female">
>			</row>
>		</table>
>	</database>
></xml-data>
>
>I want an output like :
>
># SQL-statements
>
>use test;
>insert into person
>(name, sex)
>values
>("thomas", "male");
>
>insert into person
>(name, sex)
>values
>("bill", "male");
>
>insert into person
>(name, sex)
>values
>("jane", "female");
>
>##################
>
>Perhaps the entries must have a specified type like
><field field_name="name" value="thomas" field_type="string"> etc.
>
>For simple insertion statements this shouldn`t that hard to do, but
>I`m having a hard time locating the stuff to do it.
>
>I`ve looked at RAX, SAX, Pyxie etc. If somebody could give me a hint
>on how to "transform" the example above I`d be happy to try and make a
>more generic module out of it. I just need some help to get started.
>
>Thomas

This is a quick and dirty implementation based on xmllib. I had to
change one attribute name from xml-data  to xml_data to comply with
naming conventions though there are ways of getting around that.

Hope this helps

Bob

###################

import xmllib, string

class myparser(xmllib.XMLParser):
    def __init__(self):
        xmllib.XMLParser.__init__(self)
        self.currentdatabase = ''
        self.currenttable = ''
        self.currentitems = []
        self.currentstatements = []
#        self.currentdata = []
        
    def handle_data(self, data):
#        self.currentdata.append(data)
        pass
        
    def start_xml_data(self, attrs):
        pass
                
    def end_xml_data(self):
        pass

    def start_database(self, attrs):
        self.currentdatabase = attrs['name']
                
    def end_database(self):
        print 'use %s;' % self.currentdatabase
        print string.join(p.currentstatements, '\n')        
        self.currentdatabase = ''
        self.currentstatements = []

    def start_table(self, attrs):
        self.currenttable = attrs['name']
                
    def end_table(self):
        self.currenttable = ''

    def start_row(self, attrs):
#        self.currentdata = []
        pass
                
    def end_row(self):
        fields = []
        values = []
        for f, v, t in self.currentitems:
            if f:
                fields.append(f)
                values.append(v, t)
        fieldz = string.join(fields,',')
        valz = string.join(map(typemark, values), ',')
        stmt = 'insert into %s (%s) values (%s);' %
(self.currenttable, fieldz ,valz) 
        self.currentitems = []
        self.currentstatements.append(stmt)
        
    def start_field(self, attrs):
        # if no field_name will ignore later
        self.currentitems.append((attrs.get('field_name', None),
attrs.get('value', ''), attrs.get('field_type','string')))
                
    def end_field(self):
        pass

def typemark(value_type):
    v, t = value_type
    if t == 'string':
        return '"%s"' % v
    else:
        return v

### some test code
data= """\
<xml_data>
	<database name="test">
		<table name ="person">
			<row>
			    <field field_name="name" value="thomas">
		  	    <field field_name="sex" value="male">
		  	    <field field_name="age" value="21"
field_type="int">
			</row>
			<row>
			    <field field_name="name" value="bill">
		  	    <field field_name="sex" value="male">
			</row>
			<row>
			    <field field_name="name" value="jane">
		  	    <field field_name="sex" value="female">
			</row>
		</table>
	</database>
</xml_data>
"""

if __name__ == "__main__":
    p=myparser()
    p.feed(data)
    p.close()