python系列学习七——使用sqlite管理数据

1. 前言

        Python2.5以上版本都提供了对sqlite的支持，只需导入sqlite3库，使用python标准化数据库api即可。

2. 如何使用sqlite3

# 导入sqlite3
import sqlite3
# 建立与数据库test.db的连接，test.db用来保存数据库和数据库表
conn = sqlite3.connect("test.db")
# 创建数据游标
cursor = conn.cursor()
# 执行sql
cursor.execute("select date('NOW')")
# 提交作出的修改，使修改永久保存
conn.commit()
# 完成操作关闭连接
conn.close()

3. sqlite创建数据库表

# coding=utf-8

import sqlite3

conn = sqlite3.connect("coachdata.sqlite")
cursor = conn.cursor()
cursor.execute("""
	CREATE TABLE athletes (
		id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
		name TEXT NOT NULL,
		dob Date NUT NULL
	)""")
cursor.execute("""
	create table timing_data (
		athlete_id integer not null,
		value text not null,
		foreign key (athlete_id) references athletes
	)
	""")
conn.commit()
conn.close()

4. sqlite数据库操作

import sqlite3
import glob
import athletemodel

conn = sqlite3.connect("coachdata.sqlite")
cursor = conn.cursor()

data_files = glob.glob('../data/*.txt')
athletes = athletemodel.put_to_store(data_files)

for ath in athletes:
	name = athletes[ath].name
	dob = athletes[ath].dob
	cursor.execute("Insert into athletes(name, dob) values (?, ?)", (name,dob))

	cursor.execute("select id from athletes where name=? and dob=?", (name,dob))
	the_current_id = cursor.fetchone()[0]
	for each_time in athletes[ath].clean_data:
		cursor.execute("insert into timing_data(athlete_id,value) values(?,?)", (the_current_id, each_time))
	conn.commit()

conn.close()

5. 获取数据

sql查询成功后会将返回结果增加到游标，可以在游标上调用不同方法来访问结果：

a.    cursor.fetchone()       返回下一个数据行

b.    cursor.fetchmany()    返回多个数据行

c.    cursor.fetchall()          返回所有数据

#coding=utf-8

import sqlite3

db_name = "coachdata.sqlite"

def get_namesId_from_store():
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    cursor.execute("select id, name from athletes")
    response = cursor.fetchall()
    conn.close()
    return response

def get_athlete_from_id(athlete_id):
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()

    cursor.execute("select name,dob from athletes where id=?",(athlete_id,))
    (name,dob) = cursor.fetchone()

    cursor.execute("select value from timing_data where athlete_id=?", (athlete_id,))
    data = [row[0] for row in cursor.fetchall()]

    response = {
        'name': name,
        'dob': dob,
        'data': data,
        'top3': data[0:3]
    }
    conn.close()
    return response

6. android实例

# coding=utf-8

import android
import json
import time
from urllib import urlencode
from urllib2 import urlopen

hello_msg = "Welcomd to Coach Kelly's Timing App"
list_title = "Here is your list of athletes:"
quit_msg = "Quitting Coach Kelly's App."
web_server = "http://192.168.115.1:8080"
get_name_cgi = "/cgi-bin/generate_names.py"
get_data_cgi = "/cgi-bin/generate_data.py"

def send_to_server(url, post_data=None):
	"""该函数取一个url和一些可选数据，向web服务器发送一个web请求，web响应返回给调用者"""
	if post_data:
		page = urlopen(url, urlencode(post_data))
	else:
		page = 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)

# 将web请求发送给服务器，把json相应转换为一个有序列表
athletes = sorted(json.loads(send_to_server(web_server+get_name_cgi)))
athlete_names = [row[1] for row in athletes]

# 创建一个包含两个按钮的对话框
app.dialogCreateAlert(list_title)
app.dialogSetSingleChoiceItems(athlete_names)
app.dialogSetPositiveButtonText('Select')
app.dialogSetNegativeButtonText('Quit')
app.dialogShow()

# 等待用户点击一个按钮，把结果赋给resp
resp = app.dialogGetResponse().result

if resp['which'] in ('positive'):
	selected_index = app.dialogGetSelectedItems().result[0]
	selected_id = athletes[selected_index][0]
	athlete = json.loads(send_to_server(web_server+get_data_cgi, {'athlete': selected_id}))
	athlete_title = athlete['name'] + ' (' + athlete['dob'] + '), top 3 times: '
	app.dialogCreateAlert(athlete_title)
	app.dialogSetItems(athlete['top3'])
	app.dialogSetPositiveButtonText('OK')
	app.dialogSetNegativeButtonText('AddTime')
	app.dialogShow()
	resp = app.dialogGetResponse().result
	if resp['which'] in ('positive'):
		pass
	elif resp['which'] in ('negative'):
		timing_title = 'Enter data'
		timing_msg = 'Provide a new timing value:'
		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, {'TimingValue': new_time, 'athlete':selected_id})

# 显示退出消息
status_update(quit_msg)

应用下载链接：http://download.csdn.net/detail/sonickonka/6728705