管理你的数据
使用表单或对话框接收输入
在web上,用户可以与web表单交互并输入数据。在android手机上,可以使用dialogGetInput()方法得到用户的输入,然后通过代码模拟web表单按钮的行为。
在coachapp.py中
athlete = json.loads(send_to_server(web_server + get_data_cgi,{'which_athlete': which_athlete}))
最后的which_athlete就是将数据包含在web请求中。
创建一个HTML表单模版
首先创建一个名为templates/form.html的模版
<form action="$cgi_name" method="$http_method">
Enter a timing value:
$list_of_inputs
<br />
<input type="Submit" value="$submit_text">
</form>
扩展yate.py增加两个函数
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))
数据传送到CGI脚本
在web服务器上,使用标准库cgi模块提供的功能访问CGI数据
import cgi
form=cgi.FieldStorage() #从CGI脚本访问发送到web服务器的数据并转换为一个字典
timing_value=form["TimeValue"].value
利用Python的内置支持,使用so库查询CGI脚本的环境。
import os
import time
import sys
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)
编写add_timing_data.py,它处理来自表单的数据,并把这些数据显示在web服务器的控制台屏幕上。
在android手机上请求输入
创建一个android应用,与用户交互两次。第一次对话框要求用户确认web服务器使用能够的web地址和端口。假设用户在这个对话框上点击ok按钮,会弹出第二个对话框,请求用户输入要发送到服务器的计时值。
import android
miprot urllib
server_title = 'Which server should I use?'
server_msg = "Please confirm the server address/name to use for your athlete's timing data:"
timing_title = 'Enter data'
timing_msg = 'Provide a new timing value:'
web_server = 'http://10.10.138.89:8080'
add_time_cgi = '/cgi-bin/add_timing_data.py'
app = android.Android()
def send_to_server(url, post_data=None):
if post_data:
page = urllib.urlopen(url, urllib.urlencode(post_data))
else:
page = urllib.urlopen(url)
return(page.read().decode("utf8"))
resp = app.dialogGetInput(server_title, server_msg, web_server).result
if resp is not None:
web_server = resp
resp = app.dialogGetInput(timing_title, timing_msg).result
if resp is not None:
new_time = resp
send_to_server(web_server + add_time_cgi, {'TimingValue': new_time})
Python包括SQLite
Python3预装了SQLite3,这是一个完备的,无需设置的基于sql的数据管理系统。
要使用SQLite,只需导入sqlite3库,并使用Python的标准化数据库API来编程。
- 连接:建立与所选数据库的一个连接
- 创建:创建一个游标,通过连接与数据通信
- 交互:利用游标,使用sql数据管理
- 提交或回滚
- 关闭:撤销与数据库后台的连接
数据库API的相应Python代码
import sqlite3
connettion=sqlite3.connect('test.sqiite')
cursor=connection.crusor()
cursor.execute("""SELECT DATE('NOW')""")
connection.commit()
connection.close()
定义数据库模式
创建数据库名为coachdata.sqlite,他有两个表,一个athletes,包含一个ID值,选手名和出生日期,一个timing_data,包含选手的唯一ID和具体的时间值。
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传输数据
cursor.execute("INSERT INTO athletes (name,dob) VALUES(?,?)",(name,dob)) #?占位符允许为SQL语句指定参数
查询数据库
cursor.execute("SELECT id from athletes WHERE name=? AND dob=?",(name,dob))
可以在游标上调用多个不同方法来访问结果
cursor.fetchone() 返回下一个数据行
cursor.fetchmany() 返回多个数据行
cursor.fetchall() 返回所有数据行
查询athletes表,得到选手的名字和出生日期,将结果赋至变量the_current_id。再写一个查询从pickle中抽取选手的时间,把它们增加到timing_data表。
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))
仍然需要名字列表
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)
修改generate的四个CGI脚本,将SQLite数据库移至web应用的顶层目录中。
修改android应用
import android
import json
import time
import urllib
hello_msg = "Welcome to Coach Kelly's Timing App"
list_title = 'Here is your list of athletes:'
quit_msg = "Quitting Coach Kelly's App."
web_server = 'http://10.10.138.89:8080'
get_names_cgi = '/cgi-bin/generate_names.py'
get_data_cgi = '/cgi-bin/generate_data.py'
def send_to_server(url, post_data=None):
if post_data:
page = urllib.urlopen(url, urllib.urlencode(post_data))
else:
page = urllib.urlopen(url)
return(page.read().decode("utf8"))
app = android.Android()
def status_update(msg, how_long=2):
app.makeToast(msg)
time.sleep(how_long)
status_update(hello_msg)
athletes = sorted(json.loads(send_to_server(web_server + get_names_cgi)))
athlete_names = [ath[0] for ath in athletes]
app.dialogCreateAlert(list_title)
app.dialogSetSingleChoiceItems(athlete_names)
app.dialogSetPositiveButtonText('Select')
app.dialogSetNegativeButtonText('Quit')
app.dialogShow()
resp = app.dialogGetResponse().result
if resp['which'] in ('positive'):
selected_athlete = app.dialogGetSelectedItems().result[0]
which_athlete = athletes[selected_athlete][1]
athlete = json.loads(send_to_server(web_server + get_data_cgi,{'which_athlete': which_athlete}))
athlete_title = athlete['Name'] + ' (' + athlete['DOB'] + '), top 3 times:'
app.dialogCreateAlert(athlete_title)
app.dialogSetItems(athlete['top3'])
app.dialogSetPositiveButtonText('OK')
# Need to add another button to add a timing value.
app.dialogSetNegativeButtonText('Add Time')
app.dialogShow()
resp = app.dialogGetResponse().result
if resp['which'] in ('positive'):
pass
elif resp['which'] in ('negative'):
timing_title = 'Enter a new time'
timing_msg = 'Provide a new timing value ' + athlete['Name'] + ': '
add_time_cgi = '/cgi-bin/add_timing_data.py'
resp = app.dialogGetInput(timing_title, timing_msg).result
if resp is not None:
new_time = resp
send_to_server(web_server + add_time_cgi,{'Time': new_time,'Athlete': which_athlete})
status_update(quit_msg)
在第二张图中可以看到之前上传的1.00已经更新到选手的时间列表。