# -*- coding:utf8 -*- ''' Created on 2016年11月29日 @author: qiancheng ''' import MySQLdb from email.mime.text import MIMEText from email.header import Header import smtplib import redis import datetime from decimal import Decimal class do_redis(): def __init__(self,_redishost,_redisport,_redispasswd,_db): do_redis=redis.ConnectionPool(host=_redishost,port=_redisport,password=_redispasswd,db=_db) self.redis_cli=redis.Redis(connection_pool=do_redis) def set_redis(self,_hash_name,_hash_keys): self.redis_cli.hmset(_hash_name, _hash_keys) def get_redis(self,_hash_name): print self.redis_cli.hgetall(_hash_name) class mail_to: def __init__(self,_message): self.message=_message mail_host="115.231.109.86" #smtp-server mail_user="qiancheng" #username mail_pass="xxxx" #password sender = 'qiancheng@showjoy.com' receivers = ['xxxx@showjoy.com'] message = MIMEText(self.message,'plain','utf-8') message['From'] = Header("qiancheng@showjoy.com<qiancheng@showjoy.com>") message['To'] = Header("xxxx@showjoy.com<xxxx@showjoy.com>") subject = '达人店交易信息统计' message['Subject'] = Header(subject,'utf-8') try: smtpObj=smtplib.SMTP() smtpObj.connect(mail_host) smtpObj.login(mail_user,mail_pass) smtpObj.sendmail(sender, receivers, message.as_string()) smtpObj.close() except smtplib.SMTPException: print("Error: 无法发送邮件") shopdbinfo={'host':'xxxxx','user':'root','port':22306,'passwd':'xxxx','db':'shop'} tradedbinfo={'host':'xxxxx','user':'root','port':10306,'passwd':'xxxx','db':'trade'} class do_db(object): def __init__(self,_host,_port,_user,_passwd,_db): self.host=_host self.port=_port self.passwd=_passwd self.dbf=_db self.user=_user self.db=MySQLdb.connect(host=self.host,user=self.user,passwd=self.passwd,port=self.port,db=self.dbf) self.cursor=self.db.cursor(MySQLdb.cursors.DictCursor) def execsql(self,_sql): self.sql=_sql self.cursor.execute(self.sql) return self.cursor.fetchoneDict() def closedb(self): self.cursor.close() self.db.close() def get_order_monitor(): now=datetimes day=str(now.strftime('%Y-%m-%d'))+'%' _times=day #计算前一日的统计信息sql语句 '''sql_recommission='SELECT shop_commission_order_r.ORDER_NUMBER recommission_resql from shop.shop_commission_order_r,shop.shop_commission \ where SHOP_COMMISSION_ID = ID and shop_commission.IS_DELETE = 0 and \ shop_commission_order_r.gmt_modified BETWEEN DATE_SUB(NOW(),INTERVAL 1 day) and NOW() \ group by SHOP_ID,COMMISSION,COMMISSION_TYPE,shop_commission_order_r.GMT_CREATE,ORDER_NUMBER,SKU_ID \ having count(*)>1' ''' sql_oneday_recruit='select count(DISTINCT sr.recruit_shop_id)*360 one_day_startshop \ from shop.shop_commission_recruit_r as sr \ where sr.gmt_modified like \''+_times+'\' and sr.is_delete = 0' sql_oneday_recruit_comm='select sum(sc.COMMISSION) one_day_startshop_commisson \ from shop.shop_commission as sc \ where (sc.COMMISSION_TYPE = 201 or sc.COMMISSION_TYPE = 301) \ and sc.gmt_modified like \''+_times+'\' and sc.is_delete = 0' sql_oneday_order='SELECT SUM(tpr.actual_price) one_day_order FROM trade.trade_pay_record tpr \ LEFT JOIN trade.trade_order_pay_record_r topr ON tpr.id = topr.pay_record_id \ LEFT JOIN trade.trade_order tr ON topr.order_number = tr.order_number \ WHERE tpr.paid_time like \''+_times+'\' AND tr.source_type = 1 and tr.IS_CANCEL = 0' sql_oneday_order_comm='select sum(sc.COMMISSION) one_day_order_commission from shop.shop_commission as sc \ where (sc.COMMISSION_TYPE = 200 or sc.COMMISSION_TYPE = 300 and sc.is_delete = 0) \ and sc.gmt_modified like \''+_times+'\'' '''计算一天开店收入''' todo=do_db(shopdbinfo['host'],shopdbinfo['port'],shopdbinfo['user'],shopdbinfo['passwd'],shopdbinfo['db']) one_day_startshop = todo.execsql(sql_oneday_recruit)['one_day_startshop'] todo.closedb() if one_day_startshop is None: one_day_startshop = 0 '''计算一天开店发放的收益''' todo=do_db(shopdbinfo['host'],shopdbinfo['port'],shopdbinfo['user'],shopdbinfo['passwd'],shopdbinfo['db']) one_day_startshop_commisson = todo.execsql(sql_oneday_recruit_comm)['one_day_startshop_commisson'] todo.closedb() if one_day_startshop_commisson is None: one_day_startshop_commisson = 0 '''查询重复发放收益''' '''todo=do_db(shopdbinfo['host'],shopdbinfo['port'],shopdbinfo['user'],shopdbinfo['passwd'],shopdbinfo['db']) recommission_resql = todo.execsql(sql_recommission) todo.closedb() if recommission_resql is None: recommission_resql = 0 ''' '''计算一天订单总额''' todo=do_db(tradedbinfo['host'],tradedbinfo['port'],tradedbinfo['user'],tradedbinfo['passwd'],tradedbinfo['db']) one_day_order = todo.execsql(sql_oneday_order)['one_day_order'] todo.closedb() if one_day_order is None: one_day_order = 0 '''计算一天订单收益总额''' todo=do_db(shopdbinfo['host'],shopdbinfo['port'],shopdbinfo['user'],shopdbinfo['passwd'],shopdbinfo['db']) one_day_order_commission = todo.execsql(sql_oneday_order_comm)['one_day_order_commission'] todo.closedb() if one_day_order_commission is None: one_day_order_commission = 0 if one_day_startshop != 0: recruitpercent=one_day_startshop_commisson/one_day_startshop*100 else: recruitpercent=0 if one_day_order != 0: orderpercent=one_day_order_commission/one_day_order*100 else: orderpercent=0 '''if recommission_resql != 0: recommission_message='一天内有重复发放收益的订单存在，请查询数据库确认！' else: recommission_message='一天内没有重复发放收益。' ''' '''final_result='%s \n一天开店总金额：%d，一天开店发放收益：%d，收益发放比例：%.2f \n一天达人店订单总额：%d，一天达人店订单发放收益：%d。收益发放比例：%.2f' \ %(recommission_message,one_day_startshop,one_day_startshop_commisson,recruitpercent,one_day_order,one_day_order_commission,orderpercent) #print (str(final_result))''' order_monitor_info={'startshop':float(Decimal(str(one_day_startshop)).quantize(Decimal('0.00'))), 'startshop_commisson':float(Decimal(str(one_day_startshop_commisson)).quantize(Decimal('0.00'))), 'recruitpercent':float(Decimal(str(recruitpercent)).quantize(Decimal('0.00'))), 'order':float(Decimal(str(one_day_order)).quantize(Decimal('0.00'))), 'order_commission':float(Decimal(str(one_day_order_commission)).quantize(Decimal('0.00'))), 'orderpercent':float(Decimal(str(orderpercent)).quantize(Decimal('0.00'))) }#算出订单统计的结果并且保留2位小数 return order_monitor_info #mail_to(str(final_result)) def set_order_monitor(_order_monitor_info): now=datetimes key_name=str(now.strftime('%Y-%m-%d')) hash_name='order_monitor'#hash的名字 test_redis_info={'redishost':'192.168.0.223','redisport':'6380','password':'xxxxx','db':10}#线下的redis信息 increment_redis_info={'redishost':'xxxx','redisport':'6379','password':'xxxxxx','db':8}#线上的redis信息 hash_keys={key_name:str(_order_monitor_info)}#key_name就是日期，value就是string化的统计的订单结果 if env=='test': r=do_redis(test_redis_info['redishost'],test_redis_info['redisport'],test_redis_info['password'],test_redis_info['db']) elif env=='increment': r=do_redis(increment_redis_info['redishost'],increment_redis_info['redisport'],increment_redis_info['password'],increment_redis_info['db']) else: print '请选择正确的环境参数' exit() r.set_redis(hash_name,hash_keys) r.get_redis(hash_name) if __name__ == '__main__': global datas global year global month global day global datetimes global env env='increment' datas='all' year=int(datetime.datetime.now().strftime('%Y')) month=int(datetime.datetime.now().strftime('%m')) day=int(datetime.datetime.now().strftime('%d')) d1=datetime.datetime(2016,07,01) d2=datetime.datetime(year,month,day) if datas=='all': day_values=int((d2-d1).days) elif datas=='one': day_values=0 else: print '请选择正确的数据量参数' exit() for i in range(0,day_values+1): datetimes=datetime.date(datetime.date.today().year,datetime.date.today().month,datetime.date.today().day)-datetime.timedelta(i) order_monitor_result=get_order_monitor() set_order_monitor(order_monitor_result)
把按天计算的数据存放到redis中，避免每次读取都要使用mysql的查询很慢，redis使用hash模式，key值定义好后，value就是{日期：json}这样的格式。读取出来的时候再进行json序列化就可以了。小数位的保留使用了Decimal，其实用round更简单一些，但是做这个事情的时候发现了个bug，python2.6使用round和Decimal都会出现四舍五入不精确导致float过长的问题，升级成2.7就好了。这个脚本可以做成参数启动的，觉得麻烦没写。就直接改global 参数了。
PS：邮件class没有使用，redis的数据结构是这样的：
'2016-12-02': "{'order_commission': 8521.8, 'recruitpercent': 70.61, 'orderpercent': 15.35, 'startshop_commisson': 20336.0, 'startshop': 28800.0, 'order': 55501.86}"
本站文章如无特殊说明，均为本站原创，如若转载，请注明出处：精炼mysql的数据，并且存放到redis中。 - Python技术站
微信扫一扫 
支付宝扫一扫 