wakatime2sqlite.py

#!/usr/bin/env python3
# -*- coding:utf-8 -*-
from argparse import ArgumentParser
import sqlite3
import json
import os
import sys

filename = None
sqlitefile = None

def wakatime2sqlite(filename, sqlitefile_output):
    f = open(filename,'r')
    wakatime = json.load(f)

    db = sqlite3.connect(sqlitefile_output)
    db.text_factory = str

    # Create tables
    cursor = db.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS wakatime_day(
            date_at DATE,
            total INTEGER
        );
    ''')
    cursor.execute('DELETE FROM wakatime_day;')
    db.commit();

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS wakatime_record(
            date_at DATE,
            type TEXT,
            name TEXT,
            total INTEGER
        );
    ''')
    cursor.execute('DELETE FROM wakatime_record;')
    db.commit();

    # iter
    cursor = db.cursor()
    for day in wakatime['days']:
        date_at = day['date']
        total = day['grand_total']['total_seconds']
        cursor.execute('INSERT INTO wakatime_day(date_at, total) VALUES(?,?)', [date_at, total])
        # various
        types = ['editors', 'languages', 'categories','operating_systems']
        for t in types:
            for i in day[t]:
                cursor.execute(
                        'INSERT INTO wakatime_record(date_at, type, name, total) VALUES(?,?,?,?);', 
                        [date_at, t, i['name'], i['total_seconds']]
                )
        # projects
        for p in day['projects']:
            cursor.execute(
                'INSERT INTO wakatime_record(date_at, type, name, total) VALUES(?,?,?,?);',
                [date_at, 'projects', p['name'], p['grand_total']['total_seconds']]
            )
    db.commit()

    print('Successfull !!')

argp = ArgumentParser(
    prog = 'Wakatime2SQLite',
    description = 'Wakatime2SQLite allow you to transform your data to SQLite database',
    epilog = 'GPL v3.0',
    #version = '3.0'
)

argp.add_argument('-i', '--input', dest='filename', action='store', help='Wakatime json input')
argp.add_argument('-o', '--output', dest='sqlitefile', action='store', help='SQLite file output', default='wakatime.db')
args = vars(argp.parse_args())

if args['filename'] is None or args['sqlitefile'] is None:
    argp.print_help()
else:
    filename = args['filename']
    sqlitefile = args['sqlitefile']
    if not os.path.isfile(filename):
        print('Error: file {} not exists'.format(filename))
        sys.exit()
    itsok = 'y'
    if os.path.isfile(sqlitefile):
        itsok = input('The file {} exist\'s right now, do you want replace? [y/n]: '.format(sqlitefile))
    if itsok.strip() in ['y','Y']:
        wakatime2sqlite(filename, sqlitefile)