我有一张表,上面有三列timestampsensor_idvalue。类似于:

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)

10-06 05:15
查看更多