title: How to import csv data with parent/child (category-subcategory) hierarchy to MySQL using Python
body: <p>I am importing a csv file containing a parent/child (category-subcategory) hierarchy to MySQL, using Python's MySQLdb module. Here is an example csv file:</p>
  
  <pre class="lang-none prettyprint-override"><code>vendor,category,subcategory,product_name,product_model,product_price
  First vendor,category1,subcategory1,product1,model1,100
  First vendor,category1,subcategory2,product2,model2,110
  First vendor,category2,subcategory3,product3,model3,130
  First vendor,category2,subcategory4,product5,model7,190
  </code></pre>
  
  <p>In MySQL I want to use a <code>category</code> table with a hierarchical structure, like this:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `category` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`category_id`),
  KEY `parent_id` (`parent_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
  </code></pre>
  
  <p><strong>My question is:</strong> How do I determine the <code>parent_id</code> in this table?</p>
  
  <p>Here is the Python script I have so far:</p>
  
  <pre><code>import MySQLdb
  import csv
  con = MySQLdb.connect('localhost', 'root', '', 'testdb', use_unicode=True, charset='utf8')
  with con:
      cur = con.cursor()
      csv_data = csv.reader(file('test.csv'))
      csv_data.next()
      for row in csv_data:
          cur.execute("SELECT manufacturer_id FROM manufacturer WHERE name=%s", [row[0]],)
          res = cur.fetchall()
          if res:
              vendor_id = res[0][0]
          else:
              cur.execute("INSERT INTO manufacturer (name) VALUES (%s)", (row[0],))
              vendor_id = cur.lastrowid
          cur.execute("SELECT category_id FROM category_description WHERE name=%s", [row[2]])
          res = cur.fetchall()
          if res:
              category_id = res[0][0]
          else:
  
              # What parent_id should be inserted here?
              cur.execute("INSERT INTO category (`status`, `parent_id`) VALUES (%s,%s)", (1,))
  
              category_id = cur.lastrowid
              cur.execute("INSERT INTO category_description (category_id, name) VALUES (%s,%s)", (category_id,row[2],))
              cur.execute("INSERT INTO product (model, manufacturer_id, price,) VALUES (%s, %s, %s)", (row[4], `vendor_id`, row[8],))                           
              product_id = cur.lastrowid
              cur.execute("INSERT INTO product_to_category (product_id, category_id) VALUES (%s, %s)", (product_id, category_id,))
      cur.commit()
  </code></pre>
  
  <p>Here are the definitions of the other tables used in my example:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `manufacturer` (
  `manufacturer_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`manufacturer_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
  
  CREATE TABLE IF NOT EXISTS `category_description` (
  `category_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`category_id`,`language_id`),
  KEY `name` (`name`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
  
  CREATE TABLE IF NOT EXISTS `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `model` varchar(64) NOT NULL,
  `manufacturer_id` int(11) NOT NULL,
  `price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (`product_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
  
  CREATE TABLE IF NOT EXISTS `product_to_category` (
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`product_id`,`category_id`),
  KEY `category_id` (`category_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
  </code></pre>