Python sql server和postgresql的表结构转换

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

标签： 数据库

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点！
本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。