text to DB

[Praveen]

I have a text file in this format
PRA 1:13 2:20 3:5
SRA 1:45 2:75 3:9
TRA 1:2 2:65 3:45

pattern is- Book Chapter:Verses

now i have my DB schema like this
book_id chapter_id versed_id
1 1 13
1 2 20
1 3 5
2 1 45
2 2 75
2 3 9
3 1 2
3 2 65
3 3 45

I want to write a pyhton script which read the text file and dump to
DB

could any one give me suggestion

 

[MRAB]

I have a text file in this format
PRA 1:13 2:20 3:5
SRA 1:45 2:75 3:9
TRA 1:2 2:65 3:45

pattern is- Book Chapter:Verses

now i have my DB schema like this
book_id chapter_id versed_id
1 1 13
1 2 20
1 3 5
2 1 45
2 2 75
2 3 9
3 1 2
3 2 65
3 3 45

I want to write a pyhton script which read the text file and dump to
DB

could any one give me suggestion

Read through the file a line at a time. For the first line the book_id
is 1, for the second it's 2, etc.

Split each line on whitespace, and then for all but the first entry
(which is a name?) split on the colon to get the chapter_id and
versed_id.

Insert each tuple of (book_id, chapter_id, versed_id) into the DB. You
haven't said what type of DB it is, so I can't help you there. Just read
the documentation.

 

[Dennis Lee Bieber]

I have a text file in this format
PRA 1:13 2:20 3:5
SRA 1:45 2:75 3:9
TRA 1:2 2:65 3:45

pattern is- Book Chapter:Verses

now i have my DB schema like this
book_id chapter_id versed_id

Off hand, I'd say that's an incomplete schema since there is not
enough information available to reconstruct the input data...

I'd say there should be a second relation containing

book_id book_name

could any one give me suggestion

Is there a possibility that a given book "name" can appear multiple
times?

 

[Praveen]

        Off hand, I'd say that's an incomplete schema since there is not
enough information available to reconstruct the input data...

        I'd say there should be a second relation containing

book_id book_name




        Is there a possibility that a given book "name" can appear multiple
times?

f = open("/Users/Trellisys/Desktop//BibleDB/Booktable.txt","r")
bk=[]
cv = []
j=1
d={}
for line in f:
for l in line.split():
if l.isalnum():
bk.append(j)
j = j+1
else:
cv.append(l.split(":"))
'''for i in l.split(":"):
if i.isalpha():
#print i,j
j = j+1
else:
#print i'''

print bk
print cv
output
[1,2,3] but i am not getting how to map chapterId and versesId with
bookId
Regards,
Praveen

 

[Dennis Lee Bieber]

output
[1,2,3] but i am not getting how to map chapterId and versesId with
bookId

<sigh> You also didn't answer the question about multiple
occurrences of "book" so I've created an extended sample input file:

-=-=-=-=-=-
PRA 1:13 2:20 3:5
SRA 1:45 2:75 3:9
TRA 1:2 2:65 3:45
PRA 3:6-10
2:89-103 5:5
-=-=-=-=-=-

Note that I've added a second "PRA" line after the others (so they
aren't in alphabetical order); I've added start-end verse notation; AND
I've added a line with no "book" at the start -- representing a
continuation of the previous line.

Code to process this follows is supposed to follow -- but it seems I
attached the wrong file to the email from work (I can't post from work,
so I had to email the program home... but didn't <G>)

You'll have wait for tomorrow night to see the code. However, the
database looks like:

CREATE TABLE Book
(
ID integer primary key autoincrement unique,
titleCode varchar unique
)
CREATE TABLE Citation
(
ID integer primary key autoincrement unique,
bookID integer not null,
chapter integer not null,
startVerse integer not null,
endVerse integer default null,
foreign key (bookID) references Book (ID)
)

With (in SQL export format) data of:

INSERT INTO "Book" VALUES(1,'PRA');
INSERT INTO "Book" VALUES(2,'SRA');
INSERT INTO "Book" VALUES(3,'TRA');

INSERT INTO "Citation" VALUES(1,1,1,13,NULL);
INSERT INTO "Citation" VALUES(2,1,2,20,NULL);
INSERT INTO "Citation" VALUES(3,1,3,5,NULL);
INSERT INTO "Citation" VALUES(4,2,1,45,NULL);
INSERT INTO "Citation" VALUES(5,2,2,75,NULL);
INSERT INTO "Citation" VALUES(6,2,3,9,NULL);
INSERT INTO "Citation" VALUES(7,3,1,2,NULL);
INSERT INTO "Citation" VALUES(8,3,2,65,NULL);
INSERT INTO "Citation" VALUES(9,3,3,45,NULL);
INSERT INTO "Citation" VALUES(10,1,3,6,10);
INSERT INTO "Citation" VALUES(11,1,2,89,103);
INSERT INTO "Citation" VALUES(12,1,5,5,NULL);

 

[Dennis Lee Bieber]

Code to process this follows is supposed to follow -- but it seems I
attached the wrong file to the email from work (I can't post from work,
so I had to email the program home... but didn't <G>)

As threatened... the code (watch out for line wrapping -- I see two
wrapped lines in my client here):

-=-=-=-=-=-=-
import sqlite3 as db
import os

DBNAME = "BibleDB.db"
INPUT = "BookTable.txt"

def createDB(dbn):
con = db.connect(dbn)
cur = con.cursor()
cur.execute("""create table Book
(
ID integer primary key autoincrement unique,
titleCode varchar unique
)""" )
cur.execute("""create table Citation
(
ID integer primary key autoincrement unique,
bookID integer not null,
chapter integer not null,
startVerse integer not null,
endVerse integer default null,
foreign key (bookID) references Book (ID)
)""" )
con.commit()
cur.close()
con.close

def loadDB(dbn, ifn):
con = db.connect(dbn)
cur = con.cursor()
bid = None
btitle = None
chapter = None
vstart = None
vend = None
fin = open(ifn, "r")
for ln in fin:
items = ln.split()
#look for book code as first word -- must be all alpha
if items[0].isalpha():
btitle = items[0]
#find code in book table
cur.execute("select ID from Book where titleCode = ?",
(btitle,))
data = cur.fetchall()
con.commit()
if not data:
#not found, insert new book, retrieve its ID number
cur.execute("insert into Book (titleCode) values (?)",
(btitle,))
bid = cur.lastrowid
con.commit()
else:
#fetch retrieved book ID number
bid = data[0][0]
#remove book code from list of items
items = items[1:]

for item in items:
if ":" in item:
chapter, verses = item.split(":")
chapter = int(chapter)
if "-" in verses:
vstart, vend = verses.split("-")
vstart = int(vstart)
vend = int(vend)
else:
vstart = int(verses)
vend = None
cur.execute("""insert into Citation
(bookID, chapter, startVerse, endVerse)
values (?, ?, ?, ?)""",
(bid, chapter, vstart, vend))
else:
print "Invalid entry for %s: '%s'" % (btitle, item)
con.commit()

fin.close()
cur.close()
con.close()

def dumpDB(dbn):
con = db.connect(dbn)
cur = con.cursor()
print "Book\tChapter\tVerses"
cur.execute("""select b.titleCode, c.chapter, c.startVerse,
c.endVerse
from Book as b inner join Citation as c
on b.ID = c.bookID
order by b.titleCode, c.chapter, c.startVerse""")
for (title, chapter, start, end) in cur:
if not end:
verses = "%s" % start
else:
verses = "%s-%s" % (start, end)
print "%4s\t %s\t %s" % (title, chapter, verses)
con.commit()
cur.close()
con.close()


if __name__ == "__main__":
if os.path.exists(DBNAME):
print "*** Database %s exists! Deleting to create new database"
% DBNAME
os.unlink(DBNAME)
print "*** Creating empty database schema"
createDB(DBNAME)
print "*** Loading data"
loadDB(DBNAME, INPUT)
print "*** Dumping contents"
dumpDB(DBNAME)
print "*** Complete"