text to DB

Discussion in 'Python' started by Praveen, Aug 13, 2010.

  1. Praveen

    Praveen Guest

    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
     
    Praveen, Aug 13, 2010
    #1
    1. Advertising

  2. Praveen

    MRAB Guest

    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.
     
    MRAB, Aug 13, 2010
    #2
    1. Advertising

  3. On Fri, 13 Aug 2010 09:46:34 -0700 (PDT), Praveen
    <> 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://wlfraed.home.netcom.com/
     
    Dennis Lee Bieber, Aug 14, 2010
    #3
  4. Praveen

    Praveen Guest

    On Aug 14, 11:15 am, Dennis Lee Bieber <> wrote:
    > On Fri, 13 Aug 2010 09:46:34 -0700 (PDT), Praveen
    > <> 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://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
     
    Praveen, Aug 16, 2010
    #4
  5. On Mon, 16 Aug 2010 03:17:06 -0700 (PDT), Praveen
    <> 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
    HTTP://wlfraed.home.netcom.com/
     
    Dennis Lee Bieber, Aug 17, 2010
    #5
  6. On Mon, 16 Aug 2010 22:36:22 -0700, Dennis Lee Bieber
    <> 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
    HTTP://wlfraed.home.netcom.com/
     
    Dennis Lee Bieber, Aug 18, 2010
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Kelvin
    Replies:
    2
    Views:
    584
    Andrew Balmos (abalmos)
    Nov 9, 2004
  2. Jim Owen
    Replies:
    1
    Views:
    456
    Natty Gur
    Jul 24, 2003
  3. Jeremy Chapman

    Load contents of a text file into a text box

    Jeremy Chapman, Aug 15, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    765
    Tommy
    Aug 15, 2003
  4. acko bogicevic

    Align text in text box

    acko bogicevic, Jan 9, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    60,757
    Michelle Stone
    Jan 9, 2004
  5. leo
    Replies:
    1
    Views:
    290
    Bob Lehmann
    Dec 5, 2005
Loading...

Share This Page