这期内容当中小编将会给大家带来有关怎样利用python实现mysql数据库向sqlserver的同步,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。 话不多说,
这期内容当中小编将会给大家带来有关怎样利用python实现mysql数据库向sqlserver的同步,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。
话不多说,直接上代码。
#!/usr/bin/Python# -*- coding:utf8 -*-# author: chenzhixin"""一、安装环境:python3pip install pyMysqlpip 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>&1b) 日志位置tail -f /var/log/sync_mobile_kaoqin.log四、测试sql:mysql执行select * from oa_2016.formmain_5027sqlserver上执行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 connfinally: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 connfinally: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的同步了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注编程网数据库频道。
--结束END--
本文标题: 怎样利用python实现mysql数据库向sqlserver的同步
本文链接: https://www.lsjlt.com/news/228541.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0