时间戳

首页 > 解决方案 > 已创建扩展数据表

python - 已创建扩展数据表

问题描述

我是 Python 初学者。我发现这段代码非常适合我（将 IP21 连接到 Power BI）——只是试图增加标签的数量（信息）

import pandas as pd
import pyodbc
conn = pyodbc.connect('NAME')
tag = 'MYTAG'
start = '2019-01-01 12:00:00'
end = '2019-10-02 12:00:00'
sql = "select TS,VALUE from HISTORY "\
        "where NAME='%s'"\
        "and PERIOD = 60*10"\
        "and REQUEST = 2"\
        "and REQUEST=2 and TS between TIMESTAMP'%s' and TIMESTAMP'%s'" % (tag, start, end)
data = pd.read_sql(sql,conn) # Pandas DataFrame with your data!

而不是这个，我只想要 tag1, tag2, ... 并获取包含日期和所有这些值的表的数据。如何更改代码？我得到了一个使代码运行的答案，但我认为我的续集编程搞砸了。name 不是一个标签，而是使搜索名称(tag1,tag2)不是有效的搜索名称。你如何在续集中找到多个东西？我想将其更改为

tag = ' TAG1'
tag2 =' TAG2'
start = '2019-01-01 12:00:00'
end = '2019-10-02 12:00:00'
sql = "select TS,VALUE from HISTORY "\
        "where NAME=('%s,%s)'"\
        "and PERIOD = 60*10"\
        "and REQUEST = 2"\
        "and REQUEST=2 and TS between TIMESTAMP'%s' and TIMESTAMP'%s'" % (tag, tag2, start, end)
data = pd.read_sql(sql,conn) # Pandas DataFrame with your data!

类似的东西。这有效，但给出了一个空表。谢谢！

标签： pythonsql

解决方案

If i understand well. You want to have information for some tags. So just adapt the condition for the tag in the query. What you should notice is that there is a %s for each tags. So if you want to add a tag add a %s in the query and add the corresponding variable after % in the brackets. Try to learn who to do sting replacement in python. It is easy.

import pandas as pd
import pyodbc
conn = pyodbc.connect('NAME')
tag = 'MYTAG'
start = '2019-01-01 12:00:00'
end = '2019-10-02 12:00:00'
sql = "select TS,VALUE from HISTORY "\
        "WHERE NAME in ('%s', '%s')"\
        "and PERIOD = 60*10"\
        "and REQUEST = 2"\
        "and REQUEST=2 and TS between TIMESTAMP'%s' and TIMESTAMP'%s'" % (tag, start, end)
data = pd.read_sql(sql,conn) # Pandas DataFrame with your data!

推荐阅读