python访问数据库日志_Python分析Nginx访问日志并保存到MySQL数据库实例

\"[^\"]*\""""

#以"开始, 除双引号以外的任意字符 防止匹配上下个""项目(也可以使用非贪婪匹配*?),以"结束

#"Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"'

userAgentP = r"""?P\"[^\"]*\""""

#以(开始, 除双引号以外的任意字符 防止匹配上下个()项目(也可以使用非贪婪匹配*?),以"结束

#(compatible; Googlebot/2.1; +http://www.google.com/bot.html)"'

userSystems = re.compile(r'\([^\(\)]*\)')

#以"开始，除双引号以外的任意字符防止匹配上下个""项目(也可以使用非贪婪匹配*?),以"结束

userlius = re.compile(r'[^\)]*\"')

#原理：主要通过空格和-来区分各不同项目，各项目内部写各自的匹配表达式

nginxLogPattern = re.compile(r"(%s)\ -\ -\ (%s)\ (%s)\ (%s)\ (%s)\ (%s)\ (%s)" %(ipP, timeP, requestP, statusP, bodyBytesSentP, referP, userAgentP), re.VERBOSE)

#数据库连接信息

conn=MySQLdb.connect(host='192.168.1.22',user='test',passwd='pass',port=3306,db='python')

cur=conn.cursor()

sql = "INSERT INTO python.test VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"

while True:

line = logfile.readline()

if not line:break

matchs = nginxLogPattern.match(line)

if matchs != None:

allGroup = matchs.groups()

ip = allGroup[0]

time = allGroup[1]

request = allGroup[2]

status = allGroup[3]

bodyBytesSent = allGroup[4]

refer = allGroup[5]

userAgent = allGroup[6]

Time = time.replace('T',' ')[1:-7]

if len(userAgent) > 20:

userinfo = userAgent.split(' ')

userkel = userinfo[0]

try:

usersystem = userSystems.findall(userAgent)

usersystem = usersystem[0]

print usersystem

userliu = userlius.findall(userAgent)

value = [ip,Time,request,status,bodyBytesSent,refer,userkel,usersystem,userliu[1]]

conn.commit()

print value

except IndexError:

userinfo = userAgent

value = [ip,Time,request,status,bodyBytesSent,refer,userinfo,"",""]

else:

useraa = userAgent

value = [ip,Time,request,status,bodyBytesSent,refer,useraa,"",""]

try:

result = cur.execute(sql,value)

#conn.commit()

print result

except MySQLdb.Error,e:

print "Mysql Error %d: %s" % (e.args[0], e.args[1])

conn.commit()

conn.close()

四、存入数据库后数据是如下图：

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉