python一张图浓缩_python浓缩（21）

数据库 和 Python RDBMSs, ORMs, and Python

Python 数据库应用程序程序员接口(DB-API)

关系数据库 (RDBMSs)

对象-关系管理器(ORMs)

关系模块

本章的主题是如何通过Python 访问数据库。

21.1 介绍

21.1.1 持久存储

在任何的应用程序中，都需要持久存储。一般说来，有三种基本的存储机制：文件、关系型数据库或其它的一些变种，例如现有系统的API，ORM、文件管理器、电子表格、配置文件等等。前面研究了通过基于常规文件的 Python 和 DBM 接口来实现持久存储, 比如*dbm, dbhas/bsddb 文件, shelve(pickle 和DBM 的结合). 这些接口都提供了类似字典的对象接口。

本章的主题是如何在中大型项目中使用关系型数据库.(对这些项目而言, 那些接口力不从心)

21.1.2 基本的数据库操作和SQL 语言

先简单介绍一下基本的数据库概念和结构化查询语言(SQL)。

底层存储

数据库的底层存储通常使用文件系统, 它可以是普通操作系统文件、专用操作系统文件,甚至有可能是磁盘分区。

用户界面

大部分的数据库系统会提供一个命令行工具来执行SQL 命令和查询，当然也有一些使用图形界面的客户端程序来干同样的事。

数据库

关系型数据库管理系统通常通常都支持多个数据库，例如销售库等等. 如果你使用的关系数据库管理系统是基于服务器的，这些数据库就都在同一台服务器上 (一些简单的关系型数据库没有服务器, 如sqlite). 本章的例子中, MySQL 是一种基于服务器的关系数据库管理系统(只要服务器在运行, 它就一直在等待运行指令)，SQLite 和Gadfly 则是另一种轻量型的基于文件的关系数据库(它们没有服务器)。

组件

可以将数据库存储想像为一个表格, 每行数据都有一个或多个字段对应着数据库中的列. 每个表每个列及其数据类型的集合构成数据库结构的定义. 数据库能够被创建, 也可以被删除. 表也一样. 这些操作通常作为数据库操作命令来提交. 从一个数据库中请求符合条件的数据称为查询(querying). 当你对一个数据库进行查询时, 你可以一步取回所有符合条件的数据, 也可以循环逐条取出每一行. 有些数据库使用游标的概念来表示 SQL 命令, 查询, 取回结果集等等.

SQL

数据库命令和查询操作需要通过SQL 语句来执行. 不是所有的数据库都使用SQL, 但所有主流的关系型数据库都使用SQL. 绝大多数数据库被配置为大小写不敏感,除了数据库操作命令以外. 被广为接受的书写SQL 的基本风格是关键字大写. 绝大多数命令行程序要求用一个分号来结束一条SQL 语句.

创建数据库

CREATE DATABASE test;

GRANT ALL ON test.* to user(s);

第二行将该数据库的权限赋给具体的用户(或者全部用户)

选择要使用的数据库

USE test;

删除数据库

DROP DATABASE test;

它用来删除数据库(包括数据库中所有的表及表中的数据).

创建表

CREATE TABLE users (login VARCHAR(8), uid INT, prid INT);

删除表

DROP TABLE users;

删除数据库中的一个表和它的所有数据。

插入行

INSERT INTO users VALUES('leanna', 311, 1);

语句中必须指定要插入的表及该表中各个字段的值。字符串'leanna' 对应着 login 字段，311 和 1 分别对应着uid 和prid。

更新行

UPDATE users SET prid=4 WHERE prid=2;

UPDATE users SET prid=1 WHERE uid=311;

使用 SET 关键字来指定你要修改的字段及新值,可以指定条件来筛选出需要更新的记录.

删除行

DELETE FROM users WHERE prid=%d;

DELETE FROM users;

如果未提供(可选的)筛选条件, 就象第二个例子一样, 表中所有的数据都会被删除.

21.1.3 数据库 和 Python

Python 直接通过数据库接口(Python 数据库API)或 通过 ORM来访问关系数据库.

类似数据库原理, 并发能力, 视图, 原子性, 数据完整性, 数据可恢复性, 还有左连接, 触发器, 查询优化, 事务支持, 及存储过程等等, 本章不讨论, 我们要了解在 python 框架下如何将数据保存到数据库, 如果将数据从数据库中取出来. 与数据库协同工作已经是几乎所有应用程序的核心部分，下面介绍一下 Python DB-API。

要适应其它的数据库也相当容易, 需要特别提到的是 Aaron Watter 的 Gadfly 数据库, 一个完全由Python 代码写成的数据库系统。从python 中访问数据库需要接口程序. 接口程序是一个 python 模块, 它提供数据库客户端库(通常是C 语言写成的)的接口供你访问。所有 Python 接口程序都一定程度上遵守 Python DB-API 规范.

图21.1 演绎了 Python 数据库应用程序的结构(包括使用和不使用 ORM). 你可以看到 DB-API是数据库客户端 C 库的接口.

图 21-1 数据库和应用程序之间的多层通讯.第一个盒子一般是 C/C++ 程序, 你的程序通过DB-API 兼容接口程序访问数据库.ORM 通过程序处理数据库细节来简化数据库开发.

21.2 Python 数据库应用程序

程序员接口(DB-API)

去 python.org 找到数据库主题那一节, 会发现所有支持 DB-API 2.0 的各种数据库模块, 文档, SIG 等等.  什么是 DB-API ?DB-API 是一个规范. 它定义了一系列必须的对象和数据库存取方式, 以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口。

DB-API 为不同的数据库提供了一致的访问接口, 在不同的数据库之间移植代码成为一件轻松的事情(一般来说, 只修要修改几行代码). 接下来你会看到这样的例子.

21.2.1 模块属性

一个 DB-API 兼容的模块必须定义下表 Table 21.1中定义的所有全局属性.

数据属性

apilevel

apilevel 这个字符串表示这个模块所兼容的DB-API 最高版本号. 如果未定义, 则默认是 "1.0";

threadsafety

threadsafety 这是一个整数, 取值范围如下：

0:不支持线程安全, 多个线程不能共享此模块

1:初级线程安全支持: 线程可以共享模块, 但不能共享连接

2:中级线程安全支持 线程可以共享模块和连接, 但不能共享游标

3:完全线程安全支持 线程可以共享模块, 连接及游标.

如果一个资源被共享, 就必需使用自旋锁或者是信号量这样的同步原语对其进行原子目标锁定。对这个目标来说, 磁盘文件和全局变量都不可靠, 并且有可能妨碍 .

mutex(互斥量)的操作. 参阅 threading 模块或第16 章(多线程编程)来了解如何使用锁.

paramstyle

DB-API 支持多种方式的SQL 参数风格. 这个参数是一个字符串, 表明SQL 语句中字符串替代的方式. (参阅表21.2)

表21.2 数据库参数风格

函数属性

connect 方法生成一个connect 对象, 通过这个对象来访问数据库. 符合标准的模块都会实现 connect 方法. 数据库连接参数可以以一个 DSN 字符串的形式提供, 也可以以多个位置相关参数的形式提供(如果你明确知道参数的顺序的话), 也可以以关键字参数的形式提供：

connect(dsn='myhost:MYDB',user='guido',password='234$')

使用 DSN 字符串还是独立参数? 这要看你连接的是哪种数据库. 如果你使用类似ODBC 或 JDBC 的 API, 你就应该使用 DSN 字符串. 如果你直接访问数据库, 你就会更倾向于使用独立参数. 另一个使用独立参数的原因是, 很多数据库接口程序还不支持 DSN 参数. 下面是一个非 DSN 的例子.

connect()调用. 注意不是所有的接口程序都是严格按照规范实现的. MySQLdb 就使用了 db 参数而不是规范推荐的 database 参数来表示要访问的数据库.

MySQLdb.connect(host='dbserv', db='inv', user='smith')

PgSQL.connect(database='sales')

psycopg.connect(database='template1', user='pgsql')

gadfly.dbapi20.connect('csrDB', '/usr/local/database')

sqlite3.connect('marketing/test')

异常

兼容标准的模块也应该提供这些异常类. 见表 21.4

21.2.2 连接对象

要与数据库进行通信, 必须先和数据库建立连接. 连接对象处理命令如何送往服务器, 以及如何从服务器接收数据等基础功能. 连接成功(或一个连接池)后你就能够向数据库服务器发送请求,得到响应.

方法

连接对象没有必须定义的数据属性, 但是它至少应该定义表21.5 中的这些方法.一旦执行了 close() 方法, 再试图使用连接对象的方法将会导致异常.

对不支持事务的数据库或者虽然支持事务, 但设置了自动提交(auto-commit)的数据库系统来说, commit()方法什么也不做. 如果你确实需要, 可以实现一个自定义方法来关闭自动提交行为.由于 DB-API 要求必须实现此方法, 对那些没有事务概念的数据库来说, 这个方法只需要有一条pass 语句就可以了. 在提交commit()之前关闭数据库连接将会自动调用rollback()方法.

对不支持游标的数据库来说, cursor()方法仍然会返回一个尽量模仿游标对象的对象. 这些是最低要求. 特定数据库接口程序的开发者可以任意为他们的接口程序添加额外的属性, 只要他们愿意.

DB-API 规范建议但不强制接口程序的开发者为所有数据库接口模块编写异常类. 如果没有提供异常类, 则假定该连接对象会引发一致的模块级异常. 一旦你完成了数据库连接, 并且关闭了游标对象, 你应该执行 commit() 提交你的操作, 然后关闭这个连接.

21.2.3 游标对象

建立连接之后, 就可以与数据库进行交互. 一个游标允许用户执行数据库命令和得到查询结果. 一个 DB-API 游标对象总是扮演游标的角色, 无论数据库是否真正支持游标. 从这一点讲, 数据库接口程序必须实现游标对象. 只有这样, 才能保证无论使用何种后端数据库你的代码都不需要做任何改变.

创建游标对象之后, 你就可以执行查询或其它命令(或者多个查询和多个命令), 也可以从结果集中取出一条或多条记录. 表 21.6 列举了游标对象拥有的属性和方法.

游标对象最重要的属性是 execute*() 和 fetch*() 方法. 所有对数据库服务器的请求都由它们来完成.对fetchmany()方法来说, 设置一个合理的arraysize 属性会很有用. 当然, 在不需要时关掉游标对象也是个好主意. 如果你的数据库支持存储过程, 你就可以使用callproc() 方法.

21.2.4 类型对象和构造器

通常两个不同系统的接口要求的参数类型是不一致的,譬如python调用C函数时Python对象和C类型之间就需要数据格式的转换, 反之亦然. 类似的, 在Python 对象和原生数据库对象之间也是如此. 对于 Python DB-API 的开发者来说, 你传递给数据库的参数是字符串形式的, 但数据库会根据需要将它转换为多种不同的形式. 以确保每次查询能被正确执行.

举例来说, 一个 Python 字符串可能被转换为一个 VARCHAR, 或一个TEXT, 或一个BLOB, 或一个原生 BINARY 对象, 或一个DATE 或TIME 对象. 一个字符串到底会被转换成什么类型? 必须小心的尽可能以数据库期望的数据类型来提供输入, 因此另一个DB-API 的需求是创建一个构造器以生成特殊的对象, 以便能够方便的将Python 对象转换为合适的数据库对象. 表21.7 描述了可以用于此目的的类. SQL 的 NULL 值被映射为 Pyhton 的 NULL 对象, 也就是 None.

DB-API 版本变更

有几个重要的变更发生在 DB-API 从1.0(1996)升级到2.0(1999)时:

从 API 中移除了原来必须的 dbi 模块

更新了类型对象

增加了新的属性以提供更易用的数据库绑定

变更了 callproc() 的语义及重定义了 execute() 的返回值

基于异常的错误处理

自从 DB-API 2.0 发布以来, 曾经在2002 年加入了一些可选的 DB- API 扩展, 但一直没有什么重大的变更. 在DB-SIG 邮件列表中一直在讨论DB-API 的未来版本 -- 暂时命名为 DB-API 3.0. 它将包括以下特性:

当有一个新的结果集时nextset()会有一个更合适的返回值

float 变更为 Decimal

支持更灵活的参数风格

预备语句或语句缓存

优化事务模型

确定 DB-API 可移值性的角色

增加单元测试

如果你对这些API 特别感兴趣, 欢迎积极参与. 下面有一些手边的资源.

http://python.org/topics/database

http://www.linuxjournal.com/article/2605

http://wiki.python.org/moin/DbApi3

21.2.5 关系数据库

在Pyhton 里可以使用哪种数据库接口? 换言之,Python 支持哪些平台? 是几乎所有的平台. 下面是一个不怎么完整的数据库支持列表:

商业关系数据库管理系统

􀁺 Informix

􀁺 Sybase

􀁺 Oracle

􀁺 MS SQL Server

􀁺 DB/2

􀁺 SAP

􀁺 Interbase

􀁺 Ingres

开源关系数据库管理系统

􀁺 MySQL

􀁺 PostgreSQL

􀁺 SQLite

􀁺 Gadfly

数据库APIs

􀁺 JDBC

􀁺 ODBC

要知道 Python 支持哪些数据库, 请参阅下面网址:

http://python.org/topics/database/modules.html

21.2.6 数据库和Python：接口程序

某些数据库, 比如 Sybase, SAP, Oracle 和SQLServer, 都有两个或更多个接口程序可供选择. 你要做的就是挑选一个最能满足你需求的接口程序. 你挑选接口程序的标准可以是: 性能如何? 文档或WEB 站点的质量如何? 是否有一个活跃的用户或开发社区? 接口程序的质量和稳定性如何? 等等等等. 记住绝大多数接口程序只提供基本的连接功能, 你可能需要一些额外的特性. 高级应用代码如线程和线程管理以及数据库连接池的管理等等, 需要你自己来完成.

如果你不想处理这些, 比方你不喜欢自己写SQL, 也不想参与数据库管理的细节--那么本章后面讲到的 ORM 应该可以满足你的要求. 现在来看一些使用接口程序访问数据库的例子, 关键之处在于设置数据库连接.在建立连接之后, 不管后端是何种数据库, 对 DB-API 对象的属性和方法进行操作都是一样的.

21.2.7 使用数据库接口程序举例

创建数据库, 创建表, 使用表. 分别提供了使用 MySQL,PostgreSQL, SQLite 的例子.

MySQL

使用唯一的 MySQL 接口程序: MySQLdb, 又名MySQL-python.

>>> import MySQLdb

>>> cxn = MySQLdb.connect(user='root')

>>> cxn.query('DROP DATABASE test') Traceback (most recent call last):

File "", line 1, in ?

_mysql_exceptions.OperationalError: (1008, "Can't drop database 'test'; database

doesn't exist")

>>> cxn.query('CREATE DATABASE test')

>>> cxn.query("GRANT ALL ON test.* to ''@'localhost'")

>>> cxn.commit()

>>> cxn.close()

没有使用cursor 对象。某些(但不是所有的)接口程序拥有连接对象，这些连接对象拥有query()方法， 可以执行SQL 查询。我们建议你不要使用这个方法， 或者事先检查该方法在当前接口程序当中是否可用。之后我们以普通用户身份再次连接这个新数据， 创建表，然后通过Python 执行SQL 查询和命令, 来完成我们的工作。这次我们使用游标对象(cursors)和它们的execute()方法, 下一个交互集演示了创建表。

>>> cxn = MySQLdb.connect(db='test')

>>> cur = cxn.cursor()

>>> cur.execute('CREATE TABLE users(login VARCHAR(8), uid INT)')

0L

>>> cur.execute("INSERT INTO users VALUES('john', 7000)")

1L

>>> cur.execute("INSERT INTO users VALUES('jane', 7001)")

1L

>>> cur.execute("INSERT INTO users VALUES('bob', 7200)")

1L

>>> cur.execute("SELECT * FROM users WHERE login LIKE 'j%'")

2L

>>> for data in cur.fetchall():

... print '%s\t%s' % data

...

john 7000

jane 7001

最后一个特性是更新表, 包括更新或删除数据.

>>> cur.execute("UPDATE users SET uid=7100 WHERE uid=7001")

1L

>>> cur.execute("SELECT * FROM users")

3L

>>> for data in cur.fetchall():

... print '%s\t%s' % data

...

john 7000

jane 7100

bob 7200

>>> cur.execute('DELETE FROM users WHERE login="bob"')

1L

>>> cur.execute('DROP TABLE users')

0L

>>> cur.close()

>>> cxn.commit()

>>> cxn.close()

不过Python 标准库中并没有集成这个接口程序, 这是一个第三方包, 你需要单独下载并安装它.

PostgreSQL

与 MySQL 不同, 有至少 3 个 Python 接口程序可以访问 PosgreSQL: psycopg, PyPgSQL 和 PyGreSQL. 多亏他们都支持 DB-API, 所以他们的接口基本一致, 你只需要写一个应用程序, 然后分别测试这三个接口的性能(如果性能对你的程序很重要的化). 下面我给出这三个接口的连接代码:

psycopg

>>> import psycopg

>>> cxn = psycopg.connect(user='pgsql')

PyPgSQL

>>> from pyPgSQL import PgSQL

>>> cxn = PgSQL.connect(user='pgsql')

PyGreSQL

>>> import pgdb

>>> cxn = pgdb.connect(user='pgsql')

好, 下面的代码就能够在所有接口程序下工作了.

>>> cur = cxn.cursor()

>>> cur.execute('SELECT * FROM pg_database')

>>> rows = cur.fetchall()

>>> for i in rows:

... print i

>>> cur.close()

>>> cxn.commit()

>>> cxn.close()

最后, 你会发现他们的输出有一点点轻微的不同.

PyPgSQL

sales

template1

template0

psycopg

('sales', 1, 0, 0, 1, 17140, '140626', '3221366099','', None, None)

('template1', 1, 0, 1, 1, 17140, '462', '462', '', None,'{pgsql=C*T*/pgsql}')

('template0', 1, 0, 1, 0, 17140, '462', '462', '', None,'{pgsql=C*T*/pgsql}')

PyGreSQL

['sales', 1, 0, False, True, 17140L, '140626','3221366099', '', None, None]

['template1', 1, 0, True, True, 17140L, '462', '462','', None, '{pgsql=C*T*/pgsql}']

['template0', 1, 0, True, False, 17140L, '462','462', '', None, '{pgsql=C*T*/pgsql}']

SQLite

对非常简单的应用来说, 使用文件进行持久存储通常就足够了. 但对于绝大多数数据驱动的应用程序必须使用全功能的关系数据库. SQLite 介于二者之间, 它定位于中小规模的应用.它是相当轻量级的全功能关系型数据库, 速度很快, 几乎不用配置, 并且不需要服务器.SQLite 正在快速的流行. 并且在各个平台上都能用. 在 python2.5 集成了前面介绍的pysqlite 数据库接口程序, 作为 python2.5 的 sqlite3 模块. 这是 Python 标准库第一次将一个数据库接口程序纳入标准库, 也许这标志着一个新的开始.

它被打包到 Python 当中并不是因为他比其它的数据库接口程序更优秀, 而是因为他足够简单,使用文件(或内存)作为它的后端存储, 就象 DBM 模块做的那样, 不需要服务器, 而且也不存在授权问题. 它是Python 中其它的持久存储解决方案的一个替代品, 一个拥有 SQL 访问界面的优秀替代品. 在标准库中有这么一个模块, 就能方便用户使用Python 和 SQLite 进行软件开发, 等到软件产品正式上市发布时, 只要需要, 就能够很容易的将产品使用的数据库后端变更为一个全功能的,更强大的类似 MySQL, PostgreSQL, Oracle 或 SQL Server 那样的数据库.

>>> import sqlite3

>>> cxn = sqlite3.connect('sqlite_test/test')

>>> cur = cxn.cursor()

>>> cur.execute('CREATE TABLE users(login VARCHAR(8), uid INTEGER)')

>>> cur.execute('INSERT INTO users VALUES("john", 100)')

>>> cur.execute('INSERT INTO users VALUES("jane", 110)')

>>> cur.execute('SELECT * FROM users')

>>> for eachUser in cur.fetchall():

... print eachUser

...

(u'john', 100)

(u'jane', 110)

>>> cur.execute('DROP TABLE users')

>>> cur.close()

>>> cxn.commit()

>>> cxn.close()

接下来, 我们来看一个小程序, 它类似前面使用 MySQL 的例子。

创建数据库(如果成功)

创建表

从表中增加行

从表中修改行

从表中删除行

删除表

这个例子中用到的另一个数据库是 Gadfly, 一个基本兼容 SQL 的纯 Python 写成的关系数据库. (某些关键的数据库结构有一个C 模块, 不过 Gadfly 没有它也一样可以运行[当然, 会慢不少]).

SQLite 和Gadfly 需要用户指定保存数据库文件的位置(Mysql有一个默认区域保存数据, 在使用Mysql 数据库时无需指定这个). 另外, Gadfly 目前的版本还不兼容 DB-API 2.0, 也就是说, 它缺失一些功能, 尤其是缺少我们例子中用到的 cursor 属性rowcount.

数据库接口程序应用程序举例

下面演示了如何访问数据库. 事实上, 我们的程序支持三种不同的数据库系统: Gadfly, SQLite 和 MySQL.

#!/usr/bin/env python

import os

from random import randrange as rrange

COLSIZ = 10

RDBMSs = {'s': 'sqlite', 'm': 'mysql', 'g': 'gadfly'}

# 值得留意的是 DB_EXC 常量, 它代表数据库异常. 他最终的值由用户最终选择使用的数据库决定.

DB_EXC = None

def setup():

return RDBMSs[raw_input('''

Choose a database system:

(M)ySQL

(G)adfly

(S)QLite

Enter choice: ''').strip().lower()[0]]

def connect(db, dbName):

global DB_EXC

dbDir = '%s_%s' % (db, dbName)

if db == 'sqlite':

# 首先尝试载入标准库模块 sqlite3(Python2.5 及更高版本支持), 如果载入失败, 就会去寻找第三方 pysqlite2 包.

try:

import sqlite3

except ImportError, e:

try:

from pysqlite2 import dbapi2 as sqlite3

except ImportError, e:

return None

DB_EXC = sqlite3

# 确认一下数据库文件所在的目录是否存在.(当然, 你也可以选择在内存里创建一个数据库)

if not os.path.isdir(dbDir):

os.mkdir(dbDir)

cxn = sqlite3.connect(os.path.join(dbDir, dbName))

elif db == 'mysql':

# 数据文件会存保在默认的数据存储区域, 所以不需要用户指定存储位置

try:

import MySQLdb

import _mysql_exceptions as DB_EXC

except ImportError, e:

return None

try:

cxn = MySQLdb.connect(db=dbName)

except DB_EXC.OperationalError, e:

cxn = MySQLdb.connect(user='root')

try:

cxn.query('DROP DATABASE %s' % dbName)

except DB_EXC.OperationalError, e:

pass

cxn.query('CREATE DATABASE %s' % dbName)

cxn.query("GRANT ALL ON %s.* to ''@'localhost'" % dbName)

cxn.commit()

cxn.close()

cxn = MySQLdb.connect(db=dbName)

elif db == 'gadfly':

# 使用类似SQLite 的启动机制: 它的启动目录是数据文件所在的目录

try:

from gadfly import gadfly

DB_EXC = gadfly

except ImportError, e:

return None

# 如果数据文件在那儿, OK, 如果那儿没有数据文件,你必须重新启动一个新的数据库

try:

cxn = gadfly(dbName, dbDir)

except IOError, e:

cxn = gadfly()

if not os.path.isdir(dbDir):

os.mkdir(dbDir)

cxn.startup(dbName, dbDir)

else:

return None

return cxn

def create(cur):

# 这个代码有一个缺陷, 就是当重建表仍然失败的话, 你将陷入死循环, 直至内存耗尽

try:

cur.execute('''

CREATE TABLE users (

login VARCHAR(8),

uid INTEGER,

prid INTEGER)

''')

except DB_EXC.OperationalError, e:

drop(cur)

create(cur)

drop = lambda cur: cur.execute('DROP TABLE users')

# 它由一组固定用户名及ID 值的集合及一个生成器函数 randName() 构成.

# NAMES 常量是一个元组, 因为我们在randName()这个生成器里需要改变它的值, 所以我们必须在 randName()里先将它转换为一个列表.

# 我们一次随机的移除一个名字, 直到列表为空为止. 如果 NAMES 本身是一个列表, 我们只能使用它一次(它就被消耗光了).

# 我们将它设计成为一个元组, 这样我们就可以多次从这个元组生成一个列表供生成器使用.

NAMES = (

('aaron', 8312), ('angela', 7603), ('dave', 7306),

('davina',7902), ('elliot', 7911), ('ernie', 7410),

('jess', 7912), ('jim', 7512), ('larry', 7311),

('leslie', 7808), ('melissa', 8602), ('pat', 7711),

('serena', 7003), ('stan', 7607), ('faye', 6812),

('amy', 7209),

)

def randName():

pick = list(NAMES)

while len(pick) > 0:

yield pick.pop(rrange(len(pick)))

def insert(cur, db):

# insert()函数里的代码是依赖具体数据库的. 举例来说,SQLite 和 MySQL 的接口程序都是 DB-API 兼容的, 所以它们的游标对象都拥有 executemany()方法,

# 可是是 Gadfly 没有这个方法, 因此它只能一次插入一行.另一个不同之处在于 SQLite 和 Gadfly 的参数风格是 qmark, 而MySQL 的参数风格是format.

if db == 'sqlite':

cur.executemany("INSERT INTO users VALUES(?, ?, ?)",

[(who, uid, rrange(1,5)) for who, uid in randName()])

elif db == 'gadfly':

for who, uid in randName():

cur.execute("INSERT INTO users VALUES(?, ?, ?)",

(who, uid, rrange(1,5)))

elif db == 'mysql':

cur.executemany("INSERT INTO users VALUES(%s, %s, %s)",

[(who, uid, rrange(1,5)) for who, uid in randName()])

getRC = lambda cur: cur.rowcount if hasattr(cur, 'rowcount') else -1

def update(cur):

fr = rrange(1,5)

to = rrange(1,5)

cur.execute(

"UPDATE users SET prid=%d WHERE prid=%d" % (to, fr))

return fr, to, getRC(cur)

def delete(cur):

rm = rrange(1,5)

cur.execute('DELETE FROM users WHERE prid=%d' % rm)

return rm, getRC(cur)

def dbDump(cur):

# 首先, 通过 fetchall() 方法读取数据, 然后迭代遍历每个用户, 将三列数据(login, uid,prid)转换为字符串(如果它们还不是的话),

# 并将姓和名的首字母大写, 再格式化整个字符为左对齐的COLSIZ 列.( 右边留白) .

# 由代码生成的字符串是一个列表( 通过列表解析， listcomprehension), 我们需要将它们转换成一个元组以支持 % 运算符.

cur.execute('SELECT * FROM users')

print '\n%s%s%s' % ('LOGIN'.ljust(COLSIZ),

'USERID'.ljust(COLSIZ), 'PROJ#'.ljust(COLSIZ))

for data in cur.fetchall():

print '%s%s%s' % tuple([str(s).title().ljust(COLSIZ) \

for s in data])

def main():

db = setup()

print '*** Connecting to %r database' % db

cxn = connect(db, 'test')

if not cxn:

print '\nERROR: %r not supported, exiting' % db

return

cur = cxn.cursor()

print '\n*** Creating users table'

create(cur)

print '\n*** Inserting names into table'

insert(cur, db)

dbDump(cur)

print '\n*** Randomly moving folks',

fr, to, num = update(cur)

print 'from one group (%d) to another (%d)' % (fr, to)

print '\t(%d users moved)' % num

dbDump(cur)

print '\n*** Randomly choosing group',

rm, num = delete(cur)

print '(%d) to delete' % rm

print '\t(%d users removed)' % num

dbDump(cur)

print '\n*** Dropping users table'

drop(cur)

cur.close()

cxn.commit()

cxn.close()

if __name__ == '__main__':

main()

在数据库连接建立以后, 其余的代码对数据库和接口程序来说都是透明的(不区分哪种数据库,哪种接口程序, 代码都可以工作). 有一个唯一的例外, 就是脚本的 insert()函数. 在所有三个小节的这段代码里, 数据库连接成功后会返回一个连接对象 cxn.

它返回最后一步操作所影响的行数, 如果游标对象不支持这个属性(也就是说这个接口程序不兼容 DB-API)的话, 它返回 -1. python 2.5 中新增了条件表达式, 如果你使用的是python 2.4.x 或更老版本, 你可能需要将它转换为老风格的方式, 如下:

getRC = lambda cur: (hasattr(cur, 'rowcount') \

and [cur.rowcount] or [-1])[0]

(假定它们没有因为找不到数据库接口程序或者不能得到有效连接对象而中途退出[第143-145 行]).

21.3 对象-关系管理器(ORMs)

如果你是一个喜欢折腾 Python 对象却讨厌 SQL 查询的家伙, 又想使用关系型数据库做为你的数据存储的后端, 你就完全具备成为一个 ORM 用户的天资.

21.3.1 考虑对象，而不是SQL

这些系统的创建者将绝大多数纯 SQL 层功能抽象为Python 对象, 这样你就无需编写SQL 也能够完成同样的任务. 数据库的表被转换为 Python 类, 它具有列属性和操作数据库的方法. 由于大部分工作由 ORM 代为处理, 相比直接使用接口程序来说, 一些事情可能实际需要更多的代码.

21.3.2 Python 和 ORM

如今最知名的 Python ORM 模块是 SQLAlchemy 和 SQLObject. 会分别给出 SQLAlchemy 和 SQLObject 的例子. 其它的 Python ORM 包括 PyDO/PyDO2, PDO, Dejavu, Durus, QLime 和 ForgetSQL. 一些大型的Web 开发工具/框架也可以有自己的 ORM 组件, 如 WebWare MiddleKit 和 Django 的数据库 API.

21.3.3 雇员数据库举例

将 shuffle 应用程序 ushuffle_db.py 改造为使用 SQLAlchemy 和 SQLObject 实现. 数据库后端仍然是 MySQL.  两个例子都使用了 ushuffle_db.py 中的 NAMES 集合和 随机名字选择函数.

SQLAlchemy

与SQLObject 相比, SQLAlchemy 的接口在某种程度上更接近 SQL, 所以我们先从 SQLAlchemy开始.SQLAlchemy 的抽象层确实相当完美, 而且在你必须使用SQL 完成某些功能时, 它提供了足够的灵活性. 你会发现这两个ORM 模块在设置及存取数据时使用的术语非常相似, 代码长度也很接近,都比ushuffle_db.py 少.

倡导首先导入Python 标准库模块, 然后再导入第三方或扩展模块, 最后导入本地模块这种风格. 这些常量都是自解释的, 所以无需废话.

第12-31 行

是类的构造器, 类似 ushuffle_db.connect(). 它确保数据库可用并返回一个有效连接(第18-31 行). 这也是唯一能看到原始 SQL 的地方. 这是一种典型的操作任务, 不是面向应用的任务.

第33-44 行

这个 try-except 子句(第33-40 行)用来重新载入一个已有的表, 或者在表不存在的情况下创建一个新表. 最终我们得到一个合适的对象实例.

例子21.2 SQLAlchemy ORM 例子

这个 user shuffle 程序的主角是 SQLAlchemy 前端 和 MySQL 数据库后端

1 #!/usr/bin/env python

2

3 import os

4 from random import randrange as rrange

5 from sqlalchemy import *

6 from ushuffle_db import NAMES, randName

7

8 FIELDS = ('login', 'uid', 'prid')

9 DBNAME = 'test'

10 COLSIZ = 10

11

12 class MySQLAlchemy(object):

13 def __init__(self, db, dbName):

14 import MySQLdb

15 import _mysql_exceptions

16 MySQLdb = pool.manage(MySQLdb)

17 url = 'mysql://db=%s' % DBNAME

18 eng = create_engine(url)

19 try:

20 cxn = eng.connection()

21 except _mysql_exceptions.OperationalError, e:

22 eng1 = create_engine('mysql://user=root')

23 try:

24 eng1.execute('DROP DATABASE %s' % DBNAME)

25 except _mysql_exceptions.OperationalError, e:

26 pass

27 eng1.execute('CREATE DATABASE %s' % DBNAME)

28 eng1.execute(

29 "GRANT ALL ON %s.* TO ''@'localhost'" % DBNAME)

30 eng1.commit()

31 cxn = eng.connection()

32

33 try:

Edit By Vheavens

Edit By Vheavens

34 users = Table('users', eng, autoload=True)

35 except exceptions.SQLError, e:

36 users = Table('users', eng,

37 Column('login', String(8)),

38 Column('uid', Integer),

39 Column('prid', Integer),

40 redefine=True)

41

42 self.eng = eng

43 self.cxn = cxn

44 self.users = users

45

46 def create(self):

47 users = self.users

48 try:

49 users.drop()

50 except exceptions.SQLError, e:

51 pass

52 users.create()

53

54 def insert(self):

55 d = [dict(zip(FIELDS,

56 [who, uid, rrange(1,5)])) for who,uid in randName()]

57 return self.users.insert().execute(*d).rowcount

58

59 def update(self):

60 users = self.users

61 fr = rrange(1,5)

62 to = rrange(1,5)

63 return fr, to, \

64 users.update(users.c.prid==fr).execute(prid=to).rowcount

65

66 def delete(self):

67 users = self.users

68 rm = rrange(1,5)

69 return rm, \

70 users.delete(users.c.prid==rm).execute().rowcount

71

72 def dbDump(self):

73 res = self.users.select().execute()

74 print '\n%s%s%s' % ('LOGIN'.ljust(COLSIZ),

75 'USERID'.ljust(COLSIZ), 'PROJ#'.ljust(COLSIZ))

76 for data in res.fetchall():

77 print '%s%s%s' % tuple([str(s).title().ljust

(COLSIZ) for s in data])

78

79 def __getattr__(self, attr):

80 return getattr(self.users, attr)

81

82 def finish(self):

83 self.cxn.commit()

84 self.eng.commit()

85

86 def main():

87 print '*** Connecting to %r database' % DBNAME

88 orm = MySQLAlchemy('mysql', DBNAME)

89

90 print '\n*** Creating users table'

91 orm.create()

92

93 print '\n*** Inserting names into table'

94 orm.insert()

95 orm.dbDump()

96

97 print '\n*** Randomly moving folks',

98 fr, to, num = orm.update()

99 print 'from one group (%d) to another (%d)' % (fr, to)

100 print '\t(%d users moved)' % num

101 orm.dbDump()

102

103 print '\n*** Randomly choosing group',

104 rm, num = orm.delete()

105 print '(%d) to delete' % rm

106 print '\t(%d users removed)' % num

107 orm.dbDump()

108

109 print '\n*** Dropping users table'

110 orm.drop()

111 orm.finish()

112

113 if __name__ == '__main__':

114 main()

例子21.2 SQLAlchemy ORM 示例(ushuffle_sa.py)

第46-70 行

这四个方法处理数据库核心功能: 创建表(46-52 行), 插入数据(54-57 行), 更新数据(59-64行), 删除数据(66-70 行). 我们也有一个方法用来删除表.

def drop(self):

self.users.drop()

or

drop = lambda self: self.users.drop()

不过, 我们还是决定提供另一种委托处理方式(曾在第13 章, 面向对象编程中介绍). 委托处理就是指一个方法调用不存在时, 转交给另一个拥有此方法的对象去处理. 参见第79-80 行的解释.

第72-77 行

输出内容由dbDump()方法完成. 它从数据库中得到数据, 就象 ushuffle_db.py 中那样对数据进行美化, 事实上, 这部分代码几乎完全相同.

Lines 79–80

应该尽量避免为一个表创建一个 drop() 方法, 因为这总是会调用 table 自身的 drop() 方法. 同样, 既然没有新增功能, 那我们有什么必要要创建另一个函数?无论属性查找是否成功特殊方法 __getattr__() 总是会被调用. 如果调用 orm.drop() 却发现这个对象并没有 drop() 方法,getattr(orm, 'drop')就会被调用. 发生这种情况时,__getattr__() 被调用, 之后将这个属性名委托给 self.users. 解释器会发现 self.users 有一个 drop 属性并执行.

Example 21.3 SQLObject ORM Example (ushuffle_so.py)

这个 user shuffle 应用程序的主角前端是 SQLObject, 后端是MySQL 数据库.

1 #!/usr/bin/env python

2

3 import os

4 from random import randrange as rrange

5 from sqlobject import *

6 from ushuffle_db import NAMES, randName

7

8 DBNAME = 'test'

9 COLSIZ = 10

Edit By Vheavens

Edit By Vheavens

10 FIELDS = ('login', 'uid', 'prid')

11

12 class MySQLObject(object):

13 def __init__(self, db, dbName):

14 import MySQLdb

15 import _mysql_exceptions

16 url = 'mysql://localhost/%s' % DBNAME

17

18 while True:

19 cxn = connectionForURI(url)

20 sqlhub.processConnection = cxn

21 #cxn.debug = True

22 try:

23 class Users(SQLObject):

24 class sqlmeta:

25 fromDatabase = True

26 login = StringCol(length=8)

27 uid = IntCol()

28 prid = IntCol()

29 break

30 except _mysql_exceptions.ProgrammingError, e:

31 class Users(SQLObject):

32 login = StringCol(length=8)

33 uid = IntCol()

34 prid = IntCol()

35 break

36 except _mysql_exceptions.OperationalError, e:

37 cxn1 = sqlhub.processConnection=

connectionForURI('mysql://root@localhost ')

38 cxn1.query("CREATE DATABASE %s" % DBNAME)

39 cxn1.query("GRANT ALL ON %s.* TO ''@'

localhost'" % DBNAME)

40 cxn1.close()

41 self.users = Users

42 self.cxn = cxn

43

44 def create(self):

45 Users = self.users

46 Users.dropTable(True)

47 Users.createTable()

48

49 def insert(self):

50 for who, uid in randName():

51 self.users(**dict(zip(FIELDS,

52 [who, uid, rrange(1,5)])))

53

54 def update(self):

55 fr = rrange(1,5)

56 to = rrange(1,5)

57 users = self.users.selectBy(prid=fr)

58 for i, user in enumerate(users):

59 user.prid = to

60 return fr, to, i+1

61

62 def delete(self):

63 rm = rrange(1,5)

64 users = self.users.selectBy(prid=rm)

65 for i, user in enumerate(users):

66 user.destroySelf()

67 return rm, i+1

68

69 def dbDump(self):

70 print '\n%s%s%s' % ('LOGIN'.ljust(COLSIZ),

71 'USERID'.ljust(COLSIZ), 'PROJ#'.ljust(COLSIZ))

72 for usr in self.users.select():

73 print '%s%s%s' % (tuple([str(getattr(usr,

74 field)).title().ljust(COLSIZ) \

75 for field in FIELDS]))

76

77 drop = lambda self: self.users.dropTable()

78 finish = lambda self: self.cxn.close()

79

80 def main():

81 print '*** Connecting to %r database' % DBNAME

82 orm = MySQLObject('mysql', DBNAME)

83

84 print '\n*** Creating users table'

85 orm.create()

86

87 print '\n*** Inserting names into table'

88 orm.insert()

89 orm.dbDump()

90

91 print '\n*** Randomly moving folks',

92 fr, to, num = orm.update()

93 print 'from one group (%d) to another (%d)' % (fr, to)

94 print '\t(%d users moved)' % num

95 orm.dbDump()

96

97 print '\n*** Randomly choosing group',

98 rm, num = orm.delete()

99 print '(%d) to delete' % rm

100 print '\t(%d users removed)' % num

101 orm.dbDump()

102

103 print '\n*** Dropping users table'

104 orm.drop()

105 orm.finish()

106

107 if __name__ == '__main__':

108 main()

Lines 82–84

最后一个方法是 finish, 它来提交整个事务.

第86-114 行

main() 函数是整个应用程序的入口, 它创建了一个 MySQLAlchemy 对象并通过它完成所有的数据库操作. 这段脚本和 ushuffle_db.py 功能一样. 你会注意到数据库参数 db 是可选的,而且在 ushuffle_sa.py 和即将碰到 的ushuffle_so.py 中, 它不起任何作用. 它只是一个占位符以方便你对这个应用程序添加其它的数据库支持.(参见本章后面的习题)

运行这段脚本, 你会看到类似下面的输出:

$ ushuffle_sa.py

*** Connecting to 'test' database

*** Creating users table

*** Inserting names into table

逐行解释

第1-10 行

除了我们使用的是 SQLObject 而不是 SQLAlchemy 以外, 导入模块和常量声明几乎与ushuffle_sa.py 相同.

12-42 行

类似我们的SQLAlchemy 例子, 类的构造器做大量工作以确保有一个数据库可用, 然后返回一个连接. 同样的, 这也是你能在程序里看到SQL 语句的唯一位置. 我们这个程序, 如果因为某种原因造成SQLObject 无法成功创建用户表, 就会陷入无限循环当中.

我们尝试能够聪明的处理错误, 解决掉这个重建表的问题. 因为 SQLObject 使用元类, 我们知道类的创建幕后发生特殊事件, 所以我们不得不定义两个不同的类, 一个用于表已经存在的情况,

一个用于表不存的情况. 代码工作原理如下:

1. 尝试建立一个连接到一个已经存在的表. 如果正常工作, OK. (第23-29 行)

2.如果第一步不成功, 则从零开始为这个表创建一个类, 如果成功, OK. (第31-36 行)

3. 如果第二步仍不成功, 我们的数据库可能遇到麻烦, 那就重新创建一个新的数据库(第

37-40 行)

4. 重新开始新的循环.

希望程序最终能在第一步或第二步成功完成. 当循环结束时, 类似ushuffle_sa.py, 我们得到合适的对象实例.

第44-67 行, 77-78 行

这些行处理数据库操作. 我们在 44-47 行创建了表, 并在77 行删掉了表. 在49-52 行插入数据,在54-60 行更新数据, 在62-67 行删除了数据. 78 行调用了finish()方法来关闭数据库连接. 我们不能象SQLAlchemy 那样使用删表代理, 因为SQLObject 的删表代理名为 dropTable()而不是drop().

第69-75 行

使用dbDump()方法, 我们从数据库中得到数据, 并将它显示在屏幕上.

第80-108 行

又到了 main() 函数. 它工作的方式非常类似 ushuffle_sa.py . 同样的, 构造器的 db 参数仅仅是一个占位符, 用以支持其它的数据库系统(参阅本章最后的习题)

当你运行这段脚本时, 你的输出可能类似这样:

21.3.4 总结

关于如何在python 中使用关系型数据库, 希望我们前面介绍的东西对你有用. 当你应用程序的需求超出纯文本或类似DBM 等特殊文件的能力时, 有多种数据库可以选择, 别忘了还有一个完全由Python 实现的真正的免安装维护和管理的真实数据库系统. 你能在下面找到多种Python 数据库接口程序和 ORM 系统. 我们也建议你研究一下互联网上的 DB-SIG 的网页和邮件列表. 类似其它的软件开发领域, 只不过 Python 更简单易学, 用户体验更好.