数据库

1. python数据库编程接口（API）
  • 支持SQL的数据库很多，多数在python中都有对应的客户端
  • 为了在不同模块间兼容，python定义标准的数据库编程接口（DB API），目前API版本为2.0
  • 任何支持2.0版本的DB API的数据库模块都必须定义3个描述模块特性的全局变量

变量名

用途

apilevel

所使用的DB API版本

threadsafety

模块的线程安全等级

paramstyle

在SQL中使用的参数风格

  • API级别是个字符串常量，提供正在使用的API变本号，值可能是’1.0’或’2.0’，如果不存在则不兼容2.0 API。
  • 线程安全等级（threadsafety）是取值范围0~3的整数，0表示线程完全不共享模块，3表示模块完全是线程安全的，1表示线程本身可以共享模块，但不对连接共享。
  • 参数风格（paramstyle）表示SQL中参数如何拼接，值’format’表示标准的字符串格式化，可以在参数拼接地方插入%s，pyformat表示扩展的格式代码，用于字典拼接如%(foo)，除了python风格还有三种结合方式，'qmark’表示使用问号，'numeric’表示使用:1或:2格式字段，'named’表示:foobar这样的字段，其中foobar为参数名。
  • 异常
    API中定义了一些异常类

异常类

超类

描述

StandardError

所有异常的泛型基类

Warning

StandardError

在非致命错误发生时引发

Error

StandardError

所有错误条件的泛型基类

InterfaceError

Error

关于接口而非数据库的错误

DatabaseError

Error

与数据库相关的错误的基类

DataError

DatabaseError

与数据相关的问题，比如值超出范围

OperationError

DatabaseError

数据库内部操作错误

IntegrityError

DatabaseError

关系完整性受到影响，比如键检查失败

InternalError

DatabaseError

数据库内部错误，比如非法游标

PragrammingError

DatabaseError

用户编程错误，比如未找到表

NotSupportError

DatabaseError

请求不支持的特性，比如回滚

  • 连接
  • 使用数据库必须先连接到数据库，使用connect函数，connect函数有多个参数，具体使用哪些参数取决底层数据库。
connect函数参数

参数名

描述

是否可选

dsn

数据源名称，给出该参数表示数据库依赖

否

user

用户名

是

password

密码

是

host

主机名

是

database

数据库名

是

  • connect函数返回一个连接对象，表示和数据库的会话
连接对象的方法

方法名

描述

close()

关闭连接，连接对象关闭后对象和它的游标都不可用

commit()

如果支持的话就提交挂起的事务，否则不在任何事

rollback()

回归挂起的事务（可能不可用）

cursor()

返回连接对象的游标

rollback方法可能不可用，因为不是所有数据库都支持事务

commit方法总是可用，如果数据库不支持事务则没有任何作用，如果关闭了连接但是还有未提交的事务会隐式的回滚

  • 游标
  • cursor方法返回游标对象，通过游标执行SQL并检查结果
游标对象的方法

名称

描述

callproc(name[, params])

使用给定的名称和参数调用已命名的数据库过程

close()

关闭游标，关闭后游标不可用

execute(oper[, params])

执行一个SQL操作，可能带有参数

executemany(oper[, pseq])

对序列中的每个参数集执行SQL操作

fetchone()

把查询的结果集中的下一行保存为序列，或者None

fetchmany([size])

获取查询结果集中的多行，默认尺寸为arraysize

fetchall()

将所有的行作为序列的序列

nextset()

调至下一个结果集

setinputsizes(sizes)

为参数预先定义内存区域

setoutputsize(size[, col])

为获取的大数值设定缓冲区尺寸

游标对象的特性

名称

描述

description

结果列的描述序列，只读

roucount

结果中的行数，只读

arraysize

fetchmany中返回的行数，默认为1

  • 类型
  • 为了能与底层SQL数据库更好的进行交互，DP API定义了用于特殊类型和值的构造函数和常量，数据库连接模块可以自动进行类型转换

名称

描述

Date(year, month, day)

创建保存日期的对象

Time(hour, minute, second)

创建保存时间值的对象

Timestamp(y, mon, d, h, min, s)

创建保存时间戳值的对象

DateFromTicks(ticks)

创建保存自新纪元年以来秒数的对象

TimeFromTicks(ticks)

创建保存来自秒数的时间值的对象

TimestampFromTicks(ticks)

创建保存来自秒数的时间戳值的对象

Binary(string)

创建保存二进制字符串的对象

STRING

描述基于字符串的列类型（比如CHAR）

BINARY

描述二进制列（比如LONG或RAW）

NUMBER

描述数字列

DATETIME

描述日期/时间列

ROWID

描述行ID列

2. python DB API使用
  1. 使用connect函数连接数据库，返回连接对象conn
  2. 使用连接对象cursor方法获取游标对象curs
  3. 使用游标对象的execute或executemany等方法执行SQL语句
  4. 查询的话使用游标对象curs的fetchone、fetchmany或fetchall等方法获取查询的数据
  5. 使用连接对象commit方法提交修改
  6. 使用连接对象的close方法关闭连接
  • 示例
#使用USDA的营养数据实验室提供的数据ABBERV.txt文件创建数据库
>>> def convert(value):
	if value.startswith('~'):
		return value.strip('~')
	if not value:
		return 'None'
	return float(value)

>>> conn = sqlite3.connect(r'd:\python_data\somedatabase.db')
>>> curs = conn.cursor()
>>> curs.execute('''
create table food (
id TEXT PRIMARY KEY,
desc TEXT,
water FLOAT,
kcal FLOAT,
protein FLOAT,
fat FLOAT,
ash FLOAT,
carbs FLOAT,
fiber FLOAT,
sugar FLOAT
)
''')
<sqlite3.Cursor object at 0x031F0060>
>>> query = 'insert into food values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
>>> for line in open(r'd:\python_data\ABBREV.txt'):
	fields = line.split('^')
	vals = [convert(f) for f in fields[:10]]
	curs.execute(query, vals)

	
<sqlite3.Cursor object at 0x031F0060>
<sqlite3.Cursor object at 0x031F0060>
>>> conn.commit()
>>> conn.close()
>>> conn = sqlite3.connect(r'd:\python_data\somedatabase.db')
>>> curs = conn.cursor()
>>> rlt = curs.execute('select * from food')
>>> rlt.fetchall()
[(u'01079', u'MILK,RED FAT,FLUID,2% MILKFAT,W/ ADDED VIT A & VITAMIN D', 89.21, 50.0, 3.3, 1.98, 0.71, 4.8, 0.0, 5.06), (u'01174', u'MILK,RED FAT,FLUID,2% MILKFAT,WO/ ADDED VIT A & VIT D', 89.21, 50.0, 3.3, 1.98, 0.71, 4.8, 0.0, 5.06)]
>>> print rlt.fetchall()
[]
>>> curs.fetchall()
[]
>>> rlt
<sqlite3.Cursor object at 0x02700CA0>
>>> rlt is curs
True
>>>