CH-9 管理你的数据
1、生成HTML表单：
test_form.py实现：
#! /usr/local/bin/python3 import yate print(yate.start_response('text/html')) print(yate.do_form('add_timing_data.py', ['TimeValue'], text="Send"))
yate.py实现：
from string import Template def start_response(resp="text/html"): return('Content-type: ' + resp + '\n\n') def include_header(the_title): with open('templates/header.html') as headf: head_text = headf.read() header = Template(head_text) return(header.substitute(title=the_title)) def include_footer(the_links): with open('templates/footer.html') as footf: foot_text = footf.read() link_string = '' for key in the_links: link_string += '<a href="' + the_links[key] + '">' + key + '</a> ' footer = Template(foot_text) return(footer.substitute(links=link_string)) def start_form(the_url, form_type="POST"): return('<form action="' + the_url + '" method="' + form_type + '">') def end_form(submit_msg="Submit"): return('<p></p><input type=submit value="' + submit_msg + '">') def radio_button(rb_name, rb_value): return('<input type="radio" name="' + rb_name + '" value="' + rb_value + '"> ' + rb_value + '<br />') def radio_button_id(rb_name, rb_value, rb_id): return('<input type="radio" name="' + rb_name + '" value="' + str(rb_id) + '"> ' + rb_value + '<br />') def u_list(items): u_string = '<ul>' for item in items: u_string += '<li>' + item + '</li>' u_string += '</ul>' return(u_string) def header(header_text, header_level=2): return('<h' + str(header_level) + '>' + header_text + '</h' + str(header_level) + '>') def para(para_text): return('<p>' + para_text + '</p>') def create_inputs(inputs_list): html_inputs = '' for each_input in inputs_list: html_inputs = html_inputs + '<input type="Text" name="' + each_input + '" size=40>' return(html_inputs) def do_form(name, the_inputs, method="POST", text="Submit"): with open('templates/form.html') as formf: form_text = formf.read() inputs = create_inputs(the_inputs) form = Template(form_text) return(form.substitute(cgi_name=name, http_method=method, list_of_inputs=inputs, submit_text=text))
form.html实现：
<form action="$cgi_name" method="$http_method"> Enter a timing value: $list_of_inputs <br /> <input type="Submit" value="$submit_text"> </form>
add_timing_data.py实现：表单数据传送到该CGI脚步
#! /usr/local/bin/python3 import cgi import os import time import sys import yate print(yate.start_response('text/plain')) addr = os.environ['REMOTE_ADDR'] host = os.environ['REMOTE_HOST'] method = os.environ['REQUEST_METHOD'] cur_time = time.asctime(time.localtime()) print(host + ", " + addr + ", " + cur_time + ": " + method + ": ", end='', file=sys.stderr) form = cgi.FieldStorage() for each_form_item in form.keys(): print(each_form_item + '->' + form[each_form_item].value, end=' ', file=sys.stderr) print(file=sys.stderr) print('OK.')
以上实现了一个简单的数据输入表单，并且输入到表单的数据确实已经传送到服务器上的CGI脚本。
2、利用数据库
python 的数据库API：
数据库API的相应Python代码：
import sqlite3 connection = sqlite3.connect('coachdata.sqlite') cursor = connection.cursor() cursor.execute("""SELECT DATA('NOW')""") connection.commit() connection.close()
createDBtables.py实现： 创建coachdata.sqlite数据库，包含空的athletes和timing_data两个表
import os import sqlite3 connection = sqlite3.connect('coachdata.sqlite') cursor = connection.cursor() cursor.execute("""CREATE TABLE athletes ( id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name TEXT NOT NULL, dob DATE NOT NULL )""") cursor.execute("""CREATE TABLE timing_data ( athlete_id INTEGER NOT NULL, value TEXT NOT NULL, FOREIGN KEY (athlete_id) REFERENCES athletes)""") connection.commit() connection.close()
从pickle向SQLite传输数据：
initDBtables.py实现：它从现有模型的pickle中取得选手的数据，并将数据加载到新创建的SQLite数据库表中
import sqlite3 connection = sqlite3.connect('coachdata.sqlite') cursor = connection.cursor() import glob import athletemodel data_files = glob.glob("../data/*.txt") athletes = athletemodel.put_to_store(data_files) for each_ath in athletes: name = athletes[each_ath].name dob = athletes[each_ath].dob cursor.execute("INSERT INTO athletes (name, dob) VALUES (?, ?)", (name, dob)) connection.commit() cursor.execute("SELECT id from athletes WHERE name=? AND dob=?", (name, dob)) the_current_id = cursor.fetchone()[0] for each_time in athletes[each_ath].clean_data: cursor.execute("INSERT INTO timing_data (athlete_id, value) VALUES (?, ?)", (the_current_id, each_time)) connection.commit() connection.close()
athletemodel.py实现：
import pickle from athletelist import AthleteList def get_coach_data(filename): try: with open(filename) as f: data = f.readline() templ = data.strip().split(',') return(AthleteList(templ.pop(0), templ.pop(0), templ)) except IOError as ioerr: print('File error (get_coach_data): ' + str(ioerr)) return(None) def put_to_store(files_list): all_athletes = {} for each_file in files_list: ath = get_coach_data(each_file) all_athletes[ath.name] = ath try: with open('athletes.pickle', 'wb') as athf: pickle.dump(all_athletes, athf) except IOError as ioerr: print('File error (put_and_store): ' + str(ioerr)) return(all_athletes) def get_from_store(): all_athletes = {} try: with open('athletes.pickle', 'rb') as athf: all_athletes = pickle.load(athf) except IOError as ioerr: print('File error (get_from_store): ' + str(ioerr)) return(all_athletes)
athletelist.py实现：
class AthleteList(list): def __init__(self, a_name, a_dob=None, a_times=[]): list.__init__([]) self.name = a_name self.dob = a_dob self.extend(a_times) @staticmethod def sanitize(time_string): if '-' in time_string: splitter = '-' elif ':' in time_string: splitter = ':' else: return(time_string) (mins, secs) = time_string.split(splitter) return(mins + '.' + secs) @property def top3(self): return(sorted(set([self.sanitize(t) for t in self]))[0:3]) @property def clean_data(self): return(sorted(set([self.sanitize(t) for t in self]))) @property def as_dict(self): return({'Name': self.name, 'DOB': self.dob, 'Top3': self.top3})
SQLite 数据管理工具：SQLite Manager 详见本网站随笔"SQLite Manager插件安装与使用（firefox）"
SQLite 与现有的WEB应用（详见CH7-WEB开发章节）集成：
现在要使用SQLite，而不是pickle。
首先修改athletemodel.py模块代码： 修改原来的模型
import sqlite3 db_name = 'coachdata.sqlite' #获得选手名字列表 def get_names_from_store(): connection = sqlite3.connect(db_name) cursor = connection.cursor() results = cursor.execute("""SELECT name FROM athletes""") response = [row[0] for row in results.fetchall()] connection.close() return(response) #根据ID获得选手的详细信息 def get_athlete_from_id(athlete_id): connection = sqlite3.connect(db_name) cursor = connection.cursor() results = cursor.execute("""SELECT name, dob FROM athletes WHERE id=?""", (athlete_id,)) (name, dob) = results.fetchone() results = cursor.execute("""SELECT value FROM timing_data WHERE athlete_id=?""", (athlete_id,)) data = [row[0] for row in results.fetchall()] response = {'Name': name, 'DOB': dob, 'data': data, 'top3': data[0:3]} connection.close() return(response) #获取选手名和ID def get_namesID_from_store(): connection = sqlite3.connect(db_name) cursor = connection.cursor() results = cursor.execute("""SELECT name, id FROM athletes""") response = results.fetchall() connection.close() return(response)
模型转换成功！
现在修改各个CGI脚本来使用这个新的模型API：
generate_list.py修改为：
#! /usr/local/bin/python3 import athletemodel import yate athletes = athletemodel.get_namesID_from_store() print(yate.start_response()) print(yate.include_header("The NUAC's List of Athletes")) print(yate.start_form("generate_timing_data.py")) print(yate.para("Select an athlete from the list to work with:")) for each_athlete in sorted(athletes): print(yate.radio_button_id("which_athlete", each_athlete[0], each_athlete[1])) print(yate.end_form("Select")) print(yate.include_footer({"Home": "/index.html"}))
generate_timing_data.py修改为：
#! /usr/local/bin/python3 import cgi import athletemodel import yate form_data = cgi.FieldStorage() athlete_id = form_data['which_athlete'].value athlete = athletemodel.get_athlete_from_id(athlete_id) print(yate.start_response()) print(yate.include_header("NUAC's Timing Data")) print(yate.header("Athlete: " + athlete['Name'] + ", DOB: " + athlete['DOB'] + ".")) print(yate.para("The top times for this athlete are:")) print(yate.u_list(athlete['top3'])) print(yate.para("The entire set of timing data is: " + str(athlete['data']) + " (duplicates removed).")) print(yate.include_footer({"Home": "/index.html", "Select another athlete": "generate_list.py"}))
将SQLite数据库移至WEB应用的顶层目录（index.html所在目录），
index.html实现：
<html> <head> <title>Welcome to the National Underage Athetics Committee's Website</title> <link type="text/css" rel="stylesheet" href="coach.css" /> </head> <body> <img src="images/nuac-group.jpg"> <h1>Welcome to the NUAC's Website.</h1> <p> Here is our athlete's <a href="cgi-bin/generate_list.py">timing data</a>. Enjoy! </p> <p> <strong>See you on the track!</strong> </p> </body> </html>
一切正常！！！
更新SQLite中的选手数据：
修改add_timing_data.py CGI脚本，将你提交的数据写至数据库，而不是写到WEB服务器的控制台屏幕
add_timing_data.py修改为：
#! /usr/local/bin/python3 import cgi import sqlite3 import yate print(yate.start_response('text/plain')) form = cgi.FieldStorage() the_id = form['Athlete'].value the_time = form['Time'].value connection = sqlite3.connect('coachdata.sqlite') cursor = connection.cursor() cursor.execute("INSERT INTO timing_data (athlete_id, value) VALUES (?, ?)", (the_id, the_time)) connection.commit() connection.close() print('OK.')
test_form.py修改为：
#! /usr/local/bin/python3 import yate print(yate.start_response('text/html')) print(yate.do_form('add_timing_data.py', ['Athlete','Time'], text="Send"))
yate.py保持不变：
from string import Template def start_response(resp="text/html"): return('Content-type: ' + resp + '\n\n') def include_header(the_title): with open('templates/header.html') as headf: head_text = headf.read() header = Template(head_text) return(header.substitute(title=the_title)) def include_footer(the_links): with open('templates/footer.html') as footf: foot_text = footf.read() link_string = '' for key in the_links: link_string += '<a href="' + the_links[key] + '">' + key + '</a> ' footer = Template(foot_text) return(footer.substitute(links=link_string)) def start_form(the_url, form_type="POST"): return('<form action="' + the_url + '" method="' + form_type + '">') def end_form(submit_msg="Submit"): return('<p></p><input type=submit value="' + submit_msg + '">') def radio_button(rb_name, rb_value): return('<input type="radio" name="' + rb_name + '" value="' + rb_value + '"> ' + rb_value + '<br />') def radio_button_id(rb_name, rb_value, rb_id): return('<input type="radio" name="' + rb_name + '" value="' + str(rb_id) + '"> ' + rb_value + '<br />') def u_list(items): u_string = '<ul>' for item in items: u_string += '<li>' + item + '</li>' u_string += '</ul>' return(u_string) def header(header_text, header_level=2): return('<h' + str(header_level) + '>' + header_text + '</h' + str(header_level) + '>') def para(para_text): return('<p>' + para_text + '</p>') def create_inputs(inputs_list): html_inputs = '' for each_input in inputs_list: html_inputs = html_inputs + '<input type="Text" name="' + each_input + '" size=40>' return(html_inputs) def do_form(name, the_inputs, method="POST", text="Submit"): with open('templates/form.html') as formf: form_text = formf.read() inputs = create_inputs(the_inputs) form = Template(form_text) return(form.substitute(cgi_name=name, http_method=method, list_of_inputs=inputs, submit_text=text))
启动WEB服务器：
数据成功插入数据库！！！
