pythonでsqliteを触る
エクセルでのログ集計&加工がだるすぎるので、python&sqliteを使用することにした。
sqliteはDBがファイル形式で存在するので、環境構築が楽なのが良い。
やりたいことは、日毎、時毎、分毎、秒毎の集計データを集めること。
※素直にAccess&Eccelを使えという声が聞こえる。
1.ログファイルからDBを作成する。
ログファイルから、1行1レコードとしてDBを作成する。
(sqliteは日付型のカラム設定ができないらしい。不便)
# -*- coding: utf-8 -*-
import datetime
import sqlite3
#dbの世代管理したいのでdb名を時刻にする。
d = datetime.datetime.today()
db_name='DB[%s-%s-%s_%s%s]'%(d.year, d.month, d.day,d.hour, d.minute)
#dbを作成(dbがすでに存在する場合はdbに接続)
con = sqlite3.connect(db_name+".db")
#logテーブルを作る
sql="create table log(time text, user text);"
con.execute(sql)
sql=[]
for line in open("sample.log", 'r'):
temp=line.split(",")
sql.append((temp[0],temp[1]))
con.executemany("insert into log values (?, ?)", sql)
con.commit()
con.close()
logファイルはこんな感じで、アクセス時間とユーザー名が記載されている。
"2016/2/11 22:54:10:10","sakamoto"
"2016/2/11 22:54:10:10","sakamoto"
"2016/2/11 22:54:10:10","sakamoto"
"2016/2/11 22:54:10:10","aikawa"
"2016/2/11 22:54:10:10","rucha"
"2016/2/11 22:54:10:10","tukio"
"2016/2/11 22:54:10:10","tokita"
"2016/2/11 22:54:10:12","skystar"
"2016/2/11 22:54:10:12","aikawa"
"2016/2/11 22:54:10:12","rucha"
"2016/2/11 22:55:10:10","tukio"
"2016/2/11 22:55:10:10","tokita"
"2016/2/12 22:56:10:10","skystar
下記スクリプトでdbを確認すると、上記データが登録されていることが分かる。
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("DB[2016-2-13_2312].db")
cur = con.cursor()
cur.execute("select * from log")
for row in cur:
print row[0], row[1]
2.ログファイルをいい感じに成形して表示する。
sqliteはdatetime型が無いので、substr関数を使って分毎、日毎などを指定します。
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect("DB[2016-2-13_2312].db")
cur = con.cursor()
print "by day"
sql="select substr(time,2,10), count(*) FROM log GROUP BY substr(time,2,10)"
cur.execute(sql)
for row in cur:
print row[0]+" : "+str(row[1])
print "\nby hour"
sql="select substr(time,2,12), count(*) FROM log GROUP BY substr(time,2,12)"
cur.execute(sql)
for row in cur:
print row[0]+" : "+str(row[1])
print "\nby min"
sql="select substr(time,2,15), count(*) FROM log GROUP BY substr(time,2,15)"
cur.execute(sql)
for row in cur:
print row[0]+" : "+str(row[1])
print "\nby sec"
sql="select substr(time,2,18), count(*) FROM log GROUP BY substr(time,2,18)"
cur.execute(sql)
for row in cur:
print row[0]+" : "+str(row[1])
print "\nby msec"
sql="select time, count(*) FROM log GROUP BY time"
cur.execute(sql)
for row in cur:
print row[0]+" : "+str(row[1])
実行結果はこんな感じ。
$ python select2.py
by day
2016/2/11 : 12
2016/2/12 : 1
by hour
2016/2/11 22 : 12
2016/2/12 22 : 1
by min
2016/2/11 22:54 : 10
2016/2/11 22:55 : 2
2016/2/12 22:56 : 1
by sec
2016/2/11 22:54:10 : 10
2016/2/11 22:55:10 : 2
2016/2/12 22:56:10 : 1
by msec
"2016/2/11 22:54:10:10" : 7
"2016/2/11 22:54:10:12" : 3
"2016/2/11 22:55:10:10" : 2
"2016/2/12 22:56:10:10" : 1
10年ぶりくらいでsql触ったらすっかり忘れてた。