一个用python操作网络后台mysql数据库的小案例，保存于此，以备不时之需。
# -*- coding: utf-8 -*-
from __future__ import division
import MySQLdb
import os
import time
import datetime
# 打开数据库连接
db = MySQLdb.connect("host","username","password","database")
# 使用 cursor() 方法获取操作游标
cursor = db.cursor()
# SQL 查询语句
# 网关1
## 传感器1-1
num_1_1 = "SELECT device_id FROM riskass_device_logic_data WHERE device_id=1 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_1_1_current1_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_1=0 and device_id=1 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_1_1_current2_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_2=0 and device_id=1 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_1_1_min = "SELECT MIN(create_time) FROM riskass_device_logic_data WHERE device_id=1 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_1_1_max = "SELECT MAX(create_time) FROM riskass_device_logic_data WHERE device_id=1 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
## 传感器1-2
num_1_2 = "SELECT device_id FROM riskass_device_logic_data WHERE device_id=2 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_1_2_current1_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_1=0 and device_id=2 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_1_2_current2_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_2=0 and device_id=2 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_1_2_min = "SELECT MIN(create_time) FROM riskass_device_logic_data WHERE device_id=2 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_1_2_max = "SELECT MAX(create_time) FROM riskass_device_logic_data WHERE device_id=2 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
## 传感器1-3
num_1_3 = "SELECT device_id FROM riskass_device_logic_data WHERE device_id=3 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_1_3_current1_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_1=0 and device_id=3 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_1_3_current2_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_2=0 and device_id=3 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_1_3_min = "SELECT MIN(create_time) FROM riskass_device_logic_data WHERE device_id=3 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_1_3_max = "SELECT MAX(create_time) FROM riskass_device_logic_data WHERE device_id=3 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
# 网关2
## 传感器2-1
num_2_1 = "SELECT device_id FROM riskass_device_logic_data WHERE device_id=62 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_2_1_current1_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_1=0 and device_id=62 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_2_1_current2_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_2=0 and device_id=62 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_2_1_min = "SELECT MIN(create_time) FROM riskass_device_logic_data WHERE device_id=62 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_2_1_max = "SELECT MAX(create_time) FROM riskass_device_logic_data WHERE device_id=62 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
## 传感器2-2
num_2_2 = "SELECT device_id FROM riskass_device_logic_data WHERE device_id=63 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_2_2_current1_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_1=0 and device_id=63 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_2_2_current2_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_2=0 and device_id=63 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_2_2_min = "SELECT MIN(create_time) FROM riskass_device_logic_data WHERE device_id=63 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_2_2_max = "SELECT MAX(create_time) FROM riskass_device_logic_data WHERE device_id=63 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
## 传感器2-3
num_2_3 = "SELECT device_id FROM riskass_device_logic_data WHERE device_id=64 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_2_3_current1_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_1=0 and device_id=64 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_2_3_current2_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_2=0 and device_id=64 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_2_3_min = "SELECT MIN(create_time) FROM riskass_device_logic_data WHERE device_id=64 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_2_3_max = "SELECT MAX(create_time) FROM riskass_device_logic_data WHERE device_id=64 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
# 网关3
## 传感器3-1
num_3_1 = "SELECT device_id FROM riskass_device_logic_data WHERE device_id=69 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_3_1_current1_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_1=0 and device_id=69 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_3_1_current2_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_2=0 and device_id=69 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_3_1_min = "SELECT MIN(create_time) FROM riskass_device_logic_data WHERE device_id=69 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_3_1_max = "SELECT MAX(create_time) FROM riskass_device_logic_data WHERE device_id=69 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
## 传感器3-2
num_3_2 = "SELECT device_id FROM riskass_device_logic_data WHERE device_id=70 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_3_2_current1_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_1=0 and device_id=70 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_3_2_current2_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_2=0 and device_id=70 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_3_2_min = "SELECT MIN(create_time) FROM riskass_device_logic_data WHERE device_id=70 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_3_2_max = "SELECT MAX(create_time) FROM riskass_device_logic_data WHERE device_id=70 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
# 网关4
## 传感器4-1
num_4_1 = "SELECT device_id FROM riskass_device_logic_data WHERE device_id=73 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_4_1_current1_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_1=0 and device_id=73 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_4_1_current2_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_2=0 and device_id=73 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_4_1_min = "SELECT MIN(create_time) FROM riskass_device_logic_data WHERE device_id=73 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_4_1_max = "SELECT MAX(create_time) FROM riskass_device_logic_data WHERE device_id=73 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
## 传感器4-2
num_4_2 = "SELECT device_id FROM riskass_device_logic_data WHERE device_id=74 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_4_2_current1_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_1=0 and device_id=74 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_4_2_current2_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_2=0 and device_id=74 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_4_2_min = "SELECT MIN(create_time) FROM riskass_device_logic_data WHERE device_id=74 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_4_2_max = "SELECT MAX(create_time) FROM riskass_device_logic_data WHERE device_id=74 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
## 传感器4-3
num_4_3 = "SELECT device_id FROM riskass_device_logic_data WHERE device_id=75 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_4_3_current1_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_1=0 and device_id=75 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
num_4_3_current2_0 = "SELECT device_id FROM riskass_device_logic_data WHERE current_2=0 and device_id=75 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_4_3_min = "SELECT MIN(create_time) FROM riskass_device_logic_data WHERE device_id=75 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
time_4_3_max = "SELECT MAX(create_time) FROM riskass_device_logic_data WHERE device_id=75 and create_time like '%" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + "%';"
try:
# 执行SQL语句
#网关1
## 传感器1-1
num_1_1 = cursor.execute(num_1_1)
num_1_1_current1_0 = cursor.execute(num_1_1_current1_0)
num_1_1_current2_0 = cursor.execute(num_1_1_current2_0)
## 传感器1-2
num_1_2 = cursor.execute(num_1_2)
num_1_2_current1_0 = cursor.execute(num_1_2_current1_0)
num_1_2_current2_0 = cursor.execute(num_1_2_current2_0)
## 传感器2-3
num_1_3 = cursor.execute(num_1_3)
num_1_3_current1_0 = cursor.execute(num_1_3_current1_0)
num_1_3_current2_0 = cursor.execute(num_1_3_current2_0)
#网关2
## 传感器2-1
num_2_1 = cursor.execute(num_2_1)
num_2_1_current1_0 = cursor.execute(num_2_1_current1_0)
num_2_1_current2_0 = cursor.execute(num_2_1_current2_0)
## 传感器2-2
num_2_2 = cursor.execute(num_2_2)
num_2_2_current1_0 = cursor.execute(num_2_2_current1_0)
num_2_2_current2_0 = cursor.execute(num_2_2_current2_0)
## 传感器2-3
num_2_3 = cursor.execute(num_2_3)
num_2_3_current1_0 = cursor.execute(num_2_3_current1_0)
num_2_3_current2_0 = cursor.execute(num_2_3_current2_0)
#网关3
## 传感器3-1
num_3_1 = cursor.execute(num_3_1)
num_3_1_current1_0 = cursor.execute(num_3_1_current1_0)
num_3_1_current2_0 = cursor.execute(num_3_1_current2_0)
## 传感器3-2
num_3_2 = cursor.execute(num_3_2)
num_3_2_current1_0 = cursor.execute(num_3_2_current1_0)
num_3_2_current2_0 = cursor.execute(num_3_2_current2_0)
#网关4
## 传感器4-1
num_4_1 = cursor.execute(num_4_1)
num_4_1_current1_0 = cursor.execute(num_4_1_current1_0)
num_4_1_current2_0 = cursor.execute(num_4_1_current2_0)
## 传感器4-2
num_4_2 = cursor.execute(num_4_2)
num_4_2_current1_0 = cursor.execute(num_4_2_current1_0)
num_4_2_current2_0 = cursor.execute(num_4_2_current2_0)
## 传感器4-3
num_4_3 = cursor.execute(num_4_3)
num_4_3_current1_0 = cursor.execute(num_4_3_current1_0)
num_4_3_current2_0 = cursor.execute(num_4_3_current2_0)
# 获取所有记录列表
fo = open("益马高速网关数据反馈汇总" + time.strftime('%Y-%m-%d',time.localtime(time.time())) + ".txt", "a")
now = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(time.time()))
fo.write("当前时间：%s\n\n" % now)
fo.write("----------------------------------------------------\n")
# 网关1
# 传感器1-1
fo.write("莫家大屋右线--传感器1-1：\n")
fo.write("数据总数目：%d\n" % num_1_1)
fo.write("拱顶沉降数据为0条目数：%d\n" % num_1_1_current1_0)
if num_1_1 != 0:
percent1_1_1 = num_1_1_current1_0 / num_1_1
else:
percent1_1_1 = 1
fo.write("拱顶沉降数据为0条目所占百分比：%f\n" % percent1_1_1)
fo.write("水平收敛数据为0条目数：%d\n" % num_1_1_current2_0)
if num_1_1 != 0:
percent1_1_2 = num_1_1_current2_0 / num_1_1
else:
percent1_1_2 = 1
fo.write("水平收敛数据为0条目所占百分比：%f\n" % percent1_1_2)
## 获取起始时间
time_1_1_min = cursor.execute(time_1_1_min)
time_1_1_min = cursor.fetchall()
for row in time_1_1_min:
fo.write("起始时间：%s\n" % row[0])
## 获取终止时间
time_1_1_max = cursor.execute(time_1_1_max)
time_1_1_max = cursor.fetchall()
for row in time_1_1_max:
fo.write("终止时间：%s\n" % row[0])
if row[0] == None:
fo.write("传感器1-1停止工作！\n")
tag1_1 = 0
elif (datetime.datetime.now() - row[0]).seconds > 300:
fo.write("传感器1-1停止工作！\n")
tag1_1 = 0
else:
fo.write("传感器1-1正常工作\n")
tag1_1 = 1
fo.write("----------------------------------------------------\n")
###########################################
# 传感器1-2
fo.write("莫家大屋右线--传感器1-2：\n")
fo.write("数据总数目：%d\n" % num_1_2)
fo.write("拱顶沉降数据为0条目数：%d\n" % num_1_2_current1_0)
num_1_1 = float(num_1_2)
if num_1_2 != 0:
percent1_2_1 = num_1_2_current1_0 / num_1_2
else:
percent1_2_1 = 1
fo.write("拱顶沉降数据为0条目所占百分比：%f\n" % percent1_2_1)
fo.write("水平收敛数据为0条目数：%d\n" % num_1_2_current2_0)
if num_1_2 != 0:
percent1_2_2 = num_1_2_current2_0 / num_1_2
else:
percent1_2_2 = 1
fo.write("水平收敛数据为0条目所占百分比：%f\n" % percent1_2_2)
## 获取起始时间
time_1_2_min = cursor.execute(time_1_2_min)
time_1_2_min = cursor.fetchall()
for row in time_1_2_min:
fo.write("起始时间：%s\n" % row[0])
## 获取终止时间
time_1_2_max = cursor.execute(time_1_2_max)
time_1_2_max = cursor.fetchall()
for row in time_1_2_max:
fo.write("终止时间：%s\n" % row[0])
if row[0] == None:
fo.write("传感器1-2停止工作！\n")
tag1_2 = 0
elif (datetime.datetime.now() - row[0]).seconds > 300:
fo.write("传感器1-2停止工作！\n")
tag1_2 = 0
else:
fo.write("传感器1-2正常工作\n")
tag1_2 = 1
fo.write("----------------------------------------------------\n")
###########################################
# 传感器1-3
fo.write("莫家大屋右线--传感器1-3：\n")
fo.write("数据总数目：%d\n" % num_1_3)
fo.write("拱顶沉降数据为0条目数：%d\n" % num_1_3_current1_0)
if num_1_3 != 0:
percent1_3_1 = num_1_3_current1_0 / num_1_3
else:
percent1_3_1 = 1
fo.write("拱顶沉降数据为0条目所占百分比：%f\n" % percent1_3_1)
fo.write("水平收敛数据为0条目数：%d\n" % num_1_3_current2_0)
if num_1_3 != 0:
percent1_3_2 = num_1_3_current2_0 / num_1_3
else:
percent1_3_2 = 1
fo.write("水平收敛数据为0条目所占百分比：%f\n" % percent1_3_2)
## 获取起始时间
time_1_3_min = cursor.execute(time_1_3_min)
time_1_3_min = cursor.fetchall()
for row in time_1_3_min:
fo.write("起始时间：%s\n" % row[0])
## 获取终止时间
time_1_3_max = cursor.execute(time_1_3_max)
time_1_3_max = cursor.fetchall()
for row in time_1_3_max:
fo.write("终止时间：%s\n" % row[0])
if row[0] == None:
fo.write("传感器1-3停止工作！\n")
tag1_3 = 0
elif (datetime.datetime.now() - row[0]).seconds > 300:
fo.write("传感器1-3停止工作！\n")
tag1_3 = 0
else:
fo.write("传感器1-3正常工作\n")
tag1_3 = 1
fo.write("----------------------------------------------------\n")
if tag1_1|tag1_2|tag1_3:
fo.write("网关1正常工作\n\n")
else:
fo.write("网关1停止工作！\n\n")
fo.write("====================================================\n")
#=========================================#
# 网关2
# 传感器2-1
fo.write("莫家大屋左线--传感器2-1：\n")
fo.write("数据总数目：%d\n" % num_2_1)
fo.write("拱顶沉降数据为0条目数：%d\n" % num_2_1_current1_0)
if num_2_1 != 0:
percent2_1_1 = num_2_1_current1_0 / num_2_1
else:
percent2_1_1 = 1
fo.write("拱顶沉降数据为0条目所占百分比：%f\n" % percent2_1_1)
fo.write("水平收敛数据为0条目数：%d\n" % num_2_1_current2_0)
if num_2_1 != 0:
percent2_1_2 = num_2_1_current2_0 / num_2_1
else:
percent2_1_2 = 1
fo.write("水平收敛数据为0条目所占百分比：%f\n" % percent2_1_2)
## 获取起始时间
time_2_1_min = cursor.execute(time_2_1_min)
time_2_1_min = cursor.fetchall()
for row in time_2_1_min:
fo.write("起始时间：%s\n" % row[0])
## 获取终止时间
time_2_1_max = cursor.execute(time_2_1_max)
time_2_1_max = cursor.fetchall()
for row in time_2_1_max:
fo.write("终止时间：%s\n" % row[0])
if row[0] == None:
fo.write("传感器2-1停止工作！\n")
tag2_1 = 0
elif (datetime.datetime.now() - row[0]).seconds > 300:
fo.write("传感器2-1停止工作！\n")
tag2_1 = 0
else:
fo.write("传感器2-1正常工作\n")
tag2_1 = 1
fo.write("----------------------------------------------------\n")
###########################################
# 传感器2-2
fo.write("莫家大屋左线--传感器2-2：\n")
fo.write("数据总数目：%d\n" % num_2_2)
fo.write("拱顶沉降数据为0条目数：%d\n" % num_2_2_current1_0)
num_2_1 = float(num_2_2)
if num_2_2 != 0:
percent2_2_1 = num_2_2_current1_0 / num_2_2
else:
percent2_2_1 = 1
fo.write("拱顶沉降数据为0条目所占百分比：%f\n" % percent2_2_1)
fo.write("水平收敛数据为0条目数：%d\n" % num_2_2_current2_0)
if num_2_2 != 0:
percent2_2_2 = num_2_2_current2_0 / num_2_2
else:
percent2_2_2 = 1
fo.write("水平收敛数据为0条目所占百分比：%f\n" % percent2_2_2)
## 获取起始时间
time_2_2_min = cursor.execute(time_2_2_min)
time_2_2_min = cursor.fetchall()
for row in time_2_2_min:
fo.write("起始时间：%s\n" % row[0])
## 获取终止时间
time_2_2_max = cursor.execute(time_2_2_max)
time_2_2_max = cursor.fetchall()
for row in time_2_2_max:
fo.write("终止时间：%s\n" % row[0])
if row[0] == None:
fo.write("传感器2-2停止工作！\n")
tag2_2 = 0
elif (datetime.datetime.now() - row[0]).seconds > 300:
fo.write("传感器2-2停止工作！\n")
tag2_2 = 0
else:
fo.write("传感器2-2正常工作\n")
tag2_2 = 1
fo.write("----------------------------------------------------\n")
###########################################
# 传感器2-3
fo.write("莫家大屋左线--传感器2-3：\n")
fo.write("数据总数目：%d\n" % num_2_3)
fo.write("拱顶沉降数据为0条目数：%d\n" % num_2_3_current1_0)
if num_2_3 != 0:
percent2_3_1 = num_2_3_current1_0 / num_2_3
else:
percent2_3_1 = 1
fo.write("拱顶沉降数据为0条目所占百分比：%f\n" % percent2_3_1)
fo.write("水平收敛数据为0条目数：%d\n" % num_2_3_current2_0)
if num_2_3 != 0:
percent2_3_2 = num_2_3_current2_0 / num_2_3
else:
percent2_3_2 = 1
fo.write("水平收敛数据为0条目所占百分比：%f\n" % percent2_3_2)
## 获取起始时间
time_2_3_min = cursor.execute(time_2_3_min)
time_2_3_min = cursor.fetchall()
for row in time_2_3_min:
fo.write("起始时间：%s\n" % row[0])
## 获取终止时间
time_2_3_max = cursor.execute(time_2_3_max)
time_2_3_max = cursor.fetchall()
for row in time_2_3_max:
fo.write("终止时间：%s\n" % row[0])
if row[0] == None:
fo.write("传感器2-3停止工作！\n")
tag2_3 = 0
elif (datetime.datetime.now() - row[0]).seconds > 300:
fo.write("传感器2-3停止工作！\n")
tag2_3 = 0
else:
fo.write("传感器2-3正常工作\n")
tag2_3 = 1
fo.write("----------------------------------------------------\n")
if tag2_1|tag2_2|tag2_3:
fo.write("网关2正常工作\n\n")
else:
fo.write("网关2停止工作！\n\n")
fo.write("====================================================\n")
#=========================================#
# 网关3
# 传感器3-1
fo.write("板山左线--传感器3-1：\n")
fo.write("数据总数目：%d\n" % num_3_1)
fo.write("拱顶沉降数据为0条目数：%d\n" % num_3_1_current1_0)
if num_3_1 != 0:
percent3_1_1 = num_3_1_current1_0 / num_3_1
else:
percent3_1_1 = 1
fo.write("拱顶沉降数据为0条目所占百分比：%f\n" % percent3_1_1)
fo.write("水平收敛数据为0条目数：%d\n" % num_3_1_current2_0)
if num_3_1 != 0:
percent3_1_2 = num_3_1_current2_0 / num_3_1
else:
percent3_1_2 = 1
fo.write("水平收敛数据为0条目所占百分比：%f\n" % percent3_1_2)
## 获取起始时间
time_3_1_min = cursor.execute(time_3_1_min)
time_3_1_min = cursor.fetchall()
for row in time_3_1_min:
fo.write("起始时间：%s\n" % row[0])
## 获取终止时间
time_3_1_max = cursor.execute(time_3_1_max)
time_3_1_max = cursor.fetchall()
for row in time_3_1_max:
fo.write("终止时间：%s\n" % row[0])
if row[0] == None:
fo.write("传感器3-1停止工作！\n")
tag3_1 = 0
elif (datetime.datetime.now() - row[0]).seconds > 300:
fo.write("传感器3-1停止工作！\n")
tag3_1 = 0
else:
fo.write("传感器3-1正常工作\n")
tag3_1 = 1
fo.write("----------------------------------------------------\n")
###########################################
# 传感器3-2
fo.write("板山左线--传感器3-2：\n")
fo.write("数据总数目：%d\n" % num_3_2)
fo.write("拱顶沉降数据为0条目数：%d\n" % num_3_2_current1_0)
if num_3_2 != 0:
percent3_2_1 = num_3_2_current1_0 / num_3_2
else:
percent3_2_1 = 1
fo.write("拱顶沉降数据为0条目所占百分比：%f\n" % percent3_2_1)
fo.write("水平收敛数据为0条目数：%d\n" % num_3_2_current2_0)
if num_3_2 != 0:
percent3_2_2 = num_3_2_current2_0 / num_3_2
else:
percent3_2_2 = 1
fo.write("水平收敛数据为0条目所占百分比：%f\n" % percent3_2_2)
## 获取起始时间
time_3_2_min = cursor.execute(time_3_2_min)
time_3_2_min = cursor.fetchall()
for row in time_3_2_min:
fo.write("起始时间：%s\n" % row[0])
## 获取终止时间
time_3_2_max = cursor.execute(time_3_2_max)
time_3_2_max = cursor.fetchall()
for row in time_3_2_max:
fo.write("终止时间：%s\n" % row[0])
if row[0] == None:
fo.write("传感器3-2停止工作！\n")
tag3_2 = 0
elif (datetime.datetime.now() - row[0]).seconds > 300:
fo.write("传感器3-2停止工作！\n")
tag3_2 = 0
else:
fo.write("传感器3-2正常工作\n")
tag3_2 = 1
fo.write("----------------------------------------------------\n")
if tag3_1|tag3_2:
fo.write("网关3正常工作\n\n")
else:
fo.write("网关3停止工作！\n\n")
fo.write("====================================================\n")
#=========================================#
# 网关4
# 传感器4-1
fo.write("板山右线--传感器4-1：\n")
fo.write("数据总数目：%d\n" % num_4_1)
fo.write("拱顶沉降数据为0条目数：%d\n" % num_4_1_current1_0)
if num_4_1 != 0:
percent4_1_1 = num_4_1_current1_0 / num_4_1
else:
percent4_1_1 = 1
fo.write("拱顶沉降数据为0条目所占百分比：%f\n" % percent4_1_1)
fo.write("水平收敛数据为0条目数：%d\n" % num_4_1_current2_0)
if num_4_1 != 0:
percent4_1_2 = num_4_1_current2_0 / num_4_1
else:
percent4_1_2 = 1
fo.write("水平收敛数据为0条目所占百分比：%f\n" % percent4_1_2)
## 获取起始时间
time_4_1_min = cursor.execute(time_4_1_min)
time_4_1_min = cursor.fetchall()
for row in time_4_1_min:
fo.write("起始时间：%s\n" % row[0])
## 获取终止时间
time_4_1_max = cursor.execute(time_4_1_max)
time_4_1_max = cursor.fetchall()
for row in time_4_1_max:
fo.write("终止时间：%s\n" % row[0])
if row[0] == None:
fo.write("传感器4-1停止工作！\n")
tag4_1 = 0
elif (datetime.datetime.now() - row[0]).seconds > 300:
fo.write("传感器4-1停止工作！\n")
tag4_1 = 0
else:
fo.write("传感器4-1正常工作\n")
tag4_1 = 1
fo.write("----------------------------------------------------\n")
###########################################
# 传感器4-2
fo.write("板山右线--传感器4-2：\n")
fo.write("数据总数目：%d\n" % num_4_2)
fo.write("拱顶沉降数据为0条目数：%d\n" % num_4_2_current1_0)
if num_4_2 != 0:
percent4_2_1 = num_4_2_current1_0 / float(num_4_2)
else:
percent4_2_1 = 1
fo.write("拱顶沉降数据为0条目所占百分比：%f\n" % percent4_2_1)
fo.write("水平收敛数据为0条目数：%d\n" % num_4_2_current2_0)
if num_4_2 != 0:
percent4_2_2 = num_4_2_current2_0 / num_4_2
else:
percent4_2_2 = 1
fo.write("水平收敛数据为0条目所占百分比：%f\n" % percent4_2_2)
## 获取起始时间
time_4_2_min = cursor.execute(time_4_2_min)
time_4_2_min = cursor.fetchall()
for row in time_4_2_min:
fo.write("起始时间：%s\n" % row[0])
## 获取终止时间
time_4_2_max = cursor.execute(time_4_2_max)
time_4_2_max = cursor.fetchall()
for row in time_4_2_max:
fo.write("终止时间：%s\n" % row[0])
if row[0] == None:
fo.write("传感器4-2停止工作！\n")
tag4_2 = 0
elif (datetime.datetime.now() - row[0]).seconds > 300:
fo.write("传感器4-2停止工作！\n")
tag4_2 = 0
else:
fo.write("传感器4-2正常工作\n")
tag4_2 = 1
fo.write("----------------------------------------------------\n")
###########################################
# 传感器4-3
fo.write("板山右线--传感器4-3：\n")
fo.write("数据总数目：%d\n" % num_4_3)
fo.write("拱顶沉降数据为0条目数：%d\n" % num_4_3_current1_0)
if num_4_3 != 0:
percent4_3_1 = num_4_1_current1_0 / num_4_3
else:
percent4_3_1 = 1
fo.write("拱顶沉降数据为0条目所占百分比：%f\n" % percent4_3_1)
fo.write("水平收敛数据为0条目数：%d\n" % num_4_3_current2_0)
if num_4_3 != 0:
percent4_3_2 = num_4_3_current2_0 / num_4_3
else:
percent4_3_2 = 1
fo.write("水平收敛数据为0条目所占百分比：%f\n" % percent4_3_2)
## 获取起始时间
time_4_3_min = cursor.execute(time_4_3_min)
time_4_3_min = cursor.fetchall()
for row in time_4_3_min:
fo.write("起始时间：%s\n" % row[0])
## 获取终止时间
time_4_3_max = cursor.execute(time_4_3_max)
time_4_3_max = cursor.fetchall()
for row in time_4_3_max:
fo.write("终止时间：%s\n" % row[0])
if row[0] == None:
fo.write("传感器4-3停止工作！\n")
tag4_3 = 0
elif (datetime.datetime.now() - row[0]).seconds > 300:
fo.write("传感器4-3停止工作！\n")
tag4_3 = 0
else:
fo.write("传感器4-3正常工作\n")
tag4_3 = 1
fo.write("----------------------------------------------------\n")
if tag4_1|tag4_2|tag4_3:
fo.write("网关4正常工作\n\n")
else:
fo.write("网关4停止工作！\n\n")
fo.write("====================================================\n")
fo.close()
except:
print "Error: unable to fetch data"
db.close()
没有评论:
发表评论