[Python] SQLite 메모리DB를 활용한 파이썬 IIS 로그파서

파이썬 SQLite 메모리DB를 이용한 IIS 로그파서 구현

[ 테스트 로그 ]

  - 약 640MB

  - 라인수 : 약 42만 라인

  - 수행시간 : 30초

import sqlite3
from datetime import datetime

def timecheck():
    return datetime.today().strftime('%X')


print('시작시간 :', timecheck())
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute('''CREATE TABLE memorylogdb
             (date date, time time, sitename VARCHAR(50), computername VARCHAR(50), sip VARCHAR(20), method VARCHAR(20),
              csuristem VARCHAR(500), csuriquery VARCHAR(5000), sport VARCHAR(20), csusername VARCHAR(100), cip VARCHAR(20),
             csversion VARCHAR(100), useragent VARCHAR(2000), cookie VARCHAR(3000), referer VARCHAR(3000), cshost VARCHAR(100),
             scstatus VARCHAR(20), substatus VARCHAR(20), scwin32status VARCHAR(20), scbytes INT(50), csbytes INT(50), timetaken INT(50))''')

lst = [ i for i in range(22)]

with open(r'로그경로', 'r', errors='replace') as f:
    lines = f.readlines()
    for line in lines:
        log = line.split(' ')
        if log[0][0] == '#':
            pass
        else:
            for x, y in enumerate(log):
                lst[x] = str(y)

            c.execute('''INSERT INTO memorylogdb VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',
             '%s', '%s', '%s', '%s', '%s', '%s', '%s')'''
                      %(lst[0],lst[1],lst[2],lst[3],lst[4],lst[5],lst[6],lst[7],lst[8],lst[9],lst[10],lst[11],lst[12],lst[13],lst[14],lst[15],lst[16],
                        lst[17],lst[18],lst[19],lst[20],lst[21]))

print('종료시간 :',timecheck())

# 조회 쿼리
c.execute('SELECT count(*) FROM memorylogdb')
print('총 row 갯수는', c.fetchone(), '입니다')
print('-'*30)

# scbytes 내림차순 TOP 20 정렬
c.execute('SELECT csuristem, scbytes FROM memorylogdb ORDER BY scbytes DESC LIMIT 20')
for i in c:
    print(i)
print('-'*30)

# time 내림차순 20 정렬
c.execute('SELECT time FROM memorylogdb ORDER BY time DESC LIMIT 20')
for i in c:
    print(i)

conn.close()

lines = []
log = []
lst = []
-----------------------------------------------------------------------------------------------------------------------------

시작시간 : 01:15:26
종료시간 : 01:15:57
총 row 갯수는 (419557,) 입니다
------------------------------
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
('NanumGothic.ttf', 4346106)
------------------------------
('23:58:44',)
('23:58:44',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:39',)
('23:58:31',)
('23:58:31',)