Simple way to display results of a sql query using web.py_问答_开发者

Simple way to display results of a sql query using web.py

开发者 https://www.devze.com 2023-03-07 08:43 出处：网络

Using web.py, I\'m creating a handful of result sets by using web.database.query() And I\'d like to display the query results on an html template.

Using web.py, I'm creating a handful of result sets by using

web.database.query()

And I'd like to display the query results on an html template.

Does web.py have any built-in facility for doing this? I didn't see any going through the doc and code samples.

I'd also appreciate any pointers to other modules that will coerce the sql result set into something that can be displayed by a jquery or google data grid. I started working on a google data grid converter but I had to jump through some hoops to handle the开发者_运维技巧 various datatypes. I figure this has been done tons of times before - I'd just like to know where I should be looking.

Check out the docs for web.py's built-in templating system. There are also cookbook examples for hooking in 3rd party template engines like Mako, Cheetah and Jinja2.

In order to convert the data to be displayed by Javascript you could use one of the JSON modules for Python: the standard json module (2.6+), simplejson, or python-cjson.

Here is a quick example of using the web.template module to render a simple HTML page showing some database query results.

Updated The select method doesn't return the column names as a separate attribute. It returns an iterator where each row is a dictionary. Therefore, to get the column headers you need to grab then from the first row. I've updated the example to show how this can be done:

import web

TEMPLATE = '''$def with (rows, cols)
<html><body>
<h2>Results:</h2>
<table>
<tr>
$for col in cols:
    <th>$col</th>
</tr>
$for row in rows:
    <tr>
    $for col in cols:
        <td>$row[col]</td>
    </tr>
</table></body></html>'''

class query:
    def GET(self, arg):
        res = db.select('player', what='id,name')
        cols = []
        rows = res.list()
        if rows:
            cols = rows[0].keys()
        return tmpl(rows, cols)

db = web.database(dbn='mysql')
tmpl = web.template.Template(TEMPLATE)
urls = ('/(.*)', 'query')

if __name__ == '__main__':
    app = web.application(urls, globals())
    app.run()

Output (collapsed to save space):

<html><body>
<h2>Results:</h2>
<table>
<tr><th>id</th><th>name</th></tr> 
<tr><td>1</td> <td>Joe</td></tr>
<tr><td>2</td><td>Gary</td></tr>
<tr> <td>3</td><td>Fred</td></tr>
</table></body></html>