wanzb_v3_api/src/tools/split_table.py

#coding:utf-8
import os
import time
import datetime
import sys
import django
from django.core.cache import cache
from django.db import connection
#import pymysql
import torndb
import json

sys.path.append('/mnt/wzbapi/src')
os.environ['DJANGO_SETTINGS_MODULE'] = 'settings'
django.setup()

import common.models as cm
import common.common_control as ccc

def create_table(match_id):
    sql = """
      SET NAMES utf8mb4;
      SET FOREIGN_KEY_CHECKS = 0;
      DROP TABLE IF EXISTS `player_record_%s`;
      CREATE TABLE `player_record_%s`  (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `user_id` int(11) NULL DEFAULT NULL,
          `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
          `usercode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
          `match_id` int(11) NULL DEFAULT NULL,
          `match_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
          `match_group` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
          `init_fund` double NULL DEFAULT NULL,
          `yesterday_fund` double NULL DEFAULT NULL,
          `today_fund` double NULL DEFAULT NULL,
          `yesterday_stock` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
          `today_stock` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
          `yesterday_stock_img` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
          `today_stock_img` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
          `ctime` datetime(6) NULL,
          `player_id` int(11) NULL DEFAULT NULL,
          `stock_date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
          `today_income` double NULL DEFAULT NULL,
          `total_income` double NULL DEFAULT NULL,
          `rank` int(11) NULL DEFAULT NULL,
          `is_markt` smallint(6) NOT NULL,
          `yesterday_is_markt` smallint(6) NOT NULL,
          `auto_complete` smallint(6) NOT NULL,
          `yesterday_auto_complete` smallint(6) NOT NULL,
          `group_rank` int(11) NULL DEFAULT NULL,
          `badge` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
          `cw` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
          `df` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
          `experience` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
          `pz` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
          `wanzhu_comment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
          `zq` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
          `comments_count` int(11) NULL DEFAULT NULL,
          `zans` int(11) NULL DEFAULT NULL,
          `opmode_group` int(11) NULL DEFAULT NULL,
          `opmode_group_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
          PRIMARY KEY (`id`) USING BTREE
	) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; 
    SET FOREIGN_KEY_CHECKS = 1;
    """ % (match_id,match_id)

    db = torndb.Connection(
                '172.29.110.52:3306',
                'wanzb',
                user='wanzb',
                password='wanzb890*()',
                charset='utf8mb4')
    try:
        db.execute(sql)
    except Exception as e:
        print(e)
    db.close()


def insert_data(record):
    sql = """
       INSERT INTO `player_record_%s`(
			 `id`,
			 `user_id`,
			 `username`,
			 `usercode`,
			 `match_id`,
			 `match_name`,
			 `match_group`,
			 `init_fund`,
			 `yesterday_fund`,
			 `today_fund`,
			 `yesterday_stock`,
			 `today_stock`,
			 `yesterday_stock_img`,
			 `today_stock_img`,
			 `ctime`,
			 `player_id`,
			 `stock_date`,
			 `today_income`,
			 `total_income`,
			 `rank`,
			 `is_markt`,
			 `yesterday_is_markt`,
			 `auto_complete`,
			 `yesterday_auto_complete`,
			 `group_rank`,
			 `badge`,
			 `cw`,
			 `df`,
			 `experience`,
			 `pz`,
			 `wanzhu_comment`,
			 `zq`,
			 `comments_count`,
			 `zans`
		 )VALUES (
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s",
			 "%s"
		 ); 
    """ % (
		 record["match_id"],
		 record["id"],
		 record["user_id"],
		 record["username"],
		 record["usercode"],
		 record["match_id"],
		 record["match_name"] if record["match_name"] else "",
		 record["match_group"],
		 record["init_fund"],
		 record["yesterday_fund"],
		 record["today_fund"],
		 record["yesterday_stock"].replace('"','\\"') if record["yesterday_stock"] else "[]",
		 record["today_stock"].replace('"','\\"') if record["today_stock"] else "[]",
		 record["yesterday_stock_img"].replace('"','\\"') if record["yesterday_stock_img"] else "[]",
		 record["today_stock_img"].replace('"','\\"') if record["today_stock_img"] else "[]",
		 record["ctime"],
		 record["player_id"],
		 record["stock_date"],
		 record["today_income"],
		 record["total_income"],
		 record["rank"] if record["rank"] else 0,
		 record["is_markt"],
		 record["yesterday_is_markt"],
		 record["auto_complete"],
		 record["yesterday_auto_complete"],
		 record["group_rank"],
		 record["badge"],
		 record["cw"] if record["cw"] else "",
		 record["df"] if record["df"] else "",
		 record["experience"] if record["experience"] else "",
		 record["pz"] if record["pz"] else "",
		 record["wanzhu_comment"] if record["wanzhu_comment"] else "",
		 record["zq"] if record["zq"] else "",
		 record["comments_count"] if record["comments_count"] else 0,
		 record["zans"] if record["zans"] else 0
	)
    db = torndb.Connection(
                '127.0.0.1:3306',
                'wanzb',
                user='root',
                password='xjc890*()',
                charset='utf8mb4')
    try:
        #print(sql,22222222222222222222)
        sql = sql.replace("\n","").replace("\t","")
        #print(sql,33333333333333333333)
        db.execute(sql)
    except Exception as e:
        print(e)
        print(sql)
    #db.close()


def split_table():
    """
    """
    qset = cm.Match.objects.filter(id=15)
    #qset = cm.Match.objects.all()
    for obj in qset:
        match_id = obj.id
        create_table(match_id)

def migrate_data():
    #qset = cm.Match.objects.all()
    qset = cm.Match.objects.filter(id=11)
    for obj in qset:
        match_id = obj.id
        record_set = cm.PlayerRecord.objects.filter(match_id=match_id,stock_date='2022-07-20')
        data = list(record_set.values())
        for item in data:
            insert_data(item)

if __name__ == "__main__":
    split_table()
    #migrate_data()