注意:
- 本文是基于Pyhon3.6的实现方式
- 以每100000一查询一提交进行,可根据实际情况修改
- 程序中,如传输某表时发现目标数据库中存在此表,认为该数据表是完整存在的,可优化
- 数据库连接时注意编码方式,本文为 charset='latin1',可根据实际情况修改为utf8或其他方式
- 如服务器中有多个数据库,通过修改cur_db和cur_local_db变量切换数据库
- 本人使用后愚见,本程序迁移速度相比Navicat中的数据传输功能较慢一点,但Navicat中有时存在漏传(虽情况出现次数极少)
程序代码如下:
import pymysql
import warnings
warnings.filterwarnings("ignore")
class ConnectMysql(object):
def __init__(self):
# 这里设置分页查询, 每页查询多少数据
self.page_size = 100000
def getTable(self):
#需迁移的数据库
cur_db='****'
#目标数据库
cur_local_db='****'
#需迁移服务器
conn = pymysql.connect(
host="***.***.***.***",
user="*****",
passwd="*****",
db=cur_db,
charset='latin1'
)
#目标服务器
conn_local = pymysql.connect(
host="***.***.***.***",
user="*****",
passwd="*****",
db=cur_local_db,
charset='latin1'
)
cur = conn.cursor()
cur_local = conn_local.cursor()
cur.execute('show tables')
tables = cur.fetchall()
for table in tables:
# 需要迁移的数据库查询表的列数
cur.execute(
"SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='"+cur_db+"' AND table_name='" + table[
0] + "'")
table_col_count = cur.fetchone()
# print("需要迁移数据列数",table_col_count)
# 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()
# print("迁移数据条数",total)
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='"+cur_local_db+"' AND table_name='" + str(
table[0]) + "'")
table_name = cur_local.fetchone()
if table_name is None:
cur_local.execute(create_sql)
page = int(page)
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(inserts)
# print('查询成功')
param = ''
for i in range(0, table_col_count[0]):
param = param + '%s,'
# print(param)
# print('开始插入')
cur_local.executemany('replace into ' + table[0] + ' values (' + param[0:-1] + ')', inserts)
conn_local.commit()
break
except Exception as e:
print(e)
# time.sleep(1)
cur = conn.cursor()
cur_local = conn_local.cursor()
print(table[0], ' 插入完成')
else:
print(str(table[0]),"已存在")
cur_local.close()
conn_local.close()
cur.close()
conn.close()
if __name__ == '__main__':
conn_mysql = ConnectMysql()
conn_mysql.getTable()
最后希望本文对大家有所帮助,也希望大家多多指教!