#coding=utf-8
#import _mssql
import psycopg2,pymssql
import types
TableSpace='ABS.'
class SyncDataBase():
def __init__(self):
self.pgconn=psycopg2.connect("dbname=absob host=192.168.1.32 user=postgres password=12345")
self.msconn=pymssql.connect(host="192.168.1.20",user="sa",password="sa",database="absOB090615")
def commit(self):
self.pgconn.commit()
def close(self):
self.pgconn.close()
self.msconn.close()
def rollback(self):
self.pgconn.rollback()
def exesyncdb(self):
mscursor=self.msconn.cursor()
sql=("SELECT COUNT(COLUMNNAME) AS CT,TABLENAME FROM "/
"(SELECT A.NAME AS COLUMNNAME,B.NAME AS TABLENAME FROM SYSCOLUMNS A RIGHT JOIN "/
" SYSOBJECTS B ON A.ID=B.ID WHERE B.TYPE='U' AND B.NAME NOT IN ('dtproperties','0626')) A "/
" GROUP BY TABLENAME ")
#print sql
mscursor.execute(sql)
table=mscursor.fetchall()
if(table is None or len(table)<=0):
return
else:
for row in table:
#print row[1]
self.executeTable(row[1],row[0])
print "%s is execute success"%row[1]
def executeTable(self,tablename,count):
#print tablename
sql1="SELECT * FROM %s"%tablename
mscursor=self.msconn.cursor()
mscursor.execute(sql1)
table=mscursor.fetchall()
if(table is None or len(table)<=0):
mscursor.close()
return
lst_result=self.initColumn(table)
#print "column"
mscursor.close()
sql2=self.initPgSql(tablename,count)
pgcursor=self.pgconn.cursor()
pgcursor.executemany(sql2,lst_result)
pgcursor.close()
def initPgSql(self,tablename,count):
columns=[]
for i in range(count):
columns.append("%s")
strs=",".join(columns)
sql="INSERT INTO %s%s VALUES(%s)"%(TableSpace,tablename,strs)
return sql
#-----------------------------
#字段编码和相关格式初始化
#-----------------------------
def initColumn(self,table):
if(table is None or len(table)<=0):
return None
lst_result=[]
for row in table:
i=0
lines=[]
for column in row:
if(column is not None and types.StringType==type(column)):
#lines.append(unicode(column))
try:
lines.append((column.decode('cp936')).encode('utf-8'))
except:
lines.append(column)
else:
lines.append(column)
i+=1
lst_result.append(lines)
return lst_result
#-----------------------
#测试数据表导入结果测试
#----------------------
def exeBulletin(self):
mscursor=self.msconn.cursor()
sql=("SELECT * FROM BBULLETIN")
mscursor.execute(sql)
table=mscursor.fetchall()
if(table is None or len(table)<=0):
mscursor.close()
return
lst_result=initColumn(table)
mscursor.close()
pgcursor=self.pgconn.cursor()
ret=pgcursor.executemany("INSERT INTO "+TableSpace+"BBULLETIN VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",lst_result)
pgcursor.close()
def getAllTable(self):
mscursor=self.msconn.cursor()
sql=("SELECT NAME FROM sysobjects WHERE TYPE='U' AND NAME NOT IN ('dtproperties','0626')")
mscursor.execute(sql)
table=mscursor.fetchall()
if(table is None or len(table)<=0):
mscursor.close()
return
pgcursor=self.pgconn.cursor()
for row in table:
sqlext=self.createTable(row[0])
print sqlext
if(sqlext is not None):
pgcursor.execute(sqlext)
mscursor.close()
pgcursor.close()
#----------------------
#根据SQL SERVER数据库基本结构创建PostgreSQL数据库表结构
#----------------------
def createTable(self,tablename):
mscursor=self.msconn.cursor()
# sql=("SELECT A.NAME AS COLUMNNAME,C.NAME,A.LENGTH,B.NAME AS TABLENAME "/
# " FROM SYSCOLUMNS A RIGHT JOIN SYSOBJECTS B ON A.ID=B.ID "/
# " LEFT JOIN SYSTYPES C ON C.XTYPE=A.XTYPE "/
# " WHERE B.TYPE='U' AND B.NAME=%s AND B.NAME NOT IN ('dtproperties','BUPLOADCUSTOMER','RFREIGHT')")
sql=("SELECT A.NAME AS COLUMNNAME,C.NAME,A.LENGTH,B.NAME AS TABLENAME,ISNULL(D.PKS,0) AS PKEY,E.CT "/
" FROM SYSCOLUMNS A RIGHT JOIN SYSOBJECTS B ON A.ID=B.ID "/
" LEFT JOIN SYSTYPES C ON C.XTYPE=A.XTYPE LEFT JOIN "/
" (SELECT A.NAME,1 AS PKS FROM SYSCOLUMNS A "/
" JOIN SYSINDEXKEYS B ON A.ID=B.ID AND A.COLID=B.COLID AND A.ID=OBJECT_ID(%s)"/
" JOIN SYSINDEXES C ON A.ID=C.ID AND B.INDID=C.INDID "/
" JOIN SYSOBJECTS D ON C.NAME=D.NAME AND D.XTYPE='PK') D "/
" ON A.NAME =D.NAME "/
" LEFT JOIN (SELECT COUNT(A.COLUMNNAME) AS CT,%s AS TABLENAME FROM "/
" (SELECT A.NAME AS COLUMNNAME,D.NAME AS TABLENAME FROM SYSCOLUMNS A "/
" JOIN SYSINDEXKEYS B ON A.ID=B.ID AND A.COLID=B.COLID AND A.ID=OBJECT_ID(%s) "/
" JOIN SYSINDEXES C ON A.ID=C.ID AND B.INDID=C.INDID "/
" JOIN SYSOBJECTS D ON C.NAME=D.NAME AND D.XTYPE='PK') A GROUP BY A.TABLENAME) E "/
" ON B.NAME=E.TABLENAME "/
" WHERE B.TYPE='U' AND B.NAME=%s AND B.NAME NOT IN ('dtproperties') ")
mscursor.execute(sql,(tablename,tablename,tablename,tablename))
table=mscursor.fetchall()
if(table is None or len(table)<=0):
mscursor.close()
return
csql="CREATE TABLE "+TableSpace+"%s ("%tablename
lst=[]
for row in table:
if(row[1]=="int"):
if(row[4]==1 and len(lst)<=0 and row[5]==1):
lst.append(row[0]+" serial PRIMARY KEY NOT NULL")
elif(row[4]==1 and len(lst)>0 and row[5]==1):
lst.append(","+row[0]+" serial PRIMARY KEY NOT NULL")
elif(row[4]==0 and len(lst)<=0 and row[5]!=0):
lst.append(row[0]+" INT DEFAULT 0")
elif(len(lst)>0):
lst.append(","+row[0]+" INT DEFAULT 0")
else:
lst.append(row[0]+" INT DEFAULT 0")
if(row[1]=="varchar"):
if(len(lst)<=0):
lst.append(row[0]+" varchar("+str(row[2])+")")
else:
lst.append(","+row[0]+" varchar("+str(row[2])+")")
if(row[1]=="text"):
if(len(lst)<=0):
lst.append(row[0]+" text ")
else:
lst.append(","+row[0]+" text ")
if(row[1]=="datetime"):
if(len(lst)<=0):
lst.append(row[0]+" timestamp without time zone NULL ")
else:
lst.append(","+row[0]+" timestamp without time zone NULL ")
if(row[1]=="numeric" or row[1]=="money" or row[1]=="float" or row[1]=="decimal"):
if(len(lst)<=0):
lst.append(row[0]+" decimal(18,2) DEFAULT 0.00 ")
else:
lst.append(","+row[0]+" decimal(18,2) DEFAULT 0.00 ")
if(row[1]=="bit"):
if(len(lst)<=0):
lst.append(row[0]+" boolean DEFAULT FALSE ")
else:
lst.append(","+row[0]+" boolean DEFAULT FALSE ")
if(row[1]=="tinyint"):
if(len(lst)<=0):
lst.append(row[0]+" smallint DEFAULT 0 ")
else:
lst.append(","+row[0]+" smallint DEFAULT 0 ")
if(row[1]=="char"):
if(len(lst)<=0):
lst.append(row[0]+" char("+str(row[2])+")")
else:
lst.append(","+row[0]+" char("+str(row[2])+")")
lst.append(");")
mscursor.close()
return csql+" ".join(lst)
if __name__=="__main__":
sdb=SyncDataBase()
try:
#print sdb.initPgSql("aaa",10)
#sdb.getAllTable()
sdb.exesyncdb()
except Exception,e:
print e
sdb.rollback()
else:
sdb.commit()
sdb.close()
print "ok........"
原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/tech/pnotes/8501.html