Python sql server和postgresql的表结构转换

概述Python sql server和postgresql的表结构转换

下面是内存溢出 jb51.cc 通过网络收集整理的代码片段。

内存溢出小编现在分享给大家，也给大家做个参考。

#Coding=utf-8#import _mssqlimport psycopg2,pymssqlimport typestableSpace='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,count):        columns=[]        for i in range(count):            columns.append("%s")        strs=",".join(columns)        sql="INSERT INTO %s%s VALUES(%s)"%(tableSpace,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)",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,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))        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........"

以上是内存溢出(jb51.cc)为你收集整理的全部代码内容，希望文章能够帮你解决所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

总结

以上是内存溢出为你收集整理的Python sql server和postgresql的表结构转换全部内容，希望文章能够帮你解决Python sql server和postgresql的表结构转换所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。