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)
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持毛票票。