fetching table from mysql and display it using cherrypy!

Ateeq Ahmed

unread,

Apr 19, 2015, 7:32:11 AM4/19/15

to cherryp...@googlegroups.com

the title says it all!

am trying to fetch the content of a table from mysql in my raspberry pi and display the table using cherrypy server ! the problem is i just cant find any proper solution that actually works! . I did a lot of googling and ended up with this code but it wont work! :( so ! please any help would be awesome!!

def connect(thread_index): 
        cherrypy.thread_data.db = MySQLdb.connect('localhost', 'ateeq', 'khader6@', 'usage_homeauto') 
    
cherrypy.engine.subscribe('start_thread', connect)

@cherrypy.expose
    def sqldata(self):
	return '''<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8" />
        <link rel="stylesheet" href="static/jquery.mobile-1.0.1.min.css" />
        <script src="static/jquery.min.js">
        </script>
        <script src="static/jquery.mobile-1.0.1.min.js">
        </script>
    </head>
    <body style="overflow: hidden;overflow-x:hidden;" onload="createImageLayer();">
        <div data-role="page" data-theme="a" id="page1">
            <div data-theme="a" data-role="header" data-position="">
                <h5>
                    Ateeq's <br> Home Automation
                </h5>
            </div>
    <center>
<py-code="
try:
    cursor = cherrypy.thread_data.db.cursor()
    cursor.execute("SELECT * FROM usage_made") 
    result = cursor.fetchall()
    cursor.close
except MySQLdb.OperationalError:
    result =""
    pass
">


<table>
  <tbody>
    <py-for="row in result"> 
    <tr>
      <th><py-eval="str(row[0])"></th>
      <th><py-eval="str(row[1])"></th>
      <th><py-eval="str(row[2])"></th>
    </tr>
    <tr>
      <td><py-eval="str(row[3])"></td> 
      <td><py-eval="str(row[4])"></td>
      <td><py-eval="str(row[5])"></td>
     </tr>
     
     
    </py-for>
  </tbody>
</table></center>
    </body>
</html>'''

This is just the part of code! and it wont work! eventhough i got data in that table ! it wont display anything!

Tim Roberts

unread,

Apr 20, 2015, 1:22:16 PM4/20/15

to cherryp...@googlegroups.com

Ateeq Ahmed wrote:
> the title says it all!
> am trying to fetch the content of a table from mysql in my raspberry
> pi and display the table using cherrypy server ! the problem is i just
> cant find any proper solution that actually works! . I did a lot of
> googling and ended up with this code but it wont work! :( so ! please
> any help would be awesome!!

> ...

> This is just the part of code! and it wont work! eventhough i got data
> in that table ! it wont display anything!

Seeing just part of the code doesn't help us at all. In particular,
this code relies on the "cherrytemplate" template engine to interpret
the <py-code> and <py-for> tags. Long ago, cherrytemplate was included
as part of CherryPy, but not any more, because there are so many great
template engines available. You can fetch and install cherrytemplate,
but it hasn't been updated since 2004. If you need to use a template
engine, you should probably choose another one in active development. I
happen to like Cheetah, but there are others.

For your current project, you don't really need a template engine. You
can create the HTML by hand yourself just as easily.

--
Tim Roberts, ti...@probo.com
Providenza & Boekelheide, Inc.

Ateeq Ahmed

unread,

Apr 21, 2015, 3:22:56 AM4/21/15

to cherryp...@googlegroups.com

Seeing just part of the code doesn't help us at all. In particular,
this code relies on the "cherrytemplate" template engine to interpret
the <py-code> and <py-for> tags. Long ago, cherrytemplate was included
as part of CherryPy, but not any more, because there are so many great
template engines available. You can fetch and install cherrytemplate,
but it hasn't been updated since 2004. If you need to use a template
engine, you should probably choose another one in active development. I
happen to like Cheetah, but there are others.

For your current project, you don't really need a template engine. You
can create the HTML by hand yourself just as easily.

--
Tim Roberts, ti...@probo.com
Providenza & Boekelheide, Inc.

Hey Bro! thanks for the reply! :D as you said i actually made a html page to view the tables's content by using 'php' , but in order to access that online i had to use apache2 server instead of the actuall cherrypy server!. so i want to neglect this and make things work in a single server! Basically my goal is to fetch the content of table named 'usage_made' from the database 'usage_homeauto'!. So! yeah! any help would be appreciated ! thanks again! :D

Tim Roberts

unread,

Apr 21, 2015, 1:03:03 PM4/21/15

to cherryp...@googlegroups.com

Ateeq Ahmed wrote:
>
> Hey Bro! thanks for the reply! :D as you said i actually made a html
> page to view the tables's content by using 'php' , but in order to
> access that online i had to use apache2 server instead of the actuall
> cherrypy server!. so i want to neglect this and make things work in a
> single server! Basically my goal is to fetch the content of table
> named 'usage_made' from the database 'usage_homeauto'!. So! yeah! any
> help would be appreciated ! thanks again! :D

If you are used to PHP, then you should feel right at home with one of
the many HTML template packages that are available. As I said, I like
Cheetah, but there are several others that are well-respected. All of
them have good examples, and some even have good examples showing how to
integrate them with CherryPy.

In summary, your CherryPy class goes like this:
@cherrypy.expose
def read( self, id=0 ):
# Read database row
tmpl = Cheetah.Template( file='datatable.tmpl' )
tmpl.id = id
tmpl.row = row
return tmpl.respond()

Then the file "datatable.tmpl" has the same kind of thing that would
have been in your PHP file.

However, if you are comfortable with PHP, there may be other solutions.
Lighttpd and xginx are both lightweight and extremely capable web
servers that support PHP, without the trappings of a full Apache
environment.

Tim Roberts

unread,

Apr 21, 2015, 1:04:44 PM4/21/15

to cherryp...@googlegroups.com

Tim Roberts wrote:
> However, if you are comfortable with PHP, there may be other solutions.
> Lighttpd and xginx are both lightweight and extremely capable web

> servers that support PHP, ...

Sorry, nginx, not xginx. Serves them right for choosing an
unpronounceable name.

Ateeq Ahmed

unread,

Apr 21, 2015, 1:22:14 PM4/21/15

to cherryp...@googlegroups.com

Thanks! Thanks again! Can you please provide me with a guide/tutorial of some sort.I am kinda new to this 'Cheetah' I would really love to learn and use it in my project.! More precisely can you provide me with any kind of example or guide about that 'datatable.tmpl' file. It would be really helpful for me to get started! .. And yeah thanks again!.. :D

Tim Roberts

unread,

Apr 21, 2015, 1:40:52 PM4/21/15

to cherryp...@googlegroups.com

Ateeq Ahmed wrote:
> Thanks! Thanks again! Can you please provide me with a guide/tutorial of some sort.I am kinda new to this 'Cheetah' I would really love to learn and use it in my project.! More precisely can you provide me with any kind of example or guide about that 'datatable.tmpl' file. It would be really helpful for me to get started! .. And yeah thanks again!..

If you Google "cheetah cherrypy", you'll find a host of useful links,
including the page in the CherryPy manual entitled "Choosing a
templating language" that shows some examples of three popular choices.
You should glance through the documentation for all of them and see
which makes the most sense to you. They all have tutorials.

Ateeq Ahmed

unread,

Apr 25, 2015, 8:47:03 AM4/25/15

to cherryp...@googlegroups.com

If you Google "cheetah cherrypy", you'll find a host of useful links,
including the page in the CherryPy manual entitled "Choosing a
templating language" that shows some examples of three popular choices.
You should glance through the documentation for all of them and see
which makes the most sense to you. They all have tutorials.

thanks :D i will look into it!! :D

Ateeq Ahmed

unread,

Apr 25, 2015, 10:04:51 AM4/25/15

to cherryp...@googlegroups.com

Damn! :D i finally found what i wanted with lots of digging (googling) Thanks to you!! here's the code! Thanks for helping! :)

'''
Bonus Tutorial: Using SQLObject

This is a silly little contacts manager application intended to
demonstrate how to use SQLObject from within a CherryPy2 project. It
also shows how to use inline Cheetah templates.

SQLObject is an Object/Relational Mapper that allows you to access
data stored in an RDBMS in a pythonic fashion. You create data objects
as Python classes and let SQLObject take care of all the nasty details.

This code depends on the latest development version (0.6+) of SQLObject.
You can get it from the SQLObject Subversion server. You can find all
necessary information at <http://www.sqlobject.org>. This code will NOT
work with the 0.5.x version advertised on their website!

This code also depends on a recent version of Cheetah. You can find
Cheetah at <http://www.cheetahtemplate.org>.

After starting this application for the first time, you will need to
access the /reset URI in order to create the database table and some
sample data. Accessing /reset again will drop and re-create the table,
so you may want to be careful. :-)

This application isn't supposed to be fool-proof, it's not even supposed
to be very GOOD. Play around with it some, browse the source code, smile.

:)

-- Hendrik Mans <hen...@mans.de>
'''

import cherrypy
from Cheetah.Template import Template
from sqlobject import *

# configure your database connection here
__connection__ = 'mysql://root:laka@localhost/test'

# this is our (only) data class.
class Contact(SQLObject):
    lastName = StringCol(length = 50, notNone = True)
    firstName = StringCol(length = 50, notNone = True)
    phone = StringCol(length = 30, notNone = True, default = '')
    email = StringCol(length = 30, notNone = True, default = '')
    url = StringCol(length = 100, notNone = True, default = '')


class ContactManager:
    def index(self):
        # Let's display a list of all stored contacts.
        contacts = Contact.select()

        template = Template('''
            <h2>All Contacts</h2>
            <center>
            <table border=1><tr><th>Last Name</th><th>First Name</th><th>Ater</th></tr>
            #for $contact in $contacts
                <tr><td>$contact.lastName</td><td> $contact.firstName</td>
                <td>[<a href="./edit?id=$contact.id">Edit</a>]
                [<a href="./delete?id=$contact.id">Delete</a>]</td></tr>
                
            #end for

            <p>[<a href="./edit">Add new contact</a>]</p></center>
        ''', [locals(), globals()])

        return template.respond()

    index.exposed = True


    def edit(self, id = 0):
        # we really want id as an integer. Since GET/POST parameters
        # are always passed as strings, let's convert it.
        id = int(id)

        if id > 0:
            # if an id is specified, we're editing an existing contact.
            contact = Contact.get(id)
            title = "Edit Contact"
        else:
            # if no id is specified, we're entering a new contact.
            contact = None
            title = "New Contact"


        # In the following template code, please note that we use
        # Cheetah's $getVar() construct for the form values. We have
        # to do this because contact may be set to None (see above).
        template = Template('''
            <h2>$title</h2>

            <form action="./store" method="POST">
                <input type="hidden" name="id" value="$id" />
                Last Name: <input name="lastName" value="$getVar('contact.lastName', '')" /><br/>
                First Name: <input name="firstName" value="$getVar('contact.firstName', '')" /><br/>
                Phone: <input name="phone" value="$getVar('contact.phone', '')" /><br/>
                Email: <input name="email" value="$getVar('contact.email', '')" /><br/>
                URL: <input name="url" value="$getVar('contact.url', '')" /><br/>
                <input type="submit" value="Store" />
            </form>
        ''', [locals(), globals()])

        return template.respond()

    edit.exposed = True


    def delete(self, id):
        # Delete the specified contact
        contact = Contact.get(int(id))
        contact.destroySelf()
        return 'Deleted. <a href="./">Return to Index</a>'

    delete.exposed = True


    def store(self, lastName, firstName, phone, email, url, id = None):
        if id and int(id) > 0:
            # If an id was specified, update an existing contact.
            contact = Contact.get(int(id))

            # We could set one field after another, but that would
            # cause multiple UPDATE clauses. So we'll just do it all
            # in a single pass through the set() method.
            contact.set(
                lastName = lastName,
                firstName = firstName,
                phone = phone,
                email = email,
                url = url)
        else:
            # Otherwise, add a new contact.
            contact = Contact(
                lastName = lastName,
                firstName = firstName,
                phone = phone,
                email = email,
                url = url)

        return 'Stored. <a href="./">Return to Index</a>'

    store.exposed = True


    def reset(self):
        # Drop existing table
        Contact.dropTable(True)

        # Create new table
        Contact.createTable()

        # Create some sample data
        Contact(
            firstName = 'Hendrik',
            lastName = 'Mans',
            email = 'hen...@mans.de',
            phone = '++49 89 12345678',
            url = 'http://www.mornography.de')

        return "reset completed!"

    reset.exposed = True


print("If you're running this application for the first time, please go to http://localhost:8080/reset once in order to create the database!")

import os.path
tutconf = os.path.join(os.path.dirname(__file__), 'tutorial.conf')
if __name__ == '__main__':
    # CherryPy always starts with app.root when trying to map request URIs
    # to objects, so we need to mount a request handler root. A request
    # to '/' will be mapped to HelloWorld().index().
    cherrypy.quickstart(ContactManager(), config=tutconf)

Hope it helps for every one! i have made changes for the actuall code to show data in a table ! its kinda easy! but hope it helps for the beginners! :)
heres the actual Source! which i used! :D

Message has been deleted

Ateeq Ahmed

unread,

Apr 25, 2015, 1:36:36 PM4/25/15

to cherryp...@googlegroups.com

Here's the final code ! which i was working for! :) and it works! thanks to you and all other people that helped! link!. :)

Reply all

Reply to author

Forward