Loader to generate android xml resources and sqllite file

csv_loader.py

from lxml import etree as ET
import sqlite3
import os

CREATE_ITEMS = 'create table Item (_id integer primary key, Name text, kind integer, calories integer);'
CREATE_ITEM_PER_MONTHS = 'create table ItemPerMonth (ItemId integer, Month integer, Freshness integer, Area integer);'
CREATE_ITEM_PREFERRED = 'create table PreferredItem (PreferredItemId integer);'

file_list = ['fruit.sqlinit']

areas = ['Italy',
         'SouthernUs',
         'NortheastUs',
         'NorthMidwestUs',
         'MidwestUs',
         'SouthWestUs',
         'PacificNorthwestUs',
         'Germany',
         'Uk'
         ]


def insert_item(cursor, item_id, name, kind, calories):
    statement = "insert into Item values (%d, '%s', %s, %s)"%(item_id, name, kind, calories)
    cursor.execute(statement)

def insert_record_per_month(cursor, item_id, months, area):
    for i in range(0, len(months)):
        freshness = months[i].strip()
        if freshness != '':
            statement = "insert into ItemPerMonth values (%d, %d, %s, %d)"%(item_id, i, freshness, area)
            cursor.execute(statement)

def add_name_to_strings(root, item_name, lang_name):
    name = ET.SubElement(root, "string")
    name.set("name", item_name)
    if lang_name.find('%') != -1:
        name.set("formatted", "false")
    name.text = lang_name

def process_descriptions(c, item_ids):
    f = open('descriptions.tsv', 'r')
    c.execute(CREATE_ITEMS)
    it_names_root = ET.Element("resources")
    it_descriptions_root = ET.Element("resources")
    en_names_root = ET.Element("resources")
    en_descriptions_root = ET.Element("resources")
    record_id = 0
    for line in f:
        values = line.decode('utf-8').strip().split('\t')
        item_name = values[0].strip().capitalize()
        item_type = values[1]
        calories = values[2]
        it_name = values[3].strip().replace('"', '').replace("'","\\'")
        it_desc = values[4].strip().replace('"', '').replace("'","\\'")
        en_name = values[5]
        en_desc = values[6].strip().replace('"', '').replace("'","\\'")
        add_name_to_strings(it_names_root, item_name, it_name)
        add_name_to_strings(it_descriptions_root, item_name + '_desc', it_desc)
        add_name_to_strings(en_names_root, item_name, en_name)
        add_name_to_strings(en_descriptions_root, item_name + '_desc', en_desc)
        insert_item(c, record_id, item_name, item_type, calories)
        item_ids[item_name] = record_id
        record_id = record_id + 1

    names_tree = ET.ElementTree(it_names_root)
    names_tree.write("it_names.xml", pretty_print=True, encoding='utf-8')
    desc_tree = ET.ElementTree(it_descriptions_root)
    desc_tree.write("it_descriptions.xml", pretty_print=True, encoding='utf-8')
    names_tree = ET.ElementTree(en_names_root)
    names_tree.write("en_names.xml", pretty_print=True, encoding='utf-8')
    desc_tree = ET.ElementTree(en_descriptions_root)
    desc_tree.write("en_descriptions.xml", pretty_print=True, encoding='utf-8')
    
    
def process_file(c, config_file, area, item_ids):
    print 'Processing file ' + config_file
    f = open(config_file, 'r')
    for line in f:
        values = line.decode('utf-8').strip().split('\t')
        name = values[0].strip().capitalize()
        id = item_ids[name]
        months = values[1:]
        insert_record_per_month(c, id, months, area)



conn = sqlite3.connect('fruit')
c = conn.cursor()
c.execute(CREATE_ITEM_PER_MONTHS)
c.execute(CREATE_ITEM_PREFERRED)
item_ids = {}
process_descriptions(c, item_ids)

for idx, area in enumerate(areas):
    filename = area.lower() + '.tsv'
    process_file(c, filename, idx, item_ids)

conn.commit()
conn.close()