python DBUtils数据连接池与ms sql配合用法_python dbutils mssql-优快云博客

本文链接：https://blog.youkuaiyun.com/xto/article/details/5403877

本文档展示了如何使用Python的DBUtils模块创建数据连接池，并结合MS SQL进行数据操作。通过初始化数据库、从MS SQL导出数据到列表，以及将列表数据导入到PostgreSQL数据库，详细阐述了数据迁移的流程。示例代码中涉及pymssql和psycopg2库的使用，以及异常处理机制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#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()