Pxi5tnii

1# coding: utf-8
2import glob, MySQLdb, os.path, re
3create_table = '''
4CREATE TABLE `unboundbible`.`%s` (
5    `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
6    `book_id` char(3) NOT NULL,
7    `chapter` int NOT NULL,
8    `paragraph` int NOT NULL,
9    `verse` text NOT NULL,
10    PRIMARY KEY (`id`),
11    UNIQUE `uniq_verse`(book_id, chapter, paragraph)
12) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci COMMENT='%s';
13'''
14conn = MySQLdb.connect(user='root', db='unboundbible', use_unicode=True, charset='utf8')
15c = conn.cursor()
16for name in glob.glob('*'):
17    if not os.path.isdir(name): continue
18    if not parse: continue
19    print name
20    hasz = {}
21    filepath = '%s/%s_utf8.txt' % (name, name)
22    for line in open(filepath):
23        if not line.strip(): continue
24        line = unicode(line, 'utf-8').strip()
25        if line.startswith('#'):
26            if line.startswith('#name'):
27                label = line.split('#name')[1].strip()
28            if line.startswith('#language'):
29                language = line.split('#language')[1].strip()
30                table_name =  "bible_%s_%s" % (language, name.lower())
31                c.execute("DROP TABLE IF EXISTS %s" % table_name)
32                c.execute(create_table % (table_name, label.replace("'", "'")))
33            continue
34        row = line.split('\t')
35        if len(row) > 4:
36            book_id, chapter, paragraph, verse = row[0], row[1], row[2], row[-1]
37        elif len(row) == 4:
38            book_id, chapter, paragraph, verse = row
39        else:
40            continue
41        try:
42            key = (book_id, chapter, paragraph)
43            sql = "INSERT INTO "+table_name+" (book_id,chapter,paragraph,verse) VALUES(%s,%s,%s,%s)"
44            x = c.execute(sql, (book_id, chapter, paragraph, verse))
45        except Exception, error:
46            if hasz.has_key(key):
47               hasz[key] += 1
48            else:
49                hasz[key] = 1
50            c.execute("SELECT id, verse FROM "+table_name+" WHERE book_id=%s AND chapter=%s AND paragraph=%s", (book_id, chapter, paragraph))
51            pk, old_verse = c.fetchone()
52            data = ("%s (%s) %s" % (old_verse, hasz[key], verse), pk)
53            c.execute("UPDATE "+table_name+" SET verse=%s WHERE id=%s", data)