转换数据

# !/usr/bin/env python3
# -*- coding:utf-8 -*-

import pymysql
import os
import time
from datetime import date, datetime

remoteDBUser='db_order'
remoteDBPasswd='db_order'
remoteDBHost='192.168.16.125'

try:
    conn = pymysql.connect(host=remoteDBHost,port= 3306,user = remoteDBUser,passwd=remoteDBPasswd,db='db_order_sharding')
    cur = conn.cursor()
    cur.execute("select shard_name from ord_table_shards")
    shardTuples = cur.fetchall()
    print(shardTuples)
    storeIdSet = set()
    for shardTuple in shardTuples:
        shardName = shardTuple[0]
        #print("shardName:%s" %shardName)
        storeIdStr = shardName.split('_')[-1]
        #print("storeId:%s" % storeIdStr)
        storeIdSet.add(storeIdStr)
    print("storeIdSet:%s" %storeIdSet)
    cntMap = dict()
    backupDir = '/home/developer/mysqlbackup/' + time.strftime("%Y%m%d")
    if not os.path.exists(backupDir):
        os.makedirs(backupDir)
    writeFileName = backupDir + "/updateOrderCode.sql"
    if os.path.exists(writeFileName):
        os.remove(writeFileName)
    with open(writeFileName, 'a', encoding='utf-8') as f:
        for storeId in storeIdSet:
            storeIdHex = hex(int(storeId))
            stripHex = storeIdHex[2:]
            print("storeId:%s ,storeIdHex:%s,%s" %(storeId,storeIdHex,stripHex))
            generateStr = stripHex
            if len(stripHex) < 6:
                prependZero = '0' * (6 -len(stripHex))
                generateStr = prependZero + stripHex
                print("generateStr:%s" %generateStr)
            cur.execute("select id, create_time, store_id from ord_order_info_%s" %storeId)
            createTimeTuples = cur.fetchall()
            print(createTimeTuples)
            for createTimeTuple in createTimeTuples:
                orderId = createTimeTuple[0]
                print("orderId:%s" %orderId)
                createTime = createTimeTuple[1]
                #print("createTime:%s, type:%s" %(createTime,type(createTime)))
                timeStr = createTime.strftime('%Y%m%d')
                print("timestr:%s" %timeStr)
                key = generateStr + timeStr
                print("key:%s" %key)
                cnt = 0
                if cntMap.get(key) is None:
                    print("not has key")
                    cnt = 1
                    cntMap[key] = 1
                else:
                    print("has key")
                    cntMap[key] = cntMap[key] + 1
                    cnt = cntMap[key]
                strCnt = str(cnt)
                gCnt = strCnt
                lenCnt = len(strCnt)
                if lenCnt < 4:
                    pZero = '0' * (4 - lenCnt)
                    gCnt = pZero + strCnt
                print("gCnt:%s" %gCnt)
                print("gOrderCode:%s%s" %(key, gCnt))
                gOrderCode = key + gCnt
                sid = gOrderCode[:6]
                storeIdH = int(sid,16)
                print("storeIdH:%s, storeId:%s" %(storeIdH, createTimeTuple[2]))
                #cur.execute("update pay_flow_%s p, ord_order_info_%s i set p.order_id = %s where p.order_id = i.id and i.id = %s" %(storeIdH, storeIdH, gOrderCode, orderId))
                f.writelines("update pay_flow_%s p, ord_order_info_%s i set p.order_id = '%s' where p.order_id = i.id and i.id = '%s';\n" %(storeIdH, storeIdH, gOrderCode, orderId))
                #print("update pay_flow_%s orderId:%s success!" %(storeIdH, orderId))
                f.writelines("update ord_order_detail_%s d, ord_order_info_%s i set d.order_id = '%s' where d.order_id = i.id and i.id = '%s';\n" %(storeIdH, storeIdH, gOrderCode, orderId))
                    #cur.execute("update ord_order_detail_%s d, ord_order_info_%s i set d.order_id = %s where d.order_id = i.id and i.id = %s" %(storeIdH, storeIdH, gOrderCode, orderId))
                    #cur.execute("update ord_order_info_%s set id = %s where id = %s" %(storeIdH,gOrderCode,orderId))
                f.writelines("update ord_order_info_%s set id = '%s' where id = '%s';\n" %(storeIdH,gOrderCode,orderId))
    #f.flush()
    #f.close()
    cur.close()
    conn.close()
except Exception as  e:
        print(e)