Help inserting rows of totals to a dataset from a query

I didn’t get that last part.

foo = ['a', 'b', 'c']
bar = [1, 2, 3, 4]
mapTest=("b00",foo,bar)
print mapTest

got me:

('b00', ['a', 'b', 'c'], [1, 2, 3, 4])


ah I forgot the map.

[('a', 1), ('b', 2), ('c', 3), (None, 4)]


What was that way to print pythondatasets similar to:

print (list(map(list,datasetIn)))

Except with new lines for each list in the lists?
@pascal.fragnoud ?

Frankly I’d just do

for row in pydataset:
    row
# or print repr(row)

if you really want a one liner (or have a single print, which is faster)

print '\n'.join(repr(row) for row in data)

or, if you really want the map:

print '\n'.join(map(repr, data))
2 Likes

I just wanna say... I've become a huge fan of your simplifications

1 Like

Keep up the good work!

print '\n'.join(map(repr, datasetIn))
<Row:6 columns>
<Row:6 columns>
<Row:6 columns>
<Row:6 columns>
<Row:6 columns>
<Row:6 columns>

It is close.

huh, what’s your datasetIn ? Is that a pyDataSet ?

image

1 Like

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.
image

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

@dkhayes117

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