背景:部署的外网的生产环境由于数据库配置导致部分数据没有插入成功,从内网测试环境查询相关数据插入到生产环境数据库。生产环境数据库只能通过SSH连接进行访问,所以先把需要查询更新的数据导出到文本里面,从内网数据库查询到后再进行插入操作。
#coding=utf-8
import MySQLdb
from sshtunnel import SSHTunnelForwarder
sql1='select videoType from t_video_type_relevance where videoId IN\
(select videoId from t_video where videoName=%s)'
SQL2='INSERT INTO t_video_type_relevance_copy(videoType,videoId) values(%s,%d)'
db1 = MySQLdb.connect("192.168.1.246","root","123456","dbname" )
cursor1 = db1.cursor()
cursor1.execute('SET NAMES UTF8') #设置连接编码格式,应对中文问题
data1=[]
data2=[]
with open("c://Users//huangning//Desktop//MISSING.txt",'r') as f:
lines = f.readlines()
for line in lines:
videoId = int(line.split('AB')[0].strip('"'))
videoName = line.split('AB')[1]
#print videoId,videoName
try:
cursor1.execute(sql1%videoName)
result= cursor1.fetchone()[0]
data = (result,videoId)
data1.append(data)
except Exception:
data2.append(videoId)
print 'insert not success : %d'%videoId
#print data1
#print data2
with SSHTunnelForwarder(('x.x.x.x', 22), #B机器的配置
ssh_password="pwd",
ssh_username="username",
remote_bind_address=('10.254.0.12', 3306)) as server: #A机器的配置
conn = MySQLdb.connect(host='127.0.0.1', #此处必须是是127.0.0.1
port=server.local_bind_port,
user='username',
passwd='pwd',
db='dbname')
cursor2 = conn.cursor()
cursor2.execute('SET NAMES UTF8')
for item in data1:
try:
cursor2.execute(SQL2%item)
conn.commit()
except:
conn.rollback()
© 著作权归作者所有