进行简单的web应用之后，接下来就应该学习python连接数据库，这个练习就是在上个练习的基础上将信息保存到数据库，这个联系也没有什么特别的，有之前java web的经验的话，很好理解，主要还是一个MySQLdb的学习。代码如下（创建数据库就忽略了吧）：
从数据库查询message以列表的形式显示main.py
#! /usr/bin/env python # -*- coding=utf-8 -*- import cgitb import MySQLdb # 声明文本格式 print 'Content-type:text/html ' cgitb.enable() # 连接数据库 conn = MySQLdb.connect(user='root', db='test') curs = conn.cursor() print """ <html> <head> <title>主页</title> </head> <body> <h1>论坛帖子列表</h1> """ # 将数据库中记录读取到dic curs.execute('select * from messages') # mysql没有这样的方法 # rows = curs.dictfetchall() # 获取表的列的名称 names = [d[0] for d in curs.description] print names rows = [dict(zip(names, row)) for row in curs.fetchall()] print rows # 主贴 toplevel = [] # 回复帖 children = {} # 区分出主贴和回复帖 for row in rows: parent_id = row['reply_to'] if parent_id is None: toplevel.append(row) else: children.setdefault(parent_id, []).append(row) # 格式化帖子列表 def format(row): print '<p><a href="view.py?id=%(id)s">%(subject)s </a></p>'% row try: kids = children[row['id']] except KeyError: pass else: # 递归格式化帖子的子贴（回复） print '<blockquote>' for kid in kids: format(kid) print '</blockquote>' print '<p>' # 调用format格式化帖子 for row in toplevel: format(row) print """ </p> <hr /> <p><a href="edit.py">发帖</a></p> </bofy> </html> """
查看一个具体帖子的详细内容view.py
#! /usr/bin/env python # -*- coding=utf-8 -*- import cgitb import sys import cgi import MySQLdb # 声明文本格式 print 'Content-type:text/html ' cgitb.enable() # 接受参数 form = cgi.FieldStorage() id = form.getvalue('id') try: id = int(id) except : print 'Invalid id' sys.exit() # 连接数据库 conn = MySQLdb.connect(user='root', db='test') curs = conn.cursor() print """ <html> <head> <title>View message</title> </head> <body> <h1>View Message</h1> """ # 将数据库中记录读取到dic curs.execute('select * from messages where id = %i' % id) # mysql没有这样的方法 # rows = curs.dictfetchall() # 获取表的列的名称 names = [d[0] for d in curs.description] #print names rows = [dict(zip(names, row)) for row in curs.fetchall()] #print rows # 如果该id查询不到数据，说明不存在该id if not rows: print 'Unknow message id' sys.exit() # 获取返回的第一条数据 row = rows[0] print """ <p> <b> Subject: </b>%(subject)s <br /> <b> sender: </b>%(sender)s <br /> <pre>%(text)s</pre> </p> <hr /> <a href="main.py">back to main page</a>> | <a href="edit.py?reply_to=%(id)s"> reply</a> </bofy> </html> """ % row
查看完帖子之后回帖，edit.py
#! /usr/bin/env python # -*- coding=utf-8 -*- import cgitb import sys import cgi import MySQLdb # 声明文本格式 print 'Content-type:text/html ' cgitb.enable() # 接受参数 form = cgi.FieldStorage() reply_to = form.getvalue('reply_to') # 连接数据库 conn = MySQLdb.connect(user='root', db='test') curs = conn.cursor() print """ <html> <head> <title>View message</title> </head> <body> <h1>View Message</h1> <form action="save.py" method="POST"> """ subject = '' if reply_to is not None: print "<input type='hidden' name='reply_to' value='%s' />" % reply_to curs.execute('select * from messages where id=%s' % reply_to) subject = curs.fetchone()[1] print subject if not subject.startswith('Re:'): subject = 'Re:'+ subject print """ <b>Subject:</b><br /> <input type='text' size='40' name='subject' value='%s' /><br /> <b>Sender:</b><br /> <input type='text' size='40' name='sender' /><br /> <b>Message:</b><br /> <textarea name='text' cols='40' rows='20'></textarea><br /> <input type='submit' value='Save'/> </form> <hr /> <a href='main.py'>Back to the main page</a>' </body> </html> """ % subject
编辑完帖子的时候，提交save.py
#!/usr/bin/python # -*- coding=utf-8 -*- print 'Content-type: text/html ' import cgitb; cgitb.enable() # 将单引号转义，在使用insert语句的时候字符串就不需要添加引号 def quote(string): if string: return string.replace("'", "\'") else: return string import MySQLdb conn = MySQLdb.connect(db='test', user='root') curs = conn.cursor() import cgi, sys form = cgi.FieldStorage() sender = quote(form.getvalue('sender')) subject = quote(form.getvalue('subject')) text = quote(form.getvalue('text')) reply_to = form.getvalue('reply_to') if not (sender and subject and text): print 'Please supply sender, subject, and text' sys.exit() if reply_to is not None: query = """ insert into messages(reply_to, sender, subject, text) values(%i, '%s', '%s', '%s')""" % (int(reply_to), sender, subject, text) else: query = """ insert into messages(sender, subject, text) values('%s', '%s', '%s')""" % (sender, subject, text) curs.execute(query) conn.commit() print """ <html> <head> <title>Message Saved</title> </head> <body> <h1>Message Saved</h1> <hr /> <a href='main.py'>Back to the main page</a> </body> </html>s """
完整代码