- #coding=utf-8
- import os,psycopg2
- import _mssql
- import pymssql
- from DBUtils.PooledDB import PooledDB
- hosts="192.168.1.32"
- users="test"
- passwords="12345"
- dbnames="testdb"
- ms_hosts="192.168.1.20"
- ms_users="sa"
- ms_passwords="sa"
- ms_databases="ABS0323"
- def initDatabase(filename,conn):
- fs=open(filename,"r")
- sql=fs.read()
- cursor=conn.cursor()
- cursor.execute(sql)
- conn.commit()
- cursor.close()
- conn.close()
- fs.close()
- print "ok"
- def ExportFromMs():
- conn=pymssql.connect(host=ms_hosts,user=ms_users,password=ms_passwords,database=ms_databases)
- cursor=conn.cursor()
- lst_result=[]
- try:
- cursor.execute("SELECT PSP_ID,PSP_NAME,PSP_DTS_ID,PSP_GND_ID,PSP_ADDRESS,PSP_ZIP,PSP_CTY_ID,PSP_MOBILE,PSP_VIPNUM,"\\
- " PSP_REGISTRATION_DT FROM BPROSPECT")
- table=cursor.fetchall()
- if(table is not None and len(table)>0):
- for row in table:
- line=[row[0],row[1].decode("cp936"),row[2],row[3],row[4].decode("cp936"),
- row[5].decode("cp936"),row[6],row[7],row[8],row[9]]
- lst_result.append(line)
- except Exception,e:
- print e
- cursor.close()
- conn.close()
- return lst_result
- def importToPostgresql(lstprospect,conn):
- cursor=conn.cursor()
- sql=("INSERT INTO BPROSPECT(PSP_ID,PSP_NAME,PSP_DTS_ID,PSP_GND_ID,PSP_ADDRESS,PSP_ZIP,PSP_CTY_ID,PSP_MOBILE,PSP_VIPNUM,"\\
- " PSP_REGISTRATION_DT,PSP_CREATIONUID,PSP_CREATION_DT,PSP_UPDATEUID,PSP_UPDATE_DT) VALUES(" \\
- "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,'import',now(),'import',now())")
- try:
- #for row in lstprospect:
- #print dir(cursor)
- cursor.executemany(sql,lstprospect)
- except Exception,e:
- print e
- conn.rollback()
- else:
- conn.commit()
- print "import success..."
- cursor.close()
- conn.close()
- if __name__=="__main__":
- pool=PooledDB(psycopg2,2,port=5432,database=dbnames,host=hosts,user=users,password=passwords)
- initDatabase("ob.sql",pool.connection())
- #lstprospect=ExportFromMs()
- #importToPostgresql(lstprospect,pool.connection())
- pool.close()
- #该片段来自于http://www.codesnippet.cn/detail/20112012494.html
来源: http://www.codesnippet.cn/detail/20112012494.html