Python访问Mysql分表数据的方法示例

show create table user_PRofile_252;CREATE TABLE `user_profile_252` (  `uid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  `nickname` varchar(45) CHARACTER SET utf8 NOT NULL COMMENT '用户昵称，全局唯一',  `avatar` varchar(500) CHARACTER SET utf8 NOT NULL COMMENT '用户头像地址',  `signature` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '用户签名信息',  `gender` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '用户性别，三个枚举值"male","female","unknown"',  `type` tinyint(4) NOT NULL COMMENT '用户类型:1->注册用户，2->游客',  `source` int(11) NOT NULL COMMENT '用户来源：1->新浪,2->QQ',  `phone_num_verified` tinyint(4) NOT NULL DEFAULT '-1' COMMENT '用户手机认证状态：-1->未认证,0->已认证',  `id_card_verified` tinyint(4) NOT NULL DEFAULT '-1' COMMENT '用户身份证认证状态：-1->未认证,0->已认证',  `state` tinyint(4) NOT NULL COMMENT '用户状态,0->正常用户,-1->黑名单用户',  `last_login_time` datetime NOT NULL COMMENT '用户最后一次登录时间',  `created_time` datetime NOT NULL COMMENT '用户注册时间',  `updated_time` datetime NOT NULL COMMENT '用户信息更新时间',  PRIMARY KEY (`uid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';show create table bi_find_nickname_from_uid;CREATE TABLE `bi_find_nickname_from_uid` (  `uid` varchar(100) DEFAULT NULL,  `nickname` varchar(200) DEFAULT NULL,  `etl_time` datetime DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;其中，源表是多个分表：其分表后缀是以Uid模256所得结果命名。2、执行根据Uid查找用户昵称的代码实际上，是将查找的结果，放在目标数据库表里。/Users/nisj/PycharmProjects/BiDataProc/UserPortrait/findDataFromModSubmeter.py
# -*- coding=utf-8 -*-import os,re,timedef find_nickname_from_uid(uid):    tab_tail = uid%256    nickname_uid_data = os.popen("""/usr/bin/MySQL -hsourceip -P50514 -usourceUser -psourcePass --default-character-set=utf8 -N -e "set names utf8; /    use jellyfish_user; /    select uid,nickname from user_profile_%s where uid='%s'; /    " """ % (tab_tail, uid)).readlines();    msd_list = []    for ms_list in nickname_uid_data:        ms = re.split('/t', ms_list.replace('/n', ''))        msd_list.append(ms)    for msd in msd_list:        uid = msd[0]        nickname = msd[1]        etl_time = time.strftime('%Y-%m-%d %X', time.localtime())        # print "{0}/t{1}".format(uid, nickname)        os.system("""/usr/bin/mysql -htargetIp -P6603 -utargetUser -ptargetPass --default-character-set=utf8 -e "set names utf8; /        use funnyai_data; /        insert into bi_find_nickname_from_uid(uid, nickname, etl_time) /        select '%s','%s','%s'; /         " """ % (uid, nickname, etl_time))list_uid = [200063,200067,200111,1209195139,1209195288,1209393002,1209450272]# 清空目标表数据os.system("""/usr/bin/mysql -htargetIp -P6603 -utargetUser -ptargetPass --default-character-set=utf8 -e "set names utf8; /       use funnyai_data; /       truncate table bi_find_nickname_from_uid; /        " """)for uid in list_uid:    print uid    find_nickname_from_uid(uid)3、说明在处理的过程中，碰到数据传输过程中的乱码问题；通过数据导出与导入过程中加入“--default-character-set=utf8”和“set names utf8;”解决。