cherrypy and MySQL help

tony888 · 31 Oct 2011 at 18:31

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

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)

tony888 · 2 Nov 2011 at 22:27

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:

cherrypy and MySQL help

tony888

tony888

tony888

tony888