Python高级编程之数据库sqlite3（一）

SQLite数据库是一款非常小巧的嵌入式开源数据库软件，它使用一个文件存储整个数据库，操作十分方便。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月， 至今已经有14个年头，SQLite也迎来了一个版本 SQLite 3已经发布。
不像常见的客户-服务器范例，SQLite引擎不是个程序与之通信的独立进程，而是连接到程序中成为它的一个主要部分。所以主要的通信协议是在编程语言内的直接API调用。这在消耗总量、延迟时间和整体简单性上有积极的作用。整个数据库(定义、表、索引和数据本身)都在宿主主机上存储在一个单一的文件中。它的简单的设计是通过在开始一个事务的时候锁定整个数据文件而完成的。
在有些应用场景中，我们需要为插入到数据库服务器中的数据进行数据过滤或数据清理，以保证最终插入到数据库服务器中的数据有效性。有的时候，数据是否有效，不能通过单一一条记录来进行判断，而是需要和之前一小段时间的历史数据进行特殊的计算，再通过计算的结果判断当前的数据是否合法。在这种应用中，我们可以用SQLite缓冲这部分历史数据。还有一种简单的场景也适用于SQLite，即统计数据的预计算。比如我们正在运行数据实时采集的服务程序，我们可能需要将每10秒的数据汇总后，形成每小时的统计数据，该统计数据可以极大的减少用户查询时的数据量，从而大幅提高前端程序的查询效率。在这种应用中，我们可以将1小时内的采集数据均缓存在SQLite中，在达到整点时，计算缓存数据后清空该数据。

1. 创建数据库

一个SQLite数据库是以单个文件的方式存储在文件系统中。Python中的sqlite3提供了操作SQLite数据库的接口，并在多个用户连接到该库时提供锁保护。当我们指定的数据库文件不存在的时候连接对象会自动创建数据库文件，不过应用要负责管理数据库中的数据库表定义，即模式（schema）；如果数据库文件已经存在，则连接对象不会再创建数据库文件，而是直接打开该数据库文件。下面的例子中在使用connect（）函数连接之前先查看数据库文件是否存在以确定是否需要创建数据库文件，以便了解何时为数据库创建模式。

import os
import sqlite3

db_filename = 'todo.db'
db_is_new = not os.path.exists(db_filename)
conn = sqlite3.connect(db_filename)
 
if db_is_new:
    print 'Need to create schema'
else:
    print 'Database exists, assume schema does, too'
conn.close()

首次在执行该脚本时会打印Need to create schema，并创建todo.db文件，再次执行该脚本时，会打印Database exists, assume schema does, too，因为此时数据库文件已经存在了。

在创建完数据库之后，下一步便是在数据库中创建schema来定义表。下一个例子中使用上面创建的数据库文件来创建两张关系表project和task，task为project的任务，这两张表分别为：

The "project" Table

Column	Type	Description
name	text	项目名称
description	text	项目描述
deadline	date	项目预计完成的时间

The "task" table

Column	Type	Description
id	number	唯一任务标识符
priority	integer	任务优先级，数值越小越优先
details	text	完整的任务描述
status	text	任务状态(可以是 new,pending,done, or canceled)
deakline	date	任务预计结束日期
cimpleted_on	date	任务的完成时间
project	text	任务对应的项目名称

首先将创建两张表的SQL语言存入到名为todo_schema.sql的文件中，数据库模式定义语言（Data Definition Language,DDL）内容如下：

create table project(
        name text primary key,
        description text,
        deadline date
        );

create table task(
        id integer primary key autoincrement not null,
        priority integer default 1,
        details text,
        status text,
        deadline date,
        completed_on date,
        project text not null references project(name)
        );

Connection中的executescript()方法可以执行DDL指令来创建schema.

import os
import sqlite3

db_filename = 'todo.db'
schema_filename = 'todo_schema.sql'

db_is_new = not os.path.exists(db_filename)

with sqlite3.connect(db_filename) as conn:
    if db_is_new:
        print 'Creating schema'
        with open(schema_filename,'rt') as f:
            schema = f.read()
       conn.executescript(schema)

        print 'Inserting initial data'

        conn.executescript("""
                insert into project (name, description, deadline)
                values ('pyno