Oracle, JSON and Python

donci · July 2017

What do I have to do in order to make testdb.js getting json response from the python script?

I don't really understand which server/framework should I use and why, so please explain it for a beginner in web-programming.


import cx_Oracle
import json

# Array of database columns which should be read and sent back to DataTables
_columns = ['L', 'col1', 'col2', 'col3', 'col4' ]
# Indexed column (used for fast and accurate table cardinality)
_indexColumn = "L"
# DB table to use
_sTable = "X.CATEGORY"
_dbinfo= dict(
ip = 'localhost',
port = 1521,
SID = 'xe',
user   = "myuser",
passwd = "mypass",
)
class DataTablesServer:
    def __init__(self):
        self.db = cx_Oracle.connect(_dbinfo["user"], _dbinfo["passwd"], cx_Oracle.makedsn(_dbinfo["ip"], _dbinfo["port"], _dbinfo["SID"]))
        self.runq = self.runQueries()
    def runQueries(self):
        cursormeta = self.db.cursor()
        cursormeta.execute("SELECT * FROM "+ _sTable)
        ## collect all the data (fetch -> result is a list of tuples)
        res_tuple_list = cursormeta.fetchall()
        json_res = json.dumps( {  "draw":1, "recordsTotal":100, "recordsFiltered":100,   "data":  res_tuple_list[:400] } , ensure_ascii=False, encoding="latin-1")
        print(json.JSONEncoder().encode(json_res))
# Perform the server-side actions for DataTables
dtserver = DataTablesServer()

server_processing2.py

<html>
<head>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
<script src="testdb.js"></script>
</head>
<body>
<table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>col1</th>
                <th>col2</th>
                <th>col3</th>                
            <th>col4</th>
                <th>col5</th>   
        </tr>
        </thead>
    </table>
</body> 
</html>

testdb.js:

$(document).ready(function() {
    $('#example').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": "server_processing2.py"
    } );
} );

tangerine · July 2017

I can't help you with Python, but this example shows the components of a server-side process with HTML, JS and PHP:
https://datatables.net/examples/server_side/simple.html

kthorngren · July 2017

Is there a reason you want to you Python versus the PHP code that is already available for Datatables. This page has example code:
https://github.com/DataTables/DataTables/tree/master/examples/server_side/scripts

Do you have restrictions to use your corporate standard web environment/servers? If you are restricted then you will need to determine how that environment can run Python scripts.

If you have the ability to use something other than your corporate standard then you can use a Python web framework. Choosing the web framework or how to run Pyhton scripts from your webserver is beyond the scope of this forum.

I mentioned to you, in this thread, that cherrypy is fairly easy to get started with and provided a snippet of code as an example.

Basic steps to modify my example:
1. Rename the device_types function to test.
2. The test function would return your html document including the JS. Put the document into the public/html directory.
3. Rename the manage_device_types function to get_oracle.
4. Change your ajax config to "ajax": "get_oracle"
5. Take your __init__ and runQueries functions, combine them (remove self.) and place them in the get_oracle function.
6. In the get_oracle function add the line return json_res.
7. Change the class from Testbed to Website.
8. Change line 33 to webapp = Website``. 9. Run the python script then browse tohttp://localhost:8000/test`

There are probably steps I missed but the above should get you close.

There are other types of frameworks like Flask and Django but they require more up front setup. I've seen other posts on the forum from people using Flask so you may want to search if you don't like cherrypy.

Kevin

donci · July 2017

Hallo Kevin,

Thank you very much, your script helped a lot. You are right that my question was a little bit beyond the scope of this forum, sorry for that.

kthorngren · July 2017

Great, good luck. Feel free to post any questions and I'll try to help with the Python side. Don't think there are many regulars on the forum using Python with Datatables.

Kevin

donci · August 2017

Hi! I think I am getting there, but now I have another isse: I don´t know how to pass the request parameters to the server, so instead of getting the pagination etc done, I get the whole large table (>50 000 rows) from the server.to the browser, and by attempting to sort the columns it always returns the same original request.

js:

   $(function() {
       $('#freqs').DataTable( {
               "serverSide": true,
               "processing": true,
                "pageLength": 10,
                 "paging": true,
                 "ajax": "freqs",
            });
         });

python (cherrypy):

import cherrypy
import os
from datatablesServerside import *
class Website():
    # display test.html
    @cherrypy.expose
    def test(self, *args, **kwargs):
        with open('public/html/test.html') as f:
            myhtml = f.read()
        return myhtml  # opens and returns the html page
    @cherrypy.expose
    def freqs(self, *args, **kwargs):
        #by calling the function like this the whole is loaded in the client
        dtserverresponse =  DataTablesServer().getJson()
        return dtserverresponse
if __name__ == '__main__':
    conf = {
        '/': {
        'tools.sessions.on': True,
            'tools.sessions.locking': 'explicit',
        'tools.staticdir.root': os.path.abspath(os.getcwd()),
        },
    '/static': {
        'tools.staticdir.on': True,
        'tools.staticdir.dir': 'static'
         }
    }
    webapp = Website()
    cherrypy.config.update(
                {'server.socket_host': '0.0.0.0',
                 'server.socket_port': 8000,
                 'log.screen': True,
                 'log.error_file': '',
                 'log.access_file': ''
                 }
            )
    cherrypy.quickstart(webapp, '/', conf)
datatableserver (python 2.7 + oracle  11.2)

import cx_Oracle
from cherrypy import request
try:
    import simplejson as json
except ImportError:
    import json
_dbinfo= dict(
ip = 'localhost',
port = 1521,
SID = 'xe',
user   = "SYSTEM",
passwd = "system",
)
_columns = ["COL1", "COL2", "COL3"]
_table = 'X.FREQSCOMP'
collist2string = ",".join(["to_char(" + x + ")" for x in _columns])

class DataTablesServer():
    def __init__(self):
        self.requestparams = request.params
        print "request params: ", request.params
        ## oracle parameters
        # http://www.oracle.com/technetwork/articles/dsl/python-091105.html: cclass = "HOL", purity = cx_Oracle.ATTR_PURITY_SELF
        self.db =  cx_Oracle.connect(_dbinfo["user"], _dbinfo["passwd"],
                       cx_Oracle.makedsn(_dbinfo["ip"], _dbinfo["port"], _dbinfo["SID"]), cclass = "HOL", purity = cx_Oracle.ATTR_PURITY_SELF)
        self.resultData = None
        self.countFilteredData = 0
        self.countAllData = 0
        self.runQueries()
        self.getJson()
    def getJson(self):
        json_res = json.dumps({"draw": 1,
                               "recordsTotal": self.countAllData,
                               "recordsFiltered": self.countFilteredData,
                                "data": self.resultData
                               },
                                ensure_ascii=False, encoding="latin-1")
       # print "The json response looks like this: \n", json_res[:10000]
        return json_res
    def runQueries(self):
        dataCursor = self.db.cursor()
        outerquery = "SELECT %(collist2string)s  FROM %(table_or_subquery)s " %dict(
            collist2string = collist2string,
            table_or_subquery=self.ordering(),
            # paging = self.paging()
        )
        #print outerquery
        #print outerquery
        dataCursor.execute(outerquery)
        self.resultData = dataCursor.fetchall()
        # Data set length after filtering
        self.countFilteredData = len(self.resultData)
        #print self.countFilteredData
        countCursor = self.db.cursor()
        # Data set length of the whole table
        countquery = """ SELECT count(*) FROM %(table)s """ % dict(table = _table)
        countCursor.execute(countquery)
        self.countAllData = countCursor.fetchone()[0]
        #print self.countAllData
    def filtering(self):
        filter = ""
        # about requests
        # https://stackoverflow.com/questions/464040/how-are-post-and-get-variables-handled-in-python
        if "search" in self.requestparams and self.requestparams["search"] != "":
            filter = " WHERE "
            for i in range(len(_columns)):
                filter += "%(columnindex)s LIKE '%(mysearch)s' OR " % dict(
                    columnindex = _columns[i], mysearch = self.requestparams["search"].value
                )# (_columns[i], self.requestparams["search"])
            filter = filter[:-3]
        return filter
    def ordering(self):
        order = "%(table)s" %dict(table = _table)
        if ("order" in self.requestparams):
            if len(self.requestparams["order"]) > 1:
              #  print "\n\n\n", len(self.requestparams["order"])
                order = " ( SELECT row_number() OVER ( ORDER BY %(columnindex)s %(asc_desc)s ) as rownumerator, %(table)s.* from %(table)s %(where)s ) %(paging)s " % dict(
                    columnindex = self.requestparams["order"].value[0],
                    asc_desc = self.requestparams["order"].value[1],
                    table = _table,
                    where = self.filtering(),
                    paging = self.paging()
                )
            else:
                order = " ( SELECT row_number() OVER ( ORDER BY %(columnindex)s ) as rownumerator, %(table)s.* from %(table)s %(where)s ) %(paging)s " % dict(
                    columnindex = self.requestparams["order"].value[0],
                    table = _table,
                    where=self.filtering(),
                    paging=self.paging()
                )
        return order
    def paging(self):
        page = ""
        if "start" in self.requestparams and "length" in self.requestparams:
            if self.requestparams["start"] != "" and self.requestparams["length"] != -1:
                page = " WHERE rownumerator BETWEEN "+ self.requestparams["start"]+" AND "+ self.requestparams["start"]+ self.requestparams["length"]
            else:
                return page

        return page

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

kthorngren · August 2017

I don´t know how to pass the request parameters to the server,

This function should receive the parameters in kwargs from the web page :

def freqs(self, *args, **kwargs):

kwargs should have the parameters whether you use a GET or POST request.

You can print kwargs to verify. You can then pass **kwargs to your query Class. For example: dtserverresponse = DataTablesServer().getJson(**kwargs). you will need to parse them in the DataTablesServer Class. To facilitate passing the parameters you may want to change the function call to runQueries, for example:

dtserverresponse = DataTablesServer().runQueries(**kwargs)

In runQueries parse the parameters, execute the query then call getJson and return the value. Something like this:

def runQueries(self, **kwargs):
  #code to parse kwargs
  #execute query
  return self.getJson()

Also I think you need to return the draw value sent to you in the request in this line:

json_res = json.dumps({"draw": 1,

Kevin

Oracle, JSON and Python

Oracle, JSON and Python

Answers

Howdy, Stranger!

Categories

DataTables

Oracle, JSON and Python

Oracle, JSON and Python

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables