#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........"

</pre>