python将日志导入数据库代码案例2

小鱼儿 2022-07-12 02:30 204阅读 0赞

\#!/usr/bin/env python

\#-\*-coding:utf-8-\*-

import MySQLdb

import os, sys, re,string

import time, tarfile,getopt

optmap = \{

'dbuser' : 'haoren',

'dbpass' : '123654',

'dbhost' : '192.168.10.12',

'dbport' : 3306,

'dbname' : 'JSDB'

**def get\_files(dir, pattern):**

res\_file\_list =\[\]  \#定义列表

if os.path.exists(dir):

cur\_file\_list = os.listdir(dir)

for file\_name in cur\_file\_list:

if re.search(pattern, file\_name):

res\_file\_list.append(file\_name)

return res\_file\_list

else:

return 'no'

**def main():**

**cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))**

**opts, args = getopt.getopt(sys.argv\[1:\], 'd:')**

for op, value in opts:

if op == '-d':

m = re.search('\[0-9\]\{8\}', value)

if m:

**cur\_day** = value

else:

print "请输入8位日期(比如：20160215)"

return 'no'

**log\_day** = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))

**fmt\_day** = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(**cur\_day**, '%Y%m%d'))))

print '结算统计日期:',fmt\_day

\#log\_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))

dirname="/home/haoren/logdir/%s\_67"%**log\_day**

print dirname

db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])

**db\_cursor=db\_conn.cursor()**

**db\_conn.query("use %s"%optmap\['dbname'\])**

**tabletime** = time.strftime("%y%m%d", time.localtime(time.mktime(time.strptime(**cur\_day**, "%Y%m%d"))))

sql="CREATE TABLE IF NOT EXISTS \`JIESUANTONGJI\_%s\` like JIESUANTONGJISAMPLE"%**tabletime**

db\_conn.query(sql)

**db\_conn.query**("delete from JIESUANTONGJI\_%s"%tabletime)

if os.path.exists("/tmp/JieSuanTongJi2016.txt"):

os.system("rm -f /tmp/JieSuanTongJi2016.txt")

file\_list2=get\_files(dirname,'billserver')

for file2 in file\_list2:

command = "cat %s/%s | grep -h -w  结算统计 |grep -v 人民币消费结算统计  >> /tmp/JieSuanTongJi2016.txt"%(dirname,file2)

os.system(command)

\#结算统计记录放在txt文档里面

filename='/tmp/JieSuanTongJi2016.txt'

**record = \{\}**

**a\_file = open(filename, 'r')**

\#160125-11:00:14 Bill\[40268\]  INFO: \[结算统计\]时间(1453690814)类别(1)名称(物品收入)平台(3977962)等级(2)用户(65147500)赠送(1)个物品(39)给歌唱家(65147500),歌唱家等级(28),签约(1), 消耗人民币(100), 歌唱家获得人民币(8000), 平台获得人民币(2000),歌唱家当前人民币(1320960)平台当前人民币(335560)

**for a\_line in a\_file.readlines():**

**m = re.search**("^(\\S+) Bill\\\[\\d+\\\]  INFO: \\\[结算统计\\\]时间\\((\\d+)\\)类别\\((\\d+)\\)名称\\((\\S+)\\)平台\\((\\d+)\\)等级\\((\\d+)\\)用户\\((\\d+)\\)赠送\\((\\d+)\\)个物品\\((\\d+)\\)给歌唱家\\((\\d+)\\),歌唱家等级\\((\\d+)\\),签约\\((\\d+)\\), 消耗人民币\\((\\d+)\\), 歌唱家获得人民币\\((\\d+)\\), 平台获得人民币\\((\\d+)\\),歌唱家当前人民币\\((\\d+)\\)平台当前人民币\\((\\d+)\\)", a\_line)

if m:

\#print "第一项:"+m.group(1)

\#print "第二项:"+m.group(2)

\#print "第三项:"+m.group(3)

\#print "第四项:"+m.group(4)

\#print "第五项:"+m.group(5)

\#print "第六项:"+m.group(6)

\#print "第七项:"+m.group(7)

\#print "第八项:"+m.group(8)

\#print "第九项:"+m.group(9)

\#print "第十项:"+m.group(10)

\#print "第十一项:"+m.group(11)

\#print "第十二项:"+m.group(12)

\#print "第十三项:"+m.group(13)

\#print "第十四项:"+m.group(14)

\#print "第十五项:"+m.group(15)

\#print "第十六项:"+m.group(16)

\#print "第十七项:"+m.group(17)

\#print "第十八项:"+m.group(18)

if int(m.group(14)) >0 or int(m.group(15)) >0 :

**db\_conn.query("insert into** JIESUANTONGJI\_%s(OPTIME,TYPE,ITEMNAME,CHANNELID,CHANNELLEVEL,PRESENTERID,ITEMNUM,ITEMID,SINGERID,SINGERLEVEL,SIGN,CONSUMECOIN,SINGERRECVGOLD,CHANNELRECVGOLD,CURRENTSINGERGOLD,CURRENTCHANNELGOLD) values(%d,%d,'%s',%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d)"%(tabletime,int(m.group(2)),int(m.group(3)),str(m.group(4)),int(m.group(5)),int(m.group(6)),int(m.group(7)),int(m.group(8)),int(m.group(9)),int(m.group(10)),int(m.group(11)),int(m.group(12)),int(m.group(13)),int(m.group(14)),int(m.group(15)),int(m.group(16)),int(m.group(17))))

a\_file.close()

db\_conn.commit()

db\_cursor.close()

db\_conn.close()

main()

\#if \_\_name\_\_ == "\_\_main\_\_":

\#        main()

\#!/usr/bin/env python

\#-\*-coding:utf-8-\*-

import MySQLdb

import os, sys, re,string

import time, tarfile,getopt

optmap = \{

'dbuser' : 'haoren',

'dbpass' : '123654',

'dbhost' : '192.168.10.12',

'dbport' : 3306,

'dbname' : 'JIESUANDB'

def get\_files(dir, pattern):

res\_file\_list =\[\]

if os.path.exists(dir):

cur\_file\_list = os.listdir(dir)

for file\_name in cur\_file\_list:

if re.search(pattern, file\_name):

res\_file\_list.append(file\_name)

return res\_file\_list

else:

return 'no'

def main():

cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))

opts, args = getopt.getopt(sys.argv\[1:\], 'd:')

for op, value in opts:

if op == '-d':

m = re.search('\[0-9\]\{8\}', value)

if m:

cur\_day = value

else:

print "请输入8位日期(比如：20130215)"

return 'no'

log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))

fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))

print '结算购物车日期:',fmt\_day

\#log\_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))

dirname="/home/haoren/logdir/%s\_21"%log\_day

print dirname

db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])

db\_cursor=db\_conn.cursor()

db\_conn.query("use %s"%optmap\['dbname'\])

tabletime = time.strftime("%y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))

sql="CREATE TABLE IF NOT EXISTS \`BAOGUOTONGJI\_%s\` like BAOGUOSAMPLE"%tabletime

db\_conn.query(sql)

db\_conn.query("delete from BAOGUOTONGJI\_%s"%tabletime)

if os.path.exists("/tmp/BaoGuoTongJi2016.txt"):

os.system("rm -f /tmp/BaoGuoTongJi2016.txt")

file\_list2=get\_files(dirname,'userpackageserver')

for file2 in file\_list2:

command = "cat %s/%s | grep -h -w  购物车结算  >> /tmp/BaoGuoTongJi2016.txt"%(dirname,file2)

os.system(command)

\#结算统计记录放在txt文档里面

filename='/tmp/BaoGuoTongJi2016.txt'

record = \{\}

a\_file = open(filename, 'r')

\#160201-00:00:38 UserPackage\[17501\]  INFO: \[购物车结算\]Function(17)OPName(\[箱\])Idx(86139)BackRoll(N)UID(22836501)Old(0)OP(1)(2)New(2)ID(39)Name(豆)expireTime(1454947200)Type(0)

\#160201-00:00:28 UserPackage\[17501\]  INFO: \[购物车结算\]Function(6)OPName(运输)Idx(0)BackRoll(N)UID(21993135)Old(2129)OP(1)(1)New(2130)ID(11)Name(券)expireTime(1454342428)Type(0)

\#160418-12:04:31 UserPackage\[17501\]  INFO: \[购物车结算\]Function(6)OPName(运输)Idx(8206)BackRoll(N)UID(32253058)Old(34)OP(2)减少(1)New(33)ID(41)Name(令)expireTime(0)Type(0)

\#160418-12:04:37 UserPackage\[17501\]  INFO: \[购物车结算\]Function(6)OPName(运输)Idx(8207)BackRoll(N)UID(32253058)Old(33)OP(2)减少(1)New(32)ID(41)Name(令)expireTime(0)Type(0)

for a\_line in a\_file.readlines():

\#m = re.search("^(\\S+) UserPackage\\\[\\d+\\\]  INFO: \\\[购物车结算\\\]Function\\((\\d+)\\)OPName\\((\\S+)\\)Idx\\((\\d+)\\)BackRoll\\((\\S+)\\)UID\\((\\d+)\\)Old\\((\\d+)\\)OP\\((\\d+)\\)\\((\\d+)\\)New\\((\\d+)\\)ID\\((\\d+)\\)Name\\((\\S+)\\)expireTime\\((\\d+)\\)Type\\((\\d+)\\)", a\_line)

m = re.search("^(\\S+) UserPackage\\\[\\d+\\\]  INFO: \\\[购物车结算\\\]Function\\((\\d+)\\)OPName\\((\\S+)\\)Idx\\((\\d+)\\)BackRoll\\((\\S+)\\)UID\\((\\d+)\\)Old\\((\\d+)\\)OP\\((\\d+)\\)(\\S+)\\((\\d+)\\)New\\((\\d+)\\)ID\\((\\d+)\\)Name\\((\\S+)\\)expireTime\\((\\d+)\\)Type\\((\\d+)\\)", a\_line)

if m:

print "第一项,时间:"+m.group(1)

print "第二项,Function:"+m.group(2)

print "第三项,OPName:"+m.group(3)

print "第四项,Idx:"+m.group(4)

print "第五项,BackRoll:"+m.group(5)

print "第六项,UID:"+m.group(6)

print "第七项,Old:"+m.group(7)

print "第八项,OP:"+m.group(8)

print "第九项,"+m.group(9)

print "第十项,New:"+m.group(10)

print "第十一项,ID:"+m.group(11)

print "第十二项,Name:"+m.group(12)

print "第十三项,expireTime:"+m.group(13)

print "第十四项,Type:"+m.group(14)

\#print "第十五项:"+m.group(15)

\#print "第十六项:"+m.group(16)

\#print "第十七项:"+m.group(17)

\#print "第十八项:"+m.group(18)

opname=m.group(3).replace('\[','')

opname=opname.replace('\]','')

\#print "第三项,OPName:"+opname

\#if (int(m.group(7)) < int(m.group(10))) :

\#    op=1

\#else :

\#    op=2

db\_conn.query("insert into BAOGUOTONGJI\_%s(FUNCTION,OPNAME,IDX,BACKROLL,UID,OLD,OP,NUM,NEW,ID,NAME,EXPIRETIME,TYPE) values(%d,'%s',%d,'%s',%d,%d,%d,%d,%d,%d,'%s',%d,%d)"%(tabletime,int(m.group(2)),opname,int(m.group(4)),str(m.group(5)),int(m.group(6)),int(m.group(7)),int(m.group(8)),int(m.group(10)),int(m.group(11)),int(m.group(12)),str(m.group(13)),int(m.group(14)),int(m.group(15))))

a\_file.close()

db\_conn.commit()

db\_cursor.close()

db\_conn.close()

main()

\#if \_\_name\_\_ == "\_\_main\_\_":

\#        main()