Import data from xml file into two tables w/ foreign key at MySQL database
Question: I need to load file of the following format into MySQL database.

    
    
    <item value="{$\emptyset $}">
       <subitem value="(empty language)"></subitem>
       <subitem value="(empty set)"></subitem>
    </item>
    <item value="{$\subseteq$ (subset)}">
    </item>
    <item value="{$\subset$ (proper subset)}">
    </item>
    <item value="{$:$ (such that)}">
    </item>
    <item value="{$\cap$ (set intersection)}">
    </item>
    <item value="{$\cup$ (set union)}">
    </item>
    <item value="{$-$ (set difference)}">
    </item>
    <item value="{$\left | \mskip \medmuskip \right |$}">
       <subitem value="(flow value)"></subitem>
       <subitem value="(length of a string)"></subitem>
       <subitem value="(set cardinality)"></subitem>
    </item>
    

I think in database it should be represented by two tables, Subitem table
should contain foreign key:

Item <\-- Subitem

I want to do it with python. Is it possible to accomplish it with MySQL
instructions only, or it is better to load xml file in python, create both
tables manually and then insert all entries into tables i want?


Answer: I was able to do it by reading xml with python and then inserting it into
MySQL database. First one need to install needed software:

    
    
    sudo apt install mysql-server
    sudo apt-get install python-mysqldb
    

Then this py-file will do the job:

    
    
    import xml.etree.ElementTree
    import MySQLdb
    
    try:
        db = MySQLdb.connect(host="localhost",
                             user="root",
                             passwd="!") 
    
        cur = db.cursor()
    
        cur.execute("DROP DATABASE IF EXISTS i2a")
        cur.execute("CREATE DATABASE i2a")
        cur.execute("USE i2a")
        print "Created database"
    
        cur.execute("""
            CREATE TABLE Item (
                id INT NOT NULL AUTO_INCREMENT,
                `value` VARCHAR(255) NOT NULL,
                PRIMARY KEY (`id`)
            )
            DEFAULT CHARACTER SET = utf8
            COLLATE = utf8_bin""")
        print "Created Item table"
    
        cur.execute("""
            CREATE TABLE Subitem (
                id INT NOT NULL AUTO_INCREMENT,
                item_id INT NOT NULL,
                `value` VARCHAR(255) NOT NULL,
                PRIMARY KEY (`id`),
                FOREIGN KEY (item_id) REFERENCES Item(id) ON DELETE RESTRICT
            )
            DEFAULT CHARACTER SET = utf8
            COLLATE = utf8_bin""")
        print "Created Subitem table"
    
        e = xml.etree.ElementTree.parse('index.xml').getroot()
    
        for item in e.findall('item'):
            cur.execute("INSERT INTO Item (value) VALUES (%s)", [item.get('value')])
            for subitem in item:
                cur.execute("INSERT INTO Subitem (item_id, value) VALUES (%s, %s)", (db.insert_id(), subitem.get('value')))
        print "All data are there!"
    except Exception, e:
         print str(e)
    

