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

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

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

<p>Item &lt;-- Subitem</p>

<p>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?</p>

<p>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:</p>

<pre><code>sudo apt install mysql-server
sudo apt-get install python-mysqldb
</code></pre>

<p>Then this py-file will do the job:</p>

<pre><code>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)
</code></pre>

python mysql