Python: Parse Apache log to sqlite database

April 15th, 2008

This Python script was written for a friend in Australia, for part of his Ph.D project. The script will parse the Apache server log into sqlite3 database.

Apache server log like this:

__________
23.13.171.152 - - [26/Sep/2007:21:20:36 +0800] “GET /forum/Themes/BlueStory/images/bbc/ftp://ftp.gif HTTP/1.1″ 200 191 “http://www.malaysiaindah.com/index.php?action=post;topic=587.0;num_replies=3″ “Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.7) Gecko/20070914 Firefox/2.0.0.7″
__________

could be parse to an sqlite database, with the class below.

__________
class Parser:
    def __init__(self, serverLog, db):

    if db.strip() == “”:
        db = “log.db”
    try:
        conn = sqlite3.connect(db)
        cursor = conn.cursor()

        cursor.execute(“create table if not exists log (ip, date, time, gmt, request, errorcode, bytes, referel, osa)”)

    except sqlite3.Error, error:
        wx.MessageBox(str(error), “Info”)
        exit()

    numLog = len(open(serverLog).readlines())

    for line in open(serverLog, “r”):
        data = []
        a = line.split(‘”‘)
        line = line.split()

        data.append(line[0]) #ip
        data.append(line[3][1:line[3].index(“:”)]) #date
        data.append(line[3][line[3].index(“:”) + 1:]) #time
        data.append(line[4][:-1]) #gmt
        data.append(line[5][1:] + ” “ + line[6]) #urlreq
        data.append(line[8]) #statuscode
        data.append(line[9]) #bytestr
        data.append(line[10][1:-1]) #referel
        data.append(a[-2]) #osagent

        try:
            cursor.execute(“insert into log values(?, ?, ?, ?, ?, ?, ?, ?, ?)”, (data[0], data[1], data[2], data[3], data[4], data[5], data[6], data[7], data[8]))

        except sqlite3.Error, error:
            wx.MessageBox(str(error), ‘Info’)
            exit()

    conn.commit()
    cursor.close()
    conn.close()
__________