python將日志導入數據庫代碼案例2

2024-06-28 16:00:49

字體：大中小

來源：轉載

供稿：網友

#!/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()

上一篇：puppet 管理配置索引

下一篇：簡單實現ssh免密登錄