Hello, I am pulling data from a database into a table, and I want the entire row to automatically turn red if the 'status' column contains 'Failed' or 'Error'. This way, the user can immediately see that an error has occurred. Somethin like that:
You will need to add a script transform to the data in the table to transform your row data into a list of dicts for each of the columns, inside which you can add a style
key with the styling you need (e.g. set the backgroundColor
to what you want).
E.g. (value
is assumed to be a list of dicts not a dataset, e.g. if using a named query the return should be set to JSON)
if len(value) > 0:
headers = value[0].keys()
data = []
rowColours = ('#23ef34', '#ff00ff')
for i, row in enumerate(value):
newRow = {header: {
'value': row[header],
'style': {'backgroundColor': rowColours[i % 2]}
} for header in headers}
data.append(newRow)
return data
else:
return []
Thank you for the response, I have only recently started working with databases and tables, can you explain the code to me? I am using system.db.runPrepQuery
and the parameters are coming from another view, which is used for filtering.
You should create a named query, and use it in a query binding, instead of running the query in a script.
Then you can follow the instructions below:
Set the return value of the query to json
instead of auto
or dataset
, and add a script transform that looks something like this:
return [
{
'value': row,
'style': {'backgroundColor': "red" if row['status'] in {'error', 'failed'} else None}
}
for row in value
]
Basically, you can change a row style by changing slightly the data structure you pass to the table.
Instead of having a list of objects, each describing the row's values like this:
[
{'col1': value_of_col1, 'col2': value_of_col2}, # that's row 1
{'col1': value_of_col1, 'col2': value_of_col2}, # that's row 2
....
{'col1': value_of_col1, 'col2': value_of_col2}, # that's row n
]
You add a layer on top of this, putting all that data in the 'value' key of a new object, and adding a 'style' key next to it, like this:
[
{
'value': {'col1': value_of_col1, 'col2': value_of_col2},
'style': {'backgroundColor': "blue", 'fontWeight', "bold"}
},
...
]
Then it's up to you to figure out what styles you want to apply.
This is not possible as I have an array as a parameter that doesn't have a defined length. I'm writing each element of the array as a single string and adding that to the query with + where_step_type + where_gesamtergebnis + .
script
def runAction(self):
# Please wait Icon sichtbar machen
self.getSibling("PleaseWait").meta.visible = True
# defenieren der Parameter für Übergabe
PARAMETER_TESTGRUPPE = self.view.params.PARAMETER_TESTGRUPPE
PARAMETER_CLIENT = self.view.params.PARAMETER_CLIENT
PARAMETER_VIN = self.view.params.PARAMETER_VIN
PARAMETER_MODELL = self.view.params.PARAMETER_MODELL
PARAMETER_EINZELERGEBNIS = self.view.params.PARAMETER_EINZELERGEBNIS
PARAMETER_START_ZEIT = self.view.params.PARAMETER_START_ZEIT
PARAMETER_ENDE_ZEIT = self.view.params.PARAMETER_ENDE_ZEIT
where_step_type = ""
where_gesamtergebnis = ""
PARAMETER_KOMPONENTENBAUM = self.view.params.PARAMETER_KOMPONENTENBAUM
PARAMETER_GESAMTERGEBNIS = self.view.params.PARAMETER_GESAMTERGEBNIS
# Überprüfen, ob etwas im Komponentenbaum ausgewählt ist. Ja: jedes Element als Array mit jeweiligen AND und OR Operatoren als string ausgeben
if PARAMETER_KOMPONENTENBAUM:
where_step_type += "AND ("
where_step_type += " OR ".join(["sr.step_type = '" + element +"'" for element in PARAMETER_KOMPONENTENBAUM])
where_step_type += ")"
# Überprüfen, ob etwas im Gesamtergebnis ausgewählt ist. Ja: jedes Element als Array mit jeweiligen AND und OR Operatoren als string ausgeben
if PARAMETER_GESAMTERGEBNIS:
where_gesamtergebnis += "AND ("
for index, element in enumerate(PARAMETER_GESAMTERGEBNIS):
where_gesamtergebnis += "ur.uut_status = '" + element + "'"
if index < len(PARAMETER_GESAMTERGEBNIS)-1:
where_gesamtergebnis += " OR "
where_gesamtergebnis += ")"
# query zur Datenausgabe einsetzten mit where_step_type und where_gesamtergebnis
query = "SELECT ur.batch_serial_number, ur.station_id, ur.uut_serial_number, ur.updated_date_time, CASE WHEN CHARINDEX('__', sr.step_name) > 0 THEN SUBSTRING(sr.step_name, CHARINDEX('__', sr.step_name) + 2) WHEN CHARINDEX('_ ', sr.step_name) > 0 THEN SUBSTRING(sr.step_name, CHARINDEX('_ ', sr.step_name) + 2) WHEN CHARINDEX('__ ', sr.step_name) > 0 THEN SUBSTRING(sr.step_name, CHARINDEX('__ ', sr.step_name) + 3) ELSE sr.step_name END AS step_name, sr.step_type, ur.uut_status, sr.status, COALESCE(pr.Max_Limit, '') AS Max_Limit, COALESCE(pr.Verify_Values, '') AS Verify_Values, COALESCE(pr.Min_Limit, '') AS Min_Limit, COALESCE(pr.Units, '') AS Units FROM hive_metastore.edl_current.lx_smarttest_uut_result_1_0_0 ur JOIN hive_metastore.edl_current.lx_smarttest_step_result_1_0_0 sr ON ur.id = sr.uut_result JOIN (SELECT step_result AS ID, MAX(CASE WHEN name = 'Max_Limit' THEN data END) AS Max_Limit, MAX(CASE WHEN name = 'Verify_Values' THEN data END) AS Verify_Values, MAX(CASE WHEN name = 'Min_Limit' THEN data END) AS Min_Limit, MAX(CASE WHEN name = 'Units' THEN data END) AS Units FROM hive_metastore.edl_current.lx_smarttest_prop_result_1_0_0 GROUP BY step_result) AS pr ON sr.id = pr.ID WHERE (? = '' OR ? IS NULL OR CONCAT(SUBSTRING(ur.uut_serial_number, 12, 6)) = ? OR ur.uut_serial_number = ?) AND (? = '' OR ? IS NULL OR ur.station_id = ?) AND (? = '' OR ? IS NULL OR ur.batch_serial_number = ?) AND (? = '' OR ? IS NULL OR CONCAT(SUBSTRING(ur.uut_serial_number, 4, 1), SUBSTRING(ur.uut_serial_number, 8, 1), SUBSTRING(ur.uut_serial_number, 5, 3)) = ?) AND (? = '' OR ? IS NULL OR sr.status = ?) AND(? = '' AND ? = '' OR ? IS NULL AND ? IS NULL OR ur.updated_date_time BETWEEN ? AND ?)" + where_step_type + where_gesamtergebnis + " AND sr.step_type IS NOT NULL AND sr.step_type <> '' AND sr.step_type NOT IN ('NI_Flow_Case') AND sr.step_type NOT IN ('NI_Flow_DoWhile') AND sr.step_type NOT IN ('NI_Flow_Else') AND sr.step_type NOT IN ('NI_Flow_ElseIf') AND sr.step_type NOT IN ('NI_Flow_End') AND sr.step_type NOT IN ('NI_Flow_If') AND sr.step_type NOT IN ('NI_Flow_Select') AND sr.step_type NOT IN ('NI_Flow_While') AND sr.step_type NOT IN ('NI_Wait') AND sr.step_type NOT IN ('Battery_Adaptor_Log') AND sr.step_type NOT IN ('Add_AllStepDetails') AND sr.step_type NOT IN ('Add_TestDetails') AND sr.step_type NOT IN ('CCS_Start_Stop') AND sr.step_type NOT IN ('CheckForPreviousResults') AND sr.step_type NOT IN ('Clear_DTCs') AND sr.step_type NOT IN ('Wait_With_Prompt') AND sr.step_type NOT IN ('Config_Retest_Updated') AND sr.step_type NOT IN ('Create_Report_Path') AND sr.step_type NOT IN ('Create_Shop_Summary') AND sr.step_type NOT IN ('Get_DTCs_DB') AND sr.step_type NOT IN ('Get_POI_Build_Data') AND sr.step_type NOT IN ('Get_Product_Line') AND sr.step_type NOT IN ('Get_Test_Location') AND sr.step_type NOT IN ('Get_VIN') AND sr.step_type NOT IN ('Get_Vehicle_Options_Updated') AND sr.step_type NOT IN ('Get_Verify_TLAs') AND sr.step_type NOT IN ('Goto') AND sr.step_type NOT IN ('Initialize_Monitor') AND sr.step_type NOT IN ('NI_Notification') AND sr.step_type NOT IN ('Process_Sequence_Names') AND sr.step_type NOT IN ('SequenceCall') AND sr.step_type NOT IN ('Call_Sub_Sequence') AND sr.step_type NOT IN ('Call_Sub_System_Sequence') AND sr.step_type NOT IN ('Call_Test_Location_Sequence') AND sr.step_type NOT IN ('Stop_Periodic_CAN_Frame') AND sr.step_type NOT IN ('EDAT_Statement') AND sr.step_type NOT IN ('Action') AND sr.step_type NOT IN ('AdditionalResults') AND sr.step_type NOT IN ('ClearDTCUDS') AND sr.step_type NOT IN ('Get_Apps_TLAs') AND sr.step_type NOT IN ('Label') AND sr.step_type NOT IN ('Load_CAN_Database') AND sr.step_type NOT IN ('MessagePopup') AND sr.step_type NOT IN ('Retest_Multiple_Failed_SubSystem') AND sr.step_type NOT IN ('Start_CAN_Chn_Monitor') AND sr.step_type NOT IN ('Terminate_All_Monitors') AND sr.step_type NOT IN ('Tester_Present') AND sr.step_type NOT IN ('Update_GUI_Seq_List') ORDER BY ur.updated_date_time"
# Parameter in richtiger Reihenfolge definieren
params =[PARAMETER_VIN, PARAMETER_VIN, PARAMETER_VIN, PARAMETER_VIN, PARAMETER_CLIENT, PARAMETER_CLIENT, PARAMETER_CLIENT, PARAMETER_TESTGRUPPE, PARAMETER_TESTGRUPPE, PARAMETER_TESTGRUPPE, PARAMETER_MODELL, PARAMETER_MODELL, PARAMETER_MODELL, PARAMETER_EINZELERGEBNIS, PARAMETER_EINZELERGEBNIS, PARAMETER_EINZELERGEBNIS, PARAMETER_START_ZEIT, PARAMETER_ENDE_ZEIT, PARAMETER_START_ZEIT, PARAMETER_ENDE_ZEIT, PARAMETER_START_ZEIT, PARAMETER_ENDE_ZEIT]
#Prep Query ausführen und in result schreiben
result = system.db.runPrepQuery(query, params, 'LX_EDL_ST')
system.perspective.print(result)
for row in range(getRowCount()):
row_object = {}
system.perspective.print(row_object)
# Please wait Icon unsichtbar machen
self.getSibling("PleaseWait").meta.visible = False
# Daten in die Tabelle schreiben
self.props.data = result
Well then still use runPrepQuery
and make the list of dict from the results, it should work the in pretty much the same way. You may have to process the data structure you get, but it shouldn't be too hard.
But I must say that query looks like a red flag to me.
Here it is reformatted, if someone wants to take a crack at it - I really don't have time for this:
query
query = """
SELECT
ur.batch_serial_number,
ur.station_id,
ur.uut_serial_number,
ur.updated_date_time,
CASE
WHEN CHARINDEX('__', sr.step_name) > 0 THEN SUBSTRING(sr.step_name, CHARINDEX('__', sr.step_name) + 2)
WHEN CHARINDEX('_ ', sr.step_name) > 0 THEN SUBSTRING(sr.step_name, CHARINDEX('_ ', sr.step_name) + 2)
WHEN CHARINDEX('__ ', sr.step_name) > 0 THEN SUBSTRING(sr.step_name, CHARINDEX('__ ', sr.step_name) + 3)
ELSE sr.step_name
END AS step_name,
sr.step_type,
ur.uut_status,
sr.status,
COALESCE(pr.Max_Limit, '') AS Max_Limit,
COALESCE(pr.Verify_Values, '') AS Verify_Values,
COALESCE(pr.Min_Limit, '') AS Min_Limit,
COALESCE(pr.Units, '') AS Units
FROM hive_metastore.edl_current.lx_smarttest_uut_result_1_0_0 ur
JOIN hive_metastore.edl_current.lx_smarttest_step_result_1_0_0 sr
ON ur.id = sr.uut_result
JOIN (
SELECT
step_result AS ID,
MAX(CASE
WHEN name = 'Max_Limit' THEN data
END) AS Max_Limit,
MAX(CASE
WHEN name = 'Verify_Values' THEN data
END) AS Verify_Values,
MAX(CASE
WHEN name = 'Min_Limit' THEN data
END) AS Min_Limit,
MAX(CASE
WHEN name = 'Units' THEN data
END) AS Units
FROM hive_metastore.edl_current.lx_smarttest_prop_result_1_0_0
GROUP BY step_result
) AS pr
ON sr.id = pr.ID
WHERE (? = '' OR ? IS NULL OR CONCAT(SUBSTRING(ur.uut_serial_number, 12, 6)) = ? OR ur.uut_serial_number = ?)
AND (? = '' OR ? IS NULL OR ur.station_id = ?)
AND (? = '' OR ? IS NULL OR ur.batch_serial_number = ?)
AND (? = '' OR ? IS NULL OR CONCAT(SUBSTRING(ur.uut_serial_number, 4, 1), SUBSTRING(ur.uut_serial_number, 8, 1), SUBSTRING(ur.uut_serial_number, 5, 3)) = ?)
AND (? = '' OR ? IS NULL OR sr.status = ?)
AND (? = '' AND ? = '' OR ? IS NULL AND ? IS NULL OR ur.updated_date_time BETWEEN ? AND ?)""" + where_step_type + where_gesamtergebnis + """ AND sr.step_type IS NOT NULL AND sr.step_type <> ''
AND sr.step_type NOT IN ('NI_Flow_Case')
AND sr.step_type NOT IN ('NI_Flow_DoWhile')
AND sr.step_type NOT IN ('NI_Flow_Else')
AND sr.step_type NOT IN ('NI_Flow_ElseIf')
AND sr.step_type NOT IN ('NI_Flow_End')
AND sr.step_type NOT IN ('NI_Flow_If')
AND sr.step_type NOT IN ('NI_Flow_Select')
AND sr.step_type NOT IN ('NI_Flow_While')
AND sr.step_type NOT IN ('NI_Wait')
AND sr.step_type NOT IN ('Battery_Adaptor_Log')
AND sr.step_type NOT IN ('Add_AllStepDetails')
AND sr.step_type NOT IN ('Add_TestDetails')
AND sr.step_type NOT IN ('CCS_Start_Stop')
AND sr.step_type NOT IN ('CheckForPreviousResults')
AND sr.step_type NOT IN ('Clear_DTCs')
AND sr.step_type NOT IN ('Wait_With_Prompt')
AND sr.step_type NOT IN ('Config_Retest_Updated')
AND sr.step_type NOT IN ('Create_Report_Path')
AND sr.step_type NOT IN ('Create_Shop_Summary')
AND sr.step_type NOT IN ('Get_DTCs_DB')
AND sr.step_type NOT IN ('Get_POI_Build_Data')
AND sr.step_type NOT IN ('Get_Product_Line')
AND sr.step_type NOT IN ('Get_Test_Location')
AND sr.step_type NOT IN ('Get_VIN')
AND sr.step_type NOT IN ('Get_Vehicle_Options_Updated')
AND sr.step_type NOT IN ('Get_Verify_TLAs')
AND sr.step_type NOT IN ('Goto')
AND sr.step_type NOT IN ('Initialize_Monitor')
AND sr.step_type NOT IN ('NI_Notification')
AND sr.step_type NOT IN ('Process_Sequence_Names')
AND sr.step_type NOT IN ('SequenceCall')
AND sr.step_type NOT IN ('Call_Sub_Sequence')
AND sr.step_type NOT IN ('Call_Sub_System_Sequence')
AND sr.step_type NOT IN ('Call_Test_Location_Sequence')
AND sr.step_type NOT IN ('Stop_Periodic_CAN_Frame')
AND sr.step_type NOT IN ('EDAT_Statement')
AND sr.step_type NOT IN ('Action')
AND sr.step_type NOT IN ('AdditionalResults')
AND sr.step_type NOT IN ('ClearDTCUDS')
AND sr.step_type NOT IN ('Get_Apps_TLAs')
AND sr.step_type NOT IN ('Label')
AND sr.step_type NOT IN ('Load_CAN_Database')
AND sr.step_type NOT IN ('MessagePopup')
AND sr.step_type NOT IN ('Retest_Multiple_Failed_SubSystem')
AND sr.step_type NOT IN ('Start_CAN_Chn_Monitor')
AND sr.step_type NOT IN ('Terminate_All_Monitors')
AND sr.step_type NOT IN ('Tester_Present')
AND sr.step_type NOT IN ('Update_GUI_Seq_List')
ORDER BY ur.updated_date_time
"""
Side note: Why not "merge" all the sr.step_type NOT IN ...
into just one condition ? That's what in/not in
is for. Doesn't solve anything about the current issue, but it would make it a bit more readable.
edit: I forgot I have this in my lib, I guess you can use it:
def to_json(ds):
"""
Transform a dataset into a list of dictionnaries.
"""
keys = ds.columnNames
return [dict(zip(keys, row)) for row in system.dataset.toPyDataSet(ds)]
Interesting, I didn't know the value could be a dict which becomes the column values. Means less style objects and likely more efficient, I'll have to remember this!