python脚本从sql server查询数据同步到oracle

客户要求写个python同步脚本，搞了2天才搞定，记录下写python脚本遇到的各种坑(ps:我这边python2.7.5版本)

第一个遇到坑是往oracle插入数据时候，如果数据有中文就会报错，度娘了半天都是说要设置编码如下:

import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

这行两行代码不能写在第一行，如果写在第一行还是会报错

第二个坑就是插入语法有中文的话%s需要用单引号括起来

cx_curosr.execute("insert into %s values(%s,'%s',1)" % (cx_table,1,'中文'))

完整脚本

# -*- coding: UTF-8 -*-
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

import pymssql
import cx_Oracle

ms_conn=None
cx_conn=None

ms_user="test"
ms_password="123456"
ms_databasename="testdatabase"
ms_server="127.0.0.1"
ms_table="target"

cx_user="test"
cx_password="123456"
cx_url="127.0.0.1:1521/orcl"
cx_table="source"
try:
    print "----------开始连接源库----------"
    ms_conn=pymssql.connect(ms_server,ms_user,ms_password,ms_databasename)
    ms_cursor=ms_conn.cursor()
    ms_cursor.execute("select COUNT(*) from %s"%ms_table)
    count =ms_cursor.fetchone()
    if count[0]>1:
        try:
            cx_conn=cx_Oracle.connect(cx_user,cx_password,cx_url)
            cx_curosr=cx_conn.cursor()
            cx_curosr.execute("truncate table %s"%cx_table)
        except:
            print "目标库库连接失败"

        ms_cursor.execute("select SYMBOL,SNAME from %s"%ms_table)
        result=ms_cursor.fetchall()
        for r in  result:
            cx_curosr.execute("insert into %s values(%s,'%s',1)" % (cx_table, r[0], r[1]))
        print "同步完成"
        cx_conn.commit()
    else:
        print "----------原表总数为空----------"
except:
    print "源库连接失败"
finally:
    if ms_conn:
        ms_conn.close()
    if cx_conn:
        cx_conn