# Reading values from database and converting it using python

**URL:** <https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508>\
**Category:** Maker\
**Created:** [March 16, 2021, 5:58am UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508 "2021-03-16T05:58:19Z")\
**Posts on this page:** 1\
**Showing post:** 12

<div class="post-metadata">

**Author:** ![matthew.ayre](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matthew.ayre/32/19499_2.png) [@matthew.ayre](https://forum.inductiveautomation.com/u/matthew.ayre)\
**Post date:** [March 16, 2021, 8:59pm UTC](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508/12 "2021-03-16T20:59:32Z")

</div>

Looks like @JordanCClark beat me to it 😄

However, if you do want to go down the XY chart route here is what I have created:

 ![HeatMap](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/a/2a0dfcd7770d9ee13fd4ce7249251a312970d3b2.jpeg)

> **xAxis Props**
>
> ```auto
> [
> {
> "name": "col",
> "label": {
> "enabled": false,
> "text": "Time",
> "color": ""
> },
> "inversed": false,
> "visible": false,
> "tooltip": {
> "enabled": true,
> "text": "",
> "cornerRadius": 3,
> "pointerLength": 4,
> "background": {
> "color": "",
> "opacity": 1
> }
> },
> "render": "category",
> "category": {
> "break": {
> "enabled": false,
> "startCategory": "",
> "endCategory": "",
> "size": 0.05
> }
> },
> "date": {
> "baseInterval": {
> "enabled": false,
> "timeUnit": "hour",
> "count": 1,
> "skipEmptyPeriods": false
> },
> "range": {
> "max": "",
> "min": "",
> "useStrict": false
> },
> "break": {
> "enabled": false,
> "startDate": "",
> "endDate": "",
> "size": 0.05
> },
> "inputFormat": "yyyy-MM-dd kk:mm:ss",
> "format": "M/d"
> },
> "value": {
> "range": {
> "max": "",
> "min": "",
> "useStrict": false
> },
> "logarithmic": false,
> "break": {
> "enabled": false,
> "startValue": 0,
> "endValue": 100,
> "size": 0.05
> },
> "format": "#,###.##"
> },
> "appearance": {
> "opposite": false,
> "inside": false,
> "labels": {
> "color": "",
> "opacity": 1
> },
> "grid": {
> "color": "",
> "opacity": 1,
> "dashArray": "",
> "minDistance": 60,
> "position": 0.5
> },
> "font": {
> "size": "",
> "weight": 500
> }
> }
> }
> ]
> 
> ```

> **yAxis Props**
>
> ```auto
> [
> {
> "name": "row",
> "label": {
> "enabled": false,
> "text": "Process Temp",
> "color": ""
> },
> "inversed": false,
> "visible": false,
> "tooltip": {
> "enabled": true,
> "text": "",
> "cornerRadius": 3,
> "pointerLength": 4,
> "background": {
> "color": "",
> "opacity": 1
> }
> },
> "render": "category",
> "category": {
> "break": {
> "enabled": false,
> "startCategory": "",
> "endCategory": "",
> "size": 0.05
> }
> },
> "date": {
> "baseInterval": {
> "enabled": false,
> "timeUnit": "hour",
> "count": 1,
> "skipEmptyPeriods": false
> },
> "range": {
> "max": "",
> "min": "",
> "useStrict": false
> },
> "break": {
> "enabled": false,
> "startDate": "",
> "endDate": "",
> "size": 0.05
> },
> "inputFormat": "yyyy-MM-dd kk:mm:ss",
> "format": "M/d/yyyy HH:mm:ss"
> },
> "value": {
> "range": {
> "max": "",
> "min": "",
> "useStrict": false
> },
> "logarithmic": false,
> "break": {
> "enabled": false,
> "startValue": 0,
> "endValue": 100,
> "size": 0.05
> },
> "format": "#,###.##"
> },
> "appearance": {
> "opposite": false,
> "inside": false,
> "labels": {
> "color": "",
> "opacity": 1
> },
> "grid": {
> "color": "",
> "opacity": 1,
> "dashArray": "",
> "minDistance": null,
> "position": 0.5
> },
> "font": {
> "size": "",
> "weight": 500
> }
> }
> }
> ]
> 
> ```

> **series props**
>
> ```auto
> [
> {
> "name": "data",
> "label": {
> "text": "Process Temp"
> },
> "visible": true,
> "hiddenInLegend": false,
> "defaultState": {
> "visible": true
> },
> "data": {
> "source": "data",
> "x": "col",
> "y": "row"
> },
> "xAxis": "col",
> "yAxis": "row",
> "zIndex": 0,
> "tooltip": {
> "enabled": true,
> "text": "{name}: [bold]{valueY}[/]",
> "cornerRadius": 3,
> "pointerLength": 4,
> "background": {
> "color": "",
> "opacity": 1
> }
> },
> "render": "column",
> "candlestick": {
> "open": {
> "x": "",
> "y": ""
> },
> "high": {
> "x": "",
> "y": ""
> },
> "low": {
> "x": "",
> "y": ""
> },
> "appearance": {
> "fill": {
> "color": "",
> "opacity": 1
> },
> "stroke": {
> "color": "",
> "opacity": 1,
> "width": 1
> },
> "stacked": false,
> "deriveFieldsFromData": {
> "fill": {
> "color": "",
> "opacity": ""
> },
> "stroke": {
> "color": "",
> "opacity": "",
> "width": ""
> }
> },
> "heatRules": {
> "enabled": false,
> "max": "",
> "min": "",
> "dataField": ""
> }
> }
> },
> "column": {
> "open": {
> "x": "",
> "y": ""
> },
> "appearance": {
> "fill": {
> "color": "",
> "opacity": 1
> },
> "stroke": {
> "color": "",
> "opacity": 1,
> "width": 1
> },
> "width": null,
> "height": null,
> "stacked": false,
> "deriveFieldsFromData": {
> "fill": {
> "color": "",
> "opacity": ""
> },
> "stroke": {
> "color": "",
> "opacity": "",
> "width": ""
> }
> },
> "heatRules": {
> "enabled": true,
> "max": "#F40505",
> "min": "#2D10FC",
> "dataField": "val"
> }
> }
> },
> "line": {
> "open": {
> "x": "",
> "y": ""
> },
> "appearance": {
> "connect": true,
> "tensionX": 1,
> "tensionY": 1,
> "minDistance": 0.5,
> "stroke": {
> "width": 3,
> "opacity": 1,
> "color": "",
> "dashArray": ""
> },
> "fill": {
> "color": "",
> "opacity": 0
> },
> "bullets": [
> {
> "enabled": false,
> "render": "circle",
> "width": 10,
> "height": 10,
> "label": {
> "text": "{value}",
> "position": {
> "dx": 0,
> "dy": 0
> }
> },
> "tooltip": {
> "enabled": true,
> "text": "{name}: [bold]{valueY}[/]",
> "cornerRadius": 3,
> "pointerLength": 4,
> "background": {
> "color": "",
> "opacity": 1
> }
> },
> "fill": {
> "color": "",
> "opacity": 1
> },
> "stroke": {
> "color": "",
> "width": 1,
> "opacity": 1
> },
> "rotation": 0,
> "deriveFieldsFromData": {
> "fill": {
> "color": "",
> "opacity": ""
> },
> "stroke": {
> "color": "",
> "opacity": "",
> "width": ""
> },
> "rotation": ""
> },
> "heatRules": {
> "enabled": false,
> "max": 100,
> "min": 2,
> "dataField": ""
> }
> }
> ]
> }
> },
> "stepLine": {
> "open": {
> "x": "",
> "y": ""
> },
> "appearance": {
> "connect": true,
> "tensionX": 1,
> "tensionY": 1,
> "minDistance": 0.5,
> "stroke": {
> "width": 3,
> "opacity": 1,
> "color": "",
> "dashArray": ""
> },
> "fill": {
> "color": "",
> "opacity": 0
> },
> "bullets": [
> {
> "enabled": true,
> "render": "circle",
> "width": 10,
> "height": 10,
> "label": {
> "text": "{value}",
> "position": {
> "dx": 0,
> "dy": 0
> }
> },
> "tooltip": {
> "enabled": true,
> "text": "{name}: [bold]{valueY}[/]",
> "cornerRadius": 3,
> "pointerLength": 4,
> "background": {
> "color": "",
> "opacity": 1
> }
> },
> "fill": {
> "color": "",
> "opacity": 1
> },
> "stroke": {
> "color": "",
> "width": 1,
> "opacity": 1
> },
> "rotation": 0,
> "deriveFieldsFromData": {
> "fill": {
> "color": "",
> "opacity": ""
> },
> "stroke": {
> "color": "",
> "opacity": "",
> "width": ""
> },
> "rotation": ""
> },
> "heatRules": {
> "enabled": false,
> "max": 100,
> "min": 2,
> "dataField": ""
> }
> }
> ]
> }
> }
> }
> ]
> 
> ```

> **Named Query for Binding**
>
> Binding for dataSources property in chart (I used MSSQL), also see the script transform.
> 
> ```auto
> SELECT Data_0,
> Data_1,
> Data_2,
> Data_3,
> Data_4,
> Data_5,
> Data_6,
> Data_7,
> Data_8,
> Data_9,
> Data_10,
> Data_11,
> Data_12,
> Data_13,
> Data_14,
> Data_15,
> Data_16,
> Data_17,
> Data_18,
> Data_19,
> Data_20,
> Data_21,
> Data_22,
> Data_23,
> Data_24,
> Data_25,
> Data_26,
> Data_27,
> Data_28,
> Data_29,
> Data_30,
> Data_31
> FROM heatmap
> WHERE id = :id
> 
> ```

> **Binding Script Transform**
>
> Script transform for the query binding.
> 
> ```auto
> # Return variable
> dataSources = {}
> 	
> # Formatted data array
> dataArr = []
> 	
> # Convert to pyDataSet as easier to iterate
> pyData = system.dataset.toPyDataSet(value)
> 	
> # Instantiate the row index
> rowId = 0
> 	
> # Loop through the rows
> for row in pyData:
> # Instantiate the column index
> colId = 0
> 		
> # Loop through the columns
> for val in row:
> # Create a dictionary in a format that is easy to recognise by the chart
> data = {
> 'row':rowId,
> 'col':colId,
> 'val':val
> }
> 			
> # Append the data to the data array
> dataArr.append(data)
> 			
> # Iterate the column index
> colId += 1
> 		
> # Iterate the row index
> rowId += 1
> 	
> # Create the return dictionary
> dataSources = {'data':dataArr}
> 	
> # Pass to the component property
> return dataSources
> 
> ```

You may find @JordanCClark solution is better as the chart can appear a bit laggy when rendering the data. But at least this gives you another option if you need it.

If you would like a backup of the project feel free to DM me and I can send it over to you.

---

_[View the full topic](https://forum.inductiveautomation.com/t/reading-values-from-database-and-converting-it-using-python/44508)._