Python ms sql和postgresql互相转化表结构,pythonpostgresql,#coding=utf-
#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,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"):#该片段来自于http://byrx.net
评论关闭