python, reading in text file and inserting into table

Question

I am reading in a file that is formatted as the following:

#*title
#tyear
#cVenue
#index0
#!abstract

#*title
#tyear
#cVenue
#index1
#!abstract

with thousands of blocks. Each block is separated by a blank line and each block is a row in a table. I want insert into my table after each block has been read. Then I want to clear the variables so the next block may be read and inserted. Here is my code so far: import MySQLdb

conn = MySQLdb.connect(host="localhost", user="root", db="Literature")
db1 = conn.cursor()

with open("path\to\my\file.txt", "rb") as    f:
for line in f:
    if line.startswith("#*"):
        title = line[2:]

    elif line.startswith("#t"):
        year = line[2:]

    elif line.startswith("#c"):
        Venue = line[2:]

    elif line.startswith("#index"):
        ID = line[6:]

    elif line.startswith("#!"):
        abstract = line[2:]

    elif line == '\n':

        db1.execute('''INSERT INTO my_table(
            ID, TITLE, YEAR, Venue, ABSTRACT)
            VALUES (%s,%s,%s,%s,%s)'''(ID, title, year, Venue, abstract))
        conn.commit()
        conn.close()

        title = None
        year = None
        Venue = None
        ID = None
        abstract = None

    else:
        continue

There are no errors when I run this code but my table is empty. Could someone point out where I have gone wrong. Should I possibly use a different way of checking if I have come to the end of a block?

you probably don't want to close your connection every iteration — Joe, Feb 04 '16 at 12:58
also are you sure that an empty line is "\n". Try printing it. There may be a space or it could be an empty string (I can't remember how Python does it) — Joe, Feb 04 '16 at 12:59
Is this in Windows, Linux or OSX? I know that Linux uses `\n` for newlines and Windows `\r\n` (see [this link](http://stackoverflow.com/questions/426397/do-line-endings-differ-between-windows-and-linux)). So if you use Windows, you may never find a line that only consists of `\n`... — Nander Speerstra, Feb 04 '16 at 12:59
Thank you, yes I was closing the connection when I shouldn't have! It is working now — RyanKilkelly, Feb 04 '16 at 13:24

score 0 · Accepted Answer · answered Feb 04 '16 at 13:17

You can check it if it is empty like this:

elif line.strip() == '':
    # your code

or you can insert a special character indicating end of block when generating your file. You can also use regular expressions like this:

import re
# some code
elif re.match(r'^[\s\t]*$', line):
    # your code

python, reading in text file and inserting into table

1 Answers1