pythonhtmlsql-serverstored-procedurescheetah

Execute stored procedure when a button is clicked on python web


I am creating a webpage that displays a list of object that exist in a database/table. I have a stored procedure which retrieves the data from the table and associates with each column the data will be parse into a table.

<table id="fileTable" class="display">
    <thead>
        <tr>
            <th>Jobstream Id</th>
            <th>Date</th>
            <th>Server Name</th>
            <th>Request Time</th>
            <th>Status</th>
            <th>StepStatus</th>
            <th>LastUpdate</th>
            <th>DeleteJob</th>
        </tr>
    </thead>
    <tbody>
    #for $i in range(0,$rows.__len__())
        <tr>
            <td>$rows[i].get('JobstreamName')</td>
            <td>$rows[i].get('Date')</td>
            <td>$rows[i].get('ServerName')</td>
            <td>$rows[i].get('RequestTime')</td>
            <td>$rows[i].get('Status')</td>
            <td>$rows[i].get('StepStatus')</td>
            <td>$rows[i].get('LastUpdate')</td>
            <td><input type="submit" value="Delete"/></td>
        </tr>
    #end for
    </tbody>
</table>

There will be a "Delete" button at the end of each row, which serves the purpose of deleting whichever row (in the database) that the user wishes. The deletion of row should be run in a stored procedure. I do have a function in python that would execute the stored-procedure.

However, I have no idea how to connect the button which will return the "JobStream Id" and "Date" that serves as a parameter in executing a stored-procedure.

I am using Cheetah as a web development tools to run the python code. Appreciate any help I could get, Thanks in advance.

EDIT: Let me rephrase myself here. So, the data of the table that is produce in the webpage is being pulled from the database using a stored-procedure. I am trying to add a feature (end of each row there is a delete button) that allow the user to delete any row. Therefore I will need to pass the html variable to my python variable to run another stored-procedure, but i am unable to do so. Hope this clarify most of the confusion. Thanks

Here is the full code:

def getJobStreamStatus(Delete):
    Delete = 'Just a random string'
    user,pwd,server,db =  SPCaller.parseLogin('DMClient@gpdevdb81\\dmresrchdbdev.EquityData')
    db =  DB.dbConn(computerName=server,databaseName=db,userName=user,password=pwd)
    #results = SPCaller.readSPReturnDict('getJobStreamRerunStatus',None, 'DMClient@gpdevdb81\\dmresrchdbdev.EquityData')
    cols, rs = db.getResultSetFromSP('getJobStreamRerunStatus',None)
    #html = []
    definition = """<br><br><br><br><br><br><HR>$title 
    <table id="fileTable" class="display">
        <thead>
            <tr>
                <th>Jobstream Id</th>
                <th>Date</th>
                <th>Server Name</th>
                <th>Request Time</th>
                <th>Status</th>
                <th>StepStatus</th>
                <th>LastUpdate</th>
                <th>DeleteJob</th>
            </tr>
        </thead>
        <tbody>
        #for $i in range(0,$rows.__len__())
            <tr>
            <td>$rows[i].get('JobstreamName')</td>
            <td>$rows[i].get('Date')</td>
            <td>$rows[i].get('ServerName')</td>
            <td>$rows[i].get('RequestTime')</td>
            <td>$rows[i].get('Status')</td>
            <td>$rows[i].get('StepStatus')</td>
            <td>$rows[i].get('LastUpdate')</td>
            <td><input type="button" value="Delete" name='btnEdit' class='btnEdits' /></td>
            </tr>
        #end for
        </tbody>
    </table>
    """ 

    return str(Template(definition, searchList=[{'cols':cols, 'rows':arrToDict(cols,rs),'Delete':Delete,'title':'Showing all jobs in the rerun queue with status <b>waiting: 0, running: 2, and failed: 3</b><br><br>'}]))

Solution

  • I don't know Python, but I think you can create a form for each row and add a hidden field with the id of the displayed row.

    When the user clicks on the submit button, it will be easy for you to read the value of the hidden field for the request form collection and pass it to the stored procedure:

    #for $i in range(0,$rows.__len__())
        <tr>
            <form action = "....">
               <input type="hidden" name="id" value="$rows[i].get('Id')"> 
               <td>$rows[i].get('JobstreamName')</td>
               <td>$rows[i].get('Date')</td>
               <td>$rows[i].get('ServerName')</td>
               <td>$rows[i].get('RequestTime')</td>
               <td>$rows[i].get('Status')</td>
               <td>$rows[i].get('StepStatus')</td>
               <td>$rows[i].get('LastUpdate')</td>
               <td><input type="submit" value="Delete"/></td>
            </form>
        </tr>
    #end for
    

    The code above assumes there is an id field in your data.