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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- wordpress passing data between pages
- wordpress business templates
- wordpress rss feed not working
- wordpress jquery is not defined
- create wordpress blog
- wordpress roles editor
- wordpress full rss feed
- wordpress rss feed settings
- wordpress rss feed plugin
- wordpress display rss feed
- wordpress rss feed link
- wordpress rss feed to post