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

Python  /  管理员 发布于 7年前   165

 基于Python2.7的版本环境，Python实现的数据库跨服务器(跨库)迁移, 每以5000条一查询一提交，代码中可以自行更改每次查询提交数目.

# -*- 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 etime.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()

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。