Jackey's 感悟

Do Research

Asterisk的CDR记录转换成sqlite的python脚本

Asterisk在服务过程中会成长Call Detailed Record (CDR)日志文件,可用于通话计费。

这在一定程度上保证了数据来源的可靠性,有助于后期实验设计。

但是该CDR文件虽然是以逗号隔开的CSV文件,但是导入sqlite的时候存在问题,用sqlite自带的.import命令在处理特殊数据时候存在bug,例如数据为:

“”,”307″,”001307″,”ipic-out”,”””307″” <307>”,”SIP/307-082593c8″,”Local/307@ipic-out-46c2,1″,”Dial”,”SIP/307@server_ipic4″,”2010-08-25 01:01:05″,”2010-08-25 01:01:05″,”2010-08-25 01:01:10″,5,5,”ANSWERED”,”DOCUMENTATION”,”1282698065.16″,””

上述为一条CDR日志。在将”Local/307@ipic-out-46c2,1″导入时,因为引号内部的逗号,会造成导入失败。一种解决方案是用其他工具来处理,python在自带的csv模块下,可以方便地处理这种csv文件。代码见后文,聊记以供后用。

#! Env Python
# Import the CDR csv data into sqlite3 database

import sqlite3
import csv


class CSV2Sqlite:
	def __init__(self,dbfile,table_name,csvfile):
		self.dbfile = dbfile
		self.table_name= table_name
		self.csvfile= csvfile
		#####################################
		self.table_script='''
		drop table if exists %s;
		
		CREATE TABLE %s (
		cdr_idx INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
		accountcode VARCHAR(20)  NULL,
		src VARCHAR(80)  NOT NULL,
		dst VARCHAR(80)  NOT NULL,
		dcontext VARCHAR(80)  NOT NULL,
		clid VARCHAR(80)  NOT NULL,
		channel VARCHAR(80)  NULL,
		dstchannel VARCHAR(80)  NOT NULL,
		lastapp VARCHAR(80)  NOT NULL,
		lastdata VARCHAR(80)  NOT NULL,
		start DATETIME   NULL,
		answer DATETIME  NULL,
		end DATETIME  NULL,
		duration INTEGER  NULL,
		billsec INTEGER  NULL,
		disposition VARCHAR(12)  NULL,
		amaflags VARCHAR(12)  NULL,
		uniqueid VARCHAR(32)  NULL,
		unknown VARCHAR(80)  NULL);
		''' % (self.table_name, self.table_name)
		
		schema_cols='''accountcode,src,dst,dcontext,
		clid,channel,dstchannel,lastapp,lastdata,start,
		answer,end,duration,billsec,disposition,amaflags,
		uniqueid,unknown'''

		self.sql = "insert into "+table_name \
			+" ("+schema_cols+") values ("+"?,"*17+"?"+") ;"
	
	def trans(self):
		con = sqlite3.connect(self.dbfile)
		cur = con.cursor()
		# create the table
		print self.table_script
		cur.executescript(self.table_script)

		# Read the csv file
		spamreader = csv.reader(open(self.csvfile), delimiter=',');
		for row in spamreader:
			# insert data
			cur.execute(self.sql,tuple(row))
		
		# the commit must call directly, 
		# or the transaction will be failed
		con.commit()
		cur.close()
		con.close()

		print "Importing Done!"

def help(excutefile):
	print '''Import the Asterisk CDR file (the csv separated with ',') to a sqlite file.

%s <sqlite db file> <table name> <csv file name>

	''' % (excutefile)

if __name__ == "__main__":
	import sys
	if len(sys.argv)>=4:
		# do it
		tran = CSV2Sqlite(sys.argv[1],sys.argv[2],sys.argv[3])
		tran.trans()
	else:
		help(sys.argv[0])
Advertisements

发表评论

Fill in your details below or click an icon to log in:

WordPress.com 徽标

You are commenting using your WordPress.com account. Log Out /  更改 )

Google+ photo

You are commenting using your Google+ account. Log Out /  更改 )

Twitter picture

You are commenting using your Twitter account. Log Out /  更改 )

Facebook photo

You are commenting using your Facebook account. Log Out /  更改 )

Connecting to %s

%d 博主赞过: