python 两个数据库postgresql对比

这篇文章主要介绍了python两个数据库postgresql对比,文中通过示例代码介绍的非常详细，对大

这篇文章主要介绍了python 两个数据库postgresql对比,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下

比较两个postgresql数据库，原理 比较数据库中各表的大小

1. 数据库查询语句

2. python字典比较

import psycopg2
import sys


class PdbModel:
 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()

 def init_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()

 except Exception, e:
 error_out_print("Error: connection to db %s : %s failed. check need" % (self.host, self.dbname))
 print e
 sys.exit(-1)

 def execute_sql(self, sql, is_exist=True):
 """
 execute sql and return rows
 :param sql:
 :return:
 results of execute sql
 """
 try:
 standout_print('command sql : %s' % sql)
 self.cursor.execute(sql)
 rows = self.cursor.fetchall()
 return rows

 except Exception, e:
 self.conn.rollback()
 error_out_print("Failed: failed execute sql [%s]" % sql)
 error_out_print(e)
 if is_exist:
 self.close()
 sys.exit(-1)
 else:
 return None

 def get_tables_size(self):
 """
 select table_schema || '.' || table_name as table_full_name , pg_size_pretty(pg_total_relation_size('"'||table_schema||'"."'||table_name||'"')) as size
from information_schema.tables
order by pg_total_relation_size('"'||table_schema||'"."'||table_name||'"') DESC

 :return:
 """
 standout_print("get the size of tables in db [%s]." % self.dbname)
 sql = """ 
 select table_schema || '.' || table_name as table_full_name , pg_size_pretty(pg_total_relation_size('"'||table_schema||'"."'||table_name||'"')) as size
from information_schema.tables
order by pg_total_relation_size('"'||table_schema||'"."'||table_name||'"') DESC
 """
 rows = self.execute_sql(sql)
 table_size_dic = {}
 for row in rows:
 table_name = row[0]
 table_size = row[1]
 table_size_dic[table_name] = table_size
 return table_size_dic


def standout_print(info):
 sys.stdout.write("Info: %s " % info)
 sys.stdout.flush()


def error_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()
 import pprint

 # pprint.pprint(table_size_dic1)
 # pprint.pprint(table_size_dic2)
 print cmp(table_size_dic1, table_size_dic2)
 is_equal = cmp(table_size_dic1, table_size_dic2)
 different_table_size_dic = {}
 if is_equal == 0:
 print "these tables in two database are same."
 else:
 keys1 = table_size_dic1.keys()
 keys2 = table_size_dic2.keys()

 for key in keys1:
 value1 = table_size_dic1.get(key)
 value2 = table_size_dic2.get(key)
 if cmp(value1, value2) != 0:
 different_table_size_dic[key] = (value1,value2)

 pprint.pprint(different_table_size_dic)

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本之家。