这期内容当中小编将会给大家带来有关怎样利用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 contextmanager
import pymysql as mysqldb
import pymssql as mssqldb
import time
@contextmanager
def 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()
@contextmanager
def 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.close
def execute_mysql_select_sql(conn, sql):
"""
执行mysql的select类型语句
:param conn:
:param sql:
:return:
"""
with conn as cur:
cur.execute(sql)
rows = cur.fetchall()
return rows
def 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_rows
def 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的同步了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注亿速云行业资讯频道。
亿速云「云数据库 MySQL」免部署即开即用,比自行安装部署数据库高出1倍以上的性能,双节点冗余防止单节点故障,数据自动定期备份随时恢复。点击查看>>
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。
原文链接:http://blog.itpub.net/28916011/viewspace-2660737/