[Python]第十三章 数据库支持

13.1Python数据库API

所有数据库的大多数基本功能都相同，因此从理论上说，对于使用其中一种数据库的程序，很容易对其进行修改以使用另一种数据库。问题是即便不同模块提供的功能大致相同，它们的接口（API）也是不同的。为解决Python数据库模块存在的这种问题，人们一致同意开发一个标准数据库API（DB API）

13.1.1全局变量

所有与DB API2.0兼容的数据库模块都必须包含三个全局变量，它们描述了模块的特征。通过检查这些变量，看看给定的模块是否是程序能够接受的。如果不是，就显示合适的错误消息并退出或者引发异常。
Python DB API的模块属性

变 量 名	描 述
apilevel	使用的Python DB API版本
threadsafety	模块的线程安全程度如何
paramstyle	在SQL查询中使用哪种参数风格

• API级别（apilevel）：是一个字符串常量，指出了使用的API版本。DB API 2.0指出，这个变量的值为’1.0’或’2.0’。
• 线程安全程度（threadsafety）：是一个0~3（含）的整数。
  0表示线程不能共享模块，而3表示模块是绝对线程安全的。1表示线程可共享模块本身，但不能共享连接（参见13.1.3节），而2表示线程可共享模块和连接，但不能共享游标。
• 参数风格（paramstyle）：表示当你执行多个类似的数据库查询时，如何在SQL查询中插入参数。

13.1.2异常

异 常	超 类	描 述
tandardError		所有异常的超类
arning	StandardError	发生非致命问题时引发
rror	StandardError	所有错误条件的超类
nterfaceError	Error	与接口（而不是数据库）相关的错误
atabaseError	Error	与数据库相关的错误的超类
ataError	DatabaseError	与数据相关的问题，如值不在合法的范围内
perationalError	DatabaseError	数据库操作内部的错误
ntegrityError	DatabaseError	关系完整性遭到破坏，如键未通过检查
nternalError	DatabaseError	数据库内部的错误，如游标无效
rogrammingError	DatabaseError	用户编程错误，如未找到数据库表
otSupportedError	DatabaseError	请求不支持的功能如回滚

13.1.3连接和游标

import sqlite3
con=sqlite3.connect('D:\Data\database.db')#连接对象
cur=con.cursor()#游标对象

要使用底层的数据库系统，必须先连接到它，为此可使用名称贴切的函数connect。这个函数接受多个参数具体是哪些取决于要使用的数据库。
函数connect的常用参数

参数名	描述	是否可选
dsn	数据源名称，具体含义随数据库而异	否
user	用户名	是
password	用户密码	是
host	主机名	是
database	数据库名称	是

函数connect返回一个连接对象
连接对像的方法

方法名	描述
close()	关闭连接对象。之后，连接对象及其游标将不可用
commit()	提交未提交的事务——如果支持的话；否则什么都不做
rollback()	回滚未提交的事务（可能不可用）
cursor()	返回连接的游标对象

游标对象的方法

名称	描述
callproc(name[, params])	使用指定的参数调用指定的数据库过程（可选)
close()	关闭游标。关闭后游标不可用
execute(oper[, params])	执行一个SQL操作——可能指定参数
executemany(oper, pseq)	执行指定的SQL操作多次，每次都序列中的一组参数
fetchone()	以序列的方式取回查询结果中的下一行；如果没有更多的行，就返回None
fetchmany([size])	取回查询结果中的多行，其中参数size的值默认为arraysize
fetchall()	以序列的序列的方式取回余下的所有行
nextset()	跳到下一个结果集，这个方法是可选的
setinputsizes(sizes)	用于为参数预定义内存区域
setoutputsize(size[, col])	为取回大量数据而设置缓冲区长度

注意：取数的时候注意游标所在位置，例如fetchall是取游标之后的所有行。
游标对象的属性

名 称	描 述
description	由结果列描述组成的序列（只读）
rowcount	结果包含的行数（只读）
arraysize	fetchmany返回的行数，默认为1

13.1.4类型

对于插入到某些类型的列中的值，底层SQL数据库可能要求它们满足一定的条件。为了能够与底层SQL数据库正确地互操作，DB API定义了一些构造函数和常量（单例），用于提供特殊的类型和值。
每个模块都必须实现表13-7所示的构造函数和特殊值。有些模块可能没有完全遵守这一点。

名称	描述
Date(year, month, day)	创建包含日期值的对象
Time(hour, minute, second)	创建包含时间值的对象
Timestamp(y, mon, d, h, min, s)	创建包含时间戳的对象
DateFromTicks(ticks)	根据从新纪元开始过去的秒数创建包含日期值的对象
TimeFromTicks(ticks)	根据从新纪元开始过去的秒数创建包含时间值的对象
imestampFromTicks(ticks)	根据从新纪元开始过去的秒数创建包含时间戳的对象
Binary(string)	创建包含二进制字符串值的对象
STRING	描述基于字符串的列（如CHAR）
BINARY	描述二进制列（如LONG或RAW）
NUMBER	描述数字列
DATETIME	描述日期/时间列
ROWID	描述行ID列

13.2SQLite和PySQLite（以及pymysql）

SQLite小型数据库引擎。它不需要作为独立的服务器运行，且可直接使用本地文件，而不需要集中式数据库存储机制。

13.2.1起步

要使用Python标准库中的SOLite，可通过导入模块sqlite3来导入它。

import sqlite3  #导入模块
conn = sqlite3.connect('somedatabase.db') #建立连接
curs = conn.cursor() #获取游标
conn.commit() #提交修改（如果修改了数据）
conn.close() #关闭连接

为了防止没有及时关闭，可以使用上下文管理器

with sqlite3.connect('somedatabase.db') as conn:
	curs = conn.cursor() #获取游标
	conn.commit() #提交修改（如果修改了数据）

13.2.2数据库应用程序演示

目标：将文档转化成数据库

写一个数据转化文件

###importdata.py 将数据导入数据库
import sqlite3
conn=sqlite3.connect('st.db')#创建连接
curs=conn.cursor()#获得游标

#定义一个去除两边~的函数
def convert(value):
    if value.startswith('~'):
        return value.strip('~')
    if not value:#如果value是空值，则返回0
        value='0'
    return float(value)
    
#执行游标——新建表
curs.execute('''
create table stu11(
    id text primary key,
    name text,
    county test,
    age float)
''')
    
query='insert into stu11 values (?,?,?,?)'  #qmark风格使用问号来标记字段
for line in open('sql.txt'):# 逐行读取
    fields=line.split('^') #每一行的文本用^分隔成列表
    vals=[convert(f) for f in fields[:4]] #将该列表的每个元素执行convert函数后返回新的列表，取前4个元素（[:4]中4不含）
    curs.execute(query,vals)#执行游标 ——存储数据
    print(vals)
    
conn.commit()#提交连接，针对改变数据库的删除、插入等操作，查询不需要提交
conn.close()#断开连接
----------------------------------------------------------------
['07276', 'Jack', 'China', '25']
['07274', 'May', 'Japan', '21']

这样新建的st.db存储有stu11这张表，表中有上述两行数据

写一个查询文件

#query.py
import sqlite3,sys
conn=sqlite3.connect('st.db')#创建连接
curs=conn.cursor()#获得游标
query='select * from stu11 where '+sys.argv[1]#sys.argv[1]外部给与的第一个参数
print(query)
curs.execute(query)
names=[f[0] for f in curs.description]#curs.description查看表结构 f[0]得到域的名字/列名
for row in curs.fetchall():#curs.fetchall()得到执行sql语句后返回的记录
    for pair in zip(names,row):#zip缝合序列
        print('{}:{}'.format(*pair))
print()

分解：

import sqlite3,sys
conn=sqlite3.connect('st.db')#创建连接
curs=conn.cursor()#获得游标
curs.execute('select * from stu11')
curs.description#查看表结构
---------------------------------------------------------------
(('id', None, None, None, None, None, None),
 ('name', None, None, None, None, None, None),
 ('county', None, None, None, None, None, None),
 ('age', None, None, None, None, None, None))
 -------------------------------------------------------------------
curs.fetchall()#得到执行语句后返回的记录
[('07276', 'Jack', 'China', 25.0), ('07274', 'May', 'Japan', 21.0)]

#zip缝合函数
a=(('id', None, None, None, None, None, None),
 ('name', None, None, None, None, None, None),
 ('county', None, None, None, None, None, None),
 ('age', None, None, None, None, None, None))
aa=[f[0] for f in a]
b=[('07276', 'Jack', 'China', 25.0), ('07274', 'May', 'Japan', 21.0)]
for bb in b:
	print(list(zip(aa,bb)))
---------------------------------------------------
[('id', '07276'), ('name', 'Jack'), ('county', 'China'), ('age', 25.0)]
[('id', '07274'), ('name', 'May'), ('county', 'Japan'), ('age', 21.0)]

延伸：使用pymysql连接MySQL数据库
pymysql是第三方库，Python2中则使用mysqldb。 mysql-connector 是 MySQL 官方提供的驱动器。
安装命令：pip install PyMySQL

import pymysql
con=pymysql.connect('localhost','root','123456','execise')#（主机名，用户名，密码，数据库名）与本地MYSQL数据库一致
cur=con.cursor()
q='select * from student;'
cur.execute(q)
cur.fetchall()
#con.close()