Pythonで楽にログファイルをテーブルへINSERTしたい！
ログファイルをDB(データベース)へ挿入する際、各行が1つのデータになっていて、各値はスペースやカンマ等で区切られている事が多いです。
DBとテーブルを作成し、ログファイルを読み取り1行から必要な値を正規表現で抜き取り、テーブルへINSERTするが大まかな流れです。
対象ログファイルが1種類だけなら
cur.execute('INSERT INTO テーブル名 VALUES ?, ?, ?・・・, column_tuple)
※「column_tuple」は、【res = re.search(正規表現, 対象ログファイルの1行)】→【column_tuple = res.groups()】 でレコードをカラム毎に配列(タプル)へ格納した値を指します
※curはカーソルオブジェクト
をスクリプトファイル毎に記載すれば良いです。
ただ対象ログファイルが複数ある場合、スクリプトファイルを作成するたびにカラム数はいくつかなと数えて?の数を変更していくのは大変手間だし、?の数を間違えてエラーの原因になったり、なにより面倒(本音)！
そんな方のために?の数を可変で変えていく方法を記載し、少しでも楽にPythonでDBを作成していく方法を記載しております。
そもそも「VALUES ?, ?, ?・・・」って何よ
PythonでSQLiteを使用しSQLを使用する際にカーソルオブジェクトのexecuteメソッドを使用し、第1引数にSQL文を文字列として指定します。
通常INSERT文は
cur.execute('INSERT INTO テーブル名 VALUES 値1, 値2, 値3, ・・・')
と各値をVALUES句の後にカンマ区切りで指定します。
ただそれだとログファイルからINSERTする情報を抜き出してINSERTするといった処理が出来ない！
それを解決するのが「?」くん。
VALUES句の後にINSERTするカラムの数だけ「?」を記載し、executeメソッドの第2引数に配列(タプル or リスト)を指定。
こうすることで配列の「[0][1][2]・・・」の順番でテーブルへINSERTされます。
cur.execute('CREATE TABLE AccessTable (Date TEXT, Time TEXT,
Src_IP TEXT, Dst_IP TEXT)')
for access in [('2022/07/31', '07:33:52', 192.168.0.1, '172.16.0.1'),
('2022/07/31', '07:34:52', 192.168.0.1, '172.16.0.2'),
('2022/07/31', '07:33:52', 192.168.0.2, '172.16.0.1'),
('2022/07/31', '07:34:52', 192.168.0.1, '172.16.0.2')]:
cur.execute('INSERT INTO AccessTable values ?,?,?,?', access)
AccessTable
| Date | Time | Src_IP | Dst_IP |
|---|---|---|---|
| 2022/07/31 | 07:33:52 | 192.168.0.1 | 172.16.0.1 |
| 2022/07/31 | 07:34:52 | 192.168.0.1 | 172.16.0.2 |
| 2022/07/31 | 07:33:52 | 192.168.0.2 | 172.16.0.1 |
| 2022/07/31 | 07:34:52 | 192.168.0.1 | 172.16.0.2 |
コード
全体像
#!/usr/bin/env python
# coding:uft-8
#ログ格納
import sqlite3
import glob
import os
import re
import time
class createDB:
def __init__(self, HOST, DB_PATH, LOG_FOLDER_PATH, REGEXP):
self.HOST = HOST
self.TABLE = HOST
self.DB_PATH = DB_PATH
self.LOG_FOLDER_PATH = LOG_FOLDER_PATH
self.REGEXP = REGEXP
def __format_log(self, log_line):
res = re.search(self.REGEXP, log_line)
log_line_tuple = res.groups()
return log_line_tuple
def insert(self):
#DBがなければ新規作成、あれば接続のみ
conn = sqlite3.connect(self.DB_PATH)
cur = conn.cursor()
cur.execute('CREATE TABLE IF NOT EXISTS {0} (' \
'Date TEXT,' \
'Time TEXT, ' \
'Src_IP TEXT' \
'Dst_IP TEXT)'.format(self.TABLE))
#フォルダを検索してファイルの中身を整形＆DB書き込み
for path in glob.glob(self.LOG_FOLDER_PATH + '/*'):
if os.path.isdir(path, 'r') or re.compile('.*\.db').search(path): #ディレクトリとDBは無視する
continue
else:
with open(path, 'r') as f:
for line in f.read().split('\n'): #改行区切りで1行ずつ抜き取る
if line == '' : #空白行は無視する
pass
else:
formatted_line = self.__format_log(line)
#テーブルにカラムの値を入れる(VALUES)数を計算してcur.executeの第2引数に指定する配列の各データを格納する下地を作る
formatted_line_count = '?,' * len(formatted_line)
formatted_line_count = formatted_line_count.rstrip(',') #末尾「,」が不要なので削除
#?へ配列の各値を代入してくれる
#変数を表示させると「INSERT INTO テーブル名 VALUES ?,?,?,?」となる
cur.execute('INSERT INTO {0} VALUES' \
'({1})'.format(self.TABLE, formatted_line_count), formatted_line)
conn.commit()
conn.close()
※同フォルダ内にDB、ログファイルがある事を想定して作成しております。
後はインスタンスを作成し、メソッドを実行すればOKです。
まとめ
- INSERT文でVALUES句の後に「?」を記載し、cur.executeの第2引数に配列を指定すれば「?」に各値が代入される
Qiitaの記事って書くの大変なんですね。。。
何か間違いや分かりにくい点があれば、指摘して頂けると嬉しいです。
時間と体力があればレコード重複削除の手法や肥大化したDBの空き領域の手法も載せていこうかと思います。
参照サイト
お気楽 SQLite 超入門
PythonでSQLite
【パクれ】エンジニア転職で企業から評価されるQiita記事の書き方を解説