python - 使用不同数量的变量进行查询

Question

我有一个包含三列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 中的非选定传感器中删除这些值是最简单的。

这是正确的还是我可以指定一个具有不同数量变量的查询？

我可以立即提出一个查询，给出指定的格式(按照 timestamp 后面的 sensor_id 的顺序列出具有相同时间戳的所有值)吗？

我正在使用 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_clause 设置为正确的格式......

更新 2

Ashalynd 的解决方案有效 - 非常感谢:)

Answer 1

这个怎么样？

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 )

(group_concat 中的order by 子句确保值以相同顺序列出)

查询结果如下:

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"

(假设例如在第一个时间戳只有 2 个传感器产生值)

然后您可以在 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_clause 不是一个参数，它是一个序列。它不能作为一个参数一起发送(否则它将被解释为字符串)。

更新:解决缺失传感器值的另一种方法是创建一个小表，其中包含所有可能的传感器值并与其进行左连接，这将使缺失值显示为 NULL，例如:

(假设这个小表叫做传感器，它只有一列，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)