一个对帐的小脚本

前一段时间一直在做各种对数据的事情,因为我们的业务涉及到银联钱包,并且我们的客户认为其给的某些数据有问题,所以我们写了这样一个小脚本来验证,经验证数据没有问题,不过给过来的数据需要进行一些差错处理,需要仔细一点。
废话不多说,直接上程序

#!/usr/bin/env python
# encoding: utf-8
import logging
import pymysql
import os


class compare(object):

    def __init__(self):
        self.file_path = "G:/codes/python/total"
        self.db_conn = None
        self.__cid_info = dict()
        self.get_file_list = list()
        self.pay_file_list = list()

    def get_conn(self):
        try:
            self.db_conn = pymysql.connect(host = "127.0.0.1", port=3306,
                                user="root", passwd="205339", db="dbvip",charset='utf8')
        except Exception,e:
            logging.error("mysql get connection error [%s]"%e)
            return False
        return True

    def get_filelist(self):
        if not self.file_path: return -1
        list = os.listdir(self.file_path)
        list.sort()
        for file_name in list:
            if file_name[9:12] == 'get':
                self.get_file_list.append(file_name)
            else:
                self.pay_file_list.append(file_name)
        self.get_file_list.sort()
        self.pay_file_list.sort()
        logging.info("get_file_list %s"%self.get_file_list)
        logging.info("pay_file_list %s"%self.pay_file_list)
        return 0

    def load_get_file(self):
        for filename in self.get_file_list:
            fname = self.file_path + '/' + filename
            if not os.path.exists(fname):
                logging.error("Fname %s not exists."%(fname))
                return -1
            if not os.access(fname, os.R_OK):
                logging.error("Fname %s not readable."%(fname));
                return -1
            logging.info("Open file %s..."%fname)
            fhandle = open(fname)
            flist   = fhandle.readlines()
            fhandle.close()
            count = 0
            if filename == "20140324-get.txt":
                for line in flist:
                    count += 1
                    if len(line) == 0:
                        continue
                    rec        = line.split(',')
                    cid        = rec[0]
                    addpoint   = rec[1]
                    if not self.__cid_info.has_key(cid):
                        self.__cid_info[cid] = dict()
                        self.__cid_info[cid]['addpoint'] = 0
                        self.__cid_info[cid]['decpoint'] = 0
                    self.__cid_info[cid]['addpoint'] += float(addpoint)
                    if count % 1000 == 0:
                        logging.info("processing line %d"%count)
                continue
            elif filename[:6] < "201411":
                for line in flist:
                    count += 1
                    if count == 1:
                        continue
                    if len(line) == 0:
                        continue
                    rec        = line.split(' ')
                    cid        = rec[1]
                    account    = rec[2]
                    verder_id  = rec[3]
                    date       = rec[4]
                    addpoint   = rec[13]
                    status     = rec[14]
                    if account <> '' or status <> '成功':
                        logging.info("account %s,status %s line %d"%(account,status,count))
                        continue
                    if not self.__cid_info.has_key(cid):
                        self.__cid_info[cid] = dict()
                        self.__cid_info[cid]['addpoint'] = 0
                        self.__cid_info[cid]['decpoint'] = 0
                    self.__cid_info[cid]['addpoint'] += float(addpoint)
                    if count % 1000 == 0:
                        logging.info("processing line %d"%count)
            else:
                for line in flist:
                    count += 1
                    if len(line) == 0:
                        continue
                    rec        = line.split(',')
                    cid        = rec[0].strip('\"')
                    account    = rec[1].strip('\"')
                    verder_id  = rec[2].strip('\"')
                    date       = rec[3].strip('\"')
                    if date >= "2015/07/01":
                        continue
                    addpoint   = float(rec[10][1:8])*(10 ** int(rec[10][-3:]))
                    status     = rec[11].strip('\"')[:4]
                    #if cid == 'c00055248975': print("addpoint[%s],date[%s]"%(addpoint,date))
                    if account <> '' or status <> '成功':
                        logging.info("account %s,status %s line %d"%(account,status,count))
                        continue
                    if not self.__cid_info.has_key(cid):
                        self.__cid_info[cid] = dict()
                        self.__cid_info[cid]['addpoint'] = 0
                        self.__cid_info[cid]['decpoint'] = 0
                    self.__cid_info[cid]['addpoint'] += float(addpoint)
                    if count % 1000 == 0:
                        logging.info("processing line %d"%count)
        return 0

    def load_pay_file(self):
        for filename in self.pay_file_list:
            fname = self.file_path + '/' + filename
            if not os.path.exists(fname):
                logging.error("Fname %s not exists."%(fname))
                return -1
            if not os.access(fname, os.R_OK):
                logging.error("Fname %s not readable."%(fname));
                return -1
            logging.info("Open file %s..."%fname)
            fhandle = open(fname)
            flist   = fhandle.readlines()
            fhandle.close()
            count = 0
            if filename == "20140324-pay.txt":
                for line in flist:
                    count += 1
                    if len(line) == 0:
                        continue
                    rec        = line.split(',')
                    cid        = rec[0]
                    decpoint   = rec[1]
                    if not self.__cid_info.has_key(cid):
                        logging.info("the cid has not been send point %s,line %d"%(cid,count))
                        continue
                    self.__cid_info[cid]['decpoint'] += float(decpoint)
                    if count % 1000 == 0:
                        logging.info("processing line %d"%count)
            elif filename[:6] < "201411":
                for line in flist:
                    count += 1
                    if count == 1:
                        continue
                    if len(line) == 0:
                        continue
                    rec        = line.split(' ')
                    cid        = rec[1]
                    account    = rec[2]
                    verder_id  = rec[3]
                    date       = rec[4]
                    decpoint   = rec[13]
                    status     = rec[14]
                    if account == '' or status <> '成功':
                        logging.info("account %s,status %s,line %d"%(account,status,count))
                        continue
                    if not self.__cid_info.has_key(cid):
                        logging.info("the cid has not been send point %s,line %d"%(cid,count))
                        continue
                    self.__cid_info[cid]['decpoint'] += float(decpoint)
                    if count % 1000 == 0:
                        logging.info("processing line %d"%count)
            else:
                for line in flist:
                    count += 1
                    if len(line) == 0:
                        continue
                    rec        = line.split(',')
                    cid        = rec[0].strip('\"')
                    account    = rec[1].strip('\"')
                    verder_id  = rec[2].strip('\"')
                    date       = rec[3].strip('\"')
                    if date >= "2015/07/01":
                        continue
                    decpoint   = float(rec[10][1:5])*(10 ** int(rec[10][-3:]))
                    status     = rec[11].strip('\"')[:4]
                    if account == '' or status <> '成功':
                        logging.info("account %s,status %s line %d"%(account,status,count))
                        continue
                    if not self.__cid_info.has_key(cid):
                        logging.info("the cid has not been send point %s,line %d"%(cid,count))
                        continue
                    self.__cid_info[cid]['decpoint'] += float(decpoint)
                    if count % 1000 == 0:
                        logging.info("processing line %d"%count)
        return 0

    def commit_data(self):
        count = 0
        cur = self.db_conn.cursor()
        for cid in self.__cid_info.keys():
            count += 1
            opt_sql = "insert into bm_cid_info(cid,add_point,dec_point) values('%s',%f,%f)"%(cid, round(self.__cid_info[cid]['addpoint'],2), round(self.__cid_info[cid]['decpoint'],2))
            try:
                cur.execute(opt_sql)
            except Exception,e:
                logging.error("mysql insert bm_cid_info error [%s]"%e)
                cur.close()
                return False
            if count % 10000 == 0:
                self.db_conn.commit()
                logging.info("handled user %d"%count)
        logging.info("handled total user %d"%count)
        self.db_conn.commit()
        cur.close()
        return True

    def run(self):
        if self.get_conn() < 0: return -1
        if self.get_filelist() < 0: return -1
        if self.load_get_file() < 0: return -1
        if self.load_pay_file() < 0: return -1
        if self.commit_data() < 0: return -1
        return 0

if __name__ == '__main__':
    comp = compare()
    logging.basicConfig( filename="G:\codes\logs\compare.log",
                         format  = "%(asctime)s %(levelname)s[%(process)d]:%(message)s",
                         level   = "INFO")
    if comp.run() < 0:
        logging.info("compare failed")
    else:
        logging.info("compare succeed")

一个简单的小程序,说一下流程,我们有两种文件需要获取,一种是get.txt,一种是pay.txt,这两种文件格式基本相同但是有些差别,所以写了两个函数来进行处理,中间因为有过文件格式的变动所以需要好多个分枝进行处理,处理完之后会用一个字典把得到的所有信息汇总起来,最后提交到数据库里去,只实现了这么一个小功能,不过现在有了个新的想法,因为上一次看过一个讲python闭包的小视频,等一会试一下看看能不能改写一下这个程序变得更简单一点,要是可以的话再来给大家分享。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
名称: V.B.A银行对程序V2.1??功能: 1) 进行企业银行日记与银行对单自动核对,摆脱繁重手工劳动。?? 2) 可以用于财务上除银行日记以外的其他数据自动核对。?? ??特点: 自动核对数据,显示对结果并做出标记,自动生成余额调节表,显示对完成进度。????使用说明: 1)在工作表左侧逐笔输入银行对单的借、贷方金额,行与行之间不能有空格。?? ?? 2)在工作表右侧逐笔输入银行日记的借、贷方金额,如果您公司是电算化,数据可从软件中导出粘贴到表中。?? ?? 3) 输入完毕后点击菜单下《执行对》按钮,系统开始自动核对,核对结束后未对上的数据将写入未达项表内并生成调节表。???? 4) 下月再次对前先点击菜单下《读入上月》按钮,将未达项读入输入区,然后再执行第一、二步输入数据。??---------------------------------------------------------------------------------------------------------------------------????运行环境: 最佳工作环境为 Excel 2000 ??参考资料: 顾斌(rowen)先生的营养指南。??-----------------------------------------------------------------------????编制: 秦欣 (Jurry1003@163.com)??日期: 2003-11-7??-----------------------------------------------------------------------????备注: 1) 本版本为试用版,需注册使用,未注册版可使用15次。?? 2) 请在试用结束前注册,注册费用每台电脑¥30.00。?? 3) 注册时请将您的注册信息准确发至Jurry1003@163.com  

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

leadersnowy

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值