Flickr stats to SQLite Converter

#!/usr/bin/env python

# Script for importing Flickr logs into an SQLite DB
#  by Kadin2048, http://kadin.sdf-us.org/
# Requires Python 2.5+ or (Python <2.5 and PySQLite)

# Usage: flickr_sqlite.py YYYY_MM_daily.csv
#    or: flickr_sqlite.py YYYY_MM_monthly.csv
#    or: for i in *.csv; do flickr_sqlite.py $i; done

import os.path
import sys
import time
import csv
import sqlite3

# The SQLite file to use or create
DATABASE = 'flickrstats.sqlite'

# Logging:
#LOG = sys.stderr
LOG = open('flickrstats-import.log','a')

def log(message):
    LOG.write('[' + time.ctime() + ']: ' + message + '\n')


def main(inputfilename):
    log('Reading from ' + inputfilename)

    basename = os.path.basename(inputfilename)
    log('Basename is ' + basename)

    csvreader = csv.reader( open(inputfilename, 'r')  )
    L = []
    c = 0
    for i in csvreader:
        L.append(i)
        c = c+1
    log("Successfully read %s lines from file." % c)

    L.pop(0)  # drop header row

    log("Have %s rows of data." % len(L))

    connection, cursor = setupTable(DATABASE)  # create the table in the database file, if it doesn't exist

    if (basename.find('monthly') >= 0):
        writeMonthly(connection, cursor, L)
    elif (basename.find('daily') >= 0):
        writeDaily(connection, cursor, L)
    else:
        log("Doesn't look like a daily or monthly file. Nothing more to do.")
        return 1

    return 0


def setupTable(databasefile):
    """Create a table to hold Flickr referrer data.
    Returns a Connection and Cursor."""

    # Create a 'Connection' and 'Cursor'
    connection = sqlite3.connect(databasefile)
    cursor = connection.cursor()

    # Create a table
    log("Attempting to create daily table, if it doesn't exist.")
    cursor.execute('''CREATE TABLE IF NOT EXISTS flickrdaily ("date" TEXT, "flickrPage" TEXT, "fullReferrer" TEXT, "referrerDomain" TEXT, "searchTerm" TEXT, "count" INTEGER)''')

    log("Attempting to create monthly table, if it doesn't exist.")
    cursor.execute('''CREATE TABLE IF NOT EXISTS flickrmonthly ("date" TEXT, "flickrPage" TEXT, "fullReferrer" TEXT, "referrerDomain" TEXT, "searchTerm" TEXT, "count" INTEGER)''')

    return connection, cursor


def writeDaily(connection, cursor, datalist):
    log("Writing to daily table.")

    c = 0
    for row in datalist:
        cols = tuple(row)
        #log("Inserting " + str(cols))
        cursor.execute('''INSERT INTO flickrdaily ("date", "flickrPage", "fullReferrer", "referrerDomain", "searchTerm", "count") VALUES (?, ?, ?, ?, ?, ?)''', cols)
        c = c+1

    # And commit.
    connection.commit()
    log("Committed %s rows!" % c )

    return


def writeMonthly(connection, cursor, datalist):
    log("Writing to monthly table.")

    c = 0
    for row in datalist:
        cols = tuple(row)
        #log("Inserting " + str(cols))
        cursor.execute('''INSERT INTO flickrmonthly ("date", "flickrPage", "fullReferrer", "referrerDomain", "searchTerm", "count") VALUES (?, ?, ?, ?, ?, ?)''', cols)
        c = c+1

    # And commit.
    connection.commit()
    log("Committed %s rows!\n" % c )

    return


# Interactive mode
if __name__ == "__main__":
    sys.exit( main(sys.argv[1]) )