本文介绍了一个使用Python进行MySQL跨库数据迁移的脚本。通过`DataMigrate`类，连接源库和目标库，执行SQL查询并批量插入数据，实现了数据的迁移。在`migrate_date`方法中，利用游标执行SQL语句，将源库中的数据分批写入到目标库中。
importpymysqlclassDataMigrate:def __init__(self, env):
self.num= 50self.ENV=env#From
self.db_from = self.connect_jingyi(1)
self.cursor_from=self.db_from.cursor()
self.from_sql= ‘select first_project, status from product‘
#To
self.db_to =self.connect_jingyi()
self.cursor_to=self.db_to.cursor()
self.to_sql= "insert into product_copy(first_project, status) value(%s,%s)"
def connect_jingyi(self, is_jingyi=0):ifis_jingyi:if self.ENV == ‘test‘:return pymysql.connect(host=‘****‘,
port=3306,
user=‘***‘,
passwd=‘***‘,
db=‘from_db‘,
charset=‘utf8‘)else:return pymysql.connect(host=‘****‘,
port=3306,
user=‘***‘,
passwd=‘***‘,
db=‘from_db‘,
charset=‘utf8‘)else:if self.ENV == ‘test‘:return pymysql.connect(host=‘****‘,
port=3306,
user=‘***‘,
passwd=‘***‘,
db=‘to_db‘,
charset=‘utf8‘)elif self.ENV == ‘pre‘:return pymysql.connect(host=‘*****‘,
port=3306,
user=‘***‘,
passwd=‘***‘,
db=‘to_db‘,
charset=‘utf8‘)defmigrate_date(self):
len_from=self.cursor_from.execute(self.from_sql)for i in range(int(len_from /self.num)):print(i)
data1=self.cursor_from.fetchmany(self.num)
self.cursor_to.executemany(self.to_sql, data1)
data2=self.cursor_from.fetchall()
self.cursor_to.executemany(self.to_sql, data2)
self.db_to.commit()
self.db_from.close()
self.db_to.close()if __name__ == ‘__main__‘:
data_migrate= DataMigrate(‘self-test‘)
data_migrate.migrate_date()
扫一扫
524
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
