主从数据库单向同步脚本

如题，老样子直接上代码：

#!/usr/bin/env python
# -*- coding:utf-8 -*- 
# @Modify   : 2018/8/10 11:38
# @Author   : Joy
# @Ide      : PyCharm Community Edition


import pymysql
import datetime

'''
单向同步数据库方法，返回修改数据的查询字典，key:value格式：表名:修改数据。
要求：所有表必须含有主键，表名符合SQL语法规范
'''


def syncdb():
    # 记录表修改记录查询字典
    operate_dict = {}

    # 源数据库
    with pymysql.connect(host="100.100.100.100", port=3306, user="root", passwd="qisini", db="fmdata",
                         charset="utf8") as conn1:
        try:
            # 定义源数据库表集合
            table_set = set()
            conn1.execute(
                "select table_name "
                "from information_schema.tables "
                "where table_schema='fmdata' and table_type='base table'")
            for table in conn1.fetchall():
                table_set.add(table[0])

            # 目标数据库
            with pymysql.connect(host="127.0.0.1", port=7788, user="root", passwd="hahaha", db="fmdata",
                                 charset="utf8") as conn2:
                try:
                    # 定义目标数据库表集合
                    table_set2 = set()
                    conn2.execute(
                        "select table_name "
                        "from information_schema.tables "
                        "where table_schema='fmdata' and table_type='base table'")
                    for table in conn2.fetchall():
                        table_set2.add(table[0])

# -----------------------------------------准备阶段结束-----------------------------------------

                    # 删除不存在的表
                    for t in table_set2 - table_set:
                        if t:
                            print "删除表" + t
                            conn2.execute("drop table {}".format(t))

                    # 首先创建新增的表
                    for t in table_set - table_set2:
                        print "新增表 " + t
                        if t:
                            if t == "schema":  # 此表名存在语法冲突
                                continue
                            # 查询主数据库建表语句
                            conn1.execute("show create table {}".format(t))
                            create_sqls = conn1.fetchall()
                            for create_sql in create_sqls:
                                # 在从数据库创建新表
                                conn2.execute(create_sql[1])

                    # 首先获取从数据库最新表名
                    conn2.execute(
                        "select table_name "
                        "from information_schema.tables "
                        "where table_schema='fmdata' and table_type='base table'")
                    for table in conn2.fetchall():
                        table_set2.add(table[0])

                    # 表名同时存在于两个数据库，则更新数据
                    for t in table_set & table_set2:
                        print "正在同步 " + t + " 表数据..."
                        if t == "schema" :  # 此表名存在语法冲突
                            continue
                        # 首先从主数据库查询数据
                        conn1.execute("select * from {}".format(t))
                        datas = conn1.fetchall()

                        operate_log = ""  # 记录表操作记录
                        for data in datas:
                            # 插入数据
                            status = conn2.execute(r"insert ignore into {} values %s".format(t), (data,))
                            if status > 0:  # 有插入
                                operate = "插入:{}\t".format(data)
                                operate_log += operate
                            # 更新数据
                            status = conn2.execute(r"replace into {} values %s".format(t), (data,))
                            if status == 2:  # 有更新
                                operate = "更新:{}\t".format(data)
                                operate_log += operate
                        operate_dict[t] = operate_log

                    print "\n同步结束!"

                except Exception as e:
                    raise e

        except Exception as e:
            raise e

    return operate_dict


if __name__ == "__main__":
    d = syncdb()
    # 打印新增数据信息
    print d

PS：因为replace into语句的更新操作实际上是先将重复数据删除，再更新，所以插入新数据和插入相同数据的返回值都是1，修改数据的返回值是2。
为了根据返回值统计新增和修改的数据，将这部分代码分成了两部分，其实如果不需要统计数据，用replace into 就足够了。