def assertTabledataBetweenDbByAttribute(
self,
referdb="mysql,root/[email protected]:50016/pdw",
targetdb="mysql,root/[email protected]:50016/pdw",
tables="",
attribute="period",
attr_value="201705"):
'''
根据table 的 att 字段 获取数据，比较在referdb 与targetdb 中的数据是否一致
:param referdb:
:param targetdb:
:param attribute:
:param attr_value:
:return:
'''
# 验证表在数据库中是否存在
str_tables_ref = self.assertTablesInDb(referdb, tables)
if len(str_tables_ref) == 0:
print("referanceDb:%s has no tables:%s" % (referdb, tables))
return
str_tables_target = self.assertTablesInDb(targetdb, str_tables_ref)
if len(str_tables_target) == 0:
print("targetDb:%s has no tables:%s" %
(targetdb, str_tables_target))
return
#验证
list_table = str_tables_target.split(",")
bool_rs = True
if len(list_table) > 0:
for tableName in list_table:
str_sql = "SELECT * FROM %s t WHERE t.%s = '%s'" % (
tableName, attribute, attr_value)
referdbService = DBApi(referdb)
rs1 = referdbService.listDataBySQL(str_sql)
targetdbService = DBApi(targetdb)
rs2 = targetdbService.listDataBySQL(str_sql)
if len(rs1) != len(rs2):
print(
"*ERROR* Table %-34s %s:%s miss marched! excepted:%d rows, actural:%d rows"
%
(tableName, attribute, attr_value, len(rs1), len(rs2)))
bool_rs = False
else:
sum_rows = len(rs1)
for row in range(sum_rows):
if rs1[row] not in rs2:
print(
"*ERROR* Table %s %s:%s miss marched!\nLine %d miss data:%s\n"
% (tableName, attribute, attr_value, row,
rs1[row]))
bool_rs = False
if bool_rs:
print("*INFO* table %s-34s %s:%s rows:%-5d pass" %
(tableName, attribute, attr_value, sum_rows))
else:
print("*ERROR* Empty tables!")
bool_rs = False
if not bool_rs:
self.fail()
def listAttributeNameOfTable(self, db, tablename):
attributes = []
str_sql = "DESC %s" % tablename
dbService = DBApi(db)
tableStructure = dbService.listDataBySQL(str_sql)
for sturcture in tableStructure:
attributes.append(sturcture[0])
#print(attributes)
return attributes
def listStructureByTablename(self, db, tablename):
'''
根据表名获取该表的表结构
:param db:数据库信息，dbtype,user/password@ip:port/databases
:param tablename:表名
:return:表结构列表
'''
str_sql = "DESC %s" % tablename
#str_sql = "select ordinal_position,colume_name,data_type from information_schema.'columns' where table_name ='%s' order by ordinal_position" % tablename
#str_sql = "SELECT ORDINAL_POSITION,COLUME_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.'COLUMNS' WHERE TABLE_NAME ='%S' ORDER BY ORDINAL_POSITION" % tablename
dbService = DBApi(db)
tableStructure = dbService.listDataBySQL(str_sql)
return tableStructure
def listTablesInDb(self, db):
'''
获取数据库用户表属性信息
:param db:dbtype,user/password@ip:port/databases
:return:表名列表
'''
tables = []
str_sql = "show tables"
dbService = DBApi(db)
list_table = dbService.listDataBySQL(str_sql)
for table in list_table:
tables.append(table[0])
return tables
def listStructureByTablenameAsDict(self, db, tablename):
'''
根据表名获取该表的表结构
:param db:数据库信息
:param tablename:表名
:return:表结构字典，{字段名：字段信息}
'''
tableStructureAsDict = {}
str_sql = "DESC %s" % tablename
dbService = DBApi(db)
tableStructure = dbService.listDataBySQL(str_sql)
for sturcture in tableStructure:
tableStructureAsDict[sturcture[0]] = sturcture
#print(tableStructureAsDict)
return tableStructureAsDict
def assertTabledataBetweenDb(self, referdb, targetdb, tables):
'''
比较两个数据库中的所有数据
:param referdb: dbtype,user/password@ip:port/databases
:param targetdb: dbtype,user/password@ip:port/databases
:param tables: tablename or tablename1,tablename2,tablename3
:return:
示例：
db:"mysql,root/[email protected]:50016/pdw"
tables:"BUSI_REALIZABLE_ASSET, BUSI_HOUSE_DETAIL"
'''
print("hell")
msg = []
msg_single_tabledata = []
bool_db = True
# 验证需要验证的表在数据库中是否存在,表结构是否一致
marched_tables_str, msg_structure = self.assertTableStructureBetweenDb(
referdb, targetdb, tables)
if marched_tables_str != tables:
bool_db = False
msg.append(msg_structure)
#验证表数据
if len(marched_tables_str) > 0:
msg.append(
"*****************************data detail*********************************"
)
list_tables = marched_tables_str.split(",")
data_marched_tables = []
data_unmarched_tables = []
#断言数据
for tableName in list_tables:
msg_single_tabledata.append("\n*********%s*********" %
(tableName))
bool_table = True
print("tablename:", tableName)
str_sql = "SELECT * FROM %s" % tableName
referdbService = DBApi(referdb)
rs1 = referdbService.listDataBySQL(str_sql)
targetdbService = DBApi(targetdb)
rs2 = targetdbService.listDataBySQL(str_sql)
if len(rs1) != len(rs2):
# print("数据条数不一致！参照数据库：%d条，目标数据库：%d条" %(len(rs1),len(rs2)))
msg_single_tabledata.append(
"Data count not marched！referdb：%d，targetdb：%d" %
(len(rs1), len(rs2)))
bool_db = False
bool_table = False
#获取目标表中新增数据信息：
data_add = []
for row in range(0, len(rs2)):
if rs2[row] not in rs1:
data_add.append(rs2[row])
if len(data_add) > 0:
bool_db = False
bool_table = False
data_add_list = []
for data in data_add:
data_add_list.append(str(data))
# msg_single_tabledata.append("data added：")
# msg_single_tabledata.append("\n".join(data_add_list))
#获取目标表中减少的数据
data_del = []
for row in range(0, len(rs1)):
if rs1[row] not in rs2:
data_del.append(rs1[row])
if len(data_del) > 0:
bool_db = False
bool_table = False
data_del_list = []
for data in data_del:
data_del_list.append(str(data))
# msg_single_tabledata.append("data deleted：")
# msg_single_tabledata.append("\n".join(data_del_list))
#获取数据一致的表
if bool_table:
data_marched_tables.append(tableName)
else:
data_unmarched_tables.append(tableName)
if bool_db:
print("*INFO* DB data marched!")
else:
msg.append("DB data not marched")
if len(data_marched_tables) > 0:
msg.append("data marched tables：%s" %
(",".join(data_marched_tables)))
if len(data_unmarched_tables) > 0:
msg.append("data not marched tables：%s" %
(",".join(data_unmarched_tables)))
msg.append(
"***************************details**************************************"
)
# msg.append("\n".join(msg_single_tabledata))
print("*ERROR* %s" % ("\n".join(msg)))
else:
print("*ERROR* DB data not marched!")
# msg.append("两个数据库中表结构不一致！")
return "\n".join(msg)
