def sensor_value_api_update():
"""
センサー値登録API
:param
sensor_id: センサーID
detext_dt: 検出日時
value: 検出値
:return:
result
"""
# Formパラメータの取得
sensor_id = request.form['sensor_id']
detect_dt = request.form['detect_dt']
value = request.form['value']
# detect_idを求める
sql = 'SELECT MAX(detect_id) FROM sensor_values WHERE sensor_id = ?;'
params = (sensor_id, )
row = db_utils.select(sql, params)[0]
detect_id = row[0]
if detect_id is None:
detect_id = 1
else:
detect_id += 1
# センサー値を登録する
sql = 'INSERT INTO sensor_values(sensor_id, detect_id, detect_dt, value) VALUES (?, ?, ?, ?);'
params = (sensor_id, detect_id, detect_dt, value)
db_utils.insert(sql, params)
return make_response(jsonify({'result': 'ok'}))
def sensor_list():
sql = 'SELECT sensor_id, sensor_name, description FROM sensors WHERE del_flag = ? ORDER BY sensor_id;'
params = ('0', )
rows = db_utils.select(sql, params)
sensors = []
for row in rows:
sensor = {
'sensor_id': row[0],
'sensor_name': row[1],
'description': row[2]
}
sensors.append(sensor)
return render_template('sensor_list.html', sensors=sensors)
def sensor_update():
# 初期表示
if request.method == 'GET':
# クエリパラメータの取得
sensor_id = request.args.get('sensor_id')
sensor = {}
# クエリパラメータが設定されている場合はセンサー値を取得
if sensor_id is not None:
sql = 'SELECT sensor_id, sensor_name, description FROM sensors WHERE sensor_id = ? AND del_flag = ?;'
params = (sensor_id, '0')
row = db_utils.select(sql, params)[0]
sensor = {
'sensor_id': row[0],
'sensor_name': row[1],
'description': row[2]
}
return render_template('sensor_update.html', sensor=sensor)
# 登録・更新
else:
# Formパラメータの取得
sensor_id = request.form['sensor_id']
sensor_name = request.form['sensor_name']
description = request.form['description']
# 登録の場合
if sensor_id == '':
# センサーを登録する
sql = 'INSERT INTO sensors(sensor_name, description, del_flag) VALUES (?, ?, ?);'
params = (sensor_name, description, '0')
db_utils.insert(sql, params)
# 更新の場合
else:
# センサー値を更新する
sql = 'UPDATE sensors SET sensor_name = ?, description = ? WHERE sensor_id = ?;'
params = (sensor_name, description, sensor_id)
db_utils.update(sql, params)
return redirect(url_for('sensor_list'), code=302)
def sensor_value_list():
# クエリパラメータの取得
sensor_id = request.args.get('sensor_id')
sql = 'SELECT detect_id, detect_dt, value FROM sensor_values WHERE sensor_id = ? ORDER BY sensor_id, detect_dt, detect_id;'
params = (sensor_id, )
rows = db_utils.select(sql, params)
sensor_values = []
for row in rows:
sensor_value = {
'detect_id': row[0],
'detect_dt': row[1],
'value': row[2]
}
sensor_values.append(sensor_value)
return render_template('sensor_value_list.html',
sensor_id=sensor_id,
sensor_values=sensor_values)
def select_weather(res_dict_):
sql = 'SELECT COUNT(1) FROM weather WHERE city = ? AND dt = ?;'
sql_params = get_select_params(res_dict_)
logging.debug(sql_params)
return db_utils.select(sql, params=sql_params)[0][0]
def select_weather(city):
sql = 'SELECT city, dt, temp, pressure, humidity, wind_speed, wind_deg, description, sub_description \
FROM weather WHERE city = ? ORDER BY city ASC, dt ASC;'
params = (city, )
return db_utils.select(sql, params=params)
def select_sensor_values(sensor_id):
sql = 'SELECT detect_dt, value FROM sensor_values WHERE sensor_id = ? ORDER BY detect_dt;'
params = (sensor_id, )
return db_utils.select(sql, params=params)
def sensor_value_update():
"""
センサー値登録画面
:param
sensor_id: センサーID
detect_id: 検出ID
detext_dt: 検出日時
value: 検出値
:return:
result
"""
# 初期表示
if request.method == 'GET':
# クエリパラメータの取得
sensor_id = request.args.get('sensor_id')
detect_id = request.args.get('detect_id')
sensor_value = {}
# detect_idが設定されている場合はセンサー値を取得
if detect_id is not None:
sql = 'SELECT detect_id, detect_dt, value FROM sensor_values WHERE sensor_id = ? AND detect_id = ?;'
params = (sensor_id, detect_id)
row = db_utils.select(sql, params)[0]
sensor_value = {
'detect_id': row[0],
'detect_dt': row[1],
'value': row[2]
}
return render_template('sensor_value_update.html',
sensor_id=sensor_id,
sensor_value=sensor_value)
# 登録・更新
else:
# Formパラメータの取得
sensor_id = request.form['sensor_id']
detect_id = request.form['detect_id']
detect_dt = request.form['detect_dt']
value = request.form['value']
# 登録の場合
if detect_id == '':
# detect_idを求める
sql = 'SELECT MAX(detect_id) FROM sensor_values WHERE sensor_id = ?;'
params = (sensor_id, )
row = db_utils.select(sql, params)[0]
detect_id = row[0]
if detect_id is None:
detect_id = 1
else:
detect_id += 1
# センサー値を登録する
sql = 'INSERT INTO sensor_values(sensor_id, detect_id, detect_dt, value) VALUES (?, ?, ?, ?);'
params = (sensor_id, detect_id, detect_dt, value)
db_utils.insert(sql, params)
# 更新の場合
else:
# センサー値を更新する
sql = 'UPDATE sensor_values SET detect_dt = ?, value = ? WHERE sensor_id = ? AND detect_id = ?;'
params = (detect_dt, value, sensor_id, detect_id)
db_utils.update(sql, params)
return redirect(url_for('sensor_value_list', sensor_id=sensor_id),
code=302)