This one is a pydataset.
Yes.
I don’t like the for loops because I end up with a line for each of the values, rather than a line for each row.
That's... unexpected. Can you show me the whole thing ? Where are you doing this ?
That's not supposed to happen.
1 Like
sampleHeaders = ['Line', 'Shift', 'Hours', 'Gross', 'Net', 'Timestamp']
sampleData = [['K01', 1, 12, 200, 150, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
['K02', 1, 12, 202, 152, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
['K03', 1, 12, 203, 153, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
['K01', 2, 12, 200, 150, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
['K02', 2, 12, 208, 155, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
['K03', 2, 12, 209, 157, system.date.parse('2022-09-25', 'yyyy-MM-DD')]
]
datasetIn = system.dataset.toPyDataSet(system.dataset.toDataSet(sampleHeaders, sampleData))
for row in datasetIn:
row
>>>
<Row:6 columns>
<Row:6 columns>
<Row:6 columns>
<Row:6 columns>
<Row:6 columns>
<Row:6 columns>
>>>
or:
for row in datasetIn:
for value in row:
value
>>>
u'K01'
1
12
200
150
Tue Jan 25 00:00:00 EST 2022
u'K02'
1
12
202
152
Tue Jan 25 00:00:00 EST 2022
u'K03'
1
12
203
153
Tue Jan 25 00:00:00 EST 2022
u'K01'
2
12
200
150
Tue Jan 25 00:00:00 EST 2022
u'K02'
2
12
208
155
Tue Jan 25 00:00:00 EST 2022
u'K03'
2
12
209
157
Tue Jan 25 00:00:00 EST 2022
Well obviously, if you're nesting loops, you'll get cell values instead of rows.
click for scripts
sampleHeaders = ['Line', 'Shift', 'Hours', 'Gross', 'Net', 'Timestamp']
sampleData = [['K01', 1, 12, 200, 150, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
['K02', 1, 12, 202, 152, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
['K03', 1, 12, 203, 153, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
['K01', 2, 12, 200, 150, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
['K02', 2, 12, 208, 155, system.date.parse('2022-09-25', 'yyyy-MM-DD')],
['K03', 2, 12, 209, 157, system.date.parse('2022-09-25', 'yyyy-MM-DD')]
]
data = system.dataset.toPyDataSet(system.dataset.toDataSet(sampleHeaders, sampleData))
for row in data:
row
"#"*10
for row in data:
print repr(row)
"#"*10
print '\n'.join(map(repr, data))
"#"*10
print '\n'.join(repr(row) for row in data)
click for results
[u'K01', 1, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022]
[u'K02', 1, 12, 202, 152, Tue Jan 25 00:00:00 CET 2022]
[u'K03', 1, 12, 203, 153, Tue Jan 25 00:00:00 CET 2022]
[u'K01', 2, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022]
[u'K02', 2, 12, 208, 155, Tue Jan 25 00:00:00 CET 2022]
[u'K03', 2, 12, 209, 157, Tue Jan 25 00:00:00 CET 2022]
'##########'
[u'K01', 1, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022]
[u'K02', 1, 12, 202, 152, Tue Jan 25 00:00:00 CET 2022]
[u'K03', 1, 12, 203, 153, Tue Jan 25 00:00:00 CET 2022]
[u'K01', 2, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022]
[u'K02', 2, 12, 208, 155, Tue Jan 25 00:00:00 CET 2022]
[u'K03', 2, 12, 209, 157, Tue Jan 25 00:00:00 CET 2022]
'##########'
[u'K01', 1, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022]
[u'K02', 1, 12, 202, 152, Tue Jan 25 00:00:00 CET 2022]
[u'K03', 1, 12, 203, 153, Tue Jan 25 00:00:00 CET 2022]
[u'K01', 2, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022]
[u'K02', 2, 12, 208, 155, Tue Jan 25 00:00:00 CET 2022]
[u'K03', 2, 12, 209, 157, Tue Jan 25 00:00:00 CET 2022]
'##########'
[u'K01', 1, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022]
[u'K02', 1, 12, 202, 152, Tue Jan 25 00:00:00 CET 2022]
[u'K03', 1, 12, 203, 153, Tue Jan 25 00:00:00 CET 2022]
[u'K01', 2, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022]
[u'K02', 2, 12, 208, 155, Tue Jan 25 00:00:00 CET 2022]
[u'K03', 2, 12, 209, 157, Tue Jan 25 00:00:00 CET 2022]
If you want a script to print datasets like @JordanCClark does, I have this in my library:
def to_string(ds):
headers = ds.getColumnNames()
widths = [len(str(ds.getRowCount()))] + [
max(
max(len(str(value)) for value in ds.getColumnAsList(c)),
len(headers[c])
) for c in range(ds.getColumnCount())
]
headers = [""] + headers
ds = system.dataset.toPyDataSet(ds)
return (
" | ".join(h.ljust(w) for h, w in zip(headers, widths)) + '\n'
+ "-" * (sum(widths) + 3*(len(widths)-1)) + '\n'
+ '\n'.join(" | ".join(str(v).ljust(w) for v, w in zip([i] + [r for r in row], widths)) for i, row in enumerate(ds))
)
def print_ds(ds):
print to_string(ds)
It's not pretty, but it can be useful.
note that it print datasets, not pydatasets.
edit: I'm a bit ashamed of showing this, when I spend half my time badgering people about readability and clean code :X
1 Like
If you're wondering about what I use...
1 Like
I was getting an error of keyerror:‘12’ when I tried to implements this.
Well I needed to have 16 values instead of 12 in my append for my query.
Query had 16 columns, not 12.
Other than that, it went great.
params={"machine":-1,"materialPar":-1}
queryData = system.dataset.toPyDataSet(system.db.runNamedQuery("MainFolder/SecondFolder/MyQuery",params))
value=queryData
datasetIn = system.dataset.toPyDataSet(value)
These four lines helped me test with my query.
I should use one of those functions for printings datasets. Thanks again.
I don’t know if I will do that today.
Oh no, someone made gross a string in a table.
This is terrible.
I used int() to make the string an int for summing.
Then I used str() to append a string again.
Wait, what ?
queryData is a pyDataSet.
value = queryData so value is also a pyDataSet
then you convert value to a... pyDataSet, to assign it to yet another variable !
The whole thing is equivalent to:
params={"machine":-1,"materialPar":-1}
datasetIn = system.dataset.toPyDataSet(system.db.runNamedQuery("MainFolder/SecondFolder/MyQuery",params))
Actually it's not equivalent, as I don't think toPyDataSet works if you pass it a pyDataSet.
But it's equivalent to what it was supposed to do.
2 Likes
haha
Thanks
Part of it was copied from one spot, and part from another spot.
I will need to go clean it up in my notepad++ file.
I was saving a file that contained my test with data generated for the script console.
Code for testing with a query in the script console.
Finally, it also had code used in the transform.
I am trying to make a report of this too.
I don’t see how to use scripts in the report area though.
Checking videos on it.
Named Queries return a BasicDataset
2 Likes
Oh no @JordanCClark, you didn’t see the depth of my coding sin yet.
Pascal did.
I called topydataset on the query call line and then again after.
My variable was queryData.
I mean that I think Pascal knows that when you call a query it comes in as a dataset normally.
I think he is pointing out that in my specific code though, I wrote for a pydataset to become a pydataset in the sample I gave from one of my testing subsets of scripts for using in the script console.
Yes, but on this line:
value is already a pyDataSet
2 Likes
I got to the point where I want to make a report on the same query that I wrote the script on.
Is there a way in the report module to do script transforms on your datasource?
Or do I need to use a script datasource that calls the query?
I found it in Scripting Data Source - Report Data
Sorry I pinged you.
Yes, setup up your query in the report, then add a script source after the query (order matters)
queryData = data['nameOfQuery'].getCoreResults()
... do stuff
data['datakeyOfYourChoice'] = transformedData
Do not use data as a variable name in your script, this is reserved for the data sources.
1 Like
This worked fantastically.
I copied the table I had made before, used it again with this new key, and took only like 5 minutes.
Thanks for the note there too!
Well, the integers that I had set to None now show on the report as <NA> instead of a blank space.
1 Like