【Python3】实现简单数据同步功能

工作中需要将现网服务器中的数据同步至备库中，用Python实现很简单，具体如下

import pymysql
'''
author:shikailiang
function:实现简单ETL的功能
'''
# 执行的SQL语句
sql='''
'''
# 定义备库的连接
beiku_conn = pymysql.connect(host="", user="", password="", database="", charset="utf8")
beiku_cursor = beiku_conn.cursor()

# 定义现网的连接
now_conn = pymysql.connect(host="", user="", password="", database="", charset="utf8")
now_cursor = now_conn.cursor()
now_cursor.execute(sql)
from_data=now_cursor.fetchall()
# 获取查询结果的长度
rowcount = len(from_data)
# 下面语句拼接SQL，实现一次插入1000条，j为行标记
j = 1
sql = ""
sql1 = "insert into tb_immediately_inventory_test(create_time,物料编码,物料名称,库存主单位,库存量) values"
for i in from_data:
    sql2 = (("(" + '"{}",' * 5)[:-1] + ")").format(i[0], i[1], i[2], i[3], i[4])
    sql2 = sql2.replace('"None"',"null")
    sql = sql + "," + sql2
    if divmod(j, 1000)[1] == 0 or j == rowcount:
        # 如果执行错误回滚当前事务
        try:
            beiku_cursor.execute(sql1 + sql[1:])
        except:
            beiku_conn.rollback()
            print(sql1 + sql[1:])
            continue
        sql = ""
    j = j + 1
    # 每一百条打印
    if divmod(j, 1000)[1] == 0:
        print("已经插入tb_immediately_inventory_test" + str(j) + "条")
print("插入" + str(j - 1) + "条")
# 关闭数据库连接
beiku_conn.commit()
beiku_cursor.close()
beiku_conn.close()
now_cursor.close()
now_conn.close()