需求：目前有两个pg数据库:dev 和lev ,这两个数据库内存在同样的一张表 be_school_info，dev的数据是系统创建了，lev的数据是从dev同步过去的。比较school_id的最大值，然后插入缺失数据。
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=self.port)
self.cursor = self.conn.cursor()
except Exception as 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:
self.cursor.execute(sql)
rows = self.cursor.fetchall()
return rows
except Exception as 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 insert_sql(self, sql, is_exist=True):
try:
self.cursor.execute(sql)
self.conn.commit()
return 0
except Exception as 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_school_id(self):
standout_print("get school_id [%s]." % self.dbname)
sql = """ select max(school_id) from be_school_info """
rows = self.execute_sql(sql)
max_school= []
for row in rows:
max_school.append(row[0])
return max_school
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 = 'dev'
db2 = 'lea'
password=xxx'
port='5432'
host = "xxxx"
username ="postgres"
db_model1 = PdbModel(host, db1, username, password, port)
db_model2 = PdbModel(host, db2,username, password, port)
max_school1 = db_model1.get_school_id()
max_school2 = db_model2.get_school_id()
import pprint
import operator
is_equal = (operator.eq(max_school1, max_school2))
if is_equal == 1:
print ("the two tables are same.")
else:
sql = 'select school_id,school_name from be_school_info where school_id >{0}'.format(max_school2[0])
inser_rows=db_model1.execute_sql(sql)
for inser_row in inser_rows:
sql2 = "INSERT INTO be_school_info(school_id,school_name) VALUES({0},'{1}')".format(inser_row[0],inser_row[1])
db_model2.insert_sql(sql2)
