python3数据库同步_Python同步Mysql不同数据库的表

Python同步Mysql不同数据库的表

#!/opt/python3/bin/python3

#脚本用途：同步生产环境的登陆日志到本地

#coding=utf-8

import pymysql

import time

import os

import sys

print("Start At："+time.strftime("%Y-%m-%d %H:%M:%S")) #\n换行

#连接数据库

try:

product_coon = pymysql.connect(host='mysql地址',user='用户名',passwd='密码',db='数据库名称',charset='utf8')

local_coon = pymysql.connect(host='myql地址',user='用户名',passwd='密码',db='数据库名称',charset='utf8')

product_cur = product_coon.cursor()

local_cur = local_coon.cursor()

except:

print('Connect Database Error')

exit()

#查询最新的数据，并同步,根据最大的ID 来判断是否更新

local_log_mid = 0

prodcut_log_mid = 0

try:

#查询数据库

getsql=" select records_id from login_records order by create_time desc limit 1 "

local_cur.execute( getsql )

product_cur.execute( getsql )

result_local_mid = local_cur.fetchone()

result_product_mid = product_cur.fetchone()

prodcut_log_mid = int(result_product_mid[0])

local_log_mid = int(result_local_mid[0])

try:

toAddSql = " select * from table where records_id>%s AND records_id

product_cur.execute( toAddSql ,[local_log_mid , prodcut_log_mid ])

for row in product_cur.fetchall():

print('Insert Id:',row[0])

try:

#保存数据

sql = "INSERT INTO login_records( ...... ) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)"

local_cur.execute(sql,(row[0],row[1],row[2], row[3] , row[4], row[5], row[6], row[7]))

#执行sql语句

local_coon.commit() #提交

except:

print("保存数据失败")

continue

except:

print("Get Sql from Producation database Fail")

except:

print("Get Newsest record ID Fail")

product_cur.close()

local_cur.close()

product_coon.close()

local_coon.close()

print("End At："+time.strftime("%Y-%m-%d %H:%M:%S \n")) #\n换行