fast-sqlite-test.py

import os.path
import sqlite3
#import time

#start = time.time()

db_file = 'test2.db'
log_file = '1m.log'
#db_file = 'test3.db'
#log_file = 'monitor_syslog.log'

if not os.path.exists(db_file):
    conn = sqlite3.connect(db_file)
    conn.execute("CREATE VIRTUAL TABLE SYSLOG USING FTS3(LOGS)");
    conn.close()

conn = sqlite3.connect(db_file)
conn.execute("PRAGMA SYNCHRONOUS = OFF");
conn.execute("PRAGMA JOURNAL_MODE = MEMORY");

#print('1', time.time() - start)

with open(log_file) as fh:
#    print('2', time.time() - start)
    lines = [[line] for line in fh]
    # lines = [line.replace("'", "''") for line in lines]
    # print(len(lines))

#    print('3', time.time() - start)

    try:
        # conn.executemany('insert into syslog values( ? )', [[line] for line in lines])
        conn.executemany('INSERT INTO SYSLOG VALUES( ? )', lines)
#        print('4', time.time() - start)
    except sqlite3.Error, e:
        print(e)
    else:
        conn.commit()

#print('5', time.time() - start)