Python DBUtils データ接続プールでms sqlを操作する
サンプルコード：
#coding=utf-8
import os,psycopg2
import _mssql
import pymssql
from DBUtils.PooledDB import PooledDB
hosts="192.168.1.32″
users="test"
passwords="startnews24″
dbnames="startnews24_testdb"
ms_hosts="192.168.1.20″
ms_users="sa"
ms_passwords="sa_startnews24″
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 “インポート成功…"
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()