Get multiple variable Aspentech infoplus 21

Question

I am writing a script in order to connect to an Aspentech Infoplus 21 database server. When calling for a single TAG I do not record any problem

import pandas as pd
import pyodbc
from datetime import datetime
from datetime import timedelta
#---- Connect to IP21
conn = pyodbc.connect("DRIVER={AspenTech SQLplus};HOST=192.xxx.x.xxx;PORT=10014")

#---- Query string
tag = 'BAN0E10TI110V'
end = datetime.now()
start = end-timedelta (days=2)
end = end.strftime("%Y-%m-%d %H:%M:%S")
start=start.strftime("%Y-%m-%d %H:%M:%S")
sql = "select TS,VALUE from HISTORY "\
        "where NAME='%s'"\
        "and PERIOD = 300*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!

When calling multiple tags through a csv (following script) file I can not get the required data.

import pandas as pd
import pyodbc
from datetime import datetime
from datetime import timedelta
#---- Connect to IP21
conn = pyodbc.connect("DRIVER={AspenTech SQLplus};HOST=192.xxx.x.xxx;PORT=10014")

tags = pd.read_csv("C:\\Users\\xxx\\TAGcsvIN.csv", decimal=',', sep=';', parse_dates=True)
#---- Query string
end = datetime.now()
start = end-timedelta (days=2)
end = end.strftime("%Y-%m-%d %H:%M:%S")
start=start.strftime("%Y-%m-%d %H:%M:%S")
sql = "select TS,VALUE from HISTORY "\
        "where NAME='%s'"\
        "and PERIOD = 300*10"\
        "and REQUEST = 2"\
        "and REQUEST=2 and TS between TIMESTAMP'%s' and TIMESTAMP'%s'" % (tags['TAGcsv'], start, end)
data = pd.read_sql(sql,conn) # Pandas DataFrame with your data!

Do someone know how to call multiple tags via csv file?

Answer 1

I'm not proficient in python, but if you want to query several tag, you should build a query like this:

"where NAME IN (""tag1"", ""tag2"", ""tagN"")"\

Answer 2

You will need to use the IN statement in the SQL code and also convert your Series from the CSV into a string to put within the IN statement.

If you add the lines:

tags = tags['TAGcsv'].tolist())    # Converts to list tags
tags = "','".join(tags)            # converts to string of values separted by ','

And change your lines:

"where NAME='%s'"\

to

"where NAME IN '"'%s'"\     # Careful to include the single quotes

and

"and REQUEST=2 and TS between TIMESTAMP'%s' and TIMESTAMP'%s'" % (tags['TAGcsv'], start, end)

to

"and REQUEST=2 and TS between TIMESTAMP'%s' and TIMESTAMP'%s'" %

(tags, start, end)

Then it should work for you!