怎样利用python实现mysql数据库向sqlserver的同步

这期内容当中小编将会给大家带来有关怎样利用python实现mysql数据库向sqlserver的同步，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

话不多说，直接上代码。

#!/usr/bin/python# -*- coding:utf8 -*-# author: chenzhixin"""        一、安装环境：                python3                pip install pymysql                pip install pymssql        二、实现功能：                将mysql的oa_2016.formmain_5027(手机打卡记录)数据，增量同步到sqlserver数据库的kaoqin.CHECKINOUT中        三、运行方法：                a)定时任务                        [root@oadb1 shell]# crontab -l                        * * * * *  python -W ignore /usr/local/shell/sync_mobile_kaoqin.py >> /var/log/sync_mobile_kaoqin.log 2>&1                b) 日志位置                        tail  -f /var/log/sync_mobile_kaoqin.log        四、测试sql：                mysql执行                                select * from oa_2016.formmain_5027                sqlserver上执行                                select * from CHECKINOUT where sn='手机端打卡'"""from contextlib import contextmanagerimport pymysql as mysqldbimport pymssql as mssqldbimport time@contextmanagerdef get_mysql_conn(**kwargs):        """        建立MySQL数据库连接        :param kwargs:        :return:        """        conn = mysqldb.connect(host=kwargs.get('host', 'localhost'),                                                user=kwargs.get('user'),                                                password=kwargs.get('password'),                                                port=kwargs.get('port', 3306),                                                database=kwargs.get('database')                                                )        try:                yield conn        finally:                if conn:                        conn.close()@contextmanagerdef get_mssql_conn(**kwargs):        """        建立sqlserver数据库连接        :param kwargs:        :return:        """        conn = mssqldb.connect(server=kwargs.get('host'),                                                user=kwargs.get('user'),                                                password=kwargs.get('password'),                                                database=kwargs.get('database')                                                )        try:                yield conn        finally:                if conn:                        conn.closedef execute_mysql_select_sql(conn, sql):        """        执行mysql的select类型语句        :param conn:        :param sql:        :return:        """        with conn as cur:                cur.execute(sql)                rows = cur.fetchall()        return rowsdef execute_mysql_sql(conn, sql):        """        执行mysql的dml和ddl语句，不包括select语句        :param conn:        :param sql:        :return:        """        with conn as cur:                cur.execute(sql)                def execute_mssql_sql(conn, sql):        """        执行sqlserver的dml和ddl语句，不包含select语句        :param conn:        :param sql:        :return:        """        with conn.cursor() as cur:                cur.execute(sql)                conn.commit()def get_mysql_kaoqin_data(conn):        """        获取mysql的考勤数据        :param conn:        :return:        """        sql = "select * from formmain_5027 where field0008 is null or field0008=''"        mysql_kaoqin_data_rows = execute_mysql_select_sql(conn, sql)        return mysql_kaoqin_data_rowsdef mysql_sync_to_sqlserver(mysql_conn, mssql_conn, data):        """        把mysql的考勤数据同步到sqlserver数据库里面        :param mysql_conn:        :param mssql_conn:        :param data:        :return:        """        for index, row in enumerate(data, 1):                ID=row[0]                state=row[1]                start_member_id=row[2]                start_date=row[3]                approve_member_id=row[4]                approve_date=row[5]                finishedflag=row[6]                ratifyflag=row[7]                ratify_member_id=row[8]                ratify_date=row[9]                sort=row[10]                modify_member_id=row[11]                modify_date=row[12]                field0001=row[13]                field0002=row[14]                field0003=row[15]                field0004=row[16]                field0005=row[17]                field0006=row[18]                field0007=row[19]                field0008=row[20]                field0009=row[21]        #向sqlserver插入数据                insert_data = """                        INSERT INTO [kaoqin].[dbo].[CHECKINOUT]                           ([USERID]                           ,[CHECKTIME]                           ,[CHECKTYPE]                           ,[VERIFYCODE]                           ,[SENSORID]                           ,[Memoinfo]                           ,[WorkCode]                           ,[sn]                           ,[UserExtFmt]                           ,[Synced])                 VALUES                        ((select userid from USERINFO where BADGENUMBER='{userid}'),                           '{CHECKTIME}', 'I',    1,      1,   NULL,   0,      '手机端打卡',   0,      null                        )""".format(userid=field0002, CHECKTIME=start_date)                execute_mssql_sql(mssql_conn, insert_data)                print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))                print('###############第{}条手机打卡记录###############\n'.format(index), insert_data)                marked_sql = "update formmain_5027 set field0008='synced' where id={}".format(ID)                execute_mysql_sql(mysql_conn, marked_sql)                def main():        mysql_conn_args = dict(user='root', host='127.0.0.1',  password='*******', database='oa_2016')        mssql_conn_args = dict(host='172.x.x.x', user='sa', password='********', database='kaoqin')        with get_mysql_conn(**mysql_conn_args) as mysql_conn:                mysql_data = get_mysql_kaoqin_data(mysql_conn)                        with get_mssql_conn(**mssql_conn_args) as mssql_conn:                        mysql_sync_to_sqlserver(mysql_conn, mssql_conn, mysql_data)if __name__ == '__main__':        main()

定时任务：

[root@oadb1 shell]# crontab -l#定时同步手机考勤给sqlserver* * * * *  python -W ignore /usr/local/shell/sync_mobile_kaoqin.py >> /var/log/sync_mobile_kaoqin.log 2>&1

日志：

[root@oadb1 shell]# tail -100f /var/log/sync_mobile_kaoqin.log 2019-10-20 09:04:01###############第1条手机打卡记录###############                         INSERT INTO [kaoqin].[dbo].[CHECKINOUT]                           ([USERID]                           ,[CHECKTIME]                           ,[CHECKTYPE]                           ,[VERIFYCODE]                           ,[SENSORID]                           ,[Memoinfo]                           ,[WorkCode]                           ,[sn]                           ,[UserExtFmt]                           ,[Synced])                 VALUES                        ((select userid from USERINFO where BADGENUMBER='1234'),                           '2019-10-19 14:50:25', 'I',    1,      1,   NULL,   0,      '手机端打卡',   0,      null                        )

上述就是小编为大家分享的怎样利用python实现mysql数据库向sqlserver的同步了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注行业资讯频道。