Convert dataset to string

Ignition
1

I’m trying to convert a Python dataset (returned from a SQL query) into a string of comma separated variables. The following code works:data = system.db.runQuery("SELECT Number FROM MyTable") for row in data: csvString = csvString + row[0] + "," #Remove the final comma. csvString = csvString[:-1]Is there a more elegant ‘Pythonesque’ way of doing this? I tried using ‘join’, but this only works on lists, not on datasets.

2

A couple of ideas—

  1. I tend to use += when building strings like this, i.e. csvString += row[0] + “,”. I’m not sure if it runs faster, but it’s less typing and looks better.

  2. You could concatenate Number with a comma in the query so the loop instruction was just csvString+=row[0].

3

What database are you using? You may be able to do the formatting in the query. You could also have the RDBMS system do it (ie T-Code in MSSQL) instead of the Ignition gateway or client.

For example, the MySQL Manual SELECT gives an example that outputs a CSV file. I suspect that the following code could be used without the “INTO OUTFILE” portion of the query.

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;

4

Thanks for the ideas. What I’m trying to do is convert data from a dataset into a list of comma separated variables in a string e.g. 1,2,3 so that I can then use the string in another SQL query, likeUPDATE MyTable SET Status='Complete' WHERE ID IN (1,2,3) I know I could embed the first query in the second as a sub-query, but I also need to use the IDs (1,2,3 in the above example) to create new records in another table. For this I need to create the SQL query by iterating through the dataset.

I just wondered if there was a ‘magic’ Python one-liner which would translate data in a dataset into a csv string. What I’ve got works, but unless I push the boundaries with Python I’m not going to expand my limited knowledge :slight_smile:

5

Al,
Try this:

groupConcat({Root Container.Table.data}, "FieldName", ", ")

I use it frequently in Dynamic Properties. (it is described in the Ignition Manual under “Aggregates”)

1 Like
6

ISDProj,

That is exactly what I need, except it has to be a Python equivalent to run in a script, rather than an expression function.

7

Can’t you simply use system.dataset.dataSetToCSV()?

8

When did you sneak that function in there ? :laughing:

That certainly fits the bill - it’s a pity there isn’t a PyDataSet version as well.

9

I think it has been in Ignition all along, but it wasn’t in FactoryPMI. You can convert a PyDataSet to a DataSet quite readily.