python 两个数据库postgresql对比
这篇文章主要介绍了python两个数据库postgresql对比,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
比较两个postgresql数据库，原理比较数据库中各表的大小
1.数据库查询语句
2.python字典比较
importpsycopg2
importsys
classPdbModel:
def__init__(self,host,dbname,username='postgres',password='postgres',port='5432'):
self.host=host
self.dbname=dbname
self.username=username
self.password=password
self.port=port
self.conn=None
self.cursor=None
self.init_db()
definit_db(self):
try:
self.conn=psycopg2.connect(database=self.dbname,user=self.username,password=self.password,
host=self.host,
port="5432")
self.cursor=self.conn.cursor()
exceptException,e:
error_out_print("Error:connectiontodb%s:%sfailed.checkneed"%(self.host,self.dbname))
printe
sys.exit(-1)
defexecute_sql(self,sql,is_exist=True):
"""
executesqlandreturnrows
:paramsql:
:return:
resultsofexecutesql
"""
try:
standout_print('commandsql:%s'%sql)
self.cursor.execute(sql)
rows=self.cursor.fetchall()
returnrows
exceptException,e:
self.conn.rollback()
error_out_print("Failed:failedexecutesql[%s]"%sql)
error_out_print(e)
ifis_exist:
self.close()
sys.exit(-1)
else:
returnNone
defget_tables_size(self):
"""
selecttable_schema||'.'||table_nameastable_full_name,pg_size_pretty(pg_total_relation_size('"'||table_schema||'"."'||table_name||'"'))assize
frominformation_schema.tables
orderbypg_total_relation_size('"'||table_schema||'"."'||table_name||'"')DESC
:return:
"""
standout_print("getthesizeoftablesindb[%s]."%self.dbname)
sql="""
selecttable_schema||'.'||table_nameastable_full_name,pg_size_pretty(pg_total_relation_size('"'||table_schema||'"."'||table_name||'"'))assize
frominformation_schema.tables
orderbypg_total_relation_size('"'||table_schema||'"."'||table_name||'"')DESC
"""
rows=self.execute_sql(sql)
table_size_dic={}
forrowinrows:
table_name=row[0]
table_size=row[1]
table_size_dic[table_name]=table_size
returntable_size_dic
defstandout_print(info):
sys.stdout.write("Info:%s"%info)
sys.stdout.flush()
deferror_out_print(info):
sys.stderr.write("Error:%s"%info)
sys.stderr.flush()
if__name__=='__main__':
db1=''
db2=''
host="172.16.101.92"
db_model1=PdbModel(host,db1)
db_model2=PdbModel(host,db2)
table_size_dic1=db_model1.get_tables_size()
table_size_dic2=db_model2.get_tables_size()
importpprint
#pprint.pprint(table_size_dic1)
#pprint.pprint(table_size_dic2)
printcmp(table_size_dic1,table_size_dic2)
is_equal=cmp(table_size_dic1,table_size_dic2)
different_table_size_dic={}
ifis_equal==0:
print"thesetablesintwodatabasearesame."
else:
keys1=table_size_dic1.keys()
keys2=table_size_dic2.keys()
forkeyinkeys1:
value1=table_size_dic1.get(key)
value2=table_size_dic2.get(key)
ifcmp(value1,value2)!=0:
different_table_size_dic[key]=(value1,value2)
pprint.pprint(different_table_size_dic)
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。