这篇文章给大家分享的是有关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)感谢各位的阅读！关于“python中两个数据库postgresql对比的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
