エコノナビットログDB登録

ecoregister.py

#-*- coding:utf-8 -*-
import sqlite3
import csv
import time
import os
from datetime import datetime
from itertools import islice
from itertools import izip_longest
from itertools import takewhile

take = lambda n, xs: list(islice(xs, 0, n))
drop = lambda n, xs: islice(xs, n, None)
to_utf8 = lambda s: unicode(s, 'cp932').encode('utf8')

def partition(n, iterable):
    xs = iter(iterable)
    while True:
        a = take(n, xs)
        if not a: break
        yield a

def nowtime():
    now = datetime.now()
    return now.strftime("%Y/%m/%d %H:%M:%S.") + "{0:04d}".format(now.microsecond // 1000)

def tmfmt(s):
    x = time.strptime(s, '%H:%M')
    return '{0:02d}:{1:02d}'.format(x.tm_hour,x.tm_min)


COLUMN_YMD = 'YMD'
COLUMN_KIND = 'KIND'

def block_to_values(columns, block):
    none_if_empty = lambda x: x if x!="" else None
    ymd = block[0][0]
    for row in block:
        row[0] = ymd
        d = {}
        for val, col in izip_longest(row, columns):
            d[col] = to_utf8(val)
        t = d[COLUMN_KIND], d[COLUMN_YMD]
        del d[COLUMN_KIND]
        del d[COLUMN_YMD]
        for tm, v in d.iteritems():
            yield t + (tm, none_if_empty(v))

def create_table(conn):
    sql = """
SELECT *
FROM sqlite_master
WHERE type='table'
  AND name='T_POWER';
"""
    cur = conn.cursor()
    cur.execute(sql)
    if cur.fetchone():
        return

    ddl = """
CREATE TABLE T_POWER(
KIND TEXT
,YMD TEXT
,TIME TEXT
,POWER NUMERIC
,UPDATE_DATE TEXT
,FILE TEXT
,PRIMARY KEY(KIND, YMD, TIME)
);
"""
    cur.execute(ddl)

def db_update(conn, csvfile, data):
    sql = """
INSERT OR REPLACE INTO T_POWER
(KIND, YMD, TIME, POWER, UPDATE_DATE, FILE)
VALUES
(?, ?, ?, ?, ?, ?);
"""
    cur = conn.cursor()
    for row in data:
        cur.execute(sql, row + (nowtime(), csvfile))

# def db_delete_all(conn):
#     sql = "DELETE FROM T_POWER;"
#     conn.execute(sql)

def register(csvfile, conn):
    filename = os.path.basename(csvfile)
    with open(csvfile) as f:
        r = csv.reader(f)
        cols = take(1, drop(2, r))[0]
        columns = [COLUMN_YMD, COLUMN_KIND] + map(tmfmt, cols[2:])
        lines = takewhile(len, r)
        for block in partition(5, lines):
            db_update(conn, filename, block_to_values(columns, block))

def main(db, csvfiles):
    with sqlite3.connect(db) as conn:
        conn.text_factory = str  
        create_table(conn)
        for csvfile in csvfiles:
            register(csvfile, conn)

if __name__ == '__main__':
    import sys
    main(sys.argv[1], sys.argv[2:])