def test_4get_dbdata(cls):
'''获取数据库中的数据'''
sql = "select p.v_user_account,q.v_targ_name from GPS_USER p,GPS_TARG q where p.v_dept_id=q.v_dept_id and p.v_user_account='baoyong123'"
scrpath = "F:\\PyTesting\\AutoTest\\log\\excel\\" # 指定的保存目录
export(sql, scrpath + r'GPS_TARG_DB.xlsx')
# conn = cx_Oracle.connect('gpsadmin/gpsadmin_123654@ 192.168.10.110: 1521 / ora11g') # 连接数据库
# cursor = conn.cursor()
# count = cursor.execute(
# "select p.v_user_account,q.v_targ_name from GPS_USER p,GPS_TARG q where p.v_dept_id=q.v_dept_id and p.v_user_account='baoyong123'")
# # 重置游标的位置
# # cursor.scroll(0,mode='absolute')
# # 搜取所有结果
# results = cursor.fetchall()
#
# # 获取oracle里面的数据字段名称
# fields = cursor.description
# workbook = xlwt.Workbook()
# sheet = workbook.add_sheet('DB_data', cell_overwrite_ok=True)
#
# # 写上字段信息
# for field in range(0, len(fields)):
# sheet.write(0, field, fields[field][0])
#
# # 获取并写入数据段信息
# row = 1
# col = 0
# for row in range(1, len(results) + 1):
# for col in range(0, len(fields)):
# sheet.write(row, col, u'%s' % results[row - 1][col])
# scrpath = 'F:\\PyTesting\\AutoTest\\log\\excel\\' # 指定的保存目录
# workbook.save(scrpath + r'GPS_TARG_DB.xlsx')
# cursor.close()
# conn.close()
print("获取数据库数据成功！")
def test_car_006(self):
'''测B10006（每隔阈值范围接收一条，能收到多条疲劳驾驶报警）'''
sql006 = "select * from OPERATING_COMMAND_MSG t where t.v_equipment_id='0000013100000006' and t.v_text_send like '%疲劳%' and t.d_time between to_date('" + begin_time + "','yyyy-mm-dd hh24:mi:ss') and to_date('" + end_time + "','yyyy-mm-dd hh24:mi:ss')"
export(sql006, scrpath + '测B10006.xlsx')
log.info("获取测B10006数据路中疲劳驾驶报警信息")
excle = xlrd.open_workbook(scrpath + '测B10006.xlsx')
sheet = excle.sheets()[0] # 获取第0个表
n6 = sheet.nrows
log.info("测B10006数据库中疲劳驾驶报警条数为：" + str(n6 - 1))
fopen = open(log_file, 'r', encoding='UTF-8')
count = 0
for lines in fopen.readlines(): # 按行读取text中的内容
# lines = lines.replace("\n", "").split(",")
# if 'aaa' in str(lines) and '2' not in str(lines):
# 筛选出含有'aaa'并且不含数字2的每一行
li = re.findall("测B10006 报警类型：2", lines)
if len(li) > 0:
count = count + len(li)
print(lines)
# print(count)
fopen.close()
log.info("测B10006上级平台日志中疲劳驾驶报警条数为：" + str(count))
self.assertEqual(str(n6 - 1), str(count), "数据库报警信息和上级平台日志中报警信息不一致！")
log.info("数据库报警信息和上级平台日志中报警信息一致！")
def test_3get_dbdata(cls):
'''获取数据库中的数据'''
sql1 = "select h.v_targ_id,h.v_targ_name,b.v_dept_name,p.v_equp_simnum,p.v_equp_id from GPS_EQUP p,GPS_TARG h,GPS_DEPT b where " \
" h.v_targ_id=p.v_targ_id and h.v_dept_id=b.v_dept_id and b.v_dept_name='测试鲍泳'"
scrpath = "F:\\PyTesting\\AutoTest\\log\\excel\\" # 指定的保存目录
export(sql1, scrpath + 'Sim_DB.xlsx')
log.info("获取数据库当前公司SIM数据")
def test_5get_dbdata(cls):
'''获取数据库中数据'''
sql1 = "select t.d_time as 群发录入时间,h.v_user_account as 群发录入工号,t.v_text_send as 群发内容 from OPERATING_COMMAND_MSG t,GPS_USER h where t.v_user_id=h.v_user_id and " \
"t.d_time between to_date('" + t_date + " 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_date('" + t_date + " 23:59:59', 'yyyy-mm-dd hh24:mi:ss')"
scrpath = "F:\\PyTesting\\AutoTest\\log\\excel\\" # 指定的保存目录
export(sql1, scrpath + 'MSG_T_DB.xlsx')
log.info("获取数据库今日下发报文信息")
def test_4get_dbdata(cls):
'''获取数据库中的数据'''
sql = "select h.v_targ_id,h.v_targ_name,t.n_warn_type,t.d_warn_date from GPS_TARG h,WARN_HISTORY t where h.v_targ_id= t.v_targ_id and t.d_warn_date between " \
"to_date('2018-08-08 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-08-08 23:59:59','yyyy-mm-dd hh24:mi:ss')"
scrpath = "F:\\PyTesting\\AutoTest\\log\\excel\\" # 指定的保存目录
export(sql, scrpath + r'WARN_TARG_DB.xlsx')
print("获取数据库数据成功！")
def test_3get_dbdata(cls):
'''获取数据库中数据'''
sql1 = "select t.d_static_date as 日期,h.v_targ_name as 车牌号,f.v_dept_name as 所属公司,h.v_plat_id as 所属平台,t.v_targ_duration as 行驶时间,t.n_total_mileage as 里程,t.n_refuel as 加油量,t.n_fuel_consumption as 用油量 " \
"from BI_TARG_STATIC_D t ,GPS_TARG h,GPS_DEPT f " \
"where h.v_dept_id=f.v_dept_id and t.v_targ_id=h.v_targ_id and t.d_static_date=to_date('" + Yestoday + "','YYYY-MM_DD')"
scrpath = "F:\\PyTesting\\AutoTest\\log\\excel\\" # 指定的保存目录
export(sql1, scrpath + 'Mile_Y_DB.xlsx')
log.info("获取数据库昨日数据")
def test_4get_dbdata(cls):
'''获取数据库中数据'''
sql1 = "select t.n_type,e.v_user_account,h.value,t.v_content,t.d_date from PLAT_USER_BEHAVIOR t,GPS_USER e ,GPS_ENUM h where " \
"t.n_type=h.key and h.type='BEHAVIOR_TYPE' and t.v_user_id=e.v_user_id and e.v_user_account='baoyong123' and t.d_date " \
"between to_date('" + last_week_start + " 00:00:01', 'yyyy-mm-dd hh24:mi:ss') and to_date('" + t_date + " 23:59:59', 'yyyy-mm-dd hh24:mi:ss')"
scrpath = "F:\\PyTesting\\AutoTest\\log\\excel\\" # 指定的保存目录
export(sql1, scrpath + 'USER_LOG_DB.xlsx')
log.info("获取数据库用户行为日志")
def test_004():
'''测B10004'''
print(begin_time)
user = '******'
sql004 = "select * from OPERATING_COMMAND_MSG t where t.v_equipment_id='0000013100000004' and t.v_text_send like '%疲劳%' and t.d_time between to_date('" + begin_time + "','yyyy-mm-dd hh24:mi:ss') and to_date('2018/06/22 23:59:59','yyyy-mm-dd hh24:mi:ss')"
sql = "select t.v_user_name from GPS_USER t where t.v_user_account='%s'" % user
export(sql004, scrpath + '测B10004.xlsx')
def test_3get_dbdata(cls):
'''获取数据库中的数据'''
sql1 = "select i.v_targ_name,i.d_time,i.v_owers_name from vehicle_added_base i where i.d_time between " \
"to_date('" + t_date + " 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('" + t_date + " 23:59:59','yyyy-mm-dd hh24:mi:ss')"
scrpath = "F:\\PyTesting\\AutoTest\\log\\excel\\" # 指定的保存目录
export(sql1, scrpath + 'History_Today_DB.xlsx')
log.info("获取数据库今日数据")
sql2 = "select i.v_targ_name,i.d_time,i.v_owers_name from vehicle_added_base i where i.d_time between " \
"to_date('" + Yestoday + " 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('" + Yestoday + " 23:59:59','yyyy-mm-dd hh24:mi:ss')"
scrpath = "F:\\PyTesting\\AutoTest\\log\\excel\\" # 指定的保存目录
export(sql2, scrpath + 'History_Yes_DB.xlsx')
log.info("获取数据库昨日数据")
def test_3get_dbdata(cls):
'''获取数据库中数据'''
sql1 = "select z.v_plat_id,u.v_plat_name,z.d_time,z.v_info_content,z.v_ans_content,z.d_ans_time,h.v_user_name from PLAT_POST_QUERY z,GPS_PLAT u,GPS_USER h " \
"where h.v_user_id=z.v_user_id and z.v_plat_id=u.v_plat_id and z.d_time between " \
"to_date('" + last_month1 + " 00:00:01', 'yyyy-mm-dd hh24:mi:ss') and to_date('" + last_month2 + " 23:59:59', 'yyyy-mm-dd hh24:mi:ss')"
scrpath = "F:\\PyTesting\\AutoTest\\log\\excel\\" # 指定的保存目录
export(sql1, scrpath + 'Plat_L_DB.xlsx')
log.info("获取数据库上月数据")
sql2 = "select z.v_plat_id,u.v_plat_name,z.d_time,z.v_info_content,z.v_ans_content,z.d_ans_time,h.v_user_name from PLAT_POST_QUERY z,GPS_PLAT u,GPS_USER h " \
"where h.v_user_id=z.v_user_id and z.v_plat_id=u.v_plat_id and z.d_time between " \
"to_date('" + t_date + " 00:00:01', 'yyyy-mm-dd hh24:mi:ss') and to_date('" + t_date + " 23:59:59', 'yyyy-mm-dd hh24:mi:ss')"
scrpath = "F:\\PyTesting\\AutoTest\\log\\excel\\" # 指定的保存目录
export(sql2, scrpath + 'Plat_T_DB.xlsx')
log.info("获取数据库今日数据")
sql3 = "select z.v_plat_id,u.v_plat_name,z.d_time,z.v_info_content,z.v_ans_content,z.d_ans_time,h.v_user_name from PLAT_POST_QUERY z,GPS_PLAT u,GPS_USER h " \
"where h.v_user_id=z.v_user_id and z.v_plat_id=u.v_plat_id and z.d_time between " \
"to_date('" + Yestoday + " 00:00:01', 'yyyy-mm-dd hh24:mi:ss') and to_date('" + Yestoday + " 23:59:59', 'yyyy-mm-dd hh24:mi:ss')"
scrpath = "F:\\PyTesting\\AutoTest\\log\\excel\\" # 指定的保存目录
export(sql3, scrpath + 'Plat_Y_DB.xlsx')
log.info("获取数据库昨日数据")