第26章 项目7：自定义公告板
1.问题
如何创建通过Web发布和回复信息的简单系统，可以看作网络论坛。
2.工具
-
CGI工具
-
SQL数据库：PostgreSQL
下载地址：
http://www.enterprisedb.com/products-services-training/pgdownload#windows
点击安装：
Password设置为123456，Port默认为5432。
(3)psycopg：与PostgrelSQL数据库通信的API模块。
下载地址：http://www.stickpeople.com/projects/python/win-psycopg/
注意Python和 postgrelSQL的版本号，点击安装：
使用命令：import psycopg2 ，未报错，安装成功。
3.准备工作——创建数据库
打开SQL Shell(psql)即PostgrelSQL命令行模式
回车直到出现"用户 postgres 的口令："，输入密码123456并回车。
26-1 在PostgreSQL内创建数据库
输入：
(1)create database mytable;并回车，创建DATABASE。
(2)\c mytable;并回车连接到数据库"mytable"，用户"postgres"。
(3)
CREATE TABLE messages (
id SERIAL PRIMARY KEY,
subject TEXT NOT NULL,
sender TEXT NOT NULL,
reply_to INTEGER REFERENCES messages,
text TEXT NOT NULL
);
并回车，出现CREATE TABLE，成功创建数据库。
(4)\d并回车，显示出关联列表。
拥有5个字段，
Id：用于标识唯一的消息
Subject：包括消息主体的字符串
Sender：包括发送者名字、EMAIL地址或者其他信息的字符串
Reploy_to：如果消息是回复其他消息的、那么这个字段就包括那个消息的ID
Text：包括消息内容的字符串。
(5)补充——数据库删除：
1)\l
2)DROP DATABASE mytable;
4.初次实现
一个简单测试，顺序输入：
import psycopg2
conn = psycopg2.connect('user=postgres password=123456 dbname=mytable')
curs = conn.cursor()
curs.execute('select * from messages')
curs.fetchall()
输出为[]，由于数据库现在是空的，所以什么都没有得到。
因为没有实现web接口，测试数据库需要手动输入消息。通过数据库管理工具可以添加消息，或者使用python解释器和数据库模块实现：
addmessage.py
#!D:\Program Files\python27\python.exe
# addmessage.py
import psycopg2
conn = psycopg2.connect('user=postgres password=123456 dbname=mytable')
curs = conn.cursor()
reply_to = raw_input('Reply to: ')
subject = raw_input('Subject: ')
sender = raw_input('Sender: ')
text = raw_input('Text: ')
if reply_to:
query = """
INSERT INTO messages(reply_to, sender, subject, text)
VALUES(%s, '%s', '%s','%s') """ % (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()输入以下内容，成功写入数据库。
测试后输出如下：
26-4 simple_main.cgi ——主电子公告板
#!D:\Program Files\python27\python.exe
print 'Content-type: text/html\n'
import cgitb; cgitb.enable()
import psycopg2
conn= psycopg2.connect('dbname=mytable user=postgres password=123456')
curs= conn.cursor()
print"""
<html>
<head>
<title>The FooBar Bulletin Board</title>
</head>
<body>
<h1>The FooBar BulletinBoard</h1>
"""
curs.execute('SELECT * FROM messages')
rows= curs.fetchall()
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 row['subject']
try: kids = children[row['id']]
except KeyError: pass
else:
print '<blockquote>'
for kid in kids:
format(kid)
print '</blockquote>'
print '<p>'
for row in toplevel:
format(row)
print"""
</p>
</body>
</html>
"""放在D:\Program Files\Apache24\htdocs目录中（上一章所有文件已经删除了）。
输入地址：http://localhost/simple_main.cgi
5.再次实现
26-5 main.cgi ——电子公告板主页
#!D:\Program Files\python27\python.exe
print 'Content-type: text/html\n'
import cgitb; cgitb.enable()
import psycopg2
conn= psycopg2.connect('dbname=mytable user=postgres password=123456')
curs= conn.cursor()
print"""
<html>
<head>
<title>The FooBar Bulletin Board</title>
</head>
<body>
<h1>The FooBar BulletinBoard</h1>
"""
curs.execute('SELECT * FROM messages')
rows= curs.fetchall()
toplevel= []
children= {}
for row in rows:
parent_id = row[3]
if parent_id is None:
toplevel.append(row)
else:
children.setdefault(parent_id,[]).append(row)
def format(row):
print '<p><a href="view.cgi?id=%i">%s<a>' % (row[0],row[1])
try:
kids = children[row[0]]
except KeyError:
pass
else:
print '<blockquote>'
for kid in kids:
format(kid)
print '</blockquote>'
print '<p>'
for row in toplevel:
format(row)
print"""
</p>
<hr/ >
<p><a href="edit.cgi">Post Message</a></p>
</body>
</html>
"""26-6 view.cgi ——消息浏览
#!D:\Program Files\python27\python.exe
print 'Content-type: text/html\n'
import cgitb; cgitb.enable()
import psycopg2
conn= psycopg2.connect('dbname=mytable user=postgres password=123456')
curs= conn.cursor()
import cgi, sys
form = cgi.FieldStorage()
id = form.getvalue('id')
print '''
<html>
<head>
<title>View Message</title>
</head>
<body>
<h1>View Message</h1>
'''
try: id = int(id)
except:
print 'Invalid message ID'
sys.exit()
curs.execute('SELECT * FROM messages WHERE id = %i' % id)
rows = curs.fetchall()
if not rows:
print 'Unknown message ID'
sys.exit()
row = rows[0]
print '''
<p><b>Subject:</b> %s<br/>
<b>Sender:</b>%s<br/>
<pre>%s</pre>
</p>
<hr/>
<a href='main.cgi'>Back to the main page</a>
|<a href="edit.cgi?reply_to=%s">Reply</a>
</body>
</html>
''' % (row[1],row[2],row[4],row[0])26-7 edit.cgi ——消息编辑器
#!D:\Program Files\python27\python.exe
print 'Content-type: text/html\n'
import cgitb; cgitb.enable()
import psycopg2
conn= psycopg2.connect('dbname=mytable user=postgres password=123456')
curs= conn.cursor()
import cgi,sys
form = cgi.FieldStorage()
reply_to = form.getvalue('reply_to')
print '''
<html>
<head>
<title>Compose Message</title>
</head>
<body>
<h1>Compose Message</h1>
<form action='save.cgi' method='POST'>
'''
subject = ''
if reply_to is not None:
print '<input type="hidden" name="reply_to" value="%s"/>' % reply_to
curs.execute('SELECT subject FROM messages WHERE id = %s' % reply_to)
subject = curs.fetchone()[0]
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.cgi'>back to the main page</a>
</body>
</html>
''' % subject26-8 save.cgi ——实现保存的脚本
#!D:\Program Files\python27\python.exe
print 'Content-type:text/html\n'
import cgitb;cgitb.enable()
def quote(string):
if string:
return string.replace("'","\\'")
else:
return string
import psycopg2
conn= psycopg2.connect('dbname=mytable user=postgres password=123456')
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,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 Save</title>
</head>
<body>
<h1>Message Saved</h1>
<hr />
<a href='main.cgi'>Back to the main page</a>
</body>
</html>s
'''将脚本main.cgi、view.cgi、edit.cgi、save.cgi放在D:\Program Files\Apache24\htdocs目录中：
输入地址：http://localhost/main.cgi
点击Post Message，输入
Subject：Mr. Gumby is in town
Sender：Mr. Gumby
Message：Yes, the romors are true. I have arrived.
点击保存：
返回主页：
点击Mr. Gumby is in town，出现view界面：
