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)

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