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?

score 0 · Answer 1 · answered Dec 16 '22 at 19:45

0

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"")"\

answered Dec 16 '22 at 19:45

Madgui

405
2
10

are you sure the following syntax tag = (""tag1"", ""tag2""). It seems to be not correct – niccolo_guazz Dec 19 '22 at 15:59
It's IN keyword, not =(equal). I confirm this syntax works: select NAME,TS,VALUE from HISTORY where NAME IN ('NetworkUtilizationVals', 'AverageCPUTimeVals', 'AvailableMemoryVals') and PERIOD = 300*10 and REQUEST = 2 and REQUEST=2 and TS between TIMESTAMP'2023-01-01 00:00:00' and TIMESTAMP'2023-01-02 00:00:00' – Madgui Jan 24 '23 at 16:18

Lee G · Answer 2 · 2023-04-20T07:35:32.130

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!

Get multiple variable Aspentech infoplus 21

2 Answers2