python与sql server处理表_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........"