DEV Community

InterSystems Developer for InterSystems

Posted on • Originally published at community.intersystems.com

2 steps to display cache tables data on the web by using jquery datatable and embedded python

Hi, Community,

This post will demonstrate how to display data on the web by using Embedded Python , Python Flask Web Framework and Jquery datatable

Image description

We will display processes from %SYS.ProcessQuery table.

 

Step 1: Add table to HTML page and write below javascript code to display passed data from app.py :

HTML

  <table id="myTable" class="table table-bordered table-striped">                 
   </table>
Enter fullscreen mode Exit fullscreen mode

Javascript

```        
$(document).ready(function() {
      // parse the data to local variable passed from app.py file
      let my_data = JSON.parse('{{ my_data | tojson }}');
      let my_cols = JSON.parse('{{ my_cols | tojson }}');

      $('#myTable').DataTable( {
          "data": my_data,
          "columns": my_cols,"} );
     } ); 
```
Enter fullscreen mode Exit fullscreen mode

 

Step 2: Create python function in app.py file and define the route as defined below e.g we are creating processes function and defining /processes in the route:

App.py

from flask import Flask, jsonify, render_template
import iris

app = Flask(__name__) 

@app.route("/")
def index():   
    #to render main index page
    return render_template('index.html')

@app.route("/processes")
 def processes():
   #Define sql statement 
   mySql = '''
        SELECT ID, NameSpace, Routine, LinesExecuted, GlobalReferences,
        state, PidExternal, UserName, ClientIPAddress FROM %SYS.ProcessQuery ORDER BY NameSpace desc        
        '''
   #Calling embedded python iris.sql.exec class to get result set
   resultSet = iris.sql.exec(mySql)
   #Get dataframe by calling resultset dataframe function 
   dataframe = statement.dataframe()
   #Convert and data to Json by using to_json dataframe method and json loads function
   my_data=json.loads(dataframe.to_json(orient="split"))["data"]
   #Get columns details
   my_cols=[{"title": str(col)} for col in json.loads(df.to_json(orient="split"))["columns"]]  
   #render html by passing my_data and my_cols variables which will be used to generate datatable
   return render_template('tablesdata.html',  my_data = my_data, my_cols = my_cols)    
Enter fullscreen mode Exit fullscreen mode

That's it.  By using these 2 steps cache table data can be display on the web.

Read related documentations Embedded Python Overview.

For more details please review my openexchange iris-python-apps application

 

Thanks

 

Top comments (0)