Python擷取Nginx訪問日誌，寫入資料庫

標籤：建立資料庫   utf-8   request   dbus   mys   file   執行   sys.argv   create   

#!/usr/bin/env python# coding: utf-8# Auther:liangkai# Date:2018/6/26 11:26# License: (C) Copyright 2013-2018, Node Supply Chain Manager Corporation Limited.# Describe:import pymysqlimport reimport datetimeimport sysimport time# DB variablesdbhost = "192.168.189.185"dbport = 3306dbuser = ‘root‘dbpassword = ‘********‘charset = ‘utf8‘log_file = sys.argv[1]# 串連資料庫conn_db = pymysql.connect(host=dbhost,port=dbport,user=dbuser,password=dbpassword,charset=charset)cursor=conn_db.cursor()# 建立資料庫# cursor.execute("create database if not exists nginx")## 建立表# create_table = ‘‘‘create table nginx.accesslog( id int(4), IP char(20), # time timestamp, Method char(4), Status int(4),Request_time float(4),# X_Forwarded_for char(8),Host char(20),primary key(id) );# ‘‘‘# cursor.execute(create_table)# monitor access logpos = 0while True: fd = open(log_file) if pos != 0: fd.seek(pos, 0) while True: line = fd.readline() if line.strip(): logdata=line.strip() matchObj = re.search( r‘(.*) - - \[(.*)\] \"(.*) (\/.*)\" (.*) (.*) (.*) \"(.*)\" \"(.*)\" \"(.*)\" \"(.*)\"‘, logdata) if matchObj != None: ip = matchObj.group(1) Time = matchObj.group(2)[0:20] method = matchObj.group(3) request = matchObj.group(4) status = int(matchObj.group(5)) bytesSent = int(matchObj.group(6)) request_time = float(matchObj.group(7)) refer = matchObj.group(8) agent = matchObj.group(9) X_Forwarded_for = matchObj.group(10) Host = matchObj.group(11) # 時間格式化為mysql資料庫支援的格式 format = ‘%d/%b/%Y:%H:%M:%S‘ Time = datetime.datetime.strptime(Time, format) # 插入資料SQL語句 insert_sql = "INSERT INTO nginx.accesslog(IP, TIME, Method, Status, bytesSent, Request_time, Host) values(‘%s‘,‘%s‘,‘%s‘,‘%d‘,‘%d‘, ‘%f‘,‘%s‘);" % (ip, Time, method, int(status), int(bytesSent), float(request_time), Host) # 執行插入語句 try: cursor.execute(insert_sql) conn_db.commit() except: conn_db.rollback() print("insert Error !!!") pos = pos + len(line) if not line.strip(): break fd.close() time.sleep(1)cursor.close()conn_db.close()

Python擷取Nginx訪問日誌，寫入資料庫