python处理nginx日志入库

321312

#usr/bin/evn python
#-*- coding: utf-8 -*-
import os, sys
import re
import MySQLdb
db = MySQLdb.connect(host='192.168.10.94',user='inserter',passwd='****',db='gydata')
cursor=db.cursor()
log_file=sys.argv[1]
print log_file
for line in open(log_file):
elements=line.split(' ')
cip=elements[0]
rpttime=elements[1]
rpttimelength=len(rpttime)
rpttime=rpttime[1:rpttimelength]
core=elements[4]
if core.startswith('/c.gif?'):
rpttime_elements=re.split('\W+',rpttime)
if (rpttime_elements[1]=='Jan'):
month='01'
elif (rpttime_elements[1]=='Feb'):
month='02'
elif (rpttime_elements[1]=='Mar'):
month='03'
elif (rpttime_elements[1]=='Apr'):
month='04'
elif (rpttime_elements[1]=='May'):
month='05'
elif (rpttime_elements[1]=='Jun'):
month='06'
elif (rpttime_elements[1]=='Jul'):
month='07'
elif (rpttime_elements[1]=='Aug'):
month='08'
elif (rpttime_elements[1]=='Sep'):
month='09'
elif (rpttime_elements[1]=='Oct'):
month='10'
elif (rpttime_elements[1]=='Nov'):
month='11'
elif (rpttime_elements[1]=='Dec'):
month='12'
rpttime=rpttime_elements[2]+'-'+month+'-'+rpttime_elements[0]+' '+rpttime_elements[3]+':'+rpttime_elements[4]+':'+rpttime_elements[5]
core_content=core.split('/c.gif?')[1]
keyvalue={}
items=core_content.split('&')
for item in items:
key=item.split('=')[0]
value=item.split('=')[1]
keyvalue[key]=value
tr=keyvalue.get('tr')
if (tr==None):
tr=''
else:
keyvalue.pop('tr')
subtr=keyvalue.get('subtr')
if (subtr==None):
subtr=''
else:
keyvalue.pop('subtr')
uuid=keyvalue.get('uuid')
if (uuid==None):
uuid=''
else:
keyvalue.pop('uuid')
cbios=keyvalue.get('cbios')
if (cbios==None):
cbios=''
else:
keyvalue.pop('cbios')
cmac=keyvalue.get('cmac')
if (cmac==None):
cmac=''
else:
keyvalue.pop('cmac')
seq=keyvalue.get('seq')
if (seq==None):
seq=-1
else:
keyvalue.pop('seq')
chk=keyvalue.get('chk')
if (chk==None):
chk=''
else:
keyvalue.pop('chk')
salt=keyvalue.get('salt')
if (salt==None):
salt=''
else:
keyvalue.pop('salt')

try:
cursor.execute("INSERT INTO VALUE_HEADER(UUID, CBIOS, CMAC, SEQ, TR, SUBTR, CHK, SALT, RPTTIME, CIP) VALUES('%s','%s','%s',%s,'%s','%s','%s','%s','%s','%s')"%(uuid,cbios,cmac,seq,tr,subtr,chk,salt,rpttime,cip))
except:
continue
for k in keyvalue.keys():
if (k<>''):
try:
#print "INSERT INTO VALUES_DETAIL_%s_%s(UUID, RPTTIME, RITEM, RVALUE) VALUES('%s','%s','%s','%s')"%(tr,subtr,uuid,rpttime,k,keyvalue[k])

cursor.execute("INSERT INTO VALUE_DETAIL_%s_%s(UUID, RPTTIME, RITEM, RVALUE) VALUES('%s','%s','%s','%s')"%(tr,subtr,uuid,rpttime,k,keyvalue[k]))
except:
continue


这种处理效率不高，可以将
if (rpttime_elements[1]=='Jan'):
month='01'
elif (rpttime_elements[1]=='Feb'):
month='02'
elif (rpttime_elements[1]=='Mar'):
month='03'
elif (rpttime_elements[1]=='Apr'):
month='04'
elif (rpttime_elements[1]=='May'):
month='05'
elif (rpttime_elements[1]=='Jun'):
month='06'
elif (rpttime_elements[1]=='Jul'):
month='07'
elif (rpttime_elements[1]=='Aug'):
month='08'
elif (rpttime_elements[1]=='Sep'):
month='09'
elif (rpttime_elements[1]=='Oct'):
month='10'
elif (rpttime_elements[1]=='Nov'):
month='11'
elif (rpttime_elements[1]=='Dec'):
month='12'
这个部分改用hash表方式。