python实现数据库跨服务器迁移

# -*- coding: utf-8 -*-import MySQLdbimport timeimport warningswarnings.filterwarnings("ignore")class ConnectMysql(object):  def __init__(self):#     这里设置分页查询, 每页查询多少数据    self.page_size = 5000  def getTable(self):    conn = MySQLdb.connect(      host="***.***.**.**",      user="****",      passwd="*************",      db='****',      charset='utf8'    )    conn_local = MySQLdb.connect(      host="********************************",      user="**********",      passwd="********",      db='*******',      charset='utf8'    )    cur = conn.cursor()    cur_local = conn_local.cursor()    cur.execute('show tables')    tables = cur.fetchall()    for table in tables:      print str(table[0]).lower()      # 需要迁移的数据库查询表的列数      cur.execute("SELECT COUNT(*) FROM information_schema.COLUMNS WHERE table_schema='china' AND table_name='" + table[0] + "'")      table_col_count = cur.fetchone()      # print table_col_count[0]      # 需要迁移的数据库查询表的结构      cur.execute('show create table ' + table[0])      result = cur.fetchall()      create_sql = result[0][1]      # 查询需要迁移的数据库表的数据条数      cur.execute('select count(*) from ' + table[0])      total = cur.fetchone()      page = total[0] / self.page_size      page1 = total[0] % self.page_size      if page1 != 0:        page = page + 1      # 阿里云数据库创建表      cur_local.execute("SELECT table_name FROM information_schema.`TABLES` WHERE table_schema='user' AND table_name='" + str(table[0]).lower() + "'")      table_name = cur_local.fetchone()      if table_name is None:        cur_local.execute(create_sql)      for p in range(0, page):        while True:          try:            print '开始', table[0], '的第', p + 1, '页查询'            if p == 0:              limit_param = ' limit ' + str(p * self.page_size) + ',' + str(self.page_size)            else:              limit_param = ' limit ' + str(p * self.page_size + 1) + ',' + str(self.page_size)            cur.execute('select * from ' + table[0] + limit_param)            inserts = cur.fetchall()            print '查询成功'            param = ''            for i in range(0, table_col_count[0]):              param = param + '%s,'            print '开始插入'            cur_local.executemany('replace into ' + table[0] + ' values (' + param[0:-1] + ')', inserts)            print table[0], '的第', p + 1, '页, 插入完成, 还有', page - p - 1, '页, 任重而道远'            conn_local.commit()            break          except Exception as e:            print e            time.sleep(60)            cur = conn.cursor()            cur_local = conn_local.cursor()        print table[0], ' 插入完成'        print '/n /n ======================================================================== /n/n'    cur_local.close()    conn_local.close()    cur.close()    conn.close()if __name__ == '__main__':  conn_mysql = ConnectMysql()  conn_mysql.getTable()