cherrypy and MySQL help

Associate
Joined
31 Jul 2008
Posts
1,326
Location
London
I'm trying to retrieve all the names and status from a database and print them out in a table format. I have successfully connected to the database and query it but when I print the results it will only show one row of data.

The question is how do I print all the data in a table format?

This is the code i used :D

Code:
def dist_list(self, username ='username', password ='ZoomZoom1'):
		if username == 'username' and password == 'ZoomZoom1':
			cursor = conn.cursor()
			cursor.execute ("SELECT listname, status FROM table")
			rows = cursor.fetchall()
			for row in rows:
				return "%s, %s" % (row[0], row[1])
		
		else:
			return '''
			<p>Username and/or Password is invalid Please try again</p>
			<p><a href="index"><b>click here</b></a> to go back</p>
			'''
	
	dist_list.exposed = True
	
if __name__ == '__main__':
	import os.path
	
	Pconf = os.path.join(os.path.dirname(__file__), 'test.conf')
	cherrypy.quickstart(Distribution(), config=Pconf)
 
You are returning from the function when you hit the first result, you need to buffer the output before you print or return it. I'm not quite sure what the context is but code that generates html output is typically part of a larger infrastructure. The function would probably just return the result set as a dictionary or a list of dictionaries and the rendering of the results would be up to another function or template system.
 
thanks for the reply I think I sort of understand

here's version 2
Code:
import cherrypy
import pymysql
import cgi

db = pymysql.connect(host="sql", user="user", passwd="pass", db="COMPS")

def safe_list_name(listname):
    if not listname:
        listname = "no list name"
    return listname.replace(" ", "_")

def mkIndex(lists):
	html = "<table border=2><tr>" 
	c = 0
	modulo = 5
	
	for list in lists:
		html += "<td>%s</td>" % (list)
		c+1
		if c == 0:
			html += "</tr><tr>"
			
	return html + "</table>"
	
def mkIndex2(lists):
	html = "<table border=2><tr></tr><tr>" 
	c = 0
	modulo = 2
	
	for list in lists:
		html += "<td>%s</td>" % (list)
		c+1
		if c % modulo == 0:
			# html += "</tr><tr>"
			
			
	if c % modulo !=0:
		html += "</t>"
	return html + "</table>"

def list_names():
		
	list_names_sql = "SELECT DISTINCT listname FROM tbldistributionlist WHERE status !='dead' ORDER BY listname"
	
	c = db.cursor()
	c.execute(list_names_sql)
	r = c.fetchall()
	names = [row[0] for row in r]
	name = mkIndex(names)

	return name

def list_email():

	email_sql = "SELECT emailaddr FROM tbldistributionlist WHERE status != 'dead'"
	
	c = db.cursor()
	c.execute(email_sql)
	r = c.fetchall()
	emails = [row[0] for row in r]
	email = mkIndex2(emails)

	return email

	
	
class Distribution:
	def index(self):
	    return '''
		<html>
		    <body>
			<div align="left">
			    <table border="0" cellpadding="0" width="300">
				<tr>
				    <td colspan="2">
				    <img border="0" src="/images/pirc-logo.jpg">
				    </td>
				</tr>

				<tr>
				    <td colspan="2"><font face="Arial"><h2>Distribution List</h2></font><hr></td>
				</tr>

				<tr>
				    <td colspan="2"><font face="Arial"><h3>Please Login</h3></font></td>
				</tr>

				<tr>
				    <td>
					<form action="view_dist" method="POST"
					    <p><b><font face ="Arial" size ="2">Username</font><b></p>
					    	<input type="text" name="username" value="" 
					            size="15" maxlength="30"/>
								
					    <p><b><font face="Arial" size="2">Password</font></b></p>
						<input type="password" name="password" value="" 
		   				    size="15" maxlength="30"/>

						<p><input type="submit" value="Login"/>
						<input type="reset" value="Clear"/></p>
					</form>
				    </td>
				</tr>
				<tr>
				    <td colspan="2"> 
				    <p align="right">

				    <font face="Arial" color="A00000" size="1"><i><b>(c) PIRC IT 2011</b></i>
				    </p>
		
				</tr>
			    </table>
			</div>
		    </body>
		</html>
			'''
	index.exposed = True

	def view_dist(self, username = 'username', password = 'ZoomZoom1'):
		if username == 'username' and password == 'ZoomZoom1':
		
			return list_names(), list_email()
		
		
	view_dist.exposed = True
		
	

if __name__ == '__main__':
    import os.path

    Pconf = os.path.join(os.path.dirname(__file__), 'config.conf')
    cherrypy.quickstart(Distribution(), config=Pconf)

This displays the results in a table however the second table(email table) appears under the 1st table(name) how do I change it so I can display the second table beside the 1st table?

As the database contains duplicate entries with multiple emails
e.g. Listname = Nissan with 5 different emails, so Nissan is entered 5 times each with different email. Is is possible to display the Nissan with the list of emails?

Sorry for the noobness I'm relatively new to python web programming :eek:
 
Back
Top Bottom