Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > text to DB

Reply
Thread Tools

text to DB

 
 
Praveen
Guest
Posts: n/a
 
      08-13-2010
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
 
Reply With Quote
 
 
 
 
MRAB
Guest
Posts: n/a
 
      08-13-2010
Praveen wrote:
> 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.
 
Reply With Quote
 
 
 
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      08-14-2010
On Fri, 13 Aug 2010 09:46:34 -0700 (PDT), Praveen
<(E-Mail Removed)> declaimed the following in
gmane.comp.python.general:

> 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?

--
Wulfraed Dennis Lee Bieber AF6VN
http://www.velocityreviews.com/forums/(E-Mail Removed) HTTP://wlfraed.home.netcom.com/

 
Reply With Quote
 
Praveen
Guest
Posts: n/a
 
      08-16-2010
On Aug 14, 11:15*am, Dennis Lee Bieber <(E-Mail Removed)> wrote:
> On Fri, 13 Aug 2010 09:46:34 -0700 (PDT), Praveen
> <(E-Mail Removed)> declaimed the following in
> gmane.comp.python.general:
>
> > 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?
>
> --
> * * * * Wulfraed * * * * * * * * Dennis Lee Bieber * * * * AF6VN
> * * * * (E-Mail Removed) * *HTTP://wlfraed.home.netcom.com/


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
 
Reply With Quote
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      08-17-2010
On Mon, 16 Aug 2010 03:17:06 -0700 (PDT), Praveen
<(E-Mail Removed)> declaimed the following in
gmane.comp.python.general:

> 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);



--
Wulfraed Dennis Lee Bieber AF6VN
(E-Mail Removed) HTTP://wlfraed.home.netcom.com/

 
Reply With Quote
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      08-18-2010
On Mon, 16 Aug 2010 22:36:22 -0700, Dennis Lee Bieber
<(E-Mail Removed)> declaimed the following in
gmane.comp.python.general:


>
> 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"


--
Wulfraed Dennis Lee Bieber AF6VN
(E-Mail Removed) HTTP://wlfraed.home.netcom.com/

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Controlling text in a Text Area or Text leo ASP General 1 12-05-2005 01:13 AM
Encryption from text to text... Kelvin Perl 2 11-09-2004 03:17 PM
Align text in text box acko bogicevic ASP .Net 4 01-09-2004 05:21 PM
Load contents of a text file into a text box Jeremy Chapman ASP .Net 1 08-15-2003 10:10 PM
Display text in text box - should be simple, doesn't work Jim Owen ASP .Net 1 07-24-2003 05:37 AM



Advertisments