import pymysql

# 打开数据库连接  这是虚拟机映射为网络数据库
db = pymysql.connect("192.168.1.201", "root", "KH*&6kdgl3", "platform_gala_message", port=3307, charset='utf8')
# ----------------        IP          用户名    密码     database   端口port      字符编码

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# 原表名
table_name = "basketball_token"
# 使用execute方法执行SQL语句
for i in range(0, 10):
    sql = """
          CREATE TABLE `{table_name}_{idx}`  (
              `server_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '区服id',
              `role_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色id(用于区分表后缀)',
              `game_type` int(2) NOT NULL COMMENT '游戏类型(用于区分表前缀)',
              `vendor` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '设备的厂商',
              `token` varchar(3000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '设备token',
              `md5` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '设备token的md5',
              `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
              `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
              PRIMARY KEY (`server_id`, `role_id`) USING BTREE
            ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
          """.format(table_name=table_name, idx=i)
    cursor.execute(sql)
db.commit()

cursor.execute("show tables;")
# 使用 fetchall() 方法获取返回结果数据集
data = cursor.fetchall()
for d in data:
    print(d)

# 关闭数据库连接
db.close()