我有一张表,上面有三列timestamp
、sensor_id
和value
。类似于:
timestamp1,sensor1,value1
timestamp1,sensor2,value2
timestamp1,sensor3,value3
...
timestamp2,sensor1,value1
timestamp2,sensor2,value2
timestamp2,sensor3,value3
...
现在我需要将此表的内容以以下格式写入文本文件:
timestamp1,value1,value2,value3,...
timestamp2,value1,value2,value3,...
用户选择应该写入文件的时间范围和传感器。所以传感器的数量是不同的。我想在这个时间段内获取所有传感器的值并从python中的非选定传感器中删除这些值是最容易的。
是这样,还是可以指定一个变量量不同的查询?
我可以马上建立一个查询来给出指定的格式(按照
sensor_id
后面timestamp
的顺序列出具有相同时间戳的所有值)吗?我正在使用
mysql.connector
。更新
sensor_clause = ','.join(selected_sensors)
query = """
SELECT timestamp, group_concat(value) FROM measurements
WHERE sensor_id in (%s) AND timestamp BETWEEN %s AND %s GROUP BY
timestamp ORDER BY timestamp
"""
cursor.execute(query, (sensor_clause, start, stop))
data = cursor.fetchall()
print(cursor._executed)
查询结果:
"SELECT timestamp, group_concat(value) FROM measurements WHERE sensor_id in ('1,2,4,5') AND timestamp BETWEEN '2014-04-01' AND '2014-06-22' GROUP BY timestamp ORDER BY timestamp"
并给出结果:
[(datetime.datetime(2014, 4, 1, 0, 0), '-1.736'), (date....
在固定查询中将
('1,2,4,5')
替换为('1','2','4','5')
将得到所需的结果:[(datetime.datetime(2014, 4, 1, 0, 0), '-1.736,-1.375,6.843,7.87'), (date...
所以现在要把sensor_子句转换成正确的格式。。。
更新2
Ashalynd的解决方案奏效了-非常感谢:)
最佳答案
这个怎么样?
sensors = get_sensors() #assume you have a method that returns a list
sensor_clause = ','.join(sensors) # if sensor_ids are numbers
OR
sensor_clause = "'"+ "','".join(sensors)+"'" # if sensor_ids are text values
# the result will look like 'sensor1','sensor2','sensor3'
timestamp_clause = get_timestamp_clause() #e.g. 'timestamp between <day1> and <day2>' etc
query = """SELECT timestamp, group_concat(value order by sensor_id)
FROM <yourtable> WHERE sensor_id in (%s)
AND %s GROUP BY timestamp ORDER BY timestamp""" % ( sensor_clause, timestamp_clause )
(
order by
中的group_concat
子句确保值以相同的顺序列出)结果查询将如下所示:
SELECT timestamp, group_concat(value order by sensor_id)
FROM <yourtable> WHERE sensor_id in (sensor1, sensor2, sensor3)
AND <timeclause> GROUP BY timestamp ORDER BY timestamp;
此查询将为您提供一个包含条目的列表,然后您可以将其输出到您喜欢的任何位置。
如果要检查实际选择了哪些传感器ID,可以执行以下操作
SELECT timestamp, group_concat(concat_ws(',', sensor, value) order by sensor_id)
FROM <yourtable>
WHERE sensor in (sensor1, sensor2, ....)
GROUP BY timestamp
它会给你一些东西,比如:
dd-mm-yyyy, "sensor1,value1,sensor2,value2"
dd-mm-yyyy, "sensor1,value1,sensor2,value2,sensor3,value3"
(假设在第一个时间戳只有两个传感器产生值)
然后可以在Python代码中进一步处理这些数据。
具体来说,关于OP:
sensor_clause = ','.join(selected_sensors)
query = """
SELECT timestamp, group_concat(value order by sensor_id) FROM measurements
WHERE sensor_id in (%s) AND timestamp BETWEEN '%s' AND '%s'
GROUP BY timestamp ORDER BY timestamp
""" % (sensor_clause, start, stop)
cursor.execute(query)
data = cursor.fetchall()
print(cursor._executed)
为什么OP版本不起作用:原因是sensor_子句不是一个参数,而是一个序列。它不能作为一个参数一起发送(否则将被解释为字符串)。
更新:处理丢失的传感器值的另一种方法是创建一个小表,其中包含所有可能的传感器值,并与之左连接,这将使丢失的值显示为空,例如:
(假设这个小表叫做sensors,它有一列sensor_id)
query = """
SELECT timestamp, group_concat(value order by s.sensor_id)
FROM measurements m LEFT JOIN sensors s
ON m.sensor_id=s.sensor_id
WHERE s.sensor_id in (%s) AND timestamp BETWEEN '%s' AND '%s'
GROUP BY timestamp ORDER BY timestamp
""" % (sensor_clause, start, stop)