mysql的数据怎样存储到redis_精炼mysql的数据，并且存放到redis中。

#-*- coding:utf8 -*-

'''Created on 2016年11月29日

@author: qiancheng'''

importMySQLdbfrom email.mime.text importMIMETextfrom email.header importHeaderimportsmtplibimportredisimportdatetimefrom decimal importDecimalclassdo_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)defset_redis(self,_hash_name,_hash_keys):

self.redis_cli.hmset(_hash_name, _hash_keys)defget_redis(self,_hash_name):printself.redis_cli.hgetall(_hash_name)classmail_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")

message['To'] = Header("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()exceptsmtplib.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'}classdo_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)defexecsql(self,_sql):

self.sql=_sql

self.cursor.execute(self.sql)returnself.cursor.fetchoneDict()defclosedb(self):

self.cursor.close()

self.db.close()defget_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 isNone:

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 isNone:

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 isNone:

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 isNone:

one_day_order_commission=0if one_day_startshop !=0:

recruitpercent=one_day_startshop_commisson/one_day_startshop*100

else:

recruitpercent=0if 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位小数

returnorder_monitor_info#mail_to(str(final_result))

defset_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__':globaldatasglobalyearglobalmonthglobaldayglobaldatetimesglobalenv

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=0else: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)