Access PI Library Tables

[williamhobbs]

Feature request

Abstract

It would be great to be able to access Tables from an Asset Framework using PIconnect. I'm not sure if this is directly relevant to the way PIconnect uses the SDK, but something like this: https://pisquare.osisoft.com/s/question/0D51I00004UHiLGSA1/working-with-tables-using-the-pi-af-sdk.

Motivation and summary

Sometimes metadata relevant for PI data are stored in AF Tables. It would be great to be able to access them with PIconnect.

Suggested solution

See https://pisquare.osisoft.com/s/question/0D51I00004UHiLGSA1/working-with-tables-using-the-pi-af-sdk. There's also the Web API, but maybe that isn't relevant? https://docs.aveva.com/bundle/pi-web-api-reference/page/help/controllers/table/actions/getdata.html

Rejected options

I think a custom function using the Web API (last link above) would work as an alternative.

[Hugovdberg]

this would certainly be a great addition to the package, I sometimes use these tables as well. It is relatively simple to add, as the following code snippet shows:

import PIconnect as PI

table_name = 'TableName'
with PI.PIAFDatabase() as db:
    table = db.database.Tables.get_Item(table_name)  # type: ignore
    columns = [col.ColumnName for col in table.Table.Columns]
    data = pd.DataFrame(
        [{col: row[col] for col in columns} for row in table.Table.Rows]
    )

[Hugovdberg]

For now you could also use the following function in your code to make your code a little cleaner:

import pandas as pd
import PIconnect as PI


def download_library_table(db: PI.PIAFDatabase, table_name: str) -> pd.DataFrame:
    table = db.database.Tables.get_Item(table_name)  # type: ignore
    columns = [col.ColumnName for col in table.Table.Columns]
    return pd.DataFrame(
        [{col: row[col] for col in columns} for row in table.Table.Rows]
    )

and then use it as

with PI.PIAFDatabase() as db:
    table_data = download_library_table(db, table_name)

[williamhobbs]

Thanks!

[williamhobbs]

I think this:

    table = db.database.Tables.get_Item("REDACTED")  # type: ignore

should be:

    table = db.database.Tables.get_Item(table_name)

So, I ended up with:

import pandas as pd
import PIconnect as PI


def download_library_table(db: PI.PIAFDatabase, table_name: str) -> pd.DataFrame:
    table = db.database.Tables.get_Item(table_name)
    columns = [col.ColumnName for col in table.Table.Columns]
    return pd.DataFrame(
        [{col: row[col] for col in columns} for row in table.Table.Rows]
    )

and I use it like:

table_name='ExampleTableName' # <-- change this
af_server_name = 'ExampleServerName' # <-- change this
with PI.PIAFDatabase(af_server_name) as db:
    table_data = download_library_table(db, table_name)

table_data.head() # preview table

[edit - I removed print(database.server_name), which had been left in by accident]

[Hugovdberg]

I'm sorry, copy-paste error.. I fixed my post :⁠-⁠)

[Hugovdberg]

I added a tables property to the PIAFDatabase class, that provides this functionality directly.