1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
import re
import sqlite3
filename = 'bibles.txt'
dbname = 'bibles.db'
def connect():
return sqlite3.connect(dbname)
def create_tables(conn):
conn.execute('CREATE TABLE IF NOT EXISTS book '
'(name TEXT PRIMARY KEY)')
conn.execute('CREATE TABLE IF NOT EXISTS reference '
'(book TEXT REFERENCES book (name) '
'ON DELETE RESTRICT ON UPDATE CASCADE, '
'chapter INTEGER, '
'verse INTEGER, '
'PRIMARY KEY (book, chapter, verse))')
conn.execute('CREATE TABLE IF NOT EXISTS translation '
'(name TEXT PRIMARY KEY)')
conn.execute('CREATE TABLE IF NOT EXISTS text '
'(translation TEXT REFERENCES translation (name) '
'ON DELETE RESTRICT ON UPDATE CASCADE, '
'book TEXT, '
'chapter INTEGER, '
'verse INTEGER, '
'text TEXT, '
'PRIMARY KEY (translation, book, chapter, verse), '
'FOREIGN KEY (book, chapter, verse) '
'REFERENCES reference (book, chapter, verse) '
'ON DELETE RESTRICT ON UPDATE RESTRICT)')
conn.commit()
def read_file():
content = []
with open(filename) as f:
content = f.read().splitlines()
columns = content[0]
return (columns.split('\t'), content[1:])
def read_verse(columns, line):
pieces = line.split('\t')
ref = pieces[0]
refm = re.search('([\w ]+?)\s*(\d+):(\d+)', ref)
try:
parsed = { 'book' : refm.group(1),
'chapter' : int(refm.group(2)),
'verse' : int(refm.group(3)),
'translations' : {} }
for i, col in enumerate(columns[1:]):
parsed['translations'][col] = pieces[i + 1].strip()
return parsed
except AttributeError:
print('Something wrong with your regex.\n', str(pieces))
def main():
conn = connect()
create_tables(conn)
columns, verses = read_file()
for translation in columns[1:]:
conn.execute('INSERT OR IGNORE INTO translation (name) VALUES (?)',
(translation,))
cur_book, cur_chapter = '', 0
for v in verses:
v = read_verse(columns, v)
if cur_book != v['book'] or cur_chapter != v['chapter']:
print(v['book'], v['chapter'])
cur_book, cur_chapter = v['book'], v['chapter']
conn.execute('INSERT OR IGNORE INTO book (name) VALUES (?)',
(v['book'],))
conn.execute('INSERT OR IGNORE INTO reference (book, chapter, verse) '
'VALUES (?, ?, ?)', (v['book'], v['chapter'], v['verse']))
for translation, text in v['translations'].items():
conn.execute('INSERT OR IGNORE INTO text '
'(translation, book, chapter, verse, text) '
'VALUES (?, ?, ?, ?, ?)',
(translation, v['book'], v['chapter'], v['verse'], text))
conn.commit()
conn.close()
if __name__ == '__main__':
main()
|
