#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()