Charuzu.files.wordpress.com



The script below is saved as load_csv.py

Run the script as follows:

> python load_csv.py CSVFILENAME

"""

Load a CSV file generated by Taplog into the taplog.db database

12th Dec 2011

Command line

load_csv CSVFILENUM Batchnumber

The batchnumber should be a next sequential number in case

a new taplog database is created and the id sequence restarts.

A list of new record ids is created and this is used to select records

to write to the various text files:

finance

sleeplog

ideas

Tap log configuration

Activity

Sleep Time

Wake Up

Piano Start

Piano End

Leave

Home / Office

Arrive

Home / Office

Capture

Todo / Idea / Expense / Diary / Journal / Note

Drink

Water 1 cup / Coffee / tea / Teamo / Beer / Wine / G&T

"""

import os, sys, re, sqlite3, datetime, csv

# Process command line arguments

argc = len(sys.argv)

print argc," args received"

if argc < 2:

print "Syntax is python", sys.argv[0]

# print "Syntax is python", sys.argv[0], "CSVFile batchno"

sys.exit(1)

csvfile = sys.argv[1]

# batchno = sys.argv[2]

batchno = 1

print "Loading ", csvfile

# Global variables to store program and database locations

database = "taplog_cmc.db"

# Create SQLite connector

conn = sqlite3.connect(database)

conn.text_factory = str

db_conn = conn.cursor()

new_records = dict() # list of ids added during this program run

# cast to type 'int'

#######################

def is_key_on_file(id):

stmt = 'select id, timestamp from taplog where id == "%s"' % (id)

db_conn.execute(stmt)

found_id = 0

for r in db_conn:

found_id = 1

return found_id

############################

def insert_record(id, row):

print "Inserting ", id

new_records[int(id)] = '*'

timestamp = row[12]

dayofyear = row[13]

dayofmonth = row[14]

dayofweek = row[15]

timeofday = row[16]

catone = row[17]

cattwo = row[18]

catthree = row[19]

number = row[20]

rating = row[21]

note = row[22]

datestamp = timestamp.split(' ')[0].split('/')

year = datestamp[2]

monthnum = datestamp[1]

hhmm = timestamp.split(' ')[1]

ampm = timestamp.split(' ')[2]

hh = hhmm.split(':')[0]

if hh == "12":

hh = 0

else:

hh = int(hh)

mm = int(hhmm.split(':')[1])

if ampm == 'PM':

hh += 12

stmt = "insert into taplog (id, batch,timestamp, year, monthnum, dayofmonth, dayofyear, dayofweek, "

stmt += " timeofday, catOne, catTwo, catThree, number, rating , note, hour, minute) "

stmt += " values ('%s','%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s', '%s', '%s', '%s', '%s', '%s')" % (id, batchno, timestamp, year, monthnum, dayofmonth, dayofyear, dayofweek, timeofday, catone, cattwo, catthree, number, rating, note, hh, mm)

db_conn.execute(stmt)

mit()

#####################

# new Finance entries are written to a text file for later review when I use Quicken

def finance_report():

# output Capture / Expense

stmt = 'select id, year, monthnum, dayofmonth, dayofweek, number, note from taplog '

stmt += 'where catOne == "Capture" and catTwo == "Expense" order by year, dayofyear'

db_conn.execute(stmt)

fof = open("", "a")

for r in db_conn:

if int(r[0]) in new_records:

print "append to text file"

fof.write("* %s-%s-%s %s %s %s\n" % (r[3], r[2], r[1], r[4], r[5], r[6]))

fof.write(" \n")

fof.close()

#####################

def sleep_report():

# Extract the "Sleep Time" and "Wake Up" entries and write to sleeplog.txt

stmt = 'select id, year, monthnum, dayofmonth, dayofweek, catTwo, timestamp,'

# 0 1 2 3 4 5 6

stmt += 'rating, note, hour, minute from taplog '

# 7 8 9 10

stmt += 'where catOne == "Activity" order by year, dayofyear, timeofday'

db_conn.execute(stmt)

of = open("sleeplog.txt", "a")

allrecords = 0 # set to 1 to recreate file otherwise set to 0

for r in db_conn:

ts = r[6].split(' ')[1] # rs looks like dd/mm/yyyy H:M PM

# if I go to bed after midnight the date and day of week are wrong!

# this needs to be corrected

if (allrecords == 1) or int(r[0]) in new_records:

if r[5] == "Sleep Time":

sleep_time = "%s\t%s\t%s\t%s\t%s\t%s\t%s" % (

r[1], r[2],r[3],r[6], r[4],r[9],r[10])

dec_sleep = float(r[9]) + float(r[10])/60.0

if r[9] == 0: dec_sleep += 24.0 # past midnight

if r[5] == "Wake Up":

dec_wake = float(r[9]) + float(r[10])/60.0

rec = "%s\t%s\t%s\t%2.2f\t%2.2f\t%2.2f\t%s\t%s\n" % (

sleep_time, r[9], r[10],

dec_sleep, dec_wake,

(24.0 + dec_wake - dec_sleep),

r[7], r[8])

of.write(rec)

of.flush()

of.close()

##########################################################

with open(csvfile, 'rb') as f:

reader = csv.reader(f)

for row in reader:

id = row[11] # id

if id == "_id": continue

if is_key_on_file(id) == 0:

insert_record(id, row)

print "Keys added ", new_records.keys()

finance_report()

sleep_report()

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download