Python script to read our moods into an Sqlite database

This script takes a csv file, output from the mood reporting Android app that Peter wrote us and inserts the records into a database

#! usr/bin/env python
#-*- coding:utf-8 -*-
# Daniel Belasco Rogers 2011 Wed 16 Feb 2011 23:40:35 CET
# An attempt to parse Peter's mood track log file
# Takes a user (dan or soph), path to the text file and a path to a
# database

import csv
import sys
import sqlite3
from optparse import OptionParser

def getUser(username):
    """ check whether user supplied is valid
    In future, consider making this populate from database
    username = username.lower()
    userDict = {'dan': 1, 'soph': 2}
        userid = userDict[username]
        print "user '%s' not in database" % username
    return int(userid), username

def updateDB(moodReader, dbpath, userid):
    iterate through the mood reports and execute insert sql on each
    connect = sqlite3.connect(dbpath)
    recordnum = 0
    for row in moodReader:
        # skip header
        if row[1] == 'happiness':
        recordnum += 1
        print (row)
            connect.execute("insert into mood (created, happiness, tiredness, hopeful, stress, secure, anxiety, productive, loved, note, user) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)", row)
        except sqlite3.IntegrityError:
            print "\n*** Message already entered - skipping ***\n"
            recordnum -=1
    return recordnum

def main():
    usage = "usage: %prog user (dan, soph) /path/to/csv/file.csv, /path/to/database.sqlite"
    parser = OptionParser(usage, version="1.0")
    (options, args) = parser.parse_args()
    if len(args) != 3:
        parser.error("Specify a user, csv file and a database file\n")
    userid, username = getUser(args[0])
    filename = args[1]
    dbpath = args[2]
    f = open(filename,'r')
    moodReader = csv.reader(f, skipinitialspace=True)   
    recordnum = updateDB(moodReader, dbpath, userid)
    print 'Updated %d records to database\nScript Ends sucessfully - Goodbye\n' % recordnum

if __name__ == '__main__':

This entry was posted in Android, Code, Python and tagged , . Bookmark the permalink.